CREATE TABLE IF NOT EXISTS app_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL,
    setting_value LONGTEXT 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 uniq_app_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 
CREATE TABLE IF NOT EXISTS tracked_keywords (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    keyword_query VARCHAR(160) NOT NULL,
    normalized_query VARCHAR(160) NOT NULL,
    xquik_monitor_id VARCHAR(64) NULL,
    event_types_json LONGTEXT NULL,
    is_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 uniq_tracked_keywords_query (normalized_query),
    UNIQUE KEY uniq_tracked_keywords_monitor (xquik_monitor_id),
    KEY idx_tracked_keywords_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_receipts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    payload_hash CHAR(64) NOT NULL,
    nonce VARCHAR(64) NOT NULL,
    event_type VARCHAR(50) NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'accepted',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_webhook_receipts_hash (payload_hash),
    UNIQUE KEY uniq_webhook_receipts_nonce (nonce)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS keyword_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tracked_keyword_id BIGINT UNSIGNED NULL,
    xquik_event_id VARCHAR(64) NULL,
    monitor_id VARCHAR(64) NULL,
    event_type VARCHAR(50) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    matched_query VARCHAR(160) NULL,
    author_username VARCHAR(100) NULL,
    tweet_id VARCHAR(64) NULL,
    tweet_text LONGTEXT NOT NULL,
    likes_count INT UNSIGNED NOT NULL DEFAULT 0,
    retweets_count INT UNSIGNED NOT NULL DEFAULT 0,
    replies_count INT UNSIGNED NOT NULL DEFAULT 0,
    occurred_at DATETIME NULL,
    analysis_status VARCHAR(30) NOT NULL DEFAULT 'pending',
    moderation_status VARCHAR(30) NOT NULL DEFAULT 'pending',
    priority_score DECIMAL(6,2) NULL,
    raw_payload LONGTEXT 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 uniq_keyword_events_payload_hash (payload_hash),
    UNIQUE KEY uniq_keyword_events_remote_id (xquik_event_id),
    KEY idx_keyword_events_keyword (tracked_keyword_id),
    KEY idx_keyword_events_status (analysis_status, moderation_status),
    KEY idx_keyword_events_occurred_at (occurred_at),
    CONSTRAINT fk_keyword_events_keyword
        FOREIGN KEY (tracked_keyword_id) REFERENCES tracked_keywords (id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_analysis (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id BIGINT UNSIGNED NOT NULL,
    provider VARCHAR(50) NOT NULL,
    model_name VARCHAR(100) NULL,
    sentiment_label VARCHAR(20) NOT NULL DEFAULT 'neutral',
    sentiment_score DECIMAL(6,3) NOT NULL DEFAULT 0.000,
    profanity_score DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    insult_score DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    toxicity_score DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    priority_score DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    risk_level VARCHAR(20) NOT NULL DEFAULT 'low',
    matched_terms_json LONGTEXT NULL,
    notes TEXT NULL,
    result_payload LONGTEXT 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 uniq_event_analysis_event (event_id),
    KEY idx_event_analysis_label (sentiment_label),
    KEY idx_event_analysis_risk (risk_level),
    CONSTRAINT fk_event_analysis_event
        FOREIGN KEY (event_id) REFERENCES keyword_events (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;