-- ============================================================
--  NBA UNGOGO — v2 MIGRATION (run AFTER database_fixed.sql)
--  Adds: payments, receipts, applications, membership_cards,
--        notifications, resources
-- ============================================================

USE nba_ungogo;

-- ── PAYMENT CATEGORIES ────────────────────────────────────
CREATE TABLE IF NOT EXISTS payment_categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(120)    NOT NULL,
    description TEXT            NULL,
    amount      DECIMAL(12,2)   NULL,  -- NULL = calculated (e.g. BPF)
    is_active   TINYINT(1)      NOT NULL DEFAULT 1,
    sort_order  INT             NOT NULL DEFAULT 0,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── PAYMENTS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payments (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED    NOT NULL,
    category_id         INT UNSIGNED    NULL,
    reference           VARCHAR(80)     NOT NULL UNIQUE,  -- PAY-XXXX
    paystack_reference  VARCHAR(120)    NULL UNIQUE,      -- from Paystack
    description         VARCHAR(255)    NOT NULL,
    gross_amount        DECIMAL(12,2)   NOT NULL,
    gateway_fee         DECIMAL(12,2)   NOT NULL DEFAULT 0,
    platform_commission DECIMAL(12,2)   NOT NULL DEFAULT 0,
    net_amount          DECIMAL(12,2)   NOT NULL,
    currency            CHAR(3)         NOT NULL DEFAULT 'NGN',
    status              ENUM('pending','success','failed','abandoned') NOT NULL DEFAULT 'pending',
    channel             VARCHAR(40)     NULL,   -- card, bank_transfer, ussd …
    paid_at             DATETIME        NULL,
    metadata            JSON            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user      (user_id),
    INDEX idx_status    (status),
    INDEX idx_ref       (reference)
) ENGINE=InnoDB;

-- ── RECEIPTS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS receipts (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id      INT UNSIGNED    NOT NULL UNIQUE,
    receipt_number  VARCHAR(40)     NOT NULL UNIQUE,  -- RCP-YYYYMMDD-XXXX
    issued_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    qr_token        VARCHAR(64)     NOT NULL UNIQUE,  -- for public verify URL
    INDEX idx_payment   (payment_id),
    INDEX idx_qr_token  (qr_token)
) ENGINE=InnoDB;

-- ── MEMBERSHIP CARDS ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS membership_cards (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED    NOT NULL UNIQUE,
    card_number     VARCHAR(40)     NOT NULL UNIQUE,   -- NBA-UNG-YYYYXXXX
    qr_token        VARCHAR(64)     NOT NULL UNIQUE,
    valid_from      DATE            NOT NULL,
    valid_until     DATE            NOT NULL,
    issued_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    issued_by       INT UNSIGNED    NULL,   -- admin user_id
    revoked_at      DATETIME        NULL,
    INDEX idx_user      (user_id),
    INDEX idx_qr_token  (qr_token)
) ENGINE=InnoDB;

-- ── APPLICATIONS ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS applications (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED    NOT NULL,
    type            ENUM('good_standing','id_card','stamp_seal','introduction','other') NOT NULL,
    reference       VARCHAR(40)     NOT NULL UNIQUE,   -- APP-YYYYMMDD-XXXX
    status          ENUM('submitted','under_review','query','resubmitted','approved','rejected','ready','completed') NOT NULL DEFAULT 'submitted',
    subject         VARCHAR(255)    NULL,
    details         TEXT            NULL,
    payment_id      INT UNSIGNED    NULL,   -- if fee was required
    admin_notes     TEXT            NULL,
    reviewed_by     INT UNSIGNED    NULL,
    reviewed_at     DATETIME        NULL,
    completed_at    DATETIME        NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user      (user_id),
    INDEX idx_type      (type),
    INDEX idx_status    (status)
) ENGINE=InnoDB;

-- ── APPLICATION DOCUMENTS ─────────────────────────────────
CREATE TABLE IF NOT EXISTS application_documents (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    application_id  INT UNSIGNED    NOT NULL,
    filename        VARCHAR(255)    NOT NULL,  -- stored filename on disk
    original_name   VARCHAR(255)    NOT NULL,
    file_type       VARCHAR(80)     NULL,
    file_size       INT UNSIGNED    NULL,
    uploaded_by     INT UNSIGNED    NOT NULL,
    label           VARCHAR(120)    NULL,      -- e.g. "Passport Photograph"
    uploaded_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_application (application_id)
) ENGINE=InnoDB;

-- ── NOTIFICATIONS ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NOT NULL,
    title       VARCHAR(160)    NOT NULL,
    body        TEXT            NOT NULL,
    icon        VARCHAR(40)     NOT NULL DEFAULT 'bell',
    link        VARCHAR(255)    NULL,
    is_read     TINYINT(1)      NOT NULL DEFAULT 0,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user      (user_id),
    INDEX idx_is_read   (user_id, is_read)
) ENGINE=InnoDB;

-- ── SEED: PAYMENT CATEGORIES ──────────────────────────────
INSERT IGNORE INTO payment_categories (id, name, description, amount, sort_order) VALUES
(1, 'Branch Annual Dues',  'Annual branch subscription. Amount set by the branch executive.', 15000.00, 1),
(2, 'Welfare Dues',        'Mandatory welfare levy for all financial members.', 5000.00, 2),
(3, 'BPF (Bar Practising Fee)', 'Bar Practising Fee — amount determined by years post-call.', NULL, 3),
(4, 'Insurance',           'Group insurance levy.', NULL, 4),
(5, 'Law Week',            'Law Week contribution.', NULL, 5),
(6, 'Annual Dinner',       'Annual Dinner ticket/contribution.', NULL, 6),
(7, 'Training / Seminar',  'Branch training or seminar fee.', NULL, 7),
(8, 'Other',               'Other approved branch payments.', NULL, 8);