-- ClientRadar - Radar Search
-- Migration: 001_radar_tables.sql
-- MySQL / InnoDB / utf8mb4

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS cr_radar_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  email VARCHAR(190) NOT NULL,
  specialty VARCHAR(30) NOT NULL,
  skills TEXT NOT NULL,
  level VARCHAR(20) NOT NULL,
  work_type VARCHAR(20) NOT NULL,
  budget_min INT UNSIGNED NOT NULL DEFAULT 0,
  budget_max INT UNSIGNED NOT NULL DEFAULT 0,
  country VARCHAR(80) NULL,
  timezone VARCHAR(80) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cr_radar_users_email (email),
  KEY idx_cr_radar_users_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cr_radar_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_email VARCHAR(190) NOT NULL,
  sources_scanned TEXT NOT NULL,
  results_count INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'queued',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cr_radar_runs_user_email_created (user_email, created_at),
  KEY idx_cr_radar_runs_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cr_radar_results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  title VARCHAR(255) NOT NULL,
  budget VARCHAR(64) NOT NULL DEFAULT '',
  currency VARCHAR(10) NOT NULL DEFAULT '',
  source VARCHAR(50) NOT NULL,
  posted_at DATETIME NULL,
  url VARCHAR(2048) NOT NULL,
  summary TEXT NOT NULL,
  raw_hash CHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cr_radar_results_raw_hash (raw_hash),
  KEY idx_cr_radar_results_run_id (run_id),
  KEY idx_cr_radar_results_score (score),
  KEY idx_cr_radar_results_source (source),
  KEY idx_cr_radar_results_posted_at (posted_at),
  CONSTRAINT fk_cr_radar_results_run
    FOREIGN KEY (run_id) REFERENCES cr_radar_runs(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;