CREATE TABLE IF NOT EXISTS admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('superadmin', 'admin') NOT NULL DEFAULT 'admin',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    last_login_at DATETIME NULL,
    INDEX idx_role (role),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS market_research_responses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    dni CHAR(8) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    hospital_institution VARCHAR(180) NOT NULL,
    profile ENUM('Est. Técnico', 'Técnico', 'Est. Universitario', 'Interno', 'Licenciado') NOT NULL,
    currently_working ENUM('yes', 'no') NOT NULL,
    desired_course_or_diploma TEXT NOT NULL,
    missing_topic TEXT NOT NULL,
    has_taken_courses_before ENUM('yes', 'no') NOT NULL,
    previous_institution VARCHAR(180) NULL,
    liked_most_previous_institution TEXT NULL,
    liked_least_previous_institution TEXT NULL,
    important_factors_json JSON NOT NULL,
    trust_factors TEXT NOT NULL,
    distrust_factors TEXT NOT NULL,
    search_channels_json JSON NOT NULL,
    owner_question_answer TEXT NOT NULL,
    data_consent TINYINT(1) NOT NULL DEFAULT 1,
    data_consent_at DATETIME NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_created_at (created_at),
    INDEX idx_dni (dni),
    INDEX idx_phone (phone),
    INDEX idx_profile (profile),
    INDEX idx_currently_working (currently_working),
    INDEX idx_has_taken_courses_before (has_taken_courses_before),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    module VARCHAR(80) NOT NULL,
    description TEXT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_audit_logs_admin_user FOREIGN KEY (admin_user_id)
        REFERENCES admin_users(id)
        ON DELETE SET NULL,
    INDEX idx_admin_user_id (admin_user_id),
    INDEX idx_action (action),
    INDEX idx_module (module),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;