-- AI Prompt Research Migration Script for Savit
-- This script adds the necessary tables for the Research module

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. Table: brand_prompts (Research Queries)
CREATE TABLE IF NOT EXISTS `brand_prompts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL COMMENT 'Refers to projects.id',
  `prompt_text` text NOT NULL,
  `ai_volume` int(11) DEFAULT '0',
  `visibility_score` int(11) DEFAULT '0',
  `brand_mentions` int(11) DEFAULT '0',
  `domain_citations` int(11) DEFAULT '0',
  `total_citations` int(11) DEFAULT '0',
  `intent_info` int(11) DEFAULT '0',
  `intent_nav` int(11) DEFAULT '0',
  `intent_comm` int(11) DEFAULT '0',
  `intent_tran` int(11) DEFAULT '0',
  `intent_task` int(11) DEFAULT '0',
  `country_code` varchar(5) DEFAULT 'IN',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `brand_id` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Table: prompt_runs (Results of Prompt Research)
CREATE TABLE IF NOT EXISTS `prompt_runs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prompt_id` int(11) NOT NULL,
  `model` varchar(100) DEFAULT NULL,
  `ai_response` longtext,
  `snapshot_url` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `prompt_id` (`prompt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Table: prompt_run_links (Citations within AI response)
CREATE TABLE IF NOT EXISTS `prompt_run_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `run_id` int(11) NOT NULL,
  `rank` int(11) DEFAULT NULL,
  `title` varchar(500) DEFAULT NULL,
  `url` text,
  `domain` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `run_id` (`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Table: brand_variations (Alternative names for the brand/project)
CREATE TABLE IF NOT EXISTS `brand_variations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL,
  `variation_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_id` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Table: domain_variations (Alternative domains for the brand/project)
CREATE TABLE IF NOT EXISTS `domain_variations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL,
  `variation_domain` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_id` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Table: competitors (Tracked competitors per project)
CREATE TABLE IF NOT EXISTS `competitors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL,
  `competitor_name` varchar(255) NOT NULL,
  `competitor_url` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_id` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Table: keyword_ranks (Historical rank data)
CREATE TABLE IF NOT EXISTS `keyword_ranks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `keyword` varchar(255) NOT NULL,
  `rank` int(11) DEFAULT NULL,
  `url` text,
  `source` varchar(50) DEFAULT 'google',
  `check_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `check_date` (`check_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Table: api_usage_logs (To track DataForSEO costs)
CREATE TABLE IF NOT EXISTS `api_usage_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `api_name` varchar(100) NOT NULL,
  `endpoint` varchar(255) DEFAULT NULL,
  `params` text,
  `http_code` int(11) DEFAULT NULL,
  `cost` decimal(10,5) DEFAULT '0.00000',
  `duration` decimal(10,3) DEFAULT '0.000',
  `project_id` int(11) DEFAULT NULL,
  `action_type` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 9. Table: ai_research_models (Cached list of available LLMs)
CREATE TABLE IF NOT EXISTS `ai_research_models` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `platform` varchar(50) NOT NULL,
  `model_name` varchar(100) NOT NULL,
  `label` varchar(100) NOT NULL,
  `is_latest` tinyint(1) DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform_model` (`platform`,`model_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
