-- =============================================================================
-- BTH Spider - Complete Database Schema
-- Database: skipsweb_bth  (create separately, then USE it)
-- MySQL 5.7+ / MariaDB 10.3+
-- Run this file once on a fresh database.
-- =============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

-- =============================================================================
-- GEOGRAPHIC
-- =============================================================================

CREATE TABLE IF NOT EXISTS `bth_states` (
  `id`          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  `state_code`  VARCHAR(5)       NOT NULL COMMENT '2-letter code, e.g. CA',
  `state_name`  VARCHAR(100)     NOT 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_state_code` (`state_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `bth_cities` (
  `id`          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  `state_id`    INT UNSIGNED     NOT NULL,
  `city_name`   VARCHAR(150)     NOT 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_city_state`   (`state_id`, `city_name`),
  KEY `idx_state_id`           (`state_id`),
  CONSTRAINT `fk_city_state` FOREIGN KEY (`state_id`) REFERENCES `bth_states` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- PROVIDERS
-- =============================================================================

CREATE TABLE IF NOT EXISTS `bth_providers` (
  `id`                INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  `ter_id`            INT UNSIGNED     NOT NULL COMMENT 'TER provider ID (source of truth)',

  -- Identity
  `name`              VARCHAR(255)     DEFAULT NULL,
  `description`       TEXT             DEFAULT NULL,
  `city_id`           INT UNSIGNED     DEFAULT NULL,

  -- Stats
  `number_of_reviews` INT UNSIGNED     DEFAULT 0,
  `last_review_date`  DATETIME         DEFAULT NULL,
  `ter_profile_url`   VARCHAR(500)     DEFAULT NULL,

  -- Physical attributes (stored as TEXT to handle formatted ranges like "5'3"-5'5"")
  `age`               TEXT             DEFAULT NULL,
  `height`            TEXT             DEFAULT NULL,
  `build`             TEXT             DEFAULT NULL,
  `ethnicity`         TEXT             DEFAULT NULL,
  `nationality`       TEXT             DEFAULT NULL,
  `eye_color`         TEXT             DEFAULT NULL,
  `hair_color`        TEXT             DEFAULT NULL,
  `hair_type`         TEXT             DEFAULT NULL,
  `hair_length`       TEXT             DEFAULT NULL,
  `breast_size`       TEXT             DEFAULT NULL,
  `piercings`         TEXT             DEFAULT NULL,
  `tattoos`           TEXT             DEFAULT NULL,

  -- Service info
  `incall_outcall`    TEXT             DEFAULT NULL,
  `service`           TEXT             DEFAULT NULL,
  `availability`      TEXT             DEFAULT NULL,
  `on_time`           TEXT             DEFAULT NULL,
  `languages`         TEXT             DEFAULT NULL,
  `porn_star`         TEXT             DEFAULT NULL,
  `smokes`            TEXT             DEFAULT NULL,
  `phone_type`        TEXT             DEFAULT NULL,

  `active`            TINYINT(1)       NOT NULL DEFAULT 1,
  `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_ter_id`              (`ter_id`),
  KEY `idx_city_id`                   (`city_id`),
  KEY `idx_active`                    (`active`),
  KEY `idx_number_of_reviews`         (`number_of_reviews`),
  KEY `idx_last_review_date`          (`last_review_date` DESC),
  CONSTRAINT `fk_provider_city` FOREIGN KEY (`city_id`) REFERENCES `bth_cities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Tracks all ter_ids seen in sitemaps — the canonical "active" list
CREATE TABLE IF NOT EXISTS `bth_active_ids` (
  `ter_id`            INT UNSIGNED     NOT NULL,
  `last_review_date`  DATETIME         DEFAULT NULL,
  `last_seen_at`      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- CONTACTS (phone / email separated from providers table)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `bth_provider_contacts` (
  `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `provider_id`    INT UNSIGNED  NOT NULL,
  `ter_id`         INT UNSIGNED  NOT NULL,
  `contact_type`   ENUM('phone', 'email')  NOT NULL,
  `contact_value`  TEXT          NOT NULL,
  `source`         VARCHAR(100)  DEFAULT NULL COMMENT 'e.g. ter, eros_com',
  `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_provider_contact` (`provider_id`, `contact_type`, `contact_value`(100)),
  KEY `idx_provider_id`            (`provider_id`),
  CONSTRAINT `fk_contact_provider` FOREIGN KEY (`provider_id`) REFERENCES `bth_providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- IMAGES
-- =============================================================================

CREATE TABLE IF NOT EXISTS `bth_provider_images` (
  `id`                   INT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `provider_id`          INT UNSIGNED   NOT NULL,
  `ter_id`               INT UNSIGNED   NOT NULL,
  `original_url`         TEXT           NOT NULL COMMENT 'Source URL image was downloaded from',
  `md5_hash`             VARCHAR(32)    NOT NULL COMMENT 'MD5 of the downloaded file (dedup key)',
  `local_path_relative`  VARCHAR(500)   DEFAULT NULL COMMENT 'e.g. 000/123/45/00012345_abc.avif',
  `local_filename`       VARCHAR(300)   DEFAULT NULL COMMENT 'e.g. 00012345_abc.avif',
  `image_source`         VARCHAR(50)    NOT NULL DEFAULT 'spider' COMMENT 'ter, eros_com, tryst_link, etc.',
  `format`               VARCHAR(10)    NOT NULL DEFAULT 'avif',
  `width`                SMALLINT UNSIGNED DEFAULT 0,
  `height`               SMALLINT UNSIGNED DEFAULT 0,
  `file_size_original`   INT UNSIGNED   DEFAULT 0 COMMENT 'Bytes of original downloaded file',
  `file_size_avif`       INT UNSIGNED   DEFAULT 0,
  `file_size_webp`       INT UNSIGNED   DEFAULT 0,
  `deactivation_reason`  VARCHAR(100)   DEFAULT NULL,
  `active`               TINYINT(1)     NOT NULL DEFAULT 1,
  `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_md5`              (`md5_hash`),
  KEY `idx_provider_id`            (`provider_id`),
  KEY `idx_ter_id`                 (`ter_id`),
  KEY `idx_active`                 (`active`),
  KEY `idx_image_source`           (`image_source`),
  CONSTRAINT `fk_image_provider` FOREIGN KEY (`provider_id`) REFERENCES `bth_providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- URLs that have permanently failed so we don't keep retrying them
CREATE TABLE IF NOT EXISTS `bth_bad_urls` (
  `id`              INT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `url_hash`        VARCHAR(32)    NOT NULL COMMENT 'MD5 of URL (for fast lookup)',
  `url`             TEXT           NOT NULL,
  `failure_reason`  VARCHAR(50)    NOT NULL COMMENT '404, ssl_error, timeout, invalid_size, etc.',
  `failure_count`   INT UNSIGNED   NOT NULL DEFAULT 1,
  `first_seen_at`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at`    DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_url_hash` (`url_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- DOMAINS
-- =============================================================================

-- Registry of all known scrapeable domains and which scraper class handles them
CREATE TABLE IF NOT EXISTS `bth_domains` (
  `id`              INT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `domain_name`     VARCHAR(255)   NOT NULL COMMENT 'e.g. eros.com — no www. or scheme',
  `scraper_class`   VARCHAR(100)   NOT NULL DEFAULT '' COMMENT 'Short class name, e.g. ErosScraper',
  `priority`        INT            NOT NULL DEFAULT 100 COMMENT 'Lower = spidered sooner',
  `is_active`       TINYINT(1)     NOT NULL DEFAULT 1,
  `created_at`      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_domain_name` (`domain_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Links a provider to a specific profile URL on a domain
CREATE TABLE IF NOT EXISTS `bth_provider_domains` (
  `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `provider_id`      INT UNSIGNED  NOT NULL,
  `ter_id`           INT UNSIGNED  NOT NULL,
  `domain_id`        INT UNSIGNED  NOT NULL,
  `profile_url`      TEXT          DEFAULT NULL COMMENT 'Full URL to provider profile on this domain',
  `spider_status`    ENUM('pending','in_progress','completed','failed','skipped') NOT NULL DEFAULT 'pending',
  `error_message`    VARCHAR(500)  DEFAULT NULL,
  `last_spidered_at` DATETIME      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_provider_domain`  (`provider_id`, `domain_id`),
  KEY `idx_spider_status`          (`spider_status`),
  KEY `idx_domain_id`              (`domain_id`),
  KEY `idx_ter_id`                 (`ter_id`),
  CONSTRAINT `fk_pd_provider` FOREIGN KEY (`provider_id`) REFERENCES `bth_providers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pd_domain`   FOREIGN KEY (`domain_id`)   REFERENCES `bth_domains` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Domain health check results
CREATE TABLE IF NOT EXISTS `bth_domain_health` (
  `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `domain_id`        INT UNSIGNED  NOT NULL,
  `dns_status`       VARCHAR(20)   DEFAULT 'unknown' COMMENT 'ok, nxdomain',
  `http_status_code` SMALLINT      DEFAULT 0,
  `is_wordpress`     TINYINT(1)    DEFAULT 0,
  `redirect_url`     VARCHAR(500)  DEFAULT NULL,
  `last_check_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_domain_health` (`domain_id`),
  CONSTRAINT `fk_health_domain` FOREIGN KEY (`domain_id`) REFERENCES `bth_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- LINKS
-- =============================================================================

CREATE TABLE IF NOT EXISTS `bth_provider_links` (
  `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `provider_id`    INT UNSIGNED  NOT NULL,
  `ter_id`         INT UNSIGNED  NOT NULL,
  `link_url`       TEXT          NOT NULL,
  `link_type`      ENUM('social','agency','review','personal') NOT NULL DEFAULT 'personal',
  `source_domain`  VARCHAR(255)  DEFAULT NULL COMMENT 'Which domain this link was found on',
  `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_provider_link_url` (`provider_id`, `link_url`(200)),
  KEY `idx_provider_id`             (`provider_id`),
  CONSTRAINT `fk_link_provider` FOREIGN KEY (`provider_id`) REFERENCES `bth_providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- JOB QUEUE
-- =============================================================================

CREATE TABLE IF NOT EXISTS `bth_job_runs` (
  `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `run_name`       VARCHAR(255)  NOT NULL,
  `run_type`       ENUM('full','incremental','maintenance','manual') NOT NULL DEFAULT 'manual',
  `status`         ENUM('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
  `total_jobs`     INT UNSIGNED  DEFAULT 0,
  `completed_jobs` INT UNSIGNED  DEFAULT 0,
  `failed_jobs`    INT UNSIGNED  DEFAULT 0,
  `skipped_jobs`   INT UNSIGNED  DEFAULT 0,
  `error_log`      TEXT          DEFAULT NULL,
  `started_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ended_at`       DATETIME      DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `bth_jobs` (
  `id`           INT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `run_id`       INT UNSIGNED   DEFAULT NULL,
  `job_type`     VARCHAR(50)    NOT NULL,
  `payload`      JSON           NOT NULL COMMENT 'Job-specific parameters',
  `status`       ENUM('pending','in_progress','completed','failed','skipped') NOT NULL DEFAULT 'pending',
  `priority`     INT            NOT NULL DEFAULT 100 COMMENT 'Lower = processed sooner',
  `attempts`     TINYINT        NOT NULL DEFAULT 0,
  `max_attempts` TINYINT        NOT NULL DEFAULT 3,
  `result_data`  JSON           DEFAULT NULL COMMENT 'Handler return value on success',
  `error_message`TEXT           DEFAULT NULL,
  `started_at`   DATETIME       DEFAULT NULL,
  `ended_at`     DATETIME       DEFAULT NULL,
  `created_at`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_status_type_priority`  (`status`, `job_type`, `priority`, `created_at`),
  KEY `idx_run_id`                (`run_id`),
  CONSTRAINT `fk_job_run` FOREIGN KEY (`run_id`) REFERENCES `bth_job_runs` (`id`) ON SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- CHANGE HISTORY
-- =============================================================================

CREATE TABLE IF NOT EXISTS `bth_provider_history` (
  `id`           INT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `provider_id`  INT UNSIGNED   NOT NULL,
  `ter_id`       INT UNSIGNED   NOT NULL,
  `change_type`  VARCHAR(50)    NOT NULL COMMENT 'new_profile, profile_updated, new_images, etc.',
  `field_name`   VARCHAR(100)   DEFAULT NULL,
  `old_value`    TEXT           DEFAULT NULL,
  `new_value`    TEXT           DEFAULT NULL,
  `detected_at`  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_provider_id`  (`provider_id`),
  KEY `idx_detected_at`  (`detected_at`),
  CONSTRAINT `fk_history_provider` FOREIGN KEY (`provider_id`) REFERENCES `bth_providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- SEED: Register known domains with their scrapers and priority
-- =============================================================================

INSERT IGNORE INTO `bth_domains` (domain_name, scraper_class, priority) VALUES
  ('eros.com',          'ErosScraper',          10),
  ('tryst.link',        'TrystScraper',          20),
  ('slixa.com',         'SlixaScraper',          30),
  ('adultlook.com',     'AdultlookScraper',      40),
  ('adultsearch.com',   'AdultsearchScraper',    50),
  ('onlyfans.com',      'OnlyfansScraper',       60),
  ('listcrawler.com',   'ListcrawlerScraper',    70),
  ('p411.net',          'P411Scraper',           80),
  ('skipthegames.com',  'SkipthegamesScraper',   90),
  ('cityxguide.com',    'CityxguideScraper',    100);

SET foreign_key_checks = 1;
