-- Migration: Lead Generation Engine tables
-- Created: 2026-07-23
-- Creates: ad_templates, ad_creatives, ad_keywords, lead_attribution,
--          optimization_rules, optimization_logs

-- =============================================================================
-- Ad Templates — reusable campaign structures by vertical
-- =============================================================================
CREATE TABLE IF NOT EXISTS ad_templates (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    vertical VARCHAR(50) NOT NULL,
    description TEXT DEFAULT '',
    budget_recommendation FLOAT,
    target_cpa FLOAT,
    bidding_strategy VARCHAR(50) DEFAULT 'target_cpa',
    structure_json JSON,
    is_active BOOLEAN NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS ix_ad_templates_slug ON ad_templates (slug);
CREATE INDEX IF NOT EXISTS ix_ad_templates_vertical ON ad_templates (vertical);

-- =============================================================================
-- Ad Creatives — copy variants for A/B testing
-- =============================================================================
CREATE TABLE IF NOT EXISTS ad_creatives (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36),
    vertical VARCHAR(50) NOT NULL,
    service_type VARCHAR(100) NOT NULL,
    ad_type VARCHAR(50) NOT NULL,
    headlines JSON DEFAULT '[]',
    descriptions JSON DEFAULT '[]',
    display_url VARCHAR(500) DEFAULT '',
    final_url VARCHAR(500) DEFAULT '',
    image_url VARCHAR(500) DEFAULT '',
    call_to_action VARCHAR(100) DEFAULT '',
    total_impressions BIGINT DEFAULT 0,
    total_clicks INTEGER DEFAULT 0,
    total_conversions FLOAT DEFAULT 0.0,
    avg_ctr FLOAT,
    is_active BOOLEAN NOT NULL DEFAULT 1,
    is_winner BOOLEAN DEFAULT 0,
    metadata_json JSON DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS ix_ad_creatives_company_id ON ad_creatives (company_id);
CREATE INDEX IF NOT EXISTS ix_ad_creatives_vertical ON ad_creatives (vertical);

-- =============================================================================
-- Ad Keywords — keyword tracking per campaign/ad group
-- =============================================================================
CREATE TABLE IF NOT EXISTS ad_keywords (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36) NOT NULL,
    source_service VARCHAR(50) NOT NULL,
    external_campaign_id VARCHAR(128) NOT NULL,
    external_ad_group_id VARCHAR(128),
    text VARCHAR(255) NOT NULL,
    match_type VARCHAR(20) DEFAULT 'phrase',
    max_cpc FLOAT,
    target_cpa FLOAT,
    status VARCHAR(20) DEFAULT 'enabled',
    total_impressions BIGINT DEFAULT 0,
    total_clicks INTEGER DEFAULT 0,
    total_spend FLOAT DEFAULT 0.0,
    total_conversions FLOAT DEFAULT 0.0,
    avg_position FLOAT,
    avg_ctr FLOAT,
    metadata_json JSON DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
    UNIQUE (company_id, source_service, external_ad_group_id, text, match_type)
);

CREATE INDEX IF NOT EXISTS ix_ad_keywords_company_id ON ad_keywords (company_id);
CREATE INDEX IF NOT EXISTS ix_ad_keywords_external_campaign ON ad_keywords (external_campaign_id);
CREATE INDEX IF NOT EXISTS ix_ad_keywords_external_ad_group ON ad_keywords (external_ad_group_id);

-- =============================================================================
-- Lead Attribution — UTM → lead → deal tracking
-- =============================================================================
CREATE TABLE IF NOT EXISTS lead_attribution (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36) NOT NULL,
    external_lead_id VARCHAR(200),
    lead_source VARCHAR(50) DEFAULT '',
    utm_source VARCHAR(100) DEFAULT '',
    utm_campaign VARCHAR(255) DEFAULT '',
    utm_ad_group VARCHAR(255) DEFAULT '',
    utm_ad VARCHAR(255) DEFAULT '',
    utm_content VARCHAR(255) DEFAULT '',
    utm_medium VARCHAR(50) DEFAULT '',
    utm_term VARCHAR(255) DEFAULT '',
    click_date TIMESTAMP,
    lead_date TIMESTAMP,
    click_cost FLOAT,
    total_ad_spend FLOAT,
    external_deal_id VARCHAR(200),
    deal_amount FLOAT,
    deal_closed_date TIMESTAMP,
    roas FLOAT,
    attribution_status VARCHAR(20) DEFAULT 'click',
    attribution_window_days INTEGER DEFAULT 30,
    metadata_json JSON DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS ix_lead_attribution_company_id ON lead_attribution (company_id);
CREATE INDEX IF NOT EXISTS ix_lead_attribution_external_lead ON lead_attribution (external_lead_id);
CREATE INDEX IF NOT EXISTS ix_lead_attribution_utm_campaign ON lead_attribution (utm_campaign);

-- =============================================================================
-- Optimization Rules — auto-optimization config
-- =============================================================================
CREATE TABLE IF NOT EXISTS optimization_rules (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    rule_type VARCHAR(50) NOT NULL,
    target_level VARCHAR(20) DEFAULT 'campaign',
    params_json JSON DEFAULT '{}',
    is_active BOOLEAN NOT NULL DEFAULT 1,
    last_run_at TIMESTAMP,
    total_actions INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS ix_optimization_rules_company_id ON optimization_rules (company_id);
CREATE INDEX IF NOT EXISTS ix_optimization_rules_rule_type ON optimization_rules (rule_type);

-- =============================================================================
-- Optimization Logs — audit trail for auto-changes
-- =============================================================================
CREATE TABLE IF NOT EXISTS optimization_logs (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36) NOT NULL,
    rule_id CHAR(36),
    source_service VARCHAR(50) NOT NULL,
    target_type VARCHAR(20) NOT NULL,
    target_id VARCHAR(128) NOT NULL,
    target_name VARCHAR(255) DEFAULT '',
    action VARCHAR(50) NOT NULL,
    before_json JSON DEFAULT '{}',
    after_json JSON DEFAULT '{}',
    reason TEXT DEFAULT '',
    status VARCHAR(20) DEFAULT 'success',
    error_message TEXT DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
    FOREIGN KEY (rule_id) REFERENCES optimization_rules (id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS ix_optimization_logs_company_id ON optimization_logs (company_id);
CREATE INDEX IF NOT EXISTS ix_optimization_logs_rule_id ON optimization_logs (rule_id);

-- =============================================================================
-- Track migration
-- =============================================================================
INSERT OR IGNORE INTO schema_migrations (version) VALUES ('007');
