-- Job Intelligence database schema
-- MySQL 8+ / InnoDB / utf8mb4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------------
-- Users (single-tenant today; org_id columns below keep the door open
-- for multi-tenant workspaces later without a breaking migration).
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                  CHAR(26)     NOT NULL PRIMARY KEY,
    org_id              CHAR(26)     NULL,
    name                VARCHAR(150) NOT NULL,
    email               VARCHAR(190) NOT NULL,
    password_hash       VARCHAR(255) NOT NULL,
    avatar_path         VARCHAR(255) NULL,
    preferred_currency  VARCHAR(8)   NOT NULL DEFAULT 'EUR',
    default_country     VARCHAR(80)  NOT NULL DEFAULT 'Portugal',
    salary_display      ENUM('annual','monthly') NOT NULL DEFAULT 'annual',
    role                ENUM('admin','user') NOT NULL DEFAULT 'user',
    is_active           TINYINT(1)   NOT NULL DEFAULT 1,
    last_login_at       DATETIME     NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS password_resets (
    id             CHAR(26)     NOT NULL PRIMARY KEY,
    user_id        CHAR(26)     NOT NULL,
    token_hash     VARCHAR(255) NOT NULL,
    expires_at     DATETIME     NOT NULL,
    used_at        DATETIME     NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_password_resets_user (user_id),
    CONSTRAINT fk_password_resets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Persistent sessions ("Remember me" + the Security > Sessions list).
CREATE TABLE IF NOT EXISTS user_sessions (
    id             CHAR(26)     NOT NULL PRIMARY KEY,
    user_id        CHAR(26)     NOT NULL,
    selector       VARCHAR(24)  NOT NULL,
    validator_hash VARCHAR(255) NOT NULL,
    user_agent     VARCHAR(255) NULL,
    ip_address     VARCHAR(64)  NULL,
    last_seen_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at     DATETIME     NOT NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sessions_selector (selector),
    KEY idx_sessions_user (user_id),
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- Analyses (the core object). Every row is owned by exactly one user.
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS analyses (
    id                     CHAR(26)     NOT NULL PRIMARY KEY,
    user_id                CHAR(26)     NOT NULL,
    name                   VARCHAR(190) NOT NULL,
    original_file_name     VARCHAR(255) NULL,
    original_job_description MEDIUMTEXT NULL,
    country                VARCHAR(80)  NOT NULL,
    region                 VARCHAR(120) NULL,
    city                   VARCHAR(120) NULL,
    currency               VARCHAR(8)   NOT NULL DEFAULT 'EUR',
    employment_type        ENUM('auto','full_time','contract','part_time') NOT NULL DEFAULT 'auto',
    analysis_depth         ENUM('standard','deep') NOT NULL DEFAULT 'standard',
    status                 ENUM('draft','processing','completed','failed','archived') NOT NULL DEFAULT 'draft',
    failure_reason         VARCHAR(255) NULL,
    is_saved               TINYINT(1)   NOT NULL DEFAULT 0,
    data_freshness_date    DATE         NULL,
    created_at             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at           DATETIME     NULL,
    KEY idx_analyses_user (user_id),
    KEY idx_analyses_user_status (user_id, status),
    KEY idx_analyses_user_created (user_id, created_at),
    CONSTRAINT fk_analyses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS uploaded_documents (
    id              CHAR(26)     NOT NULL PRIMARY KEY,
    analysis_id     CHAR(26)     NOT NULL,
    user_id         CHAR(26)     NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_path     VARCHAR(255) NOT NULL,
    mime_type       VARCHAR(120) NULL,
    size_bytes      INT UNSIGNED NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_docs_analysis (analysis_id),
    KEY idx_docs_user (user_id),
    CONSTRAINT fk_docs_analysis FOREIGN KEY (analysis_id) REFERENCES analyses(id) ON DELETE CASCADE,
    CONSTRAINT fk_docs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_profiles (
    id                  CHAR(26)     NOT NULL PRIMARY KEY,
    analysis_id         CHAR(26)     NOT NULL,
    suggested_title     VARCHAR(190) NOT NULL,
    alternative_titles  JSON         NULL,
    seniority           VARCHAR(80)  NULL,
    occupation_code     VARCHAR(40)  NULL,
    occupation_label    VARCHAR(190) NULL,
    responsibilities    JSON         NULL,
    core_skills         JSON         NULL,
    optional_skills     JSON         NULL,
    technologies        JSON         NULL,
    key_requirements    JSON         NULL,
    experience_min      TINYINT UNSIGNED NULL,
    experience_max      TINYINT UNSIGNED NULL,
    leadership_signals  JSON         NULL,
    match_percent       TINYINT UNSIGNED NULL,
    confidence          ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
    improved_job_description MEDIUMTEXT NULL,
    recruiter_dos       JSON         NULL,
    recruiter_donts     JSON         NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_profiles_analysis (analysis_id),
    CONSTRAINT fk_role_profiles_analysis FOREIGN KEY (analysis_id) REFERENCES analyses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- A benchmark_version captures one point-in-time market snapshot for an
-- analysis. Re-running/refreshing an analysis adds a new version rather
-- than overwriting the previous one, so salary evolution over time can be
-- shown later.
CREATE TABLE IF NOT EXISTS benchmark_versions (
    id             CHAR(26)     NOT NULL PRIMARY KEY,
    analysis_id    CHAR(26)     NOT NULL,
    version_label  VARCHAR(40)  NOT NULL,
    is_current     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_benchmark_versions_analysis (analysis_id),
    CONSTRAINT fk_benchmark_versions_analysis FOREIGN KEY (analysis_id) REFERENCES analyses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS salary_statistics (
    id                   CHAR(26)     NOT NULL PRIMARY KEY,
    benchmark_version_id CHAR(26)     NOT NULL,
    p10                  INT          NULL,
    p25                  INT          NULL,
    p50                  INT          NULL,
    p75                  INT          NULL,
    p90                  INT          NULL,
    mean                 INT          NULL,
    recommended_min      INT          NULL,
    recommended_max      INT          NULL,
    sample_size          INT UNSIGNED NULL,
    salary_sample_size   INT UNSIGNED NULL,
    currency             VARCHAR(8)   NOT NULL DEFAULT 'EUR',
    confidence           ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
    source               ENUM('real_postings','ai_estimate','none') NOT NULL DEFAULT 'none',
    UNIQUE KEY uq_salary_stats_version (benchmark_version_id),
    CONSTRAINT fk_salary_stats_version FOREIGN KEY (benchmark_version_id) REFERENCES benchmark_versions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS market_statistics (
    id                   CHAR(26)     NOT NULL PRIMARY KEY,
    benchmark_version_id CHAR(26)     NOT NULL,
    market_demand        INT UNSIGNED NULL,
    demand_change_pct    DECIMAL(6,2) NULL,
    remote_pct           TINYINT UNSIGNED NULL,
    hybrid_pct           TINYINT UNSIGNED NULL,
    onsite_pct           TINYINT UNSIGNED NULL,
    salary_trend_yoy_pct DECIMAL(6,2) NULL,
    jobs_with_salary     INT UNSIGNED NULL,
    data_period_days     SMALLINT UNSIGNED NULL,
    reconciliation_note  TEXT         NULL,
    eurostat_reference_value  DECIMAL(12,2) NULL,
    eurostat_reference_period VARCHAR(20)   NULL,
    UNIQUE KEY uq_market_stats_version (benchmark_version_id),
    CONSTRAINT fk_market_stats_version FOREIGN KEY (benchmark_version_id) REFERENCES benchmark_versions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS comparable_jobs (
    id                   CHAR(26)     NOT NULL PRIMARY KEY,
    benchmark_version_id CHAR(26)     NOT NULL,
    external_id          VARCHAR(120) NULL,
    provider             VARCHAR(80)  NULL,
    title                VARCHAR(190) NOT NULL,
    company              VARCHAR(190) NULL,
    location             VARCHAR(190) NULL,
    salary_min           INT          NULL,
    salary_max           INT          NULL,
    source_url           VARCHAR(500) NULL,
    published_at         DATE         NULL,
    relevance_score      TINYINT UNSIGNED NULL,
    KEY idx_comparable_jobs_version (benchmark_version_id),
    CONSTRAINT fk_comparable_jobs_version FOREIGN KEY (benchmark_version_id) REFERENCES benchmark_versions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS activity_log (
    id             CHAR(26)     NOT NULL PRIMARY KEY,
    user_id        CHAR(26)     NOT NULL,
    analysis_id    CHAR(26)     NULL,
    action         VARCHAR(60)  NOT NULL,
    description    VARCHAR(255) NOT NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_activity_user (user_id, created_at),
    CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_analysis FOREIGN KEY (analysis_id) REFERENCES analyses(id) ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;