-- =============================================================================
-- Module 6.5: Website Management System
-- Migration 009 — websites
-- =============================================================================

CREATE TABLE IF NOT EXISTS `websites` (
    `id`               BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `user_id`          BIGINT UNSIGNED  NOT NULL                  COMMENT 'Owner',

    -- Core identity
    `name`             VARCHAR(255)     NOT NULL                  COMMENT 'Human-readable website name',
    `slug`             VARCHAR(255)     NOT NULL                  COMMENT 'URL-safe unique identifier',
    `description`      TEXT             NULL       DEFAULT NULL,

    -- Module connections
    `template_id`      BIGINT UNSIGNED  NULL       DEFAULT NULL   COMMENT 'FK → templates.id (Module 3)',
    `conversation_id`  BIGINT UNSIGNED  NULL       DEFAULT NULL   COMMENT 'FK → conversations.id (Module 2)',
    `website_state_id` BIGINT UNSIGNED  NULL       DEFAULT NULL   COMMENT 'FK → website_states.id (Module 6)',

    -- Browser preview metadata
    `thumbnail_url`    VARCHAR(512)     NULL       DEFAULT NULL   COMMENT 'Last browser preview screenshot URL (Module 5)',
    `preview_device`   ENUM('desktop','tablet','mobile') NOT NULL DEFAULT 'desktop' COMMENT 'Default device frame for browser preview',

    -- Status lifecycle
    `status`           ENUM('draft','building','published','archived') NOT NULL DEFAULT 'draft',
    `published_at`     DATETIME         NULL       DEFAULT NULL,
    `archived_at`      DATETIME         NULL       DEFAULT NULL,

    -- Soft delete
    `deleted_at`       DATETIME         NULL       DEFAULT NULL   COMMENT 'NULL = not deleted; set = soft-deleted',

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

    PRIMARY KEY (`id`),
    UNIQUE  KEY `uq_websites_slug`            (`slug`),
    INDEX         `idx_websites_user`         (`user_id`),
    INDEX         `idx_websites_status`       (`status`),
    INDEX         `idx_websites_template`     (`template_id`),
    INDEX         `idx_websites_conversation` (`conversation_id`),
    INDEX         `idx_websites_state`        (`website_state_id`),
    INDEX         `idx_websites_deleted`      (`deleted_at`),
    INDEX         `idx_websites_published`    (`published_at`),
    INDEX         `idx_websites_updated`      (`updated_at`),

    CONSTRAINT `fk_websites_state`
        FOREIGN KEY (`website_state_id`)
        REFERENCES  `website_states` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;