-- ============================================================
--  Buy Local Lowveld — Phase 2b schema additions
-- ============================================================
--
--  ADDITIVE migration on top of schema.sql (Phase 2a).
--  Safe to run against a populated Phase 2a database.
--
-- ============================================================

SET NAMES utf8mb4;

-- ------------------------------------------------------------
-- Payments log
-- ------------------------------------------------------------
-- One row per ITN received from PayFast. Gives us full audit +
-- idempotency (pf_payment_id is unique so replays are no-ops).
DROP TABLE IF EXISTS payments;
CREATE TABLE payments (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id         INT UNSIGNED DEFAULT NULL,
    invoice_id        INT UNSIGNED DEFAULT NULL,
    order_id          INT UNSIGNED DEFAULT NULL,
    gateway           VARCHAR(40)  NOT NULL DEFAULT 'payfast',
    pf_payment_id     VARCHAR(100) DEFAULT NULL,
    pf_token          VARCHAR(100) DEFAULT NULL,
    payment_status    VARCHAR(40)  DEFAULT NULL,            -- COMPLETE / CANCELLED / FAILED
    amount_gross_cents INT         NOT NULL,
    amount_fee_cents   INT         DEFAULT 0,
    amount_net_cents   INT         DEFAULT 0,
    m_payment_id      VARCHAR(100) DEFAULT NULL,            -- our side reference
    raw_payload       MEDIUMTEXT   DEFAULT NULL,            -- full ITN post for debugging
    signature_ok      TINYINT(1)   NOT NULL DEFAULT 0,
    ip_ok             TINYINT(1)   NOT NULL DEFAULT 0,
    processed         TINYINT(1)   NOT NULL DEFAULT 0,
    created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_payments_pfid (pf_payment_id),
    KEY idx_payments_invoice (invoice_id),
    KEY idx_payments_member  (member_id),
    CONSTRAINT fk_payments_member  FOREIGN KEY (member_id)  REFERENCES members(id)  ON DELETE SET NULL,
    CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    CONSTRAINT fk_payments_order   FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Subscription tokens (PayFast "tokenisation" for recurring)
-- ------------------------------------------------------------
DROP TABLE IF EXISTS payment_tokens;
CREATE TABLE payment_tokens (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id      INT UNSIGNED NOT NULL,
    gateway        VARCHAR(40)  NOT NULL DEFAULT 'payfast',
    token          VARCHAR(100) NOT NULL,                   -- PayFast token UUID
    purpose        ENUM('membership','general') NOT NULL DEFAULT 'membership',
    status         ENUM('active','cancelled','failed')      NOT NULL DEFAULT 'active',
    next_charge_at DATE         DEFAULT NULL,
    last_charge_at DATETIME     DEFAULT NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cancelled_at   DATETIME     DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tokens_token (token),
    KEY idx_tokens_member (member_id),
    CONSTRAINT fk_tokens_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Cron run log
-- ------------------------------------------------------------
-- Every invocation of a cron script writes one row here.
-- Admins can see when each job last ran and how many rows it touched.
DROP TABLE IF EXISTS cron_runs;
CREATE TABLE cron_runs (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_name    VARCHAR(80)  NOT NULL,
    started_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at DATETIME     DEFAULT NULL,
    rows_processed INT       DEFAULT 0,
    outcome     ENUM('running','ok','partial','failed') NOT NULL DEFAULT 'running',
    detail      TEXT         DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_cron_job (job_name),
    KEY idx_cron_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Rate limit buckets
-- ------------------------------------------------------------
-- Simple counter per (key, window). The window is a rounded time
-- like "2026-04-21 14:00". `key_hash` is SHA-256(purpose + ip) so
-- we don't store raw IPs in plaintext.
DROP TABLE IF EXISTS rate_limits;
CREATE TABLE rate_limits (
    key_hash    CHAR(64)     NOT NULL,
    window_key  VARCHAR(40)  NOT NULL,          -- e.g. "login:2026-04-21 14:00"
    hits        INT UNSIGNED NOT NULL DEFAULT 1,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (key_hash, window_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;