-- ============================================================================
-- Storefront database schema
-- This is a SEPARATE database from Dolibarr (e.g. "shop_db").
-- It caches product/category data synced FROM Dolibarr for fast browsing,
-- and stages carts/orders that get pushed back INTO Dolibarr.
-- Nothing here touches Dolibarr's own llx_* tables directly.
-- ============================================================================

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

-- ----------------------------------------------------------------------------
-- Local cache of Dolibarr products (llx_product). Rebuilt/updated by sync.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shop_products (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    dolibarr_id       INT NOT NULL UNIQUE,          -- llx_product.rowid
    ref               VARCHAR(128) NOT NULL,
    label             VARCHAR(255) NOT NULL,
    description       TEXT NULL,
    price_ht          DECIMAL(20,8) NOT NULL DEFAULT 0,   -- price excl. tax
    price_ttc         DECIMAL(20,8) NOT NULL DEFAULT 0,   -- price incl. tax
    tva_tx            DECIMAL(7,4)  NOT NULL DEFAULT 0,   -- VAT rate %
    weight            DECIMAL(20,8) NULL,
    weight_units      TINYINT NULL,
    barcode           VARCHAR(180) NULL,
    stock_qty         DECIMAL(20,5) NOT NULL DEFAULT 0,   -- summed across warehouses
    stock_alert       DECIMAL(20,5) NULL,
    image_url         VARCHAR(500) NULL,
    on_sell           TINYINT(1) NOT NULL DEFAULT 1,      -- mirrors tosell
    product_type      TINYINT NOT NULL DEFAULT 0,         -- 0=product, 1=service
    entity            INT NOT NULL DEFAULT 1,
    last_synced_at    DATETIME NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_ref (ref),
    KEY idx_on_sell (on_sell),
    FULLTEXT KEY ft_label_desc (label, description)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Local cache of Dolibarr categories (llx_categorie, type=0 product categories)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shop_categories (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    dolibarr_id       INT NOT NULL UNIQUE,          -- llx_categorie.rowid
    parent_dolibarr_id INT NULL,
    label             VARCHAR(255) NOT NULL,
    description       TEXT NULL,
    image_url         VARCHAR(500) NULL,
    visible           TINYINT(1) NOT NULL DEFAULT 1,
    last_synced_at    DATETIME NULL,
    KEY idx_parent (parent_dolibarr_id)
) ENGINE=InnoDB;

-- Many-to-many product <-> category (mirrors llx_categorie_product)
CREATE TABLE IF NOT EXISTS shop_product_categories (
    product_id        INT NOT NULL,
    category_id       INT NOT NULL,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id) REFERENCES shop_products(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES shop_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Shop customer accounts. Each one maps to a Dolibarr third party (llx_societe).
-- If dolibarr_soc_id is NULL, the customer hasn't been pushed to Dolibarr yet
-- (created lazily on first order, or immediately on registration - your choice).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shop_customers (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    dolibarr_soc_id   INT NULL UNIQUE,              -- llx_societe.rowid
    email             VARCHAR(180) NOT NULL UNIQUE,
    password_hash     VARCHAR(255) NOT NULL,
    firstname         VARCHAR(120) NOT NULL,
    lastname          VARCHAR(120) NOT NULL,
    company_name      VARCHAR(255) NULL,
    phone             VARCHAR(40) NULL,
    address           VARCHAR(255) NULL,
    zip               VARCHAR(25) NULL,
    town              VARCHAR(180) NULL,
    country_code      VARCHAR(3) NULL DEFAULT 'US',
    is_guest          TINYINT(1) NOT NULL DEFAULT 0,   -- 1 = created via guest checkout, no real login
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Cart (session-based for guests, customer-based once logged in)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shop_carts (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    session_id        VARCHAR(128) NULL,
    customer_id       INT NULL,
    status            ENUM('open','converted','abandoned') NOT NULL DEFAULT 'open',
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_session (session_id),
    FOREIGN KEY (customer_id) REFERENCES shop_customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shop_cart_items (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    cart_id           INT NOT NULL,
    product_id        INT NOT NULL,
    qty               DECIMAL(20,5) NOT NULL DEFAULT 1,
    unit_price_ht     DECIMAL(20,8) NOT NULL,
    unit_price_ttc    DECIMAL(20,8) NOT NULL,
    added_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_cart_product (cart_id, product_id),
    FOREIGN KEY (cart_id) REFERENCES shop_carts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES shop_products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Orders placed on the shop. Pushed into Dolibarr's llx_commande once synced.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shop_orders (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    order_ref         VARCHAR(40) NOT NULL UNIQUE,   -- shop-local ref, e.g. WEB-000123
    dolibarr_order_id INT NULL UNIQUE,                -- llx_commande.rowid once pushed
    customer_id       INT NOT NULL,
    total_ht          DECIMAL(20,8) NOT NULL DEFAULT 0,
    total_tva         DECIMAL(20,8) NOT NULL DEFAULT 0,
    total_ttc         DECIMAL(20,8) NOT NULL DEFAULT 0,
    shipping_address  VARCHAR(255) NULL,
    shipping_zip      VARCHAR(25) NULL,
    shipping_town     VARCHAR(180) NULL,
    shipping_country  VARCHAR(3) NULL,
    payment_method    VARCHAR(60) NULL,
    payment_reference VARCHAR(100) NULL UNIQUE,       -- Paystack transaction reference
    paid_at           DATETIME NULL,
    status            ENUM('awaiting_payment','paid','sync_failed','synced','payment_failed','cancelled') NOT NULL DEFAULT 'awaiting_payment',
    sync_error        TEXT NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    synced_at         DATETIME NULL,
    FOREIGN KEY (customer_id) REFERENCES shop_customers(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shop_order_items (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    order_id          INT NOT NULL,
    product_id        INT NOT NULL,
    dolibarr_product_id INT NOT NULL,
    label             VARCHAR(255) NOT NULL,
    qty               DECIMAL(20,5) NOT NULL,
    unit_price_ht     DECIMAL(20,8) NOT NULL,
    tva_tx            DECIMAL(7,4) NOT NULL,
    total_ht          DECIMAL(20,8) NOT NULL,
    total_ttc          DECIMAL(20,8) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES shop_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Sync run history/log, so failures are visible and debuggable
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shop_sync_log (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    sync_type         VARCHAR(40) NOT NULL,   -- products | categories | stock | order_push
    status            ENUM('success','error') NOT NULL,
    records_processed INT NOT NULL DEFAULT 0,
    message           TEXT NULL,
    started_at        DATETIME NOT NULL,
    finished_at       DATETIME NOT NULL
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Admin dashboard accounts (separate from shop_customers on purpose — a
-- storefront login bug should never be able to reach admin tooling).
-- Create the first admin with cron/create_admin.php (see README).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    username          VARCHAR(80) NOT NULL UNIQUE,
    password_hash     VARCHAR(255) NOT NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at     DATETIME NULL
) ENGINE=InnoDB;
