USE `se_spiderdb`;

CREATE TABLE IF NOT EXISTS `se_pdf_doc` (
  `doc_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `text_sha256` BINARY(32) NOT NULL,
  `bin_sha256` BINARY(32) NULL,
  `bin_path` VARCHAR(1024) NULL,
  `bin_size` BIGINT UNSIGNED NULL,
  `carrier_kind` VARCHAR(16) NULL,
  `http_etag` VARCHAR(255) NULL,
  `http_last_modified` VARCHAR(255) NULL,
  `title` VARCHAR(512) NULL,
  `author` VARCHAR(255) NULL,
  `subject` VARCHAR(255) NULL,
  `lang` CHAR(5) NULL,
  `pages` INT NULL,
  `fulltext` MEDIUMTEXT NULL,
  `snippet` TEXT NULL,
  `descr` VARCHAR(1024) NULL,
  `created_raw` VARCHAR(128) NULL,
  `modified_raw` VARCHAR(128) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`doc_id`),
  UNIQUE KEY `uq_text_sha256` (`text_sha256`),
  KEY `idx_bin_sha256` (`bin_sha256`),
  FULLTEXT KEY `ft_pdf_fulltext` (`fulltext`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `se_pdf_map` (
  `hostname` VARCHAR(100) NOT NULL,
  `page` VARCHAR(255) NOT NULL,
  `url_id` BIGINT UNSIGNED NULL,
  `doc_id` BIGINT UNSIGNED NOT NULL,
  `first_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`hostname`, `page`),
  UNIQUE KEY `uq_url_id` (`url_id`),
  KEY `idx_doc_id` (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `se_pdf_version` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `hostname` VARCHAR(100) NOT NULL,
  `page` VARCHAR(255) NOT NULL,
  `url_id` BIGINT UNSIGNED NULL,
  `doc_id` BIGINT UNSIGNED NOT NULL,
  `version` INT NOT NULL,
  `change_type` ENUM('NEW','BIN_CHANGED','TEXT_CHANGED','META_CHANGED') NOT NULL,
  `indexed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_url_version` (`hostname`, `page`, `version`),
  KEY `idx_doc_id` (`doc_id`),
  KEY `idx_url_id` (`url_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `se_pdf_drift_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `snapshot_day` DATE NOT NULL,
  `hostname` VARCHAR(100) NOT NULL,
  `page` VARCHAR(255) NOT NULL,
  `doc_id` BIGINT UNSIGNED NULL,
  `prev_doc_id` BIGINT UNSIGNED NULL,
  `version` INT NULL,
  `prev_version` INT NULL,
  `drift_pct` INT NOT NULL DEFAULT 0,
  `severity` ENUM('Stable','Minor','Medium','Major') NOT NULL DEFAULT 'Stable',
  `source_query_hash` CHAR(40) NOT NULL,
  `captured_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pdf_drift_day` (`hostname`,`page`,`snapshot_day`),
  KEY `idx_pdf_drift_doc` (`doc_id`),
  KEY `idx_pdf_drift_day` (`snapshot_day`),
  KEY `idx_pdf_drift_query` (`source_query_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `se_pdf_similarity_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `snapshot_day` DATE NOT NULL,
  `doc_id_a` BIGINT UNSIGNED NOT NULL,
  `doc_id_b` BIGINT UNSIGNED NOT NULL,
  `similarity_pct` INT NOT NULL,
  `severity` ENUM('Low','Medium','High','Critical') NOT NULL DEFAULT 'Low',
  `source_query_hash` CHAR(40) NOT NULL,
  `captured_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pdf_similarity_day` (`doc_id_a`,`doc_id_b`,`snapshot_day`),
  KEY `idx_pdf_similarity_day` (`snapshot_day`),
  KEY `idx_pdf_similarity_query` (`source_query_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
