-- Luckus schema (MySQL/MariaDB). Run via install.php or `mysql < schema.sql`.

CREATE TABLE IF NOT EXISTS `products` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `slug`          VARCHAR(120) NOT NULL UNIQUE,
  `name`          VARCHAR(200) NOT NULL,
  `series`        VARCHAR(100) DEFAULT NULL,
  `type`          ENUM('charger','cable','accessory') NOT NULL DEFAULT 'charger',
  `summary`       TEXT,
  `key_specs`     JSON NOT NULL,
  `full_specs`    JSON NOT NULL,
  `moq`           INT NOT NULL DEFAULT 1000,
  `lead_time`     VARCHAR(60) DEFAULT NULL,
  `sample`        VARCHAR(120) DEFAULT NULL,
  `color`         VARCHAR(120) DEFAULT NULL,
  `image`         VARCHAR(80)  DEFAULT 'gradient-1',
  `badge`         VARCHAR(40)  DEFAULT NULL,
  `certifications` JSON NOT NULL,
  `sort_order`    INT NOT NULL DEFAULT 0,
  `status`        TINYINT NOT NULL DEFAULT 1,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_type_status` (`type`,`status`),
  KEY `idx_series` (`series`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `inquiries` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_id`       VARCHAR(40) NOT NULL UNIQUE,
  `name`            VARCHAR(120) DEFAULT NULL,
  `company`         VARCHAR(200) DEFAULT NULL,
  `country`         VARCHAR(100) DEFAULT NULL,
  `email`           VARCHAR(200) DEFAULT NULL,
  `whatsapp`        VARCHAR(60)  DEFAULT NULL,
  `product_interest` JSON NOT NULL,
  `est_qty`         VARCHAR(40)  DEFAULT NULL,
  `customization`   VARCHAR(40)  DEFAULT NULL,
  `target_price`    VARCHAR(60)  DEFAULT NULL,
  `message`         TEXT,
  `source_url`      VARCHAR(500) DEFAULT NULL,
  `ip_hash`         VARCHAR(60)  DEFAULT NULL,
  `user_agent`      VARCHAR(500) DEFAULT NULL,
  `status`          ENUM('new','contacted','quoted','won','lost') NOT NULL DEFAULT 'new',
  `notes`           TEXT,
  `assigned_to`     VARCHAR(80)  DEFAULT NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admin_users` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `username`      VARCHAR(60) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `name`          VARCHAR(120) DEFAULT NULL,
  `role`          ENUM('admin','manager') NOT NULL DEFAULT 'admin',
  `last_login`    DATETIME DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `content_pages` (
  `slug`        VARCHAR(100) PRIMARY KEY,
  `title`       VARCHAR(200) NOT NULL,
  `body`        MEDIUMTEXT NOT NULL,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Runtime-editable site settings (key/value). config.php stays for
-- deploy-time config (DB, paths); site_settings is content that the
-- admin can change at any time without redeploy.
CREATE TABLE IF NOT EXISTS `site_settings` (
  `key`         VARCHAR(80)  NOT NULL PRIMARY KEY,
  `value`       MEDIUMTEXT,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- News / blog posts. Drafts (status='draft') are only visible in admin.
-- Cover image is stored as a relative path (uploads/posts/...) or empty
-- (no cover → use a gradient placeholder).
CREATE TABLE IF NOT EXISTS `posts` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `slug`          VARCHAR(160) NOT NULL UNIQUE,
  `title`         VARCHAR(255) NOT NULL,
  `excerpt`       VARCHAR(500) DEFAULT NULL,
  `body`          MEDIUMTEXT NOT NULL,
  `cover_image`   VARCHAR(120) DEFAULT NULL,
  `author`        VARCHAR(80)  DEFAULT NULL,
  `tags`          JSON NOT NULL,
  `status`        ENUM('draft','published') NOT NULL DEFAULT 'draft',
  `published_at`  DATETIME DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status_pub` (`status`, `published_at`),
  KEY `idx_created`    (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Product categories. Supports a 2-level hierarchy (parent + child).
-- `type` is the product.type enum so a category can be a top-level
-- "Charger / Cable / Accessory" bucket, or a sub-category like
-- "GaN Series" / "Braided Cables". `match_field` + `match_value`
-- filters the product list when this category is selected
-- (e.g. type=charger, or series='GaN Series').
CREATE TABLE IF NOT EXISTS `categories` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `parent_id`     INT DEFAULT NULL,
  `name`          VARCHAR(120) NOT NULL,
  `slug`          VARCHAR(140) NOT NULL UNIQUE,
  `type`          ENUM('charger','cable','accessory') DEFAULT NULL,
  `match_field`   ENUM('type','series') DEFAULT 'type',
  `match_value`   VARCHAR(120) DEFAULT NULL,
  `description`   VARCHAR(500) DEFAULT NULL,
  `icon`          VARCHAR(40)  DEFAULT NULL,
  `sort_order`    INT NOT NULL DEFAULT 0,
  `status`        TINYINT NOT NULL DEFAULT 1,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_parent` (`parent_id`),
  KEY `idx_type`   (`type`),
  KEY `idx_status_sort` (`status`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;