-- ============================================================================
-- QuickCatalog Master — Store Registry
-- ----------------------------------------------------------------------------
-- ONE small database for quickecommerce.co itself (separate from every
-- individual store's own database). Tracks every store ever created, so
-- you have a real admin dashboard, can enforce trial expiry, and can find
-- "which store is at which URL, using which database" without digging
-- through cPanel by hand.
-- ============================================================================

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

CREATE TABLE IF NOT EXISTS `stores` (
    `id`              INT(11) NOT NULL AUTO_INCREMENT,

    -- Identity
    `store_name`      VARCHAR(255) NOT NULL,
    `subdomain`       VARCHAR(63)  NOT NULL,           -- e.g. "sgpneumatics"
    `full_url`        VARCHAR(255) NOT NULL,           -- e.g. "https://sgpneumatics.quickecommerce.co"

    -- Owner
    `owner_name`      VARCHAR(255) NOT NULL,
    `owner_email`     VARCHAR(255) NOT NULL,
    `owner_phone`     VARCHAR(30)  DEFAULT NULL,

    -- Provisioning details (what got created for this store)
    `db_name`         VARCHAR(100) NOT NULL,           -- full cPanel-prefixed name
    `db_user`         VARCHAR(100) NOT NULL,           -- full cPanel-prefixed name
    `doc_root`        VARCHAR(500) NOT NULL,            -- server path to this store's files
    `business_type`   VARCHAR(100) DEFAULT NULL,         -- which demo pack was seeded, if any
    `admin_username`  VARCHAR(100) NOT NULL,

    -- Lifecycle
    `status`          ENUM('provisioning','active','suspended','expired','deleted')
                          NOT NULL DEFAULT 'provisioning',
    `plan`            VARCHAR(50)  NOT NULL DEFAULT 'demo',
    `trial_ends_at`   DATETIME     DEFAULT NULL,

    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_subdomain` (`subdomain`),
    KEY `idx_owner_email` (`owner_email`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every meaningful step of a provisioning run, kept even after success —
-- when something fails halfway through (DB created but codebase copy
-- failed, say), this is how you find out WHERE it stopped without
-- guessing, instead of a customer just seeing "Error" with no trail.
CREATE TABLE IF NOT EXISTS `provisioning_log` (
    `id`         INT(11) NOT NULL AUTO_INCREMENT,
    `store_id`   INT(11) DEFAULT NULL,       -- NULL if it failed before a store row existed
    `subdomain`  VARCHAR(63) NOT NULL,
    `step`       VARCHAR(100) NOT NULL,      -- e.g. "create_database", "copy_codebase"
    `status`     ENUM('ok','failed') NOT NULL,
    `message`    TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_subdomain` (`subdomain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Simple reserved-name blocklist so nobody can claim "admin", "www", etc.
CREATE TABLE IF NOT EXISTS `reserved_subdomains` (
    `subdomain` VARCHAR(63) NOT NULL,
    PRIMARY KEY (`subdomain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `reserved_subdomains` (`subdomain`) VALUES
    ('www'),('admin'),('api'),('mail'),('ftp'),('cpanel'),('webmail'),
    ('demo'),('test'),('staging'),('app'),('store'),('shop'),('blog'),
    ('support'),('help'),('billing'),('dashboard'),('master'),('root'),
    ('quickecommerce'),('signup'),('login'),('assets'),('static'),('cdn');