CREATE TABLE IF NOT EXISTS provinces (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    code TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS regions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    province_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    code TEXT NOT NULL,
    FOREIGN KEY (province_id) REFERENCES provinces(id)
);

CREATE TABLE IF NOT EXISTS branches (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    region_id INTEGER NOT NULL,
    province_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    code TEXT NOT NULL,
    ward_code TEXT,
    municipality TEXT,
    FOREIGN KEY (region_id) REFERENCES regions(id),
    FOREIGN KEY (province_id) REFERENCES provinces(id)
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL,
    branch_id INTEGER,
    region_id INTEGER,
    province_id INTEGER,
    two_factor_channel TEXT,
    created_at TEXT NOT NULL,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (region_id) REFERENCES regions(id),
    FOREIGN KEY (province_id) REFERENCES provinces(id)
);

CREATE TABLE IF NOT EXISTS numbering_sequences (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    province_id INTEGER NOT NULL,
    region_id INTEGER NOT NULL,
    branch_id INTEGER NOT NULL,
    code TEXT NOT NULL,
    next_sequence INTEGER NOT NULL DEFAULT 1,
    UNIQUE (province_id, region_id, branch_id),
    FOREIGN KEY (province_id) REFERENCES provinces(id),
    FOREIGN KEY (region_id) REFERENCES regions(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id)
);

CREATE TABLE IF NOT EXISTS members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    membership_number TEXT NOT NULL UNIQUE,
    user_id INTEGER,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    national_id TEXT NOT NULL UNIQUE,
    phone TEXT,
    address_line TEXT,
    city TEXT,
    province_id INTEGER NOT NULL,
    region_id INTEGER NOT NULL,
    branch_id INTEGER NOT NULL,
    status_note TEXT,
    profile_photo TEXT,
    membership_status TEXT NOT NULL,
    payment_status TEXT NOT NULL,
    renewal_due_at TEXT NOT NULL,
    elected_role TEXT,
    barcode_value TEXT NOT NULL,
    registered_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (province_id) REFERENCES provinces(id),
    FOREIGN KEY (region_id) REFERENCES regions(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id)
);

CREATE TABLE IF NOT EXISTS payment_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_id INTEGER NOT NULL,
    type TEXT NOT NULL,
    amount REAL NOT NULL,
    reference_number TEXT NOT NULL,
    status TEXT NOT NULL,
    imported_statement INTEGER NOT NULL DEFAULT 0,
    statement_batch_id INTEGER,
    reconciliation_status TEXT NOT NULL DEFAULT 'unreconciled',
    payment_method TEXT,
    notes TEXT,
    paid_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (statement_batch_id) REFERENCES statement_import_batches(id)
);

CREATE TABLE IF NOT EXISTS statement_import_batches (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    imported_by INTEGER NOT NULL,
    source_name TEXT NOT NULL,
    total_rows INTEGER NOT NULL DEFAULT 0,
    matched_rows INTEGER NOT NULL DEFAULT 0,
    unmatched_rows INTEGER NOT NULL DEFAULT 0,
    imported_at TEXT NOT NULL,
    notes TEXT,
    FOREIGN KEY (imported_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS news_posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    scope_level TEXT NOT NULL,
    scope_reference INTEGER,
    status TEXT NOT NULL,
    submitted_by INTEGER,
    approved_by INTEGER,
    wordpress_status TEXT NOT NULL,
    published_at TEXT NOT NULL,
    FOREIGN KEY (submitted_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    scope_level TEXT NOT NULL,
    scope_reference INTEGER,
    starts_at TEXT NOT NULL,
    ends_at TEXT NOT NULL,
    location TEXT NOT NULL,
    wordpress_status TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS notifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    audience_type TEXT NOT NULL,
    audience_reference INTEGER,
    channel TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    actor_user_id INTEGER,
    event_type TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (actor_user_id) REFERENCES users(id)
);