-- ====================================================================
-- AI Ad Autopilot module (Marketing → Ads) — see ai-ad-autopilot-module-spec.md
-- Applied on 2026-09-25 through migrations/2026-09-25_ads_module.sql, which
-- runs this file plus the permission rows. Safe to run more than once.
--
-- Differences from the spec, on purpose:
--   • ad_accounts also stores the account's name/currency/timezone (Meta
--     budgets are in the ad account's currency, not the store's).
--   • autopilot_settings also stores the AI provider/model/key (encrypted)
--     and an optional Ad Library token, so everything is set from the admin.
--   • ad_competitors + competitor_ads: competitor research — ads found via
--     the Meta Ad Library API or saved by hand, plus the AI's analysis.
--   • Campaign objective defaults to OUTCOME_TRAFFIC (link clicks to the
--     product page). OUTCOME_SALES needs a Meta Pixel with purchase events.
-- ====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `ad_accounts` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `fb_ad_account_id` VARCHAR(50) NOT NULL COMMENT 'Numeric id, stored without the act_ prefix',
    `fb_page_id` VARCHAR(50) NOT NULL,
    `business_manager_id` VARCHAR(50) DEFAULT NULL,
    `access_token_encrypted` TEXT NOT NULL COMMENT 'Encrypted with src/lib/encryption.ts',
    `token_expires_at` TIMESTAMP NULL DEFAULT NULL,
    `account_name` VARCHAR(200) DEFAULT NULL,
    `page_name` VARCHAR(200) DEFAULT NULL,
    `currency` VARCHAR(3) DEFAULT NULL COMMENT 'Ad account currency — budgets and spend are in this',
    `timezone_name` VARCHAR(64) DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX `idx_ad_accounts_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ad_campaigns` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `ad_account_id` VARCHAR(36) NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `objective` VARCHAR(50) NOT NULL DEFAULT 'OUTCOME_TRAFFIC',
    `fb_campaign_id` VARCHAR(50) DEFAULT NULL COMMENT 'Returned by Facebook after creation',
    `status` ENUM('draft','pending_review','active','paused','archived') NOT NULL DEFAULT 'draft',
    `daily_budget` DECIMAL(12,2) NOT NULL,
    `created_by_type` ENUM('ai','employee') NOT NULL DEFAULT 'ai',
    `created_by` VARCHAR(36) DEFAULT NULL COMMENT 'employee_id if created_by_type = employee',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`ad_account_id`) REFERENCES `ad_accounts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    INDEX `idx_ad_campaigns_status` (`status`),
    INDEX `idx_ad_campaigns_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ad_sets` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `campaign_id` VARCHAR(36) NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `fb_adset_id` VARCHAR(50) DEFAULT NULL,
    `targeting` LONGTEXT NOT NULL COMMENT 'JSON: {"countries":["PK"],"age_min":18,"age_max":55,"genders":[]}',
    `daily_budget` DECIMAL(12,2) NOT NULL,
    `status` ENUM('draft','pending_review','active','paused','archived') NOT NULL DEFAULT 'draft',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`campaign_id`) REFERENCES `ad_campaigns`(`id`) ON DELETE CASCADE,
    INDEX `idx_ad_sets_campaign` (`campaign_id`),
    INDEX `idx_ad_sets_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ads` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `ad_set_id` VARCHAR(36) NOT NULL,
    `product_id` VARCHAR(36) DEFAULT NULL COMMENT 'Nullable — ad may not be tied to a single product',
    `fb_ad_id` VARCHAR(50) DEFAULT NULL,
    `fb_creative_id` VARCHAR(50) DEFAULT NULL,
    `headline` VARCHAR(255) NOT NULL,
    `primary_text` TEXT NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL COMMENT 'Link description under the headline',
    `cta` VARCHAR(30) NOT NULL DEFAULT 'SHOP_NOW',
    `image_url` VARCHAR(500) DEFAULT NULL,
    `destination_url` VARCHAR(500) DEFAULT NULL COMMENT 'Where the ad links to (product page)',
    `language_code` VARCHAR(10) NOT NULL DEFAULT 'en',
    `status` ENUM('pending_review','approved','rejected','active','paused','archived') NOT NULL DEFAULT 'pending_review',
    `generated_by_type` ENUM('ai','employee') NOT NULL DEFAULT 'ai',
    `ai_prompt_snapshot` TEXT DEFAULT NULL COMMENT 'What was sent to the AI, for audit/debug',
    `inspiration_ad_ids` TEXT DEFAULT NULL COMMENT 'JSON array of competitor_ads.id used as inspiration',
    `publish_error` VARCHAR(1000) DEFAULT NULL COMMENT 'Last Facebook error when publishing',
    `approved_by` VARCHAR(36) DEFAULT NULL,
    `approved_at` TIMESTAMP NULL DEFAULT NULL,
    `rejected_reason` TEXT DEFAULT NULL,
    `created_by` VARCHAR(36) DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`ad_set_id`) REFERENCES `ad_sets`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    INDEX `idx_ads_status` (`status`),
    INDEX `idx_ads_product` (`product_id`),
    INDEX `idx_ads_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ad_insights` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `ad_id` VARCHAR(36) NOT NULL,
    `date` DATE NOT NULL,
    `impressions` INT DEFAULT 0,
    `reach` INT DEFAULT 0,
    `clicks` INT DEFAULT 0,
    `spend` DECIMAL(12,2) DEFAULT 0.00,
    `conversions` INT DEFAULT 0,
    `conversion_value` DECIMAL(12,2) DEFAULT 0.00,
    `ctr` DECIMAL(8,4) DEFAULT 0.0000,
    `cpc` DECIMAL(10,2) DEFAULT 0.00,
    `roas` DECIMAL(10,2) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`ad_id`) REFERENCES `ads`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `uk_ad_date` (`ad_id`, `date`),
    INDEX `idx_ad_insights_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Single-row settings table, same pattern as `site_settings`
CREATE TABLE IF NOT EXISTS `autopilot_settings` (
    `id` TINYINT PRIMARY KEY DEFAULT 1,
    `auto_approve_enabled` BOOLEAN DEFAULT FALSE,
    `auto_approve_after_manual_count` INT DEFAULT 50 COMMENT 'Manually-approved ads required before auto-approve can be switched on',
    `max_daily_budget_per_ad` DECIMAL(12,2) DEFAULT 500.00,
    `max_active_ads` INT DEFAULT 20,
    `allowed_countries` LONGTEXT DEFAULT NULL COMMENT 'JSON array of ISO country codes, e.g. ["PK"]',
    `auto_pause_roas_threshold` DECIMAL(6,2) DEFAULT 1.00 COMMENT 'Auto-pause an ad if ROAS falls below this after min spend',
    `auto_pause_min_spend` DECIMAL(12,2) DEFAULT 2000.00 COMMENT 'Minimum spend before the ROAS auto-pause rule applies',
    `ai_provider` ENUM('deepseek','anthropic') NOT NULL DEFAULT 'deepseek',
    `ai_model` VARCHAR(100) DEFAULT NULL COMMENT 'Empty = provider default',
    `ai_api_key_encrypted` TEXT DEFAULT NULL,
    `ad_library_token_encrypted` TEXT DEFAULT NULL COMMENT 'Optional; Ad Library API token (else the ad account token is used)',
    `last_autopilot_run_at` TIMESTAMP NULL DEFAULT NULL,
    `last_insights_sync_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `single_row_autopilot` CHECK (`id` = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `autopilot_settings` (`id`, `allowed_countries`) VALUES (1, '["PK"]');

-- Competitors being watched (a brand / Facebook page)
CREATE TABLE IF NOT EXISTS `ad_competitors` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `name` VARCHAR(200) NOT NULL,
    `fb_page_id` VARCHAR(50) DEFAULT NULL COMMENT 'Lets the Ad Library search this page directly',
    `page_url` VARCHAR(500) DEFAULT NULL,
    `website_url` VARCHAR(500) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_by` VARCHAR(36) DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`created_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    INDEX `idx_ad_competitors_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Saved competitor ads (from the Ad Library API or entered by hand)
CREATE TABLE IF NOT EXISTS `competitor_ads` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `competitor_id` VARCHAR(36) DEFAULT NULL,
    `source` ENUM('ad_library_api','manual') NOT NULL DEFAULT 'manual',
    `library_ad_id` VARCHAR(50) DEFAULT NULL COMMENT 'Ad Library archive id',
    `page_name` VARCHAR(200) DEFAULT NULL,
    `headline` VARCHAR(500) DEFAULT NULL,
    `primary_text` TEXT DEFAULT NULL,
    `description` VARCHAR(500) DEFAULT NULL,
    `cta` VARCHAR(50) DEFAULT NULL,
    `image_url` VARCHAR(500) DEFAULT NULL,
    `snapshot_url` VARCHAR(1000) DEFAULT NULL COMMENT 'Ad Library snapshot / ad link',
    `landing_url` VARCHAR(1000) DEFAULT NULL,
    `platforms` VARCHAR(255) DEFAULT NULL COMMENT 'Comma list: facebook, instagram…',
    `countries` VARCHAR(255) DEFAULT NULL,
    `started_at` DATE DEFAULT NULL,
    `ended_at` DATE DEFAULT NULL,
    `ai_analysis` LONGTEXT DEFAULT NULL COMMENT 'JSON from analyzeCompetitorAd',
    `analyzed_at` TIMESTAMP NULL DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `raw` LONGTEXT DEFAULT NULL COMMENT 'Original Ad Library API record (JSON)',
    `created_by` VARCHAR(36) DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`competitor_id`) REFERENCES `ad_competitors`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `uk_library_ad` (`library_ad_id`),
    INDEX `idx_competitor_ads_competitor` (`competitor_id`),
    INDEX `idx_competitor_ads_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
