-- ============================================================================ -- 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;