-- AI Overview: Prompt tracking per project
CREATE TABLE IF NOT EXISTS project_prompts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    prompt_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    brand_mentions INT DEFAULT 0,
    domain_citations INT DEFAULT 0,
    total_citations INT DEFAULT 0,
    competitors_mentions INT DEFAULT 0,
    ai_volume INT DEFAULT 0,
    intent_info INT DEFAULT 25,
    intent_nav INT DEFAULT 25,
    intent_comm INT DEFAULT 25,
    intent_tran INT DEFAULT 25,
    intent_task INT DEFAULT 0,
    visibility_score INT DEFAULT 0,
    country_code VARCHAR(20) DEFAULT 'IN',
    CONSTRAINT fk_pp_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_pp_project (project_id),
    INDEX idx_pp_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Overview: Run history per prompt
CREATE TABLE IF NOT EXISTS project_prompt_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prompt_id INT NOT NULL,
    model VARCHAR(255) NOT NULL,
    ai_response TEXT,
    snapshot_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ppr_prompt FOREIGN KEY (prompt_id) REFERENCES project_prompts(id) ON DELETE CASCADE,
    INDEX idx_ppr_prompt (prompt_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Overview: Extracted links/citations per run
CREATE TABLE IF NOT EXISTS project_prompt_run_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    run_id INT NOT NULL,
    `rank` INT DEFAULT NULL,
    title VARCHAR(500) DEFAULT NULL,
    url TEXT,
    domain VARCHAR(255) DEFAULT NULL,
    CONSTRAINT fk_pprl_run FOREIGN KEY (run_id) REFERENCES project_prompt_runs(id) ON DELETE CASCADE,
    INDEX idx_pprl_run (run_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Overview: Competitor tracking per project
CREATE TABLE IF NOT EXISTS project_competitors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    competitor_name VARCHAR(255) NOT NULL,
    competitor_url VARCHAR(255) NOT NULL,
    CONSTRAINT fk_pc_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_pc_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Overview: Brand name variations for accurate mention matching
CREATE TABLE IF NOT EXISTS project_brand_variations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    variation_name VARCHAR(255) NOT NULL,
    CONSTRAINT fk_pbv_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_pbv_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Overview: Domain variations for accurate citation matching
CREATE TABLE IF NOT EXISTS project_domain_variations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    variation_domain VARCHAR(255) NOT NULL,
    CONSTRAINT fk_pdv_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_pdv_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
