-- ============================================================
--  Buy Local Lowveld — Phase 2a schema
--  MySQL / MariaDB
-- ============================================================
--
--  Safe to run against an empty database.
--  Run db/seed.php afterwards to import the Phase 1 dummy data.
--
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- Members (auth + profile) --------------------------
DROP TABLE IF EXISTS members;
CREATE TABLE members (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email           VARCHAR(255) NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    phone           VARCHAR(30)  DEFAULT NULL,
    business_name   VARCHAR(255) NOT NULL,
    industry        VARCHAR(100) DEFAULT NULL,       -- the slug from categories
    tier            ENUM('Bronze','Silver','Gold','Platinum','Diamond') DEFAULT 'Bronze',
    role            ENUM('member','admin')           NOT NULL DEFAULT 'member',
    status          ENUM('pending','active','suspended','cancelled') NOT NULL DEFAULT 'pending',
    join_date       DATE         DEFAULT NULL,
    renewal_date    DATE         DEFAULT NULL,
    banking_details TEXT         DEFAULT NULL,       -- free text for Phase 2a
    last_login_at   DATETIME     DEFAULT NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_members_email (email),
    KEY idx_members_renewal (renewal_date),
    KEY idx_members_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Categories ---------------------------------------
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug        VARCHAR(80)  NOT NULL,
    name        VARCHAR(120) NOT NULL,
    icon        VARCHAR(8)   DEFAULT NULL,           -- emoji
    sort_order  INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Listings (the public directory entries) ---------
DROP TABLE IF EXISTS listings;
CREATE TABLE listings (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id     INT UNSIGNED DEFAULT NULL,         -- null = legacy / unclaimed
    slug          VARCHAR(160) NOT NULL,
    name          VARCHAR(255) NOT NULL,
    category_slug VARCHAR(80)  NOT NULL,
    tier          ENUM('Bronze','Silver','Gold','Platinum','Diamond') DEFAULT 'Bronze',
    featured      TINYINT(1)   NOT NULL DEFAULT 0,
    description   TEXT         NOT NULL,
    phone         VARCHAR(30)  DEFAULT NULL,
    email         VARCHAR(255) DEFAULT NULL,
    website       VARCHAR(255) DEFAULT NULL,
    address       VARCHAR(255) DEFAULT NULL,
    published     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_listings_slug (slug),
    KEY idx_listings_member (member_id),
    KEY idx_listings_cat    (category_slug),
    CONSTRAINT fk_listings_member  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Blog posts ---------------------------------------
DROP TABLE IF EXISTS blog_posts;
CREATE TABLE blog_posts (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug        VARCHAR(160) NOT NULL,
    title       VARCHAR(255) NOT NULL,
    excerpt     TEXT         DEFAULT NULL,
    body        MEDIUMTEXT   NOT NULL,
    published   TINYINT(1)   NOT NULL DEFAULT 1,
    posted_at   DATE         NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_blog_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Branding items (shop catalogue) ------------------
DROP TABLE IF EXISTS branding_items;
CREATE TABLE branding_items (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug          VARCHAR(80)  NOT NULL,
    name          VARCHAR(160) NOT NULL,
    price_cents   INT          NOT NULL,                -- store in cents to avoid float
    price_display VARCHAR(40)  NOT NULL,                -- "R 150" or "From R 2 500"
    description   TEXT         NOT NULL,
    active        TINYINT(1)   NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_branding_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Orders (a cart, possibly checked out) -----------
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id    INT UNSIGNED NOT NULL,
    status       ENUM('cart','pending_payment','paid','cancelled','refunded') NOT NULL DEFAULT 'cart',
    subtotal_cents INT        NOT NULL DEFAULT 0,
    total_cents    INT        NOT NULL DEFAULT 0,
    notes        TEXT         DEFAULT NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    paid_at      DATETIME     DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_orders_member (member_id),
    KEY idx_orders_status (status),
    CONSTRAINT fk_orders_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Order items --------------------------------------
DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id         INT UNSIGNED NOT NULL,
    branding_item_id INT UNSIGNED NOT NULL,
    quantity         INT          NOT NULL DEFAULT 1,
    unit_price_cents INT          NOT NULL,
    line_total_cents INT          NOT NULL,
    PRIMARY KEY (id),
    KEY idx_oi_order (order_id),
    CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_oi_item  FOREIGN KEY (branding_item_id) REFERENCES branding_items(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Invoices -----------------------------------------
-- Membership invoices are generated on signup / renewal.
-- Order invoices are generated when an order is checked out.
DROP TABLE IF EXISTS invoices;
CREATE TABLE invoices (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id    INT UNSIGNED NOT NULL,
    type         ENUM('membership','order') NOT NULL,
    order_id     INT UNSIGNED DEFAULT NULL,
    number       VARCHAR(40)  NOT NULL,                 -- e.g. INV-2026-0001
    description  VARCHAR(255) NOT NULL,
    amount_cents INT          NOT NULL,
    status       ENUM('unpaid','paid','overdue','cancelled') NOT NULL DEFAULT 'unpaid',
    issued_at    DATE         NOT NULL,
    due_at       DATE         DEFAULT NULL,
    paid_at      DATETIME     DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_invoice_number (number),
    KEY idx_invoice_member (member_id),
    KEY idx_invoice_status (status),
    CONSTRAINT fk_invoice_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    CONSTRAINT fk_invoice_order  FOREIGN KEY (order_id)  REFERENCES orders(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Transactions (ledger) ----------------------------
DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id    INT UNSIGNED NOT NULL,
    invoice_id   INT UNSIGNED DEFAULT NULL,
    type         ENUM('charge','payment','refund','adjustment') NOT NULL,
    amount_cents INT          NOT NULL,                 -- positive = debit (charge), negative = credit (payment)
    description  VARCHAR(255) NOT NULL,
    reference    VARCHAR(100) DEFAULT NULL,             -- gateway ref / bank ref
    occurred_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_tx_member     (member_id),
    KEY idx_tx_occurred   (occurred_at),
    CONSTRAINT fk_tx_member  FOREIGN KEY (member_id)  REFERENCES members(id)  ON DELETE CASCADE,
    CONSTRAINT fk_tx_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Password reset tokens ----------------------------
DROP TABLE IF EXISTS password_resets;
CREATE TABLE password_resets (
    token       CHAR(64)     NOT NULL,                  -- hex sha256 of the emailed token
    member_id   INT UNSIGNED NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  DATETIME     NOT NULL,
    used_at     DATETIME     DEFAULT NULL,
    PRIMARY KEY (token),
    KEY idx_pwr_member (member_id),
    CONSTRAINT fk_pwr_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Business claim requests --------------------------
DROP TABLE IF EXISTS business_claims;
CREATE TABLE business_claims (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    listing_id  INT UNSIGNED NOT NULL,
    email       VARCHAR(255) NOT NULL,
    first_name  VARCHAR(100) NOT NULL,
    last_name   VARCHAR(100) NOT NULL,
    phone       VARCHAR(30)  DEFAULT NULL,
    proof       TEXT         DEFAULT NULL,              -- free text / URL
    status      ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_at DATETIME     DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_claim_status (status),
    CONSTRAINT fk_claim_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Mailchimp sync log (audit trail) -----------------
DROP TABLE IF EXISTS mailchimp_sync_log;
CREATE TABLE mailchimp_sync_log (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id  INT UNSIGNED DEFAULT NULL,
    email      VARCHAR(255) DEFAULT NULL,
    event      VARCHAR(80)  NOT NULL,                   -- 'signup', 'profile_update', 'tier_change', etc.
    success    TINYINT(1)   NOT NULL,
    detail     TEXT         DEFAULT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mc_member  (member_id),
    KEY idx_mc_event   (event),
    KEY idx_mc_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;