-- =============================================================================
-- Migration 012: Create AI Usage Table and Supporting Tables
-- Module 6.7 — CostService infrastructure
-- =============================================================================

-- AI Usage tracking table
CREATE TABLE IF NOT EXISTS `ai_usage` (
    `id`            BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `website_id`    INT UNSIGNED      NOT NULL,
    `provider`      VARCHAR(50)       NOT NULL COMMENT 'openai | claude | openrouter',
    `model`         VARCHAR(100)      NOT NULL COMMENT 'e.g. gpt-4o, claude-3-5-sonnet',
    `input_tokens`  INT UNSIGNED      NOT NULL DEFAULT 0,
    `output_tokens` INT UNSIGNED      NOT NULL DEFAULT 0,
    `total_tokens`  INT UNSIGNED      NOT NULL DEFAULT 0,
    `cost_usd`      DECIMAL(14, 8)    NOT NULL DEFAULT 0.00000000 COMMENT 'USD cost to 8 decimal places',
    `meta`          JSON              NULL     COMMENT 'Optional metadata: request_id, session_id, etc.',
    `created_at`    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_ai_usage_website`    (`website_id`),
    INDEX `idx_ai_usage_provider`   (`provider`),
    INDEX `idx_ai_usage_model`      (`model`),
    INDEX `idx_ai_usage_created_at` (`created_at`),

    CONSTRAINT `fk_ai_usage_website`
        FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Tracks AI provider token usage and cost per website per request';

-- =============================================================================
-- Website state snapshots (used by PatchService + DiffService)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `website_state_snapshots` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `website_id`  INT UNSIGNED    NOT NULL,
    `trigger`     VARCHAR(100)    NOT NULL COMMENT 'Operation that caused this snapshot',
    `payload`     LONGTEXT        NOT NULL COMMENT 'Full JSON state at time of snapshot',
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_snapshots_website`    (`website_id`),
    INDEX `idx_snapshots_created_at` (`created_at`),

    CONSTRAINT `fk_snapshots_website`
        FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Point-in-time state snapshots for rollback and undo/redo support';

-- =============================================================================
-- Website patch log (audit trail of every applied patch)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `website_patch_log` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `website_id`  INT UNSIGNED    NOT NULL,
    `operation`   VARCHAR(100)    NOT NULL COMMENT 'e.g. add_section, update_theme, rollback',
    `data`        JSON            NOT NULL COMMENT 'Operation context and snapshot reference',
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_patch_log_website`    (`website_id`),
    INDEX `idx_patch_log_operation`  (`operation`),
    INDEX `idx_patch_log_created_at` (`created_at`),

    CONSTRAINT `fk_patch_log_website`
        FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Immutable audit log of every patch operation applied to a website';

-- =============================================================================
-- Website diff history (undo/redo stack for DiffService)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `website_diff_history` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `website_id`   INT UNSIGNED    NOT NULL,
    `action`       ENUM('undo','redo','restore') NOT NULL,
    `snapshot_id`  BIGINT UNSIGNED NOT NULL COMMENT 'References website_state_snapshots.id',
    `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_diff_history_website`    (`website_id`),
    INDEX `idx_diff_history_action`     (`action`),
    INDEX `idx_diff_history_created_at` (`created_at`),

    CONSTRAINT `fk_diff_history_website`
        FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Undo/redo/restore operation history for DiffService';