-- ============================================================
-- VILLAGE FOOD MART — FULL DATABASE SCHEMA
-- Version: 1.1.0 — Fixed for MySQL 9.7
-- ============================================================

CREATE DATABASE IF NOT EXISTS village_food_mart CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE village_food_mart;

-- ============================================================
-- 1. ROLES & USERS
-- ============================================================

CREATE TABLE roles (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE stores (
    id              INT PRIMARY KEY AUTO_INCREMENT,
    name            VARCHAR(100) NOT NULL,
    address         TEXT NOT NULL,
    phone           VARCHAR(20),
    delivery_zones  JSON,
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id               INT PRIMARY KEY AUTO_INCREMENT,
    name             VARCHAR(100) NOT NULL,
    email            VARCHAR(100) UNIQUE,
    phone            VARCHAR(20) NOT NULL UNIQUE,
    password_hash    VARCHAR(255) NOT NULL,
    role_id          INT NOT NULL,
    store_id         INT NULL,
    is_active        BOOLEAN DEFAULT TRUE,
    avatar_url       VARCHAR(255) NULL,
    last_login_at    TIMESTAMP NULL DEFAULT NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id)  REFERENCES roles(id),
    FOREIGN KEY (store_id) REFERENCES stores(id)
);

CREATE TABLE permissions (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    role_id    INT NOT NULL,
    module     VARCHAR(50) NOT NULL,
    can_view   BOOLEAN DEFAULT FALSE,
    can_create BOOLEAN DEFAULT FALSE,
    can_edit   BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    UNIQUE KEY unique_role_module (role_id, module)
);

CREATE TABLE refresh_tokens (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    user_id     INT NOT NULL,
    token_hash  VARCHAR(255) NOT NULL,
    expires_at  TIMESTAMP NOT NULL,
    is_revoked  BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE staff_sessions (
    id                INT PRIMARY KEY AUTO_INCREMENT,
    user_id           INT NOT NULL,
    store_id          INT NOT NULL,
    fingerprint_token VARCHAR(255) NULL,
    device_id         VARCHAR(100) NULL,
    login_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_at         TIMESTAMP NULL DEFAULT NULL,
    is_active         BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id)  REFERENCES users(id),
    FOREIGN KEY (store_id) REFERENCES stores(id)
);

-- ============================================================
-- 2. CUSTOMERS
-- ============================================================

CREATE TABLE customers (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    name           VARCHAR(100) NOT NULL,
    email          VARCHAR(100) UNIQUE,
    phone          VARCHAR(20) NOT NULL UNIQUE,
    password_hash  VARCHAR(255) NOT NULL,
    is_verified    BOOLEAN DEFAULT FALSE,
    otp_code       VARCHAR(10) NULL,
    otp_expires_at TIMESTAMP NULL DEFAULT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE customer_addresses (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    label       VARCHAR(50) NULL,
    address     TEXT NOT NULL,
    city        VARCHAR(100) NULL,
    state       VARCHAR(100) NULL,
    is_default  BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- ============================================================
-- 3. PRODUCTS & CATEGORIES
-- ============================================================

CREATE TABLE categories (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL,
    slug       VARCHAR(100) NOT NULL UNIQUE,
    image_url  VARCHAR(255) NULL,
    is_active  BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE subcategories (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(100) NOT NULL,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    UNIQUE KEY unique_cat_slug (category_id, slug)
);

CREATE TABLE products (
    id              INT PRIMARY KEY AUTO_INCREMENT,
    master_id       VARCHAR(20) NOT NULL UNIQUE,
    name            VARCHAR(150) NOT NULL,
    brand_name      VARCHAR(100) NULL,
    category_id     INT NOT NULL,
    subcategory_id  INT NULL,
    description     TEXT NULL,
    unit_type       VARCHAR(50) NULL,
    weight          VARCHAR(50) NULL,
    nafdac_no       VARCHAR(100) NULL,
    barcode         VARCHAR(100) UNIQUE,
    cost_price      DECIMAL(10,2) NOT NULL DEFAULT 0,
    selling_price   DECIMAL(10,2) NOT NULL DEFAULT 0,
    images          JSON NULL,
    is_available    BOOLEAN DEFAULT TRUE,
    created_by      INT NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id)    REFERENCES categories(id),
    FOREIGN KEY (subcategory_id) REFERENCES subcategories(id),
    FOREIGN KEY (created_by)     REFERENCES users(id)
);

-- ============================================================
-- 4. SUPPLIERS
-- ============================================================

CREATE TABLE suppliers (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id    VARCHAR(20) NOT NULL UNIQUE,
    name           VARCHAR(100) NOT NULL,
    phone          VARCHAR(20) NOT NULL,
    address        TEXT NULL,
    bank_name      VARCHAR(100) NULL,
    account_number VARCHAR(20) NULL,
    account_name   VARCHAR(100) NULL,
    is_verified    BOOLEAN DEFAULT FALSE,
    is_active      BOOLEAN DEFAULT TRUE,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE supplier_change_requests (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id   INT NOT NULL,
    field_changed VARCHAR(50) NOT NULL,
    old_value     TEXT NULL,
    new_value     TEXT NULL,
    status        ENUM('pending','approved','rejected') DEFAULT 'pending',
    requested_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_by   INT NULL,
    reviewed_at   TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (reviewed_by) REFERENCES users(id)
);

-- ============================================================
-- 5. INVENTORY & BATCHES
-- ============================================================

CREATE TABLE product_batches (
    id                     INT PRIMARY KEY AUTO_INCREMENT,
    product_id             INT NOT NULL,
    batch_number           VARCHAR(50) NOT NULL UNIQUE,
    supplier_id            INT NULL,
    qty_received           INT NOT NULL DEFAULT 0,
    qty_remaining          INT NOT NULL DEFAULT 0,
    manufacturing_date     DATE NULL,
    expiry_date            DATE NULL,
    warehouse_arrival_date DATE NULL,
    batch_barcode          VARCHAR(100) NULL,
    status                 ENUM('active','expired','depleted','returned') DEFAULT 'active',
    created_by             INT NOT NULL,
    created_at             TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id)  REFERENCES products(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (created_by)  REFERENCES users(id)
);

CREATE TABLE warehouse_inventory (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    batch_id   INT NOT NULL,
    quantity   INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (batch_id)   REFERENCES product_batches(id),
    UNIQUE KEY unique_product_batch (product_id, batch_id)
);

CREATE TABLE store_inventory (
    id                  INT PRIMARY KEY AUTO_INCREMENT,
    store_id            INT NOT NULL,
    product_id          INT NOT NULL,
    batch_id            INT NULL,
    quantity            INT NOT NULL DEFAULT 0,
    low_stock_threshold INT DEFAULT 10,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (store_id)   REFERENCES stores(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    UNIQUE KEY unique_store_product (store_id, product_id)
);

CREATE TABLE stock_requests (
    id                  INT PRIMARY KEY AUTO_INCREMENT,
    requesting_store_id INT NOT NULL,
    product_id          INT NOT NULL,
    batch_id            INT NULL,
    qty_requested       INT NOT NULL,
    qty_approved        INT DEFAULT 0,
    status              ENUM('pending','approved','partial','rejected','received') DEFAULT 'pending',
    notes               TEXT NULL,
    requested_by        INT NOT NULL,
    approved_by         INT NULL,
    approved_at         TIMESTAMP NULL DEFAULT NULL,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requesting_store_id) REFERENCES stores(id),
    FOREIGN KEY (product_id)          REFERENCES products(id),
    FOREIGN KEY (requested_by)        REFERENCES users(id),
    FOREIGN KEY (approved_by)         REFERENCES users(id)
);

CREATE TABLE stock_transfers (
    id               INT PRIMARY KEY AUTO_INCREMENT,
    stock_request_id INT NULL,
    product_id       INT NOT NULL,
    batch_id         INT NOT NULL,
    from_location    ENUM('warehouse','store') DEFAULT 'warehouse',
    from_store_id    INT NULL,
    to_store_id      INT NOT NULL,
    qty_sent         INT NOT NULL,
    qty_confirmed    INT DEFAULT 0,
    status           ENUM('dispatched','confirmed','partial') DEFAULT 'dispatched',
    dispatched_by    INT NOT NULL,
    confirmed_by     INT NULL,
    dispatched_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    confirmed_at     TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (product_id)    REFERENCES products(id),
    FOREIGN KEY (batch_id)      REFERENCES product_batches(id),
    FOREIGN KEY (to_store_id)   REFERENCES stores(id),
    FOREIGN KEY (dispatched_by) REFERENCES users(id),
    FOREIGN KEY (confirmed_by)  REFERENCES users(id)
);

CREATE TABLE inventory_adjustments (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    store_id     INT NULL,
    product_id   INT NOT NULL,
    batch_id     INT NULL,
    type         ENUM('damage','expired','returned','manual','theft_flag') NOT NULL,
    qty_adjusted INT NOT NULL,
    reason       TEXT NOT NULL,
    adjusted_by  INT NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id)  REFERENCES products(id),
    FOREIGN KEY (adjusted_by) REFERENCES users(id)
);

-- ============================================================
-- 6. SUPPLY RECORDS
-- ============================================================

CREATE TABLE supply_records (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    master_tx_id   VARCHAR(20) NOT NULL UNIQUE,
    supplier_id    INT NOT NULL,
    status         ENUM('pending','verified','approved','rejected','paid') DEFAULT 'pending',
    total_amount   DECIMAL(10,2) DEFAULT 0,
    payment_status ENUM('unpaid','partial','paid') DEFAULT 'unpaid',
    submitted_by   INT NOT NULL,
    approved_by    INT NULL,
    approved_at    TIMESTAMP NULL DEFAULT NULL,
    notes          TEXT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id)  REFERENCES suppliers(id),
    FOREIGN KEY (submitted_by) REFERENCES users(id),
    FOREIGN KEY (approved_by)  REFERENCES users(id)
);

CREATE TABLE supply_items (
    id               INT PRIMARY KEY AUTO_INCREMENT,
    supply_record_id INT NOT NULL,
    product_id       INT NOT NULL,
    batch_id         INT NULL,
    qty_supplied     INT NOT NULL,
    cost_price       DECIMAL(10,2) NOT NULL,
    manufacturing_date DATE NULL,
    expiry_date      DATE NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supply_record_id) REFERENCES supply_records(id),
    FOREIGN KEY (product_id)       REFERENCES products(id)
);

-- ============================================================
-- 7. SALES (POS)
-- ============================================================

CREATE TABLE sales (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    receipt_id     VARCHAR(20) NOT NULL UNIQUE,
    store_id       INT NOT NULL,
    staff_id       INT NOT NULL,
    session_id     INT NULL,
    sale_type      ENUM('POS','online') DEFAULT 'POS',
    subtotal       DECIMAL(10,2) NOT NULL DEFAULT 0,
    discount       DECIMAL(10,2) DEFAULT 0,
    total          DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_method ENUM('cash','transfer','card','online') DEFAULT 'cash',
    payment_status ENUM('paid','pending','refunded') DEFAULT 'paid',
    is_voided      BOOLEAN DEFAULT FALSE,
    voided_by      INT NULL,
    void_reason    TEXT NULL,
    voided_at      TIMESTAMP NULL DEFAULT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (store_id)   REFERENCES stores(id),
    FOREIGN KEY (staff_id)   REFERENCES users(id),
    FOREIGN KEY (session_id) REFERENCES staff_sessions(id)
);

CREATE TABLE sale_items (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    sale_id    INT NOT NULL,
    product_id INT NOT NULL,
    batch_id   INT NOT NULL,
    qty        INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal   DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (sale_id)    REFERENCES sales(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (batch_id)   REFERENCES product_batches(id)
);

-- ============================================================
-- 8. ONLINE ORDERS
-- ============================================================

CREATE TABLE orders (
    id               INT PRIMARY KEY AUTO_INCREMENT,
    order_id         VARCHAR(20) NOT NULL UNIQUE,
    customer_id      INT NOT NULL,
    store_id         INT NOT NULL,
    status           ENUM('received','processing','packaging','packaged','out_for_delivery','delivered','failed','cancelled') DEFAULT 'received',
    subtotal         DECIMAL(10,2) NOT NULL DEFAULT 0,
    delivery_fee     DECIMAL(10,2) DEFAULT 0,
    total            DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_method   ENUM('transfer','opay','palmpay','paystack') DEFAULT 'transfer',
    payment_status   ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
    payment_ref      VARCHAR(100) NULL,
    dispatch_option  ENUM('own_rider','vfm_delivery') DEFAULT 'vfm_delivery',
    delivery_address TEXT NULL,
    delivery_city    VARCHAR(100) NULL,
    delivery_phone   VARCHAR(20) NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (store_id)    REFERENCES stores(id)
);

CREATE TABLE order_items (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    qty        INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal   DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE order_status_history (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    order_id   INT NOT NULL,
    status     VARCHAR(50) NOT NULL,
    note       TEXT NULL,
    updated_by INT NULL,
    timestamp  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id)   REFERENCES orders(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE order_processing (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    order_id       INT NOT NULL UNIQUE,
    sales_rep_id   INT NULL,
    accepted_at    TIMESTAMP NULL DEFAULT NULL,
    packed_by      INT NULL,
    packed_at      TIMESTAMP NULL DEFAULT NULL,
    package_status ENUM('pending','in_progress','packed','handed_over') DEFAULT 'pending',
    handover_note  TEXT NULL,
    FOREIGN KEY (order_id)     REFERENCES orders(id),
    FOREIGN KEY (sales_rep_id) REFERENCES users(id),
    FOREIGN KEY (packed_by)    REFERENCES users(id)
);

-- ============================================================
-- 9. ATTENDANCE
-- ============================================================

CREATE TABLE attendance_logs (
    id                INT PRIMARY KEY AUTO_INCREMENT,
    staff_id          INT NOT NULL,
    store_id          INT NOT NULL,
    clock_in          TIMESTAMP NULL DEFAULT NULL,
    clock_out         TIMESTAMP NULL DEFAULT NULL,
    fingerprint_token VARCHAR(255) NULL,
    work_date         DATE NOT NULL,
    total_hours       DECIMAL(5,2) NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id)  REFERENCES users(id),
    FOREIGN KEY (store_id)  REFERENCES stores(id),
    UNIQUE KEY unique_staff_date (staff_id, work_date)
);

-- ============================================================
-- 10. INTERNAL CHAT
-- ============================================================

CREATE TABLE messages (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    sender_id    INT NOT NULL,
    receiver_id  INT NOT NULL,
    content      TEXT NOT NULL,
    is_read      BOOLEAN DEFAULT FALSE,
    is_anonymous BOOLEAN DEFAULT FALSE,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id)   REFERENCES users(id),
    FOREIGN KEY (receiver_id) REFERENCES users(id)
);

-- ============================================================
-- 11. FINANCE
-- ============================================================

CREATE TABLE expenses (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    store_id     INT NULL,
    title        VARCHAR(150) NOT NULL,
    amount       DECIMAL(10,2) NOT NULL,
    category     VARCHAR(100) NULL,
    recorded_by  INT NOT NULL,
    expense_date DATE NOT NULL,
    note         TEXT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (store_id)    REFERENCES stores(id),
    FOREIGN KEY (recorded_by) REFERENCES users(id)
);

-- ============================================================
-- 12. NOTIFICATIONS
-- ============================================================

CREATE TABLE notifications (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    user_id      INT NOT NULL,
    title        VARCHAR(150) NOT NULL,
    body         TEXT NOT NULL,
    type         VARCHAR(50) NULL,
    reference_id INT NULL,
    is_read      BOOLEAN DEFAULT FALSE,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ============================================================
-- 13. AUDIT & SECURITY
-- ============================================================

CREATE TABLE audit_logs (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NULL,
    action     VARCHAR(50) NOT NULL,
    module     VARCHAR(50) NOT NULL,
    record_id  INT NULL,
    old_data   JSON NULL,
    new_data   JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    timestamp  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE deleted_records (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    deleted_by    INT NULL,
    table_name    VARCHAR(50) NOT NULL,
    record_id     INT NOT NULL,
    original_data JSON NOT NULL,
    delete_reason TEXT NULL,
    deleted_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE suspicious_activity_flags (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    store_id     INT NULL,
    type         VARCHAR(100) NOT NULL,
    description  TEXT NOT NULL,
    reference_id INT NULL,
    flagged_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_resolved  BOOLEAN DEFAULT FALSE,
    resolved_by  INT NULL,
    resolved_at  TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (store_id)    REFERENCES stores(id),
    FOREIGN KEY (resolved_by) REFERENCES users(id)
);

-- ============================================================
-- 14. SUBSCRIPTIONS
-- ============================================================

CREATE TABLE subscription_plans (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(100) NOT NULL,
    duration_days INT NOT NULL,
    price         DECIMAL(10,2) NOT NULL,
    items         JSON NULL,
    is_active     BOOLEAN DEFAULT FALSE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer_subscriptions (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    plan_id     INT NOT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,
    status      ENUM('active','paused','expired','cancelled') DEFAULT 'active',
    payment_ref VARCHAR(100) NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (plan_id)     REFERENCES subscription_plans(id)
);

-- ============================================================
-- 15. SEED — ROLES & PERMISSIONS
-- ============================================================

INSERT INTO roles (name) VALUES
('CEO'), ('Manager'), ('Staff'), ('SalesRep'), ('Rider'), ('Customer');

INSERT INTO permissions (role_id, module, can_view, can_create, can_edit, can_delete)
SELECT 1, module, TRUE, TRUE, TRUE, TRUE FROM (
    SELECT 'products' AS module UNION SELECT 'inventory' UNION SELECT 'sales'
    UNION SELECT 'orders' UNION SELECT 'staff' UNION SELECT 'suppliers'
    UNION SELECT 'reports' UNION SELECT 'finance' UNION SELECT 'audit'
    UNION SELECT 'warehouse' UNION SELECT 'stores' UNION SELECT 'customers'
    UNION SELECT 'subscriptions'
) AS modules;

INSERT INTO permissions (role_id, module, can_view, can_create, can_edit, can_delete)
SELECT 2, module, TRUE, can_create, can_edit, FALSE FROM (
    SELECT 'products' AS module, TRUE AS can_create, TRUE AS can_edit
    UNION SELECT 'inventory', TRUE, TRUE
    UNION SELECT 'sales', TRUE, FALSE
    UNION SELECT 'orders', TRUE, TRUE
    UNION SELECT 'staff', TRUE, FALSE
    UNION SELECT 'reports', FALSE, FALSE
    UNION SELECT 'finance', TRUE, TRUE
) AS modules;

INSERT INTO permissions (role_id, module, can_view, can_create, can_edit, can_delete)
VALUES
(3, 'sales',     TRUE, TRUE,  FALSE, FALSE),
(3, 'products',  TRUE, FALSE, FALSE, FALSE),
(3, 'inventory', TRUE, FALSE, FALSE, FALSE);

INSERT INTO permissions (role_id, module, can_view, can_create, can_edit, can_delete)
VALUES
(4, 'orders',   TRUE, FALSE, TRUE,  FALSE),
(4, 'products', TRUE, FALSE, FALSE, FALSE);