CREATE TABLE IF NOT EXISTS ai_resume_evaluations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    application_id INT UNSIGNED NOT NULL,
    provider VARCHAR(50) NOT NULL DEFAULT 'openai',
    model VARCHAR(120) NOT NULL,
    overall_score DECIMAL(5,2) NULL,
    recommendation VARCHAR(120) NULL,
    summary TEXT NULL,
    strengths_json LONGTEXT NULL,
    weaknesses_json LONGTEXT NULL,
    missing_information_json LONGTEXT NULL,
    detected_skills_json LONGTEXT NULL,
    education_json LONGTEXT NULL,
    experience_json LONGTEXT NULL,
    interview_focus_json LONGTEXT NULL,
    raw_response LONGTEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ai_resume_application
        FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_usage_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NULL,
    application_id INT UNSIGNED NULL,
    action_type VARCHAR(80) NOT NULL,
    provider VARCHAR(50) NOT NULL DEFAULT 'openai',
    model VARCHAR(120) NOT NULL,
    status ENUM('success','failed') NOT NULL,
    input_tokens INT NULL,
    output_tokens INT NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;