CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    role TEXT NOT NULL CHECK(role IN ('admin','student')) DEFAULT 'student',
    name TEXT NOT NULL,
    cpf TEXT UNIQUE,
    email TEXT NOT NULL UNIQUE,
    whatsapp TEXT,
    uf TEXT,
    city TEXT,
    bairro TEXT,
    category TEXT,
    license_process TEXT NULL,
    license_category TEXT NULL,
    requested_lessons_a INTEGER NOT NULL DEFAULT 0,
    requested_lessons_b INTEGER NOT NULL DEFAULT 0,
    requested_lessons_other INTEGER NOT NULL DEFAULT 0,
    registration_requested_at TEXT NULL,
    registration_reviewed_at TEXT NULL,
    password_hash TEXT NOT NULL,
    student_status TEXT NOT NULL DEFAULT 'active',
    student_level TEXT NULL,
    student_level_note TEXT NULL,
    student_level_updated_at TEXT NULL,
    student_level_professional_id INTEGER NULL,
    admin_notes TEXT NULL,
    finished_at TEXT NULL,
    deleted_at TEXT NULL,
    profile_photo_path TEXT NULL,
    internal_code TEXT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS professionals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'Instrutor',
    whatsapp TEXT,
    username TEXT UNIQUE,
    password_hash TEXT NULL,
    access_enabled INTEGER NOT NULL DEFAULT 1,
    admin_notes TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    panel_token TEXT UNIQUE,
    internal_code TEXT NULL,
    profile_photo_path TEXT NULL,
    can_manage_theory_groups INTEGER NOT NULL DEFAULT 0,
    can_manage_theory_videos INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS availability_slots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    professional_id INTEGER NOT NULL,
    start_at TEXT NOT NULL,
    end_at TEXT NOT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    UNIQUE (professional_id, start_at)
);

CREATE TABLE IF NOT EXISTS appointments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    professional_id INTEGER NOT NULL,
    slot_id INTEGER NOT NULL,
    active_slot_key INTEGER UNIQUE,
    status TEXT NOT NULL CHECK(status IN ('booked','canceled','completed')) DEFAULT 'booked',
    cancel_reason TEXT,
    student_rating INTEGER NULL,
    student_rating_comment TEXT NULL,
    student_rated_at TEXT NULL,
    professional_rating INTEGER NULL,
    professional_rating_comment TEXT NULL,
    professional_rated_at TEXT NULL,
    absence_notified_at TEXT NULL,
    absence_reason TEXT NULL,
    student_no_show_at TEXT NULL,
    student_no_show_reason TEXT NULL,
    professional_absence_notified_at TEXT NULL,
    professional_absence_reason TEXT NULL,
    lesson_color TEXT NOT NULL DEFAULT 'blue',
    lesson_category TEXT NULL,
    lesson_subject TEXT NULL,
    lesson_process TEXT NULL,
    lesson_units INTEGER NULL,
    lesson_confirmed_at TEXT NULL,
    lesson_confirmed_by INTEGER NULL,
    created_at TEXT NOT NULL,
    canceled_at TEXT,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    FOREIGN KEY (slot_id) REFERENCES availability_slots(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS settings (
    setting_key TEXT PRIMARY KEY,
    setting_value TEXT,
    updated_at TEXT
);

CREATE TABLE IF NOT EXISTS ebooks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT NULL,
    file_path TEXT NOT NULL,
    original_name TEXT NULL,
    mime_type TEXT NULL,
    file_size INTEGER NULL,
    cover_path TEXT NULL,
    cover_mime_type TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS ebook_student_access (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ebook_id INTEGER NOT NULL,
    student_id INTEGER NOT NULL,
    granted_at TEXT NOT NULL,
    FOREIGN KEY (ebook_id) REFERENCES ebooks(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE (ebook_id, student_id)
);


CREATE TABLE IF NOT EXISTS theoretical_lessons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    lesson_date TEXT NOT NULL,
    start_time TEXT NOT NULL,
    end_time TEXT NULL,
    course_duration TEXT NULL,
    subject TEXT NULL,
    notes TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);


CREATE TABLE IF NOT EXISTS professional_materials (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    professional_id INTEGER NULL,
    title TEXT NOT NULL,
    description TEXT NULL,
    file_path TEXT NOT NULL,
    original_name TEXT NULL,
    mime_type TEXT NULL,
    file_size INTEGER NULL,
    cover_path TEXT NULL,
    cover_mime_type TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS quick_links (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    notes TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS quick_contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    owner_type TEXT NOT NULL,
    owner_id INTEGER NULL,
    name TEXT NOT NULL,
    whatsapp TEXT NOT NULL,
    notes TEXT NULL,
    category TEXT NOT NULL DEFAULT 'outros',
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS professional_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sender_id INTEGER NOT NULL,
    recipient_id INTEGER NOT NULL,
    subject TEXT NOT NULL,
    message_body TEXT NOT NULL,
    password_hash TEXT NULL,
    is_read INTEGER NOT NULL DEFAULT 0,
    read_at TEXT NULL,
    sender_deleted_at TEXT NULL,
    recipient_deleted_at TEXT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (sender_id) REFERENCES professionals(id) ON DELETE CASCADE,
    FOREIGN KEY (recipient_id) REFERENCES professionals(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_slots_start_at ON availability_slots(start_at);
CREATE INDEX IF NOT EXISTS idx_appointments_student ON appointments(student_id);
CREATE INDEX IF NOT EXISTS idx_appointments_professional ON appointments(professional_id);
CREATE INDEX IF NOT EXISTS idx_appointments_status ON appointments(status);
CREATE INDEX IF NOT EXISTS idx_appointments_ratings ON appointments(student_rating, professional_rating);
CREATE INDEX IF NOT EXISTS idx_appointments_absence ON appointments(absence_notified_at);
CREATE INDEX IF NOT EXISTS idx_appointments_student_no_show ON appointments(student_no_show_at);
CREATE INDEX IF NOT EXISTS idx_appointments_prof_absence ON appointments(professional_absence_notified_at);
CREATE INDEX IF NOT EXISTS idx_ebooks_active ON ebooks(is_active);
CREATE INDEX IF NOT EXISTS idx_ebook_access_student ON ebook_student_access(student_id);
CREATE INDEX IF NOT EXISTS idx_theory_date ON theoretical_lessons(lesson_date, start_time);
CREATE INDEX IF NOT EXISTS idx_theory_active ON theoretical_lessons(is_active);

CREATE INDEX IF NOT EXISTS idx_prof_material_professional ON professional_materials(professional_id);
CREATE INDEX IF NOT EXISTS idx_prof_material_active ON professional_materials(is_active);
CREATE INDEX IF NOT EXISTS idx_quick_links_active ON quick_links(is_active);
CREATE INDEX IF NOT EXISTS idx_quick_contacts_owner ON quick_contacts(owner_type, owner_id);
CREATE INDEX IF NOT EXISTS idx_prof_messages_recipient ON professional_messages(recipient_id, created_at);
CREATE INDEX IF NOT EXISTS idx_prof_messages_sender ON professional_messages(sender_id, created_at);


CREATE TABLE IF NOT EXISTS holidays (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    holiday_date TEXT NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'national',
    uf TEXT NULL,
    city TEXT NULL,
    repeat_yearly INTEGER NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_holidays_date ON holidays(holiday_date);
CREATE INDEX IF NOT EXISTS idx_holidays_scope ON holidays(type, uf, city, is_active);
CREATE INDEX IF NOT EXISTS idx_holidays_repeat ON holidays(repeat_yearly, is_active);

CREATE TABLE IF NOT EXISTS student_payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    amount REAL NOT NULL DEFAULT 0,
    due_date TEXT NULL,
    payment_link TEXT NULL,
    pix_code TEXT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    is_active INTEGER NOT NULL DEFAULT 1,
    notes TEXT NULL,
    created_at TEXT NOT NULL,
    paid_at TEXT NULL,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_student_payments_student ON student_payments(student_id);
CREATE INDEX IF NOT EXISTS idx_student_payments_status ON student_payments(status, is_active);


CREATE TABLE IF NOT EXISTS student_lesson_packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    category TEXT NOT NULL,
    contracted_lessons INTEGER NOT NULL DEFAULT 0,
    used_lessons INTEGER NOT NULL DEFAULT 0,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE (student_id, category)
);
CREATE INDEX IF NOT EXISTS idx_student_lesson_packages_student ON student_lesson_packages(student_id);

CREATE TABLE IF NOT EXISTS beginner_instructors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    cpf TEXT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    whatsapp TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    admin_notes TEXT NULL,
    internal_code TEXT NULL,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_beginner_status ON beginner_instructors(status);

CREATE TABLE IF NOT EXISTS training_contents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT NULL,
    content_type TEXT NOT NULL DEFAULT 'ebook',
    file_path TEXT NULL,
    original_name TEXT NULL,
    mime_type TEXT NULL,
    file_size INTEGER NULL,
    video_url TEXT NULL,
    price REAL NOT NULL DEFAULT 0,
    cover_path TEXT NULL,
    cover_mime_type TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_training_contents_active ON training_contents(is_active, content_type);

CREATE TABLE IF NOT EXISTS training_orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    beginner_id INTEGER NOT NULL,
    content_id INTEGER NOT NULL,
    amount REAL NOT NULL DEFAULT 0,
    status TEXT NOT NULL DEFAULT 'pending',
    payment_link TEXT NULL,
    pix_code TEXT NULL,
    admin_message TEXT NULL,
    proof_path TEXT NULL,
    proof_original_name TEXT NULL,
    proof_mime_type TEXT NULL,
    proof_submitted_at TEXT NULL,
    approved_at TEXT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (beginner_id) REFERENCES beginner_instructors(id) ON DELETE CASCADE,
    FOREIGN KEY (content_id) REFERENCES training_contents(id) ON DELETE CASCADE,
    UNIQUE (beginner_id, content_id)
);
CREATE INDEX IF NOT EXISTS idx_training_orders_status ON training_orders(status);
CREATE INDEX IF NOT EXISTS idx_training_orders_beginner ON training_orders(beginner_id);


-- Módulos V8: videoaulas, bancos/cobranças, grupos teóricos e mensagens internas.
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_internal_code ON users(internal_code);
CREATE UNIQUE INDEX IF NOT EXISTS idx_professionals_internal_code ON professionals(internal_code);
CREATE UNIQUE INDEX IF NOT EXISTS idx_beginner_internal_code ON beginner_instructors(internal_code);

CREATE TABLE IF NOT EXISTS video_lessons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT NULL,
    source_type TEXT NOT NULL DEFAULT 'link',
    video_url TEXT NULL,
    file_path TEXT NULL,
    original_name TEXT NULL,
    mime_type TEXT NULL,
    file_size INTEGER NULL,
    cover_path TEXT NULL,
    audience TEXT NOT NULL DEFAULT 'student',
    access_mode TEXT NOT NULL DEFAULT 'individual',
    created_by_professional_id INTEGER NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_video_lessons_active ON video_lessons(is_active, audience);

CREATE TABLE IF NOT EXISTS video_lesson_access (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_lesson_id INTEGER NOT NULL,
    recipient_type TEXT NOT NULL,
    recipient_id INTEGER NOT NULL,
    granted_at TEXT NOT NULL,
    FOREIGN KEY (video_lesson_id) REFERENCES video_lessons(id) ON DELETE CASCADE,
    UNIQUE (video_lesson_id, recipient_type, recipient_id)
);
CREATE INDEX IF NOT EXISTS idx_video_access_recipient ON video_lesson_access(recipient_type, recipient_id);

CREATE TABLE IF NOT EXISTS payment_banks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT NULL,
    access_url TEXT NULL,
    pix_code TEXT NULL,
    image_path TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_payment_banks_active ON payment_banks(is_active);

CREATE TABLE IF NOT EXISTS payment_charges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bank_id INTEGER NOT NULL,
    recipient_type TEXT NOT NULL,
    recipient_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    amount REAL NOT NULL DEFAULT 0,
    due_date TEXT NULL,
    notes TEXT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    paid_at TEXT NULL,
    proof_path TEXT NULL,
    proof_name TEXT NULL,
    proof_mime TEXT NULL,
    proof_size INTEGER NULL,
    proof_submitted_at TEXT NULL,
    FOREIGN KEY (bank_id) REFERENCES payment_banks(id)
);
CREATE INDEX IF NOT EXISTS idx_payment_charges_recipient ON payment_charges(recipient_type, recipient_id, is_active);
CREATE INDEX IF NOT EXISTS idx_payment_charges_status ON payment_charges(status);

CREATE TABLE IF NOT EXISTS theory_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT NULL,
    instructor_id INTEGER NOT NULL,
    image_path TEXT NULL,
    group_code TEXT NOT NULL UNIQUE,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_theory_group_instructor ON theory_groups(instructor_id);

CREATE TABLE IF NOT EXISTS theory_group_members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id INTEGER NOT NULL,
    student_id INTEGER NOT NULL,
    joined_at TEXT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES theory_groups(id) ON DELETE CASCADE,
    UNIQUE (group_id, student_id)
);
CREATE INDEX IF NOT EXISTS idx_theory_member_student ON theory_group_members(student_id);

CREATE TABLE IF NOT EXISTS internal_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    conversation_type TEXT NOT NULL,
    group_id INTEGER NULL,
    sender_type TEXT NOT NULL,
    sender_id INTEGER NOT NULL,
    recipient_type TEXT NULL,
    recipient_id INTEGER NULL,
    message_body TEXT NOT NULL,
    attachment_path TEXT NULL,
    attachment_name TEXT NULL,
    attachment_mime TEXT NULL,
    attachment_size INTEGER NULL,
    deleted_for_all_at TEXT NULL,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_internal_direct ON internal_messages(recipient_type, recipient_id, conversation_type);
CREATE INDEX IF NOT EXISTS idx_internal_group ON internal_messages(group_id, conversation_type);
CREATE INDEX IF NOT EXISTS idx_internal_created ON internal_messages(created_at);

CREATE TABLE IF NOT EXISTS internal_message_deletions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER NOT NULL,
    actor_type TEXT NOT NULL,
    actor_id INTEGER NOT NULL,
    deleted_at TEXT NOT NULL,
    FOREIGN KEY (message_id) REFERENCES internal_messages(id) ON DELETE CASCADE,
    UNIQUE (message_id, actor_type, actor_id)
);

CREATE TABLE IF NOT EXISTS internal_message_reads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER NOT NULL,
    actor_type TEXT NOT NULL,
    actor_id INTEGER NOT NULL,
    read_at TEXT NOT NULL,
    FOREIGN KEY (message_id) REFERENCES internal_messages(id) ON DELETE CASCADE,
    UNIQUE (message_id, actor_type, actor_id)
);



-- Grupos privados de mensagens entre instrutores
CREATE TABLE IF NOT EXISTS professional_chat_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    owner_professional_id INTEGER NOT NULL,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_prof_chat_owner ON professional_chat_groups(owner_professional_id);

CREATE TABLE IF NOT EXISTS professional_chat_group_members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id INTEGER NOT NULL,
    professional_id INTEGER NOT NULL,
    joined_at TEXT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES professional_chat_groups(id) ON DELETE CASCADE,
    UNIQUE (group_id, professional_id)
);
CREATE INDEX IF NOT EXISTS idx_prof_chat_member_prof ON professional_chat_group_members(professional_id);


-- Contatos internos salvos para conversas rápidas
CREATE TABLE IF NOT EXISTS internal_contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    owner_type TEXT NOT NULL,
    owner_id INTEGER NOT NULL,
    contact_type TEXT NOT NULL,
    contact_id INTEGER NOT NULL,
    created_at TEXT NOT NULL,
    UNIQUE (owner_type, owner_id, contact_type, contact_id)
);
CREATE INDEX IF NOT EXISTS idx_internal_contact_owner ON internal_contacts(owner_type, owner_id);


CREATE TABLE IF NOT EXISTS ebook_group_access (
    id INTEGER PRIMARY KEY AUTOINCREMENT, ebook_id INTEGER NOT NULL, group_id INTEGER NOT NULL, granted_at TEXT NOT NULL,
    FOREIGN KEY (ebook_id) REFERENCES ebooks(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES theory_groups(id) ON DELETE CASCADE,
    UNIQUE (ebook_id, group_id)
);
CREATE INDEX IF NOT EXISTS idx_ebook_group_group ON ebook_group_access(group_id);

CREATE TABLE IF NOT EXISTS student_learning_levels (
    id INTEGER PRIMARY KEY AUTOINCREMENT, student_id INTEGER NOT NULL, professional_id INTEGER NOT NULL,
    category_key TEXT NOT NULL, level_value TEXT NOT NULL, note TEXT NULL, updated_at TEXT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    UNIQUE (student_id, professional_id, category_key)
);
CREATE INDEX IF NOT EXISTS idx_learning_student ON student_learning_levels(student_id);
CREATE INDEX IF NOT EXISTS idx_learning_professional ON student_learning_levels(professional_id);

CREATE TABLE IF NOT EXISTS payment_receipts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    charge_id INTEGER NOT NULL,
    recipient_type TEXT NOT NULL,
    recipient_id INTEGER NOT NULL,
    receipt_code TEXT NOT NULL,
    company_name TEXT NOT NULL,
    company_document TEXT NULL,
    logo_path TEXT NULL,
    client_name TEXT NOT NULL,
    service_title TEXT NOT NULL,
    amount REAL NOT NULL DEFAULT 0,
    payment_method TEXT NULL,
    paid_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (charge_id) REFERENCES payment_charges(id) ON DELETE CASCADE,
    UNIQUE (charge_id),
    UNIQUE (receipt_code)
);
CREATE INDEX IF NOT EXISTS idx_payment_receipt_actor ON payment_receipts(recipient_type, recipient_id);

-- CFCV19: quadro de avisos e permissões de gestão teórica.
CREATE TABLE IF NOT EXISTS student_announcements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    style TEXT NOT NULL DEFAULT 'info',
    starts_at TEXT NULL,
    ends_at TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_student_announcements_active ON student_announcements(is_active, starts_at, ends_at);
