-- IOM Platform — Full MariaDB Schema (consolidated)
-- Includes all migrations through 2026-05-06 (competitiveness upgrade)
-- Run this file on a fresh database, then seed.sql

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

-- =============================================
-- TENANTS
-- =============================================
CREATE TABLE IF NOT EXISTS tenants (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(255) NOT NULL,
    base_currency CHAR(3)      NOT NULL DEFAULT 'AUD',
    slug          VARCHAR(100) NOT NULL UNIQUE,
    plan          ENUM('starter','pro','enterprise') DEFAULT 'starter',
    is_active     BOOLEAN DEFAULT TRUE,
    settings      JSON DEFAULT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug   (slug),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- =============================================
-- USERS
-- =============================================
CREATE TABLE IF NOT EXISTS users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NOT NULL,
    email         VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name    VARCHAR(100) NOT NULL,
    last_name     VARCHAR(100) NOT NULL,
    role          ENUM('admin','manager','staff') DEFAULT 'staff',
    is_active     BOOLEAN DEFAULT TRUE,
    last_login    TIMESTAMP NULL,
    totp_secret   VARCHAR(64) NULL,
    totp_enabled  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_email (tenant_id, email),
    INDEX idx_tenant (tenant_id),
    INDEX idx_email  (email),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- PASSWORD RESET TOKENS
-- =============================================
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    token_hash VARCHAR(64)  NOT NULL,
    expires_at TIMESTAMP    NOT NULL,
    used_at    TIMESTAMP    NULL,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_token (token_hash),
    INDEX idx_user  (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- CATEGORIES
-- =============================================
CREATE TABLE IF NOT EXISTS categories (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    name      VARCHAR(100) NOT NULL,
    parent_id INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- PRICE TIERS (B2B support)
-- =============================================
CREATE TABLE IF NOT EXISTS price_tiers (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT UNSIGNED NOT NULL,
    name             VARCHAR(100) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0.00,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- PRODUCTS
-- =============================================
CREATE TABLE IF NOT EXISTS products (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    category_id       INT UNSIGNED NULL,
    name              VARCHAR(255) NOT NULL,
    sku               VARCHAR(100) NOT NULL,
    barcode           VARCHAR(100) NULL,
    vendor            VARCHAR(255) NULL,
    tags              TEXT NULL,
    description       TEXT NULL,
    cost_price        DECIMAL(12,2) DEFAULT 0.00,
    base_price        DECIMAL(12,2) DEFAULT 0.00,
    compare_at_price  DECIMAL(12,2) NULL,
    weight            DECIMAL(8,3)  NULL,
    dimensions_length DECIMAL(8,2)  NULL,
    dimensions_width  DECIMAL(8,2)  NULL,
    dimensions_height DECIMAL(8,2)  NULL,
    unit_of_measure   VARCHAR(50)   DEFAULT 'unit',
    requires_shipping BOOLEAN NOT NULL DEFAULT TRUE,
    taxable           BOOLEAN NOT NULL DEFAULT TRUE,
    track_inventory   BOOLEAN NOT NULL DEFAULT TRUE,
    seo_title         VARCHAR(255) NULL,
    seo_description   TEXT NULL,
    is_active         BOOLEAN DEFAULT TRUE,
    tracking_type     ENUM('none','batch','serial') NOT NULL DEFAULT 'none',
    has_variants      BOOLEAN DEFAULT FALSE,
    variant_options   JSON NULL,
    image_path        VARCHAR(500) NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_sku (tenant_id, sku),
    INDEX idx_tenant   (tenant_id),
    INDEX idx_sku      (sku),
    INDEX idx_category (category_id),
    FULLTEXT INDEX ft_name_sku (name, sku),
    FOREIGN KEY (tenant_id)   REFERENCES tenants(id)    ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- PRODUCT IMAGES
-- =============================================
CREATE TABLE IF NOT EXISTS product_images (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    tenant_id  INT UNSIGNED NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    is_feature BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    alt_text   VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product (product_id),
    INDEX idx_tenant  (tenant_id),
    INDEX idx_feature (is_feature),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- PRODUCT VARIANTS
-- =============================================
CREATE TABLE IF NOT EXISTS product_variants (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    tenant_id  INT UNSIGNED NOT NULL,
    name       VARCHAR(255) NOT NULL,
    sku        VARCHAR(100) NOT NULL,
    attributes JSON NULL,
    cost_price DECIMAL(12,2) NULL,
    price      DECIMAL(12,2) NULL,
    is_active  BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_variant_sku (tenant_id, sku),
    INDEX idx_product (product_id),
    INDEX idx_tenant  (tenant_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- PRODUCT PRICE TIERS
-- =============================================
CREATE TABLE IF NOT EXISTS product_price_tiers (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id    INT UNSIGNED NOT NULL,
    price_tier_id INT UNSIGNED NOT NULL,
    tenant_id     INT UNSIGNED NOT NULL,
    price         DECIMAL(12,2) NOT NULL,
    UNIQUE KEY uq_product_tier (product_id, price_tier_id),
    FOREIGN KEY (product_id)    REFERENCES products(id)     ON DELETE CASCADE,
    FOREIGN KEY (price_tier_id) REFERENCES price_tiers(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- PRODUCT PRICES (multi-currency)
-- =============================================
CREATE TABLE IF NOT EXISTS product_prices (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    currency   CHAR(3)      NOT NULL,
    price      DECIMAL(15,4) NOT NULL,
    cost       DECIMAL(15,4) NULL,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_product_currency (product_id, currency),
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- EXCHANGE RATES
-- =============================================
CREATE TABLE IF NOT EXISTS exchange_rates (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    base_currency   CHAR(3) NOT NULL,
    currency        CHAR(3) NOT NULL,
    rate            DECIMAL(20,8) NOT NULL,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_pair (base_currency, currency)
) ENGINE=InnoDB;

-- =============================================
-- BATCHES / LOTS
-- =============================================
CREATE TABLE IF NOT EXISTS batches (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT UNSIGNED NOT NULL,
    product_id       INT UNSIGNED NOT NULL,
    batch_number     VARCHAR(100) NOT NULL,
    lot_number       VARCHAR(100) NULL,
    manufacture_date DATE NULL,
    expiry_date      DATE NULL,
    quantity         DECIMAL(10,3) NOT NULL DEFAULT 0,
    notes            TEXT NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant  (tenant_id),
    INDEX idx_product (product_id),
    INDEX idx_expiry  (expiry_date),
    UNIQUE KEY uq_tenant_product_batch (tenant_id, product_id, batch_number),
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- BATCH INVENTORY (stock per warehouse)
-- =============================================
CREATE TABLE IF NOT EXISTS batch_inventory (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT UNSIGNED NOT NULL,
    batch_id     INT UNSIGNED NOT NULL,
    warehouse_id INT UNSIGNED NOT NULL,
    quantity     DECIMAL(10,3) NOT NULL DEFAULT 0,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_batch_warehouse (batch_id, warehouse_id),
    FOREIGN KEY (tenant_id)    REFERENCES tenants(id)  ON DELETE CASCADE,
    FOREIGN KEY (batch_id)     REFERENCES batches(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- SERIAL NUMBERS
-- =============================================
CREATE TABLE IF NOT EXISTS serial_numbers (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    product_id        INT UNSIGNED NOT NULL,
    serial_number     VARCHAR(255) NOT NULL,
    warehouse_id      INT UNSIGNED NULL,
    status            ENUM('in_stock','reserved','sold','returned','damaged') DEFAULT 'in_stock',
    order_id          INT UNSIGNED NULL,
    purchase_order_id INT UNSIGNED NULL,
    notes             TEXT NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant  (tenant_id),
    INDEX idx_product (product_id),
    INDEX idx_status  (status),
    UNIQUE KEY uq_tenant_serial (tenant_id, serial_number),
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- WAREHOUSES
-- =============================================
CREATE TABLE IF NOT EXISTS warehouses (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT UNSIGNED NOT NULL,
    name       VARCHAR(255) NOT NULL,
    code       VARCHAR(50)  NOT NULL,
    address    TEXT NULL,
    city       VARCHAR(100) NULL,
    state      VARCHAR(100) NULL,
    country    VARCHAR(100) DEFAULT 'AU',
    is_active  BOOLEAN DEFAULT TRUE,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_code (tenant_id, code),
    INDEX idx_tenant (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- INVENTORY (stock levels per product per warehouse)
-- =============================================
CREATE TABLE IF NOT EXISTS inventory (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    product_id        INT UNSIGNED NOT NULL,
    variant_id        INT UNSIGNED NULL,
    warehouse_id      INT UNSIGNED NOT NULL,
    quantity          DECIMAL(10,3) DEFAULT 0,
    reserved_quantity DECIMAL(10,3) DEFAULT 0,
    reorder_level     DECIMAL(10,3) DEFAULT 0,
    reorder_quantity  DECIMAL(10,3) DEFAULT 0,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_stock (tenant_id, product_id, variant_id, warehouse_id),
    INDEX idx_tenant    (tenant_id),
    INDEX idx_product   (product_id),
    INDEX idx_warehouse (warehouse_id),
    FOREIGN KEY (tenant_id)    REFERENCES tenants(id)          ON DELETE CASCADE,
    FOREIGN KEY (product_id)   REFERENCES products(id)         ON DELETE CASCADE,
    FOREIGN KEY (variant_id)   REFERENCES product_variants(id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)       ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- INVENTORY MOVEMENTS (audit log)
-- =============================================
CREATE TABLE IF NOT EXISTS inventory_movements (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT UNSIGNED NOT NULL,
    product_id     INT UNSIGNED NOT NULL,
    variant_id     INT UNSIGNED NULL,
    warehouse_id   INT UNSIGNED NOT NULL,
    type           ENUM('receive','ship','adjust','transfer_in','transfer_out','return') NOT NULL,
    quantity       DECIMAL(10,3) NOT NULL,
    balance_after  DECIMAL(10,3) NOT NULL,
    reference_type VARCHAR(50)   NULL,
    reference_id   INT UNSIGNED  NULL,
    batch_id       INT UNSIGNED  NULL,
    serial_id      INT UNSIGNED  NULL,
    notes          TEXT NULL,
    created_by     INT UNSIGNED  NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant     (tenant_id),
    INDEX idx_product    (product_id),
    INDEX idx_warehouse  (warehouse_id),
    INDEX idx_type       (type),
    INDEX idx_created_at (created_at),
    INDEX idx_batch      (batch_id),
    INDEX idx_serial     (serial_id),
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- CUSTOMERS
-- =============================================
CREATE TABLE IF NOT EXISTS customers (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NOT NULL,
    name          VARCHAR(255) NOT NULL,
    email         VARCHAR(255) NULL,
    phone         VARCHAR(50)  NULL,
    company       VARCHAR(255) NULL,
    customer_type ENUM('retail','wholesale','b2b') DEFAULT 'retail',
    price_tier_id INT UNSIGNED NULL,
    notes         TEXT NULL,
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    INDEX idx_email  (email),
    FULLTEXT INDEX ft_name_company (name, company),
    FOREIGN KEY (tenant_id)     REFERENCES tenants(id)     ON DELETE CASCADE,
    FOREIGN KEY (price_tier_id) REFERENCES price_tiers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- CUSTOMER ADDRESSES
-- =============================================
CREATE TABLE IF NOT EXISTS customer_addresses (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT UNSIGNED NOT NULL,
    tenant_id    INT UNSIGNED NOT NULL,
    address_type ENUM('billing','shipping') DEFAULT 'shipping',
    street       VARCHAR(500) NOT NULL,
    city         VARCHAR(100) NOT NULL,
    state        VARCHAR(100) NULL,
    postcode     VARCHAR(20)  NULL,
    country      VARCHAR(100) DEFAULT 'AU',
    is_default   BOOLEAN DEFAULT FALSE,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id),
    INDEX idx_tenant   (tenant_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- SUPPLIERS
-- =============================================
CREATE TABLE IF NOT EXISTS suppliers (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT UNSIGNED NOT NULL,
    name           VARCHAR(255) NOT NULL,
    email          VARCHAR(255) NULL,
    phone          VARCHAR(50)  NULL,
    contact_person VARCHAR(255) NULL,
    company        VARCHAR(255) NULL,
    address        TEXT NULL,
    city           VARCHAR(100) NULL,
    country        VARCHAR(100) NULL,
    payment_terms  VARCHAR(100) NULL,
    notes          TEXT NULL,
    is_active      BOOLEAN DEFAULT TRUE,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- ORDERS
-- =============================================
CREATE TABLE IF NOT EXISTS orders (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT UNSIGNED NOT NULL,
    order_number    VARCHAR(50)  NOT NULL,
    customer_id     INT UNSIGNED NULL,
    status          ENUM('draft','pending','paid','fulfilling','fulfilled','shipped','delivered','cancelled','refunded') DEFAULT 'pending',
    channel         ENUM('manual','online','api','pos','b2b') DEFAULT 'manual',
    subtotal        DECIMAL(12,2) DEFAULT 0.00,
    tax_amount      DECIMAL(12,2) DEFAULT 0.00,
    shipping_amount DECIMAL(12,2) DEFAULT 0.00,
    discount_amount DECIMAL(12,2) DEFAULT 0.00,
    total_amount    DECIMAL(12,2) DEFAULT 0.00,
    cost_total      DECIMAL(12,2) DEFAULT 0.00,
    currency        VARCHAR(3)   DEFAULT 'AUD',
    shipping_address JSON NULL,
    billing_address  JSON NULL,
    notes           TEXT NULL,
    warehouse_id    INT UNSIGNED NULL,
    created_by      INT UNSIGNED NULL,
    fulfilled_at    TIMESTAMP NULL,
    shipped_at      TIMESTAMP NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_order_number (tenant_id, order_number),
    INDEX idx_tenant     (tenant_id),
    INDEX idx_status     (status),
    INDEX idx_customer   (customer_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (tenant_id)    REFERENCES tenants(id)   ON DELETE CASCADE,
    FOREIGN KEY (customer_id)  REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- ORDER ITEMS
-- =============================================
CREATE TABLE IF NOT EXISTS order_items (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id          INT UNSIGNED NOT NULL,
    tenant_id         INT UNSIGNED NOT NULL,
    product_id        INT UNSIGNED NOT NULL,
    variant_id        INT UNSIGNED NULL,
    quantity          DECIMAL(10,3) NOT NULL,
    fulfilled_quantity DECIMAL(10,3) DEFAULT 0,
    unit_price        DECIMAL(12,2) NOT NULL,
    unit_cost         DECIMAL(12,2) DEFAULT 0.00,
    total_price       DECIMAL(12,2) NOT NULL,
    discount_percent  DECIMAL(5,2)  DEFAULT 0.00,
    notes             TEXT NULL,
    INDEX idx_order   (order_id),
    INDEX idx_tenant  (tenant_id),
    INDEX idx_product (product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(id)           ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- ORDER ITEM ALLOCATIONS (warehouse execution)
-- =============================================
CREATE TABLE IF NOT EXISTS order_item_allocations (
    id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT UNSIGNED NOT NULL,
    order_id             INT UNSIGNED NOT NULL,
    order_item_id        INT UNSIGNED NOT NULL,
    product_id           INT UNSIGNED NOT NULL,
    warehouse_id         INT UNSIGNED NOT NULL,
    inventory_product_id INT UNSIGNED NOT NULL,
    batch_id             INT UNSIGNED NULL,
    serial_id            INT UNSIGNED NULL,
    step                 ENUM('receive','pick','pack','ship') NOT NULL,
    allocated_quantity   DECIMAL(10,3) NOT NULL DEFAULT 0,
    exception_type       ENUM('short_pick','damaged','substitute') NULL,
    substitute_product_id INT UNSIGNED NULL,
    scanned_code         VARCHAR(255) NULL,
    audit_note           TEXT NULL,
    created_by           INT UNSIGNED NULL,
    created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_order_step (tenant_id, order_id, step),
    INDEX idx_order_item        (order_item_id),
    INDEX idx_batch             (batch_id),
    INDEX idx_serial            (serial_id),
    FOREIGN KEY (tenant_id)             REFERENCES tenants(id)        ON DELETE CASCADE,
    FOREIGN KEY (order_id)              REFERENCES orders(id)         ON DELETE CASCADE,
    FOREIGN KEY (order_item_id)         REFERENCES order_items(id)    ON DELETE CASCADE,
    FOREIGN KEY (product_id)            REFERENCES products(id),
    FOREIGN KEY (inventory_product_id)  REFERENCES products(id),
    FOREIGN KEY (warehouse_id)          REFERENCES warehouses(id),
    FOREIGN KEY (batch_id)              REFERENCES batches(id)        ON DELETE SET NULL,
    FOREIGN KEY (serial_id)             REFERENCES serial_numbers(id) ON DELETE SET NULL,
    FOREIGN KEY (substitute_product_id) REFERENCES products(id)       ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- PURCHASE ORDERS
-- =============================================
CREATE TABLE IF NOT EXISTS purchase_orders (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT UNSIGNED NOT NULL,
    po_number    VARCHAR(50)  NOT NULL,
    supplier_id  INT UNSIGNED NOT NULL,
    warehouse_id INT UNSIGNED NOT NULL,
    status       ENUM('draft','sent','partial','received','cancelled') DEFAULT 'draft',
    expected_date DATE NULL,
    subtotal     DECIMAL(12,2) DEFAULT 0.00,
    tax_amount   DECIMAL(12,2) DEFAULT 0.00,
    total_amount DECIMAL(12,2) DEFAULT 0.00,
    notes        TEXT NULL,
    created_by   INT UNSIGNED NULL,
    received_at  TIMESTAMP NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_po (tenant_id, po_number),
    INDEX idx_tenant   (tenant_id),
    INDEX idx_supplier (supplier_id),
    INDEX idx_status   (status),
    FOREIGN KEY (tenant_id)    REFERENCES tenants(id)   ON DELETE CASCADE,
    FOREIGN KEY (supplier_id)  REFERENCES suppliers(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB;

-- =============================================
-- PURCHASE ORDER ITEMS
-- =============================================
CREATE TABLE IF NOT EXISTS purchase_order_items (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id INT UNSIGNED NOT NULL,
    tenant_id         INT UNSIGNED NOT NULL,
    product_id        INT UNSIGNED NOT NULL,
    variant_id        INT UNSIGNED NULL,
    quantity          DECIMAL(10,3) NOT NULL,
    received_quantity DECIMAL(10,3) DEFAULT 0,
    unit_cost         DECIMAL(12,2) NOT NULL,
    total_cost        DECIMAL(12,2) NOT NULL,
    INDEX idx_po     (purchase_order_id),
    INDEX idx_tenant (tenant_id),
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id)        REFERENCES products(id),
    FOREIGN KEY (variant_id)        REFERENCES product_variants(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- API KEYS
-- =============================================
CREATE TABLE IF NOT EXISTS api_keys (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id          INT UNSIGNED NOT NULL,
    name               VARCHAR(255) NOT NULL,
    key_hash           VARCHAR(255) NOT NULL,
    key_prefix         VARCHAR(20)  NOT NULL,
    permissions        JSON NOT NULL DEFAULT ('["read"]'),
    rate_limit_per_hour INT DEFAULT 1000,
    is_active          BOOLEAN DEFAULT TRUE,
    last_used_at       TIMESTAMP NULL,
    created_by         INT UNSIGNED NULL,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at         TIMESTAMP NULL,
    INDEX idx_tenant (tenant_id),
    INDEX idx_prefix (key_prefix),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- API USAGE LOGS
-- =============================================
CREATE TABLE IF NOT EXISTS api_logs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT UNSIGNED NOT NULL,
    api_key_id      INT UNSIGNED NULL,
    endpoint        VARCHAR(500) NOT NULL,
    method          VARCHAR(10)  NOT NULL,
    status_code     INT NOT NULL,
    response_time_ms INT NULL,
    ip_address      VARCHAR(45)  NULL,
    user_agent      TEXT NULL,
    request_body    JSON NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant    (tenant_id),
    INDEX idx_api_key   (api_key_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- STOCK TRANSFERS
-- =============================================
CREATE TABLE IF NOT EXISTS stock_transfers (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    transfer_number   VARCHAR(50)  NOT NULL,
    from_warehouse_id INT UNSIGNED NOT NULL,
    to_warehouse_id   INT UNSIGNED NOT NULL,
    status            ENUM('draft','in_transit','received','cancelled') DEFAULT 'draft',
    notes             TEXT NULL,
    created_by        INT UNSIGNED NULL,
    received_at       TIMESTAMP NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_transfer (tenant_id, transfer_number),
    INDEX idx_tenant (tenant_id),
    FOREIGN KEY (tenant_id)         REFERENCES tenants(id)    ON DELETE CASCADE,
    FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (to_warehouse_id)   REFERENCES warehouses(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stock_transfer_items (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transfer_id       INT UNSIGNED NOT NULL,
    tenant_id         INT UNSIGNED NOT NULL,
    product_id        INT UNSIGNED NOT NULL,
    variant_id        INT UNSIGNED NULL,
    quantity          DECIMAL(10,3) NOT NULL,
    received_quantity DECIMAL(10,3) DEFAULT 0,
    FOREIGN KEY (transfer_id) REFERENCES stock_transfers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id)  REFERENCES products(id)
) ENGINE=InnoDB;

-- =============================================
-- NOTIFICATIONS
-- =============================================
CREATE TABLE IF NOT EXISTS notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT UNSIGNED NOT NULL,
    user_id    INT UNSIGNED NULL,
    type       VARCHAR(100) NOT NULL,
    title      VARCHAR(255) NOT NULL,
    message    TEXT NOT NULL,
    data       JSON NULL,
    is_read    BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_user (tenant_id, user_id),
    INDEX idx_read        (is_read),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- INTEGRATIONS (Shopify, WooCommerce, Amazon, ShipStation)
-- =============================================
CREATE TABLE IF NOT EXISTS integrations (
    id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT UNSIGNED NOT NULL,
    platform             ENUM('shopify','woocommerce','amazon','shipstation') NOT NULL,
    shop_domain          VARCHAR(255) NOT NULL,
    api_base_url         VARCHAR(255) NULL,
    access_token         TEXT NOT NULL,
    api_key              TEXT NULL,
    api_secret           TEXT NULL,
    webhook_secret       TEXT NULL,
    metadata             JSON NULL,
    status               ENUM('active','inactive','error','paused','reauth_required') DEFAULT 'active',
    last_sync_at         TIMESTAMP NULL,
    last_success_at      TIMESTAMP NULL,
    last_failure_at      TIMESTAMP NULL,
    paused_until         TIMESTAMP NULL,
    consecutive_failures INT NOT NULL DEFAULT 0,
    failed_count         INT NOT NULL DEFAULT 0,
    reauth_required_at   TIMESTAMP NULL,
    sync_orders          TINYINT(1) DEFAULT 1,
    sync_inventory       TINYINT(1) DEFAULT 1,
    sync_products        TINYINT(1) DEFAULT 0,
    error_message        TEXT NULL,
    created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_tenant_platform_domain (tenant_id, platform, shop_domain),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- INTEGRATION ORDER MAP (idempotency)
-- =============================================
CREATE TABLE IF NOT EXISTS integration_order_map (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    integration_id    INT UNSIGNED NOT NULL,
    external_order_id VARCHAR(100) NOT NULL,
    iom_order_id      INT UNSIGNED NOT NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_external (integration_id, external_order_id),
    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)      ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE,
    FOREIGN KEY (iom_order_id)   REFERENCES orders(id)       ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- INTEGRATION PRODUCT MAP
-- =============================================
CREATE TABLE IF NOT EXISTS integration_product_map (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           INT UNSIGNED NOT NULL,
    integration_id      INT UNSIGNED NOT NULL,
    external_product_id VARCHAR(100) NOT NULL,
    external_variant_id VARCHAR(100) NULL,
    iom_product_id      INT UNSIGNED NOT NULL,
    iom_variant_id      INT UNSIGNED NULL,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_external_variant (integration_id, external_variant_id),
    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)      ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- INTEGRATION SYNC LOG
-- =============================================
CREATE TABLE IF NOT EXISTS integration_sync_log (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT UNSIGNED NOT NULL,
    integration_id INT UNSIGNED NOT NULL,
    event          VARCHAR(100) NOT NULL,
    status         ENUM('success','error','skipped') DEFAULT 'success',
    message        TEXT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant      (tenant_id),
    INDEX idx_integration (integration_id),
    INDEX idx_created_at  (created_at),
    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)      ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- INTEGRATION SYNC JOBS (queue / retry)
-- =============================================
CREATE TABLE IF NOT EXISTS integration_sync_jobs (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT UNSIGNED NOT NULL,
    integration_id   INT UNSIGNED NOT NULL,
    platform         VARCHAR(50)  NOT NULL,
    job_type         VARCHAR(100) NOT NULL,
    dedupe_key       VARCHAR(255) NULL,
    trigger_source   VARCHAR(50)  NOT NULL DEFAULT 'manual',
    status           ENUM('pending','running','success','error','dead_letter') DEFAULT 'pending',
    attempt_count    INT NOT NULL DEFAULT 0,
    max_attempts     INT NOT NULL DEFAULT 3,
    next_attempt_at  TIMESTAMP NULL,
    started_at       TIMESTAMP NULL,
    finished_at      TIMESTAMP NULL,
    latency_ms       INT NULL,
    request_payload  JSON NULL,
    response_payload JSON NULL,
    last_error       TEXT NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dedupe_key         (dedupe_key),
    INDEX idx_tenant_status          (tenant_id, status),
    INDEX idx_integration_job        (integration_id, job_type),
    INDEX idx_next_attempt           (next_attempt_at),
    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)      ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- RECONCILIATION SNAPSHOTS
-- =============================================
CREATE TABLE IF NOT EXISTS reconciliation_snapshots (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           INT UNSIGNED NOT NULL,
    integration_id      INT UNSIGNED NULL,
    platform            VARCHAR(50)  NULL,
    reconciliation_type ENUM('orders','inventory','accounting') NOT NULL,
    snapshot_date       DATE NOT NULL,
    external_count      DECIMAL(12,3) NOT NULL DEFAULT 0,
    internal_count      DECIMAL(12,3) NOT NULL DEFAULT 0,
    external_total      DECIMAL(15,2) NOT NULL DEFAULT 0,
    internal_total      DECIMAL(15,2) NOT NULL DEFAULT 0,
    variance_count      DECIMAL(12,3) NOT NULL DEFAULT 0,
    variance_amount     DECIMAL(15,2) NOT NULL DEFAULT 0,
    details             JSON NULL,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_type_date  (tenant_id, reconciliation_type, snapshot_date),
    INDEX idx_integration_date  (integration_id, snapshot_date),
    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)      ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- ACCOUNTING INTEGRATIONS (Xero, QuickBooks, Beezifi)
-- =============================================
CREATE TABLE IF NOT EXISTS accounting_integrations (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT UNSIGNED NOT NULL,
    platform         ENUM('xero','quickbooks','beezifi') NOT NULL,
    access_token     TEXT NOT NULL,
    refresh_token    TEXT NOT NULL,
    token_expires_at TIMESTAMP NOT NULL,
    xero_tenant_id   VARCHAR(100) NULL,
    realm_id         VARCHAR(100) NULL,
    account_mapping  JSON NULL,
    status           ENUM('active','inactive','token_expired') DEFAULT 'active',
    last_sync_at     TIMESTAMP NULL,
    auto_sync        TINYINT(1) DEFAULT 1,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_tenant_platform (tenant_id, platform),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- ACCOUNTING SYNC LOG
-- =============================================
CREATE TABLE IF NOT EXISTS accounting_sync_log (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id               INT UNSIGNED NOT NULL,
    integration_id          INT UNSIGNED NOT NULL,
    order_id                INT UNSIGNED NULL,
    external_invoice_id     VARCHAR(100) NULL,
    external_invoice_number VARCHAR(100) NULL,
    status                  ENUM('pending','success','error') DEFAULT 'pending',
    error_message           TEXT NULL,
    synced_at               TIMESTAMP NULL,
    created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant   (tenant_id),
    INDEX idx_order_id (order_id),
    UNIQUE KEY uq_order_integration (integration_id, order_id),
    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)                 ON DELETE CASCADE,
    FOREIGN KEY (integration_id) REFERENCES accounting_integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- SUBSCRIPTIONS (billing — duration-based)
-- =============================================
CREATE TABLE IF NOT EXISTS subscriptions (
    id                   INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT UNSIGNED   NOT NULL,
    status               ENUM('trialing','active','expired') NOT NULL DEFAULT 'trialing',
    trial_ends_at        DATE           NULL,
    expires_at           DATE           NULL  COMMENT 'When current paid access ends; NULL = trial only',
    user_count           INT            NOT NULL DEFAULT 1,
    stripe_customer_id   VARCHAR(100)   NULL,
    created_at           TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant  (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- BILLING INVOICES (Stripe payment records)
-- =============================================
CREATE TABLE IF NOT EXISTS billing_invoices (
    id                        INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    tenant_id                 INT UNSIGNED   NOT NULL,
    invoice_number            VARCHAR(50)    NOT NULL,
    user_count                INT            NOT NULL,
    duration_months           INT            NOT NULL,
    amount_cents              INT            NOT NULL  COMMENT 'Total in USD cents',
    status                    ENUM('pending','paid','void') NOT NULL DEFAULT 'pending',
    stripe_payment_intent_id  VARCHAR(100)   NULL,
    paid_at                   TIMESTAMP      NULL,
    created_at                TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_invoice     (tenant_id, invoice_number),
    INDEX idx_tenant          (tenant_id),
    INDEX idx_stripe_pi       (stripe_payment_intent_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- SYSTEM ADMINS (super-admin portal)
-- =============================================
CREATE TABLE IF NOT EXISTS system_admins (
    id            INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255)   NOT NULL UNIQUE,
    password_hash VARCHAR(255)   NOT NULL,
    first_name    VARCHAR(100)   NOT NULL,
    last_name     VARCHAR(100)   NOT NULL,
    totp_secret   VARCHAR(100)   NULL,
    totp_enabled  TINYINT(1)     NOT NULL DEFAULT 0,
    is_active     TINYINT(1)     NOT NULL DEFAULT 1,
    last_login    TIMESTAMP      NULL,
    created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- PROMO CODES
-- =============================================
CREATE TABLE IF NOT EXISTS promo_codes (
    id              INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    code            VARCHAR(50)    NOT NULL UNIQUE,
    duration_months INT            NOT NULL COMMENT '3, 6, or 12',
    max_uses        INT            NOT NULL DEFAULT 1,
    uses_count      INT            NOT NULL DEFAULT 0,
    created_by      INT UNSIGNED   NOT NULL,
    expires_at      DATE           NULL,
    is_active       TINYINT(1)     NOT NULL DEFAULT 1,
    created_at      TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES system_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- PROMO CODE REDEMPTIONS
-- =============================================
CREATE TABLE IF NOT EXISTS promo_code_redemptions (
    id              INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    promo_code_id   INT UNSIGNED   NOT NULL,
    tenant_id       INT UNSIGNED   NOT NULL,
    extended_until  DATE           NOT NULL,
    redeemed_at     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_code (promo_code_id, tenant_id),
    FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id),
    FOREIGN KEY (tenant_id)     REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CONNECTOR CERTIFICATIONS
-- =============================================
CREATE TABLE IF NOT EXISTS connector_certifications (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           INT UNSIGNED NOT NULL,
    integration_scope   ENUM('commerce','accounting') NOT NULL,
    integration_id      INT UNSIGNED NOT NULL,
    platform            VARCHAR(50) NOT NULL,
    certification_level ENUM('sandbox','partner_validated','production_certified') NOT NULL DEFAULT 'sandbox',
    checklist           JSON NULL,
    notes               TEXT NULL,
    last_certified_at   TIMESTAMP NULL,
    created_by          INT UNSIGNED NULL,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_scope_integration (tenant_id, integration_scope, integration_id),
    INDEX idx_tenant_platform (tenant_id, platform),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- ONBOARDING DIAGNOSTICS
-- =============================================
CREATE TABLE IF NOT EXISTS onboarding_diagnostics (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    integration_scope ENUM('commerce','accounting') NOT NULL,
    integration_id    INT UNSIGNED NULL,
    platform          VARCHAR(50) NOT NULL,
    severity          ENUM('info','warning','error') NOT NULL DEFAULT 'info',
    diagnostic_code   VARCHAR(80) NOT NULL,
    message           VARCHAR(500) NOT NULL,
    payload           JSON NULL,
    resolved_at       TIMESTAMP NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_created  (tenant_id, created_at),
    INDEX idx_tenant_platform (tenant_id, platform),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- DEMAND FORECASTS
-- =============================================
CREATE TABLE IF NOT EXISTS demand_forecasts (
    id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT UNSIGNED NOT NULL,
    product_id           INT UNSIGNED NOT NULL,
    warehouse_id         INT UNSIGNED NULL,
    forecast_window_days INT UNSIGNED NOT NULL DEFAULT 30,
    horizon_days         INT UNSIGNED NOT NULL DEFAULT 30,
    avg_daily_demand     DECIMAL(12,4) NOT NULL DEFAULT 0,
    projected_demand     DECIMAL(12,4) NOT NULL DEFAULT 0,
    confidence_score     DECIMAL(5,2) NOT NULL DEFAULT 0,
    safety_stock_qty     DECIMAL(12,4) NOT NULL DEFAULT 0,
    reorder_point_qty    DECIMAL(12,4) NOT NULL DEFAULT 0,
    model_version        VARCHAR(30) NOT NULL DEFAULT 'moving_avg_v1',
    generated_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_product_wh_window (tenant_id, product_id, warehouse_id, forecast_window_days, horizon_days),
    INDEX idx_tenant_generated (tenant_id, generated_at),
    FOREIGN KEY (tenant_id)    REFERENCES tenants(id)    ON DELETE CASCADE,
    FOREIGN KEY (product_id)   REFERENCES products(id)   ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- SUPPLIER LEAD TIME STATS
-- =============================================
CREATE TABLE IF NOT EXISTS supplier_lead_time_stats (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id          INT UNSIGNED NOT NULL,
    supplier_id        INT UNSIGNED NOT NULL,
    product_id         INT UNSIGNED NULL,
    avg_lead_time_days DECIMAL(10,2) NOT NULL DEFAULT 0,
    p90_lead_time_days DECIMAL(10,2) NOT NULL DEFAULT 0,
    sample_size        INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_supplier_product (tenant_id, supplier_id, product_id),
    FOREIGN KEY (tenant_id)   REFERENCES tenants(id)   ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id)  REFERENCES products(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- REPLENISHMENT RECOMMENDATIONS
-- =============================================
CREATE TABLE IF NOT EXISTS replenishment_recommendations (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id               INT UNSIGNED NOT NULL,
    product_id              INT UNSIGNED NOT NULL,
    warehouse_id            INT UNSIGNED NULL,
    supplier_id             INT UNSIGNED NULL,
    recommended_qty         DECIMAL(12,3) NOT NULL,
    projected_stockout_date DATE NULL,
    target_service_level    DECIMAL(5,2) NOT NULL DEFAULT 95.00,
    rationale               JSON NULL,
    status                  ENUM('open','approved','converted','dismissed') NOT NULL DEFAULT 'open',
    generated_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_by             INT UNSIGNED NULL,
    approved_at             TIMESTAMP NULL,
    INDEX idx_tenant_status_generated (tenant_id, status, generated_at),
    FOREIGN KEY (tenant_id)    REFERENCES tenants(id)    ON DELETE CASCADE,
    FOREIGN KEY (product_id)   REFERENCES products(id)   ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id)  REFERENCES suppliers(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- WORKFLOW AUTOMATION RULES
-- =============================================
CREATE TABLE IF NOT EXISTS workflow_automation_rules (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT UNSIGNED NOT NULL,
    name       VARCHAR(255) NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    conditions JSON NULL,
    actions    JSON NOT NULL,
    priority   INT NOT NULL DEFAULT 100,
    is_active  TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant_event_active (tenant_id, event_type, is_active),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- WORKFLOW AUTOMATION RUNS
-- =============================================
CREATE TABLE IF NOT EXISTS workflow_automation_runs (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NOT NULL,
    rule_id       INT UNSIGNED NOT NULL,
    event_type    VARCHAR(80) NOT NULL,
    context       JSON NULL,
    outcome       ENUM('triggered','skipped','failed') NOT NULL,
    error_message TEXT NULL,
    executed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_executed (tenant_id, executed_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)                   ON DELETE CASCADE,
    FOREIGN KEY (rule_id)   REFERENCES workflow_automation_rules(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- APPROVAL REQUESTS
-- =============================================
CREATE TABLE IF NOT EXISTS approval_requests (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT UNSIGNED NOT NULL,
    source_type     VARCHAR(50) NOT NULL,
    source_id       INT UNSIGNED NOT NULL,
    reason          VARCHAR(255) NOT NULL,
    status          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    requested_by    INT UNSIGNED NULL,
    resolved_by     INT UNSIGNED NULL,
    resolution_note VARCHAR(500) NULL,
    requested_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at     TIMESTAMP NULL,
    INDEX idx_tenant_status_requested (tenant_id, status, requested_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- ROLE PERMISSIONS
-- =============================================
CREATE TABLE IF NOT EXISTS role_permissions (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT UNSIGNED NOT NULL,
    role           ENUM('admin','manager','staff') NOT NULL,
    permission_key VARCHAR(120) NOT NULL,
    granted        TINYINT(1) NOT NULL DEFAULT 1,
    updated_by     INT UNSIGNED NULL,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_role_permission (tenant_id, role, permission_key),
    INDEX idx_tenant_role (tenant_id, role),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- AUDIT EVENTS
-- =============================================
CREATE TABLE IF NOT EXISTS audit_events (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT UNSIGNED NOT NULL,
    user_id     INT UNSIGNED NULL,
    event_type  VARCHAR(120) NOT NULL,
    entity_type VARCHAR(120) NULL,
    entity_id   VARCHAR(120) NULL,
    payload     JSON NULL,
    ip_address  VARCHAR(64) NULL,
    user_agent  VARCHAR(255) NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_created (tenant_id, created_at),
    INDEX idx_tenant_event   (tenant_id, event_type),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- SSO CONFIGS
-- =============================================
CREATE TABLE IF NOT EXISTS sso_configs (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NOT NULL,
    provider      ENUM('oidc','saml') NOT NULL,
    issuer        VARCHAR(255) NULL,
    client_id     VARCHAR(255) NULL,
    client_secret TEXT NULL,
    metadata_url  VARCHAR(500) NULL,
    domain_hint   VARCHAR(255) NULL,
    is_active     TINYINT(1) NOT NULL DEFAULT 0,
    created_by    INT UNSIGNED NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_provider (tenant_id, provider),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- IMPORT JOBS
-- =============================================
CREATE TABLE IF NOT EXISTS import_jobs (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT UNSIGNED NOT NULL,
    import_type  ENUM('products','customers','orders','inventory') NOT NULL,
    status       ENUM('uploaded','validated','processing','completed','failed') NOT NULL DEFAULT 'uploaded',
    source_file  VARCHAR(500) NULL,
    total_rows   INT UNSIGNED NOT NULL DEFAULT 0,
    valid_rows   INT UNSIGNED NOT NULL DEFAULT 0,
    invalid_rows INT UNSIGNED NOT NULL DEFAULT 0,
    summary      JSON NULL,
    created_by   INT UNSIGNED NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant_type_created (tenant_id, import_type, created_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- IMPORT JOB ROWS
-- =============================================
CREATE TABLE IF NOT EXISTS import_job_rows (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NOT NULL,
    import_job_id INT UNSIGNED NOT NULL,
    row_no        INT UNSIGNED NOT NULL,
    status        ENUM('valid','invalid','imported') NOT NULL,
    error_message VARCHAR(500) NULL,
    row_payload   JSON NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_job_status (tenant_id, import_job_id, status),
    FOREIGN KEY (tenant_id)     REFERENCES tenants(id)     ON DELETE CASCADE,
    FOREIGN KEY (import_job_id) REFERENCES import_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- CUTOVER PLAYBOOKS
-- =============================================
CREATE TABLE IF NOT EXISTS cutover_playbooks (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    name              VARCHAR(255) NOT NULL,
    status            ENUM('draft','in_progress','completed') NOT NULL DEFAULT 'draft',
    target_go_live_at TIMESTAMP NULL,
    owner_user_id     INT UNSIGNED NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant_status (tenant_id, status),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- CUTOVER STEPS
-- =============================================
CREATE TABLE IF NOT EXISTS cutover_steps (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT UNSIGNED NOT NULL,
    playbook_id  INT UNSIGNED NOT NULL,
    step_order   INT UNSIGNED NOT NULL,
    title        VARCHAR(255) NOT NULL,
    owner_role   ENUM('admin','manager','staff') NOT NULL DEFAULT 'manager',
    status       ENUM('todo','in_progress','done','blocked') NOT NULL DEFAULT 'todo',
    checklist    JSON NULL,
    notes        VARCHAR(500) NULL,
    completed_by INT UNSIGNED NULL,
    completed_at TIMESTAMP NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_playbook_step_order    (playbook_id, step_order),
    INDEX idx_tenant_playbook_status     (tenant_id, playbook_id, status),
    FOREIGN KEY (tenant_id)   REFERENCES tenants(id)           ON DELETE CASCADE,
    FOREIGN KEY (playbook_id) REFERENCES cutover_playbooks(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- POS SESSIONS
-- =============================================
CREATE TABLE IF NOT EXISTS pos_sessions (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT UNSIGNED NOT NULL,
    warehouse_id   INT UNSIGNED NOT NULL,
    register_name  VARCHAR(100) NOT NULL DEFAULT 'Register 1',
    opened_by      INT UNSIGNED NOT NULL,
    opened_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_by      INT UNSIGNED NULL,
    closed_at      TIMESTAMP NULL,
    opening_float  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    closing_float  DECIMAL(10,2) NULL,
    cash_sales     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    card_sales     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    other_sales    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    refunds_total  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    expected_cash  DECIMAL(10,2) NULL,
    status         ENUM('open','closed') NOT NULL DEFAULT 'open',
    notes          TEXT NULL,
    INDEX idx_tenant_status (tenant_id, status),
    INDEX idx_tenant_opened (tenant_id, opened_at),
    FOREIGN KEY (tenant_id)    REFERENCES tenants(id)    ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (opened_by)    REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- POS CASH MOVEMENTS
-- =============================================
CREATE TABLE IF NOT EXISTS pos_cash_movements (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT UNSIGNED NOT NULL,
    session_id INT UNSIGNED NOT NULL,
    type       ENUM('open','in','out','close') NOT NULL,
    amount     DECIMAL(10,2) NOT NULL,
    note       VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES pos_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- POS TRANSACTIONS
-- =============================================
CREATE TABLE IF NOT EXISTS pos_transactions (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id          INT UNSIGNED NOT NULL,
    session_id         INT UNSIGNED NOT NULL,
    order_id           INT UNSIGNED NULL,
    transaction_number VARCHAR(50) NOT NULL,
    customer_id        INT UNSIGNED NULL,
    subtotal           DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tendered_amount    DECIMAL(10,2) NULL,
    change_amount      DECIMAL(10,2) NULL,
    payment_method     ENUM('cash','card','split','other') NOT NULL DEFAULT 'cash',
    payment_reference  VARCHAR(255) NULL,
    status             ENUM('completed','refunded','voided') NOT NULL DEFAULT 'completed',
    refund_of          INT UNSIGNED NULL,
    notes              TEXT NULL,
    created_by         INT UNSIGNED NULL,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_txn_number      (tenant_id, transaction_number),
    INDEX idx_session             (session_id),
    INDEX idx_tenant_created      (tenant_id, created_at),
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id)      ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES pos_sessions(id),
    FOREIGN KEY (order_id)   REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- POS TRANSACTION PAYMENTS
-- =============================================
CREATE TABLE IF NOT EXISTS pos_transaction_payments (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT UNSIGNED NOT NULL,
    method         ENUM('cash','card','other') NOT NULL,
    amount         DECIMAL(10,2) NOT NULL,
    reference      VARCHAR(255) NULL,
    FOREIGN KEY (transaction_id) REFERENCES pos_transactions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
