CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('admin','student') NOT NULL DEFAULT 'student',
    name VARCHAR(160) NOT NULL,
    cpf VARCHAR(20) UNIQUE,
    email VARCHAR(180) NOT NULL UNIQUE,
    whatsapp VARCHAR(30),
    uf VARCHAR(2),
    city VARCHAR(120),
    bairro VARCHAR(120),
    category VARCHAR(10),
    license_process VARCHAR(40) NULL,
    license_category VARCHAR(10) NULL,
    requested_lessons_a INT NOT NULL DEFAULT 0,
    requested_lessons_b INT NOT NULL DEFAULT 0,
    requested_lessons_other INT NOT NULL DEFAULT 0,
    registration_requested_at DATETIME NULL,
    registration_reviewed_at DATETIME NULL,
    password_hash VARCHAR(255) NOT NULL,
    student_status VARCHAR(20) NOT NULL DEFAULT 'active',
    student_level VARCHAR(50) NULL,
    student_level_note TEXT NULL,
    student_level_updated_at DATETIME NULL,
    student_level_professional_id INT NULL,
    admin_notes TEXT NULL,
    finished_at DATETIME NULL,
    deleted_at DATETIME NULL,
    profile_photo_path VARCHAR(255) NULL,
    internal_code VARCHAR(9) NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY idx_users_internal_code (internal_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS professionals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    type VARCHAR(80) NOT NULL DEFAULT 'Instrutor',
    whatsapp VARCHAR(30),
    username VARCHAR(120) UNIQUE NULL,
    password_hash VARCHAR(255) NULL,
    access_enabled TINYINT(1) NOT NULL DEFAULT 1,
    admin_notes TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    panel_token VARCHAR(80) UNIQUE NULL,
    internal_code VARCHAR(9) NULL,
    profile_photo_path VARCHAR(255) NULL,
    can_manage_theory_groups TINYINT(1) NOT NULL DEFAULT 0,
    can_manage_theory_videos TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    UNIQUE KEY idx_professionals_internal_code (internal_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS availability_slots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    professional_id INT NOT NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_slots_professional FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    UNIQUE KEY uq_professional_start (professional_id, start_at),
    INDEX idx_slots_start_at (start_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    professional_id INT NOT NULL,
    slot_id INT NOT NULL,
    active_slot_key INT UNIQUE NULL,
    status ENUM('booked','canceled','completed') NOT NULL DEFAULT 'booked',
    cancel_reason VARCHAR(255),
    student_rating TINYINT NULL,
    student_rating_comment TEXT NULL,
    student_rated_at DATETIME NULL,
    professional_rating TINYINT NULL,
    professional_rating_comment TEXT NULL,
    professional_rated_at DATETIME NULL,
    absence_notified_at DATETIME NULL,
    absence_reason VARCHAR(255) NULL,
    student_no_show_at DATETIME NULL,
    student_no_show_reason VARCHAR(255) NULL,
    professional_absence_notified_at DATETIME NULL,
    professional_absence_reason VARCHAR(255) NULL,
    lesson_color VARCHAR(20) NOT NULL DEFAULT 'blue',
    lesson_category VARCHAR(5) NULL,
    lesson_subject VARCHAR(255) NULL,
    lesson_process VARCHAR(30) NULL,
    lesson_units INT NULL,
    lesson_confirmed_at DATETIME NULL,
    lesson_confirmed_by INT NULL,
    created_at DATETIME NOT NULL,
    canceled_at DATETIME NULL,
    CONSTRAINT fk_appointments_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_appointments_professional FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    CONSTRAINT fk_appointments_slot FOREIGN KEY (slot_id) REFERENCES availability_slots(id) ON DELETE CASCADE,
    INDEX idx_appointments_student (student_id),
    INDEX idx_appointments_professional (professional_id),
    INDEX idx_appointments_status (status),
    INDEX idx_appointments_ratings (student_rating, professional_rating),
    INDEX idx_appointments_absence (absence_notified_at),
    INDEX idx_appointments_student_no_show (student_no_show_at),
    INDEX idx_appointments_prof_absence (professional_absence_notified_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS theoretical_lessons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(160) NOT NULL,
    lesson_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NULL,
    course_duration VARCHAR(120) NULL,
    subject TEXT NULL,
    notes TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_theory_date (lesson_date, start_time),
    INDEX idx_theory_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(120) PRIMARY KEY,
    setting_value TEXT,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ebooks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT NULL,
    cover_path VARCHAR(255) NULL,
    cover_mime_type VARCHAR(120) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_ebooks_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ebook_student_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ebook_id INT NOT NULL,
    student_id INT NOT NULL,
    granted_at DATETIME NOT NULL,
    CONSTRAINT fk_ebook_access_ebook FOREIGN KEY (ebook_id) REFERENCES ebooks(id) ON DELETE CASCADE,
    CONSTRAINT fk_ebook_access_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_ebook_student (ebook_id, student_id),
    INDEX idx_ebook_access_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS professional_materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    professional_id INT NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT NULL,
    cover_path VARCHAR(255) NULL,
    cover_mime_type VARCHAR(120) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_prof_material_professional (professional_id),
    INDEX idx_prof_material_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quick_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    url VARCHAR(500) NOT NULL,
    notes TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_quick_links_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quick_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_type VARCHAR(30) NOT NULL,
    owner_id INT NULL,
    name VARCHAR(160) NOT NULL,
    whatsapp VARCHAR(30) NOT NULL,
    notes TEXT NULL,
    category VARCHAR(20) NOT NULL DEFAULT 'outros',
    created_at DATETIME NOT NULL,
    INDEX idx_quick_contacts_owner (owner_type, owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS professional_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    recipient_id INT NOT NULL,
    subject VARCHAR(180) NOT NULL,
    message_body TEXT NOT NULL,
    password_hash VARCHAR(255) NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    read_at DATETIME NULL,
    sender_deleted_at DATETIME NULL,
    recipient_deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_prof_message_sender FOREIGN KEY (sender_id) REFERENCES professionals(id) ON DELETE CASCADE,
    CONSTRAINT fk_prof_message_recipient FOREIGN KEY (recipient_id) REFERENCES professionals(id) ON DELETE CASCADE,
    INDEX idx_prof_messages_recipient (recipient_id, created_at),
    INDEX idx_prof_messages_sender (sender_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS holidays (
    id INT AUTO_INCREMENT PRIMARY KEY,
    holiday_date DATE NOT NULL,
    name VARCHAR(180) NOT NULL,
    type VARCHAR(20) NOT NULL DEFAULT 'national',
    uf VARCHAR(2) NULL,
    city VARCHAR(120) NULL,
    repeat_yearly TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_holidays_date (holiday_date),
    INDEX idx_holidays_scope (type, uf, city, is_active),
    INDEX idx_holidays_repeat (repeat_yearly, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    title VARCHAR(180) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    due_date DATE NULL,
    payment_link TEXT NULL,
    pix_code TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    paid_at DATETIME NULL,
    INDEX idx_student_payments_student (student_id),
    INDEX idx_student_payments_status (status, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS student_lesson_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    category VARCHAR(5) NOT NULL,
    contracted_lessons INT NOT NULL DEFAULT 0,
    used_lessons INT NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_student_lesson_package_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_student_lesson_category (student_id, category),
    INDEX idx_student_lesson_packages_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS beginner_instructors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    cpf VARCHAR(20) NULL UNIQUE,
    email VARCHAR(190) NOT NULL UNIQUE,
    whatsapp VARCHAR(30) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    admin_notes TEXT NULL,
    internal_code VARCHAR(9) NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_beginner_status (status),
    UNIQUE KEY idx_beginner_internal_code (internal_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS training_contents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(190) NOT NULL,
    description TEXT NULL,
    content_type VARCHAR(20) NOT NULL DEFAULT 'ebook',
    file_path VARCHAR(255) NULL,
    original_name VARCHAR(255) NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT NULL,
    video_url VARCHAR(700) NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    cover_path VARCHAR(255) NULL,
    cover_mime_type VARCHAR(120) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_training_contents_active (is_active, content_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS training_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beginner_id INT NOT NULL,
    content_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    payment_link VARCHAR(700) NULL,
    pix_code TEXT NULL,
    admin_message TEXT NULL,
    proof_path VARCHAR(255) NULL,
    proof_original_name VARCHAR(255) NULL,
    proof_mime_type VARCHAR(120) NULL,
    proof_submitted_at DATETIME NULL,
    approved_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_training_order_beginner FOREIGN KEY (beginner_id) REFERENCES beginner_instructors(id) ON DELETE CASCADE,
    CONSTRAINT fk_training_order_content FOREIGN KEY (content_id) REFERENCES training_contents(id) ON DELETE CASCADE,
    UNIQUE KEY uq_training_order (beginner_id, content_id),
    INDEX idx_training_orders_status (status),
    INDEX idx_training_orders_beginner (beginner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Módulos V8: videoaulas, bancos/cobranças, grupos teóricos e mensagens internas.
CREATE TABLE IF NOT EXISTS video_lessons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    source_type VARCHAR(20) NOT NULL DEFAULT 'link',
    video_url TEXT NULL,
    file_path VARCHAR(255) NULL,
    original_name VARCHAR(255) NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT NULL,
    cover_path VARCHAR(255) NULL,
    audience VARCHAR(20) NOT NULL DEFAULT 'student',
    access_mode VARCHAR(20) NOT NULL DEFAULT 'individual',
    created_by_professional_id INT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_video_lessons_active (is_active, audience)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS video_lesson_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_lesson_id INT NOT NULL,
    recipient_type VARCHAR(20) NOT NULL,
    recipient_id INT NOT NULL,
    granted_at DATETIME NOT NULL,
    UNIQUE KEY uq_video_access (video_lesson_id, recipient_type, recipient_id),
    INDEX idx_video_access_recipient (recipient_type, recipient_id),
    CONSTRAINT fk_video_access_lesson FOREIGN KEY (video_lesson_id) REFERENCES video_lessons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_banks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    description TEXT NULL,
    access_url TEXT NULL,
    pix_code TEXT NULL,
    image_path VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_payment_banks_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_charges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bank_id INT NOT NULL,
    recipient_type VARCHAR(20) NOT NULL,
    recipient_id INT NOT NULL,
    title VARCHAR(180) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    due_date DATE NULL,
    notes TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    paid_at DATETIME NULL,
    proof_path VARCHAR(255) NULL,
    proof_name VARCHAR(255) NULL,
    proof_mime VARCHAR(120) NULL,
    proof_size BIGINT NULL,
    proof_submitted_at DATETIME NULL,
    INDEX idx_payment_charges_recipient (recipient_type, recipient_id, is_active),
    INDEX idx_payment_charges_status (status),
    CONSTRAINT fk_payment_charge_bank FOREIGN KEY (bank_id) REFERENCES payment_banks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theory_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    description TEXT NULL,
    instructor_id INT NOT NULL,
    image_path VARCHAR(255) NULL,
    group_code VARCHAR(9) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_theory_group_code (group_code),
    INDEX idx_theory_group_instructor (instructor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theory_group_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    student_id INT NOT NULL,
    joined_at DATETIME NOT NULL,
    UNIQUE KEY uq_theory_group_member (group_id, student_id),
    INDEX idx_theory_member_student (student_id),
    CONSTRAINT fk_theory_member_group FOREIGN KEY (group_id) REFERENCES theory_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS internal_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conversation_type VARCHAR(20) NOT NULL,
    group_id INT NULL,
    sender_type VARCHAR(20) NOT NULL,
    sender_id INT NOT NULL,
    recipient_type VARCHAR(20) NULL,
    recipient_id INT NULL,
    message_body TEXT NOT NULL,
    attachment_path VARCHAR(255) NULL,
    attachment_name VARCHAR(255) NULL,
    attachment_mime VARCHAR(120) NULL,
    attachment_size BIGINT NULL,
    deleted_for_all_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_internal_direct (recipient_type, recipient_id, conversation_type),
    INDEX idx_internal_group (group_id, conversation_type),
    INDEX idx_internal_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS internal_message_deletions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    actor_type VARCHAR(20) NOT NULL,
    actor_id INT NOT NULL,
    deleted_at DATETIME NOT NULL,
    UNIQUE KEY uq_internal_deletion (message_id, actor_type, actor_id),
    CONSTRAINT fk_internal_deletion_message FOREIGN KEY (message_id) REFERENCES internal_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS internal_message_reads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    actor_type VARCHAR(20) NOT NULL,
    actor_id INT NOT NULL,
    read_at DATETIME NOT NULL,
    UNIQUE KEY uq_internal_read (message_id, actor_type, actor_id),
    CONSTRAINT fk_internal_read_message FOREIGN KEY (message_id) REFERENCES internal_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- Grupos privados de mensagens entre instrutores
CREATE TABLE IF NOT EXISTS professional_chat_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    owner_professional_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_prof_chat_owner (owner_professional_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS professional_chat_group_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    professional_id INT NOT NULL,
    joined_at DATETIME NOT NULL,
    UNIQUE KEY uq_prof_chat_member (group_id, professional_id),
    INDEX idx_prof_chat_member_prof (professional_id),
    CONSTRAINT fk_prof_chat_member_group FOREIGN KEY (group_id) REFERENCES professional_chat_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Contatos internos salvos para conversas rápidas
CREATE TABLE IF NOT EXISTS internal_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_type VARCHAR(20) NOT NULL,
    owner_id INT NOT NULL,
    contact_type VARCHAR(20) NOT NULL,
    contact_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_internal_contact (owner_type, owner_id, contact_type, contact_id),
    INDEX idx_internal_contact_owner (owner_type, owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS ebook_group_access (
    id INT AUTO_INCREMENT PRIMARY KEY, ebook_id INT NOT NULL, group_id INT NOT NULL, granted_at DATETIME NOT NULL,
    UNIQUE KEY uq_ebook_group (ebook_id, group_id), INDEX idx_ebook_group_group (group_id),
    CONSTRAINT fk_ebook_group_ebook FOREIGN KEY (ebook_id) REFERENCES ebooks(id) ON DELETE CASCADE,
    CONSTRAINT fk_ebook_group_group FOREIGN KEY (group_id) REFERENCES theory_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_learning_levels (
    id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, professional_id INT NOT NULL,
    category_key VARCHAR(40) NOT NULL, level_value VARCHAR(50) NOT NULL, note TEXT NULL, updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_student_learning_level (student_id, professional_id, category_key),
    INDEX idx_learning_student (student_id), INDEX idx_learning_professional (professional_id),
    CONSTRAINT fk_learning_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_learning_professional FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    charge_id INT NOT NULL,
    recipient_type VARCHAR(20) NOT NULL,
    recipient_id INT NOT NULL,
    receipt_code VARCHAR(24) NOT NULL,
    company_name VARCHAR(180) NOT NULL,
    company_document VARCHAR(40) NULL,
    logo_path VARCHAR(255) NULL,
    client_name VARCHAR(180) NOT NULL,
    service_title VARCHAR(180) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_method VARCHAR(160) NULL,
    paid_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_payment_receipt_charge (charge_id),
    UNIQUE KEY uq_payment_receipt_code (receipt_code),
    INDEX idx_payment_receipt_actor (recipient_type, recipient_id),
    CONSTRAINT fk_payment_receipt_charge FOREIGN KEY (charge_id) REFERENCES payment_charges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CFCV19: quadro de avisos. As colunas opcionais em professionals/video_lessons
-- também são criadas automaticamente por Database::migrate em instalações existentes.
CREATE TABLE IF NOT EXISTS student_announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(180) NOT NULL,
    message TEXT NOT NULL,
    style VARCHAR(20) NOT NULL DEFAULT 'info',
    starts_at DATETIME NULL,
    ends_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_student_announcements_active (is_active, starts_at, ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
