-- =============================================================================
-- Module 6: AI Memory Manager
-- Migration 008 — website_revisions
-- =============================================================================

CREATE TABLE IF NOT EXISTS `website_revisions` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `website_state_id` BIGINT UNSIGNED NOT NULL,
    `user_id`          BIGINT UNSIGNED NOT NULL,
    `revision_number`  INT UNSIGNED    NOT NULL DEFAULT 1 COMMENT 'Sequential per website_state_id',
    `label`            VARCHAR(255)    NOT NULL DEFAULT 'Change'  COMMENT 'Human-readable description of the change',
    `snapshot`         JSON            NOT NULL                   COMMENT 'Full state at this revision: {sections,colors,fonts,content,images}',
    `is_autosave`      TINYINT(1)      NOT NULL DEFAULT 0         COMMENT '1 = autosave, 0 = manual save',
    `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_revisions_state`    (`website_state_id`),
    INDEX `idx_revisions_user`     (`user_id`),
    INDEX `idx_revisions_number`   (`website_state_id`, `revision_number`),
    INDEX `idx_revisions_autosave` (`website_state_id`, `is_autosave`),
    INDEX `idx_revisions_created`  (`created_at`),

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