-- Crawl Engine schema migration

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `crawl_runs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `project_id` INT(11) NOT NULL,
  `domain` VARCHAR(255) NOT NULL,
  `status` ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
  `started_at` DATETIME NOT NULL,
  `finished_at` DATETIME DEFAULT NULL,
  `total_pages` INT(11) NOT NULL DEFAULT 0,
  `error_message` TEXT DEFAULT NULL,
  `max_depth` INT(11) NOT NULL DEFAULT 2,
  `max_pages` INT(11) NOT NULL DEFAULT 200,
  `concurrency` INT(11) NOT NULL DEFAULT 5,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_crawl_runs_project_id` (`project_id`),
  INDEX `idx_crawl_runs_status` (`status`),
  CONSTRAINT `fk_crawl_runs_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crawl_pages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `crawl_run_id` INT(11) NOT NULL,
  `url` VARCHAR(2083) NOT NULL,
  `normalized_url` VARCHAR(2083) NOT NULL,
  `status_code` SMALLINT DEFAULT NULL,
  `content_type` VARCHAR(255) DEFAULT NULL,
  `title` VARCHAR(512) DEFAULT NULL,
  `title_length` INT(11) DEFAULT 0,
  `meta_description` TEXT DEFAULT NULL,
  `meta_description_length` INT(11) DEFAULT 0,
  `h1` VARCHAR(512) DEFAULT NULL,
  `h2_list` JSON DEFAULT NULL,
  `h3_list` JSON DEFAULT NULL,
  `canonical_url` VARCHAR(2083) DEFAULT NULL,
  `meta_robots` VARCHAR(255) DEFAULT NULL,
  `x_robots_tag` VARCHAR(255) DEFAULT NULL,
  `word_count` INT(11) DEFAULT 0,
  `response_time` INT(11) DEFAULT 0,
  `depth` INT(11) DEFAULT 0,
  `content_hash` CHAR(40) DEFAULT NULL,
  `near_duplicate_hash` CHAR(64) DEFAULT NULL,
  `json_ld_schema` JSON DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_crawl_pages_run_url` (`crawl_run_id`, `url`),
  INDEX `idx_crawl_pages_run_id` (`crawl_run_id`),
  INDEX `idx_crawl_pages_normalized_url` (`normalized_url`),
  INDEX `idx_crawl_pages_status_code` (`status_code`),
  INDEX `idx_crawl_pages_depth` (`depth`),
  CONSTRAINT `fk_crawl_pages_run` FOREIGN KEY (`crawl_run_id`) REFERENCES `crawl_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crawl_links` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `crawl_run_id` INT(11) NOT NULL,
  `from_url` VARCHAR(2083) NOT NULL,
  `to_url` VARCHAR(2083) NOT NULL,
  `anchor_text` VARCHAR(1024) DEFAULT NULL,
  `is_internal` TINYINT(1) NOT NULL DEFAULT 1,
  `is_nofollow` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_crawl_links_run_id` (`crawl_run_id`),
  INDEX `idx_crawl_links_to_url` (`to_url`),
  INDEX `idx_crawl_links_from_url` (`from_url`),
  INDEX `idx_crawl_links_internal` (`crawl_run_id`, `is_internal`),
  CONSTRAINT `fk_crawl_links_run` FOREIGN KEY (`crawl_run_id`) REFERENCES `crawl_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crawl_images` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `crawl_run_id` INT(11) NOT NULL,
  `page_url` VARCHAR(2083) NOT NULL,
  `image_url` VARCHAR(2083) NOT NULL,
  `alt_text` VARCHAR(1024) DEFAULT NULL,
  `is_missing_alt` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_crawl_images_run_id` (`crawl_run_id`),
  INDEX `idx_crawl_images_page_url` (`page_url`),
  CONSTRAINT `fk_crawl_images_run` FOREIGN KEY (`crawl_run_id`) REFERENCES `crawl_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crawl_queue` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `crawl_run_id` INT(11) NOT NULL,
  `url` VARCHAR(2083) NOT NULL,
  `normalized_url` VARCHAR(2083) NOT NULL,
  `depth` INT(11) NOT NULL DEFAULT 0,
  `status` ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
  `attempts` INT(11) NOT NULL DEFAULT 0,
  `last_error` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_crawl_queue_run_normalized_url` (`crawl_run_id`, `normalized_url`),
  INDEX `idx_crawl_queue_run_id` (`crawl_run_id`),
  INDEX `idx_crawl_queue_status` (`status`),
  CONSTRAINT `fk_crawl_queue_run` FOREIGN KEY (`crawl_run_id`) REFERENCES `crawl_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
