-- ============================================================
--  NXA LITE — DATABASE SCHEMA
--  Target: MySQL / MariaDB on Namecheap shared hosting (cPanel)
--  Database: wxqylcpz_BahaaIsmail
--  PHP: 8.4  ·  Engine: InnoDB  ·  Charset: utf8mb4
--
--  HOW TO IMPORT (see INSTALL.md for full steps):
--    cPanel → phpMyAdmin → select database wxqylcpz_BahaaIsmail
--    → Import tab → choose this file → Go
--
--  This schema starts EMPTY (no demo data). You enter all data
--  through the admin panel. Only ONE super-admin row is created
--  at install time — via the installer page, NOT here — so the
--  password is hashed on your own server and never stored in a file.
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ------------------------------------------------------------
--  brands
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `brands` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(150) NOT NULL,
  `hidden`      TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order`  INT NOT NULL DEFAULT 0,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_brand_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
--  categories  (belong to a brand)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `brand_id`    INT UNSIGNED NOT NULL,
  `name`        VARCHAR(200) NOT NULL,
  `description` VARCHAR(500) NOT NULL DEFAULT '',
  `icon`        VARCHAR(16)  NOT NULL DEFAULT '',
  `color`       VARCHAR(16)  NOT NULL DEFAULT '',
  `hidden`      TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order`  INT NOT NULL DEFAULT 0,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cat_brand` (`brand_id`),
  CONSTRAINT `fk_cat_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
--  families  (belong to a category)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `families` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NOT NULL,
  `name`        VARCHAR(200) NOT NULL,
  `description` VARCHAR(500) NOT NULL DEFAULT '',
  `icon`        VARCHAR(16)  NOT NULL DEFAULT '',
  `color`       VARCHAR(16)  NOT NULL DEFAULT '',
  `hidden`      TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order`  INT NOT NULL DEFAULT 0,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_fam_cat` (`category_id`),
  CONSTRAINT `fk_fam_cat` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
--  family_params  (filter parameters defined per family)
--  These are the family-specific filter/spec fields.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `family_params` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `family_id`   INT UNSIGNED NOT NULL,
  `param_key`   VARCHAR(100) NOT NULL,
  `label`       VARCHAR(200) NOT NULL,
  `sort_order`  INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_fp_fam` (`family_id`),
  UNIQUE KEY `uq_fp_fam_key` (`family_id`,`param_key`),
  CONSTRAINT `fk_fp_fam` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
--  products  (belong to a family)
--  Parameter values stored as JSON: {"param_key":"value", ...}
--  System fields = order_code, sap_code, description.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `family_id`   INT UNSIGNED NOT NULL,
  `order_code`  VARCHAR(190) NOT NULL,
  `sap_code`    VARCHAR(190) NOT NULL DEFAULT '',
  `description` VARCHAR(600) NOT NULL DEFAULT '',
  `params`      JSON NULL,
  `image_file`  VARCHAR(255) NOT NULL DEFAULT '',
  `sort_order`  INT NOT NULL DEFAULT 0,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_prod_fam` (`family_id`),
  UNIQUE KEY `uq_prod_order` (`order_code`),
  KEY `idx_prod_sap` (`sap_code`),
  CONSTRAINT `fk_prod_fam` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
--  datasheet_config  (per-family datasheet builder config, JSON)
--  Full six-tab config is stored here as JSON in the backend phase.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `datasheet_config` (
  `family_id`   INT UNSIGNED NOT NULL,
  `config`      JSON NULL,
  `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`family_id`),
  CONSTRAINT `fk_ds_fam` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
--  admin_users  (role-based: 'super' or 'admin')
--  - super : full control incl. managing users + reset key
--  - admin : catalog/product work only, no user management
--  Passwords + reset key are stored HASHED (password_hash()).
--  The first super-admin is created by install.php, not here.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`       VARCHAR(100) NOT NULL,
  `password_hash`  VARCHAR(255) NOT NULL,
  `role`           ENUM('super','admin') NOT NULL DEFAULT 'admin',
  `created_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admin_user` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
--  settings  (key/value store: logos, api key, reset key hash, etc.)
--  reset_key_hash is stored here (single global recovery key).
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `skey`   VARCHAR(100) NOT NULL,
  `svalue` TEXT NULL,
  PRIMARY KEY (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed only the setting KEYS (empty values) so the app can read them safely.
INSERT INTO `settings` (`skey`,`svalue`) VALUES
  ('header_logo',   ''),   -- filename in /uploads for the site header logo
  ('datasheet_logo',''),   -- filename in /uploads for the datasheet logo
  ('reset_key_hash',''),   -- hashed secret recovery key (set at install)
  ('site_title','Non-Official CHINT LV Product')
ON DUPLICATE KEY UPDATE `skey`=`skey`;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
--  END OF SCHEMA
-- ============================================================