-- SEO Metrics Tracking Table
CREATE TABLE IF NOT EXISTS `seo_metrics` (
  `metric_id` int(11) NOT NULL AUTO_INCREMENT,
  `page_url` varchar(500) NOT NULL,
  `page_type` varchar(50) DEFAULT NULL,
  `score` int(11) DEFAULT 0,
  `grade` varchar(5) DEFAULT NULL,
  `word_count` int(11) DEFAULT 0,
  `keyword_density` decimal(5,2) DEFAULT 0.00,
  `title_length` int(11) DEFAULT 0,
  `h1_count` int(11) DEFAULT 0,
  `h2_count` int(11) DEFAULT 0,
  `images_count` int(11) DEFAULT 0,
  `images_with_alt` int(11) DEFAULT 0,
  `internal_links` int(11) DEFAULT 0,
  `external_links` int(11) DEFAULT 0,
  `has_canonical` tinyint(1) DEFAULT 0,
  `has_schema` tinyint(1) DEFAULT 0,
  `has_og_tags` tinyint(1) DEFAULT 0,
  `tracked_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`metric_id`),
  UNIQUE KEY `page_url` (`page_url`),
  KEY `score` (`score`),
  KEY `tracked_at` (`tracked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SEO Keywords Tracking
CREATE TABLE IF NOT EXISTS `seo_keywords` (
  `keyword_id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(255) NOT NULL,
  `page_url` varchar(500) NOT NULL,
  `target_position` int(11) DEFAULT NULL,
  `current_position` int(11) DEFAULT NULL,
  `search_volume` int(11) DEFAULT 0,
  `difficulty` int(11) DEFAULT 0,
  `tracked_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`keyword_id`),
  KEY `keyword` (`keyword`),
  KEY `page_url` (`page_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SEO Performance History
CREATE TABLE IF NOT EXISTS `seo_performance` (
  `performance_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `organic_traffic` int(11) DEFAULT 0,
  `impressions` int(11) DEFAULT 0,
  `clicks` int(11) DEFAULT 0,
  `ctr` decimal(5,2) DEFAULT 0.00,
  `avg_position` decimal(5,2) DEFAULT 0.00,
  `indexed_pages` int(11) DEFAULT 0,
  `backlinks` int(11) DEFAULT 0,
  `domain_authority` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`performance_id`),
  UNIQUE KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SEO Issues Tracking
CREATE TABLE IF NOT EXISTS `seo_issues` (
  `issue_id` int(11) NOT NULL AUTO_INCREMENT,
  `page_url` varchar(500) NOT NULL,
  `issue_type` varchar(100) NOT NULL,
  `severity` enum('low','medium','high','critical') DEFAULT 'medium',
  `description` text,
  `recommendation` text,
  `status` enum('open','in_progress','resolved','ignored') DEFAULT 'open',
  `detected_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` datetime DEFAULT NULL,
  PRIMARY KEY (`issue_id`),
  KEY `page_url` (`page_url`),
  KEY `status` (`status`),
  KEY `severity` (`severity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
