CREATE TABLE IF NOT EXISTS provinces (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS regions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    province_id INT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(20) NOT NULL,
    CONSTRAINT fk_regions_province FOREIGN KEY (province_id) REFERENCES provinces(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS branches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    region_id INT UNSIGNED NOT NULL,
    province_id INT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(20) NOT NULL,
    ward_code VARCHAR(50) NULL,
    municipality VARCHAR(120) NULL,
    CONSTRAINT fk_branches_region FOREIGN KEY (region_id) REFERENCES regions(id),
    CONSTRAINT fk_branches_province FOREIGN KEY (province_id) REFERENCES provinces(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(60) NOT NULL,
    branch_id INT UNSIGNED NULL,
    region_id INT UNSIGNED NULL,
    province_id INT UNSIGNED NULL,
    two_factor_channel VARCHAR(40) NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_users_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_users_region FOREIGN KEY (region_id) REFERENCES regions(id),
    CONSTRAINT fk_users_province FOREIGN KEY (province_id) REFERENCES provinces(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS numbering_sequences (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    province_id INT UNSIGNED NOT NULL,
    region_id INT UNSIGNED NOT NULL,
    branch_id INT UNSIGNED NOT NULL,
    code VARCHAR(60) NOT NULL,
    next_sequence INT UNSIGNED NOT NULL DEFAULT 1,
    UNIQUE KEY uq_numbering_sequences (province_id, region_id, branch_id),
    CONSTRAINT fk_numbering_province FOREIGN KEY (province_id) REFERENCES provinces(id),
    CONSTRAINT fk_numbering_region FOREIGN KEY (region_id) REFERENCES regions(id),
    CONSTRAINT fk_numbering_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS statement_import_batches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    imported_by INT UNSIGNED NOT NULL,
    source_name VARCHAR(190) NOT NULL,
    total_rows INT NOT NULL DEFAULT 0,
    matched_rows INT NOT NULL DEFAULT 0,
    unmatched_rows INT NOT NULL DEFAULT 0,
    imported_at DATETIME NOT NULL,
    notes TEXT NULL,
    CONSTRAINT fk_statement_batches_user FOREIGN KEY (imported_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    membership_number VARCHAR(80) NOT NULL UNIQUE,
    user_id INT UNSIGNED NULL,
    full_name VARCHAR(190) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    national_id VARCHAR(30) NOT NULL UNIQUE,
    phone VARCHAR(40) NULL,
    address_line VARCHAR(255) NULL,
    city VARCHAR(120) NULL,
    province_id INT UNSIGNED NOT NULL,
    region_id INT UNSIGNED NOT NULL,
    branch_id INT UNSIGNED NOT NULL,
    status_note TEXT NULL,
    profile_photo VARCHAR(255) NULL,
    membership_status VARCHAR(40) NOT NULL,
    payment_status VARCHAR(40) NOT NULL,
    renewal_due_at DATE NOT NULL,
    elected_role VARCHAR(120) NULL,
    barcode_value VARCHAR(120) NOT NULL,
    registered_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_members_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_members_province FOREIGN KEY (province_id) REFERENCES provinces(id),
    CONSTRAINT fk_members_region FOREIGN KEY (region_id) REFERENCES regions(id),
    CONSTRAINT fk_members_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_records (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT UNSIGNED NOT NULL,
    type VARCHAR(40) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    reference_number VARCHAR(120) NOT NULL,
    status VARCHAR(40) NOT NULL,
    imported_statement TINYINT(1) NOT NULL DEFAULT 0,
    statement_batch_id INT UNSIGNED NULL,
    reconciliation_status VARCHAR(40) NOT NULL DEFAULT 'unreconciled',
    payment_method VARCHAR(40) NULL,
    notes TEXT NULL,
    paid_at DATE NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_payment_member FOREIGN KEY (member_id) REFERENCES members(id),
    CONSTRAINT fk_payment_batch FOREIGN KEY (statement_batch_id) REFERENCES statement_import_batches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS news_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(190) NOT NULL,
    body TEXT NOT NULL,
    scope_level VARCHAR(40) NOT NULL,
    scope_reference INT NULL,
    status VARCHAR(40) NOT NULL,
    submitted_by INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    wordpress_status VARCHAR(60) NOT NULL,
    published_at DATETIME NOT NULL,
    CONSTRAINT fk_news_submitted_by FOREIGN KEY (submitted_by) REFERENCES users(id),
    CONSTRAINT fk_news_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(190) NOT NULL,
    description TEXT NOT NULL,
    scope_level VARCHAR(40) NOT NULL,
    scope_reference INT NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    location VARCHAR(190) NOT NULL,
    wordpress_status VARCHAR(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(190) NOT NULL,
    body TEXT NOT NULL,
    audience_type VARCHAR(60) NOT NULL,
    audience_reference INT NULL,
    channel VARCHAR(40) NOT NULL,
    created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_user_id INT UNSIGNED NULL,
    event_type VARCHAR(80) NOT NULL,
    message TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;