CREATE TABLE IF NOT EXISTS source_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_type VARCHAR(64) NOT NULL,
    source_id VARCHAR(255) NOT NULL,
    source_url TEXT NULL,
    retrieved_at DATETIME NOT NULL,
    published_at DATETIME NULL,
    content_hash CHAR(64) NOT NULL,
    raw_json LONGTEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_source_hash (source_type, source_id, content_hash),
    KEY idx_source_type_retrieved (source_type, retrieved_at),
    KEY idx_source_id (source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS opportunities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_type VARCHAR(64) NOT NULL,
    source_id VARCHAR(255) NOT NULL,
    title TEXT NOT NULL,
    agency VARCHAR(255) NULL,
    office VARCHAR(255) NULL,
    notice_type VARCHAR(128) NULL,
    set_aside VARCHAR(255) NULL,
    naics VARCHAR(32) NULL,
    posted_date DATE NULL,
    response_date DATE NULL,
    archive_date DATE NULL,
    place_of_performance TEXT NULL,
    description MEDIUMTEXT NULL,
    source_url TEXT NULL,
    last_seen_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uniq_opportunity_source (source_type, source_id),
    KEY idx_posted_date (posted_date),
    KEY idx_response_date (response_date),
    KEY idx_naics (naics),
    KEY idx_agency (agency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(128) NOT NULL,
    opportunity_id BIGINT UNSIGNED NULL,
    source_record_id BIGINT UNSIGNED NULL,
    event_date DATETIME NOT NULL,
    title TEXT NOT NULL,
    agency VARCHAR(255) NULL,
    event_payload JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_event_source (event_type, opportunity_id, source_record_id),
    KEY idx_event_type_date (event_type, event_date),
    KEY idx_event_opportunity (opportunity_id),
    CONSTRAINT fk_events_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE SET NULL,
    CONSTRAINT fk_events_source_record FOREIGN KEY (source_record_id) REFERENCES source_records(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS client_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    profile_json JSON NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS event_scores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    client_profile_id BIGINT UNSIGNED NOT NULL,
    score INT NOT NULL,
    score_reason TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_event_profile (event_id, client_profile_id),
    KEY idx_score (score),
    CONSTRAINT fk_scores_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_scores_profile FOREIGN KEY (client_profile_id) REFERENCES client_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;