<?php
class Database
{
    private static ?PDO $pdo = null;

    public static function pdo(): PDO
    {
        if (self::$pdo instanceof PDO) {
            return self::$pdo;
        }

        $db = config_value('db', []);
        $driver = $db['driver'] ?? 'sqlite';

        if ($driver === 'mysql') {
            $charset = $db['mysql_charset'] ?? 'utf8mb4';
            $dsn = "mysql:host={$db['mysql_host']};dbname={$db['mysql_database']};charset={$charset}";
            self::$pdo = new PDO($dsn, $db['mysql_user'], $db['mysql_password'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);
        } else {
            $path = $db['sqlite_path'] ?? (STORAGE_PATH . '/database.sqlite');
            $dir = dirname($path);
            if (!is_dir($dir)) {
                mkdir($dir, 0775, true);
            }
            self::$pdo = new PDO('sqlite:' . $path, null, null, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
            self::$pdo->exec('PRAGMA foreign_keys = ON');
        }

        return self::$pdo;
    }

    public static function driver(): string
    {
        return config_value('db.driver', 'sqlite');
    }

    public static function resetConnection(): void
    {
        self::$pdo = null;
    }

    public static function migrate(): void
    {
        $pdo = self::pdo();
        if (!self::tableExists('users')) {
            return;
        }

        self::addColumnIfMissing('users', 'student_status', "TEXT NOT NULL DEFAULT 'active'", "VARCHAR(20) NOT NULL DEFAULT 'active'");
        self::addColumnIfMissing('users', 'finished_at', 'TEXT NULL', 'DATETIME NULL');
        self::addColumnIfMissing('users', 'deleted_at', 'TEXT NULL', 'DATETIME NULL');
        self::addColumnIfMissing('users', 'student_level', 'TEXT NULL', 'VARCHAR(50) NULL');
        self::addColumnIfMissing('users', 'student_level_note', 'TEXT NULL', 'TEXT NULL');
        self::addColumnIfMissing('users', 'student_level_updated_at', 'TEXT NULL', 'DATETIME NULL');
        self::addColumnIfMissing('users', 'student_level_professional_id', 'INTEGER NULL', 'INT NULL');
        self::addColumnIfMissing('users', 'admin_notes', 'TEXT NULL', 'TEXT NULL');
        self::addColumnIfMissing('users', 'license_process', 'TEXT NULL', 'VARCHAR(40) NULL');
        self::addColumnIfMissing('users', 'license_category', 'TEXT NULL', 'VARCHAR(10) NULL');
        self::addColumnIfMissing('users', 'requested_lessons_a', 'INTEGER NOT NULL DEFAULT 0', 'INT NOT NULL DEFAULT 0');
        self::addColumnIfMissing('users', 'requested_lessons_b', 'INTEGER NOT NULL DEFAULT 0', 'INT NOT NULL DEFAULT 0');
        self::addColumnIfMissing('users', 'requested_lessons_other', 'INTEGER NOT NULL DEFAULT 0', 'INT NOT NULL DEFAULT 0');
        self::addColumnIfMissing('users', 'registration_requested_at', 'TEXT NULL', 'DATETIME NULL');
        self::addColumnIfMissing('users', 'registration_reviewed_at', 'TEXT NULL', 'DATETIME NULL');

        self::createTheoreticalLessonsTableIfMissing();
        self::fixAvailabilityUniqueIndexes();

        if (self::tableExists('professionals')) {
            self::addColumnIfMissing('professionals', 'panel_token', 'TEXT NULL', 'VARCHAR(80) NULL');
            self::addColumnIfMissing('professionals', 'whatsapp', 'TEXT NULL', 'VARCHAR(30) NULL');
            self::addColumnIfMissing('professionals', 'username', 'TEXT NULL', 'VARCHAR(120) NULL');
            self::addColumnIfMissing('professionals', 'password_hash', 'TEXT NULL', 'VARCHAR(255) NULL');
            self::addColumnIfMissing('professionals', 'access_enabled', 'INTEGER NOT NULL DEFAULT 1', 'TINYINT(1) NOT NULL DEFAULT 1');
            self::addColumnIfMissing('professionals', 'admin_notes', 'TEXT NULL', 'TEXT NULL');
            self::addColumnIfMissing('professionals', 'can_manage_theory_groups', 'INTEGER NOT NULL DEFAULT 0', 'TINYINT(1) NOT NULL DEFAULT 0');
            self::addColumnIfMissing('professionals', 'can_manage_theory_videos', 'INTEGER NOT NULL DEFAULT 0', 'TINYINT(1) NOT NULL DEFAULT 0');
            self::addColumnIfMissing('professionals', 'daily_lesson_limit', 'INTEGER NOT NULL DEFAULT 0', 'INT NOT NULL DEFAULT 0');
            self::ensureIndex('idx_professionals_panel_token', 'professionals', 'panel_token', true);
            self::ensureIndex('idx_professionals_username', 'professionals', 'username', true);
            self::fillProfessionalTokens();
            ProfessionalDayOff::ensureTable();
        }

        if (self::tableExists('appointments')) {
            self::addColumnIfMissing('appointments', 'student_rating', 'INTEGER NULL', 'TINYINT NULL');
            self::addColumnIfMissing('appointments', 'student_rating_comment', 'TEXT NULL', 'TEXT NULL');
            self::addColumnIfMissing('appointments', 'student_rated_at', 'TEXT NULL', 'DATETIME NULL');
            self::addColumnIfMissing('appointments', 'professional_rating', 'INTEGER NULL', 'TINYINT NULL');
            self::addColumnIfMissing('appointments', 'professional_rating_comment', 'TEXT NULL', 'TEXT NULL');
            self::addColumnIfMissing('appointments', 'professional_rated_at', 'TEXT NULL', 'DATETIME NULL');
            self::addColumnIfMissing('appointments', 'absence_notified_at', 'TEXT NULL', 'DATETIME NULL');
            self::addColumnIfMissing('appointments', 'absence_reason', 'TEXT NULL', 'VARCHAR(255) NULL');
            self::addColumnIfMissing('appointments', 'student_no_show_at', 'TEXT NULL', 'DATETIME NULL');
            self::addColumnIfMissing('appointments', 'student_no_show_reason', 'TEXT NULL', 'VARCHAR(255) NULL');
            self::addColumnIfMissing('appointments', 'professional_absence_notified_at', 'TEXT NULL', 'DATETIME NULL');
            self::addColumnIfMissing('appointments', 'professional_absence_reason', 'TEXT NULL', 'VARCHAR(255) NULL');
            self::addColumnIfMissing('appointments', 'lesson_color', "TEXT NOT NULL DEFAULT 'blue'", "VARCHAR(20) NOT NULL DEFAULT 'blue'");
            self::addColumnIfMissing('appointments', 'lesson_category', 'TEXT NULL', 'VARCHAR(5) NULL');
            self::addColumnIfMissing('appointments', 'lesson_subject', 'TEXT NULL', 'VARCHAR(255) NULL');
            self::addColumnIfMissing('appointments', 'lesson_process', 'TEXT NULL', 'VARCHAR(30) NULL');
            self::addColumnIfMissing('appointments', 'lesson_units', 'INTEGER NULL', 'INT NULL');
            self::addColumnIfMissing('appointments', 'lesson_confirmed_at', 'TEXT NULL', 'DATETIME NULL');
            self::addColumnIfMissing('appointments', 'lesson_confirmed_by', 'INTEGER NULL', 'INT NULL');
            self::ensureIndex('idx_appointments_professional', 'appointments', 'professional_id', false);
            self::ensureIndex('idx_appointments_ratings', 'appointments', 'student_rating, professional_rating', false);
            self::ensureIndex('idx_appointments_absence', 'appointments', 'absence_notified_at', false);
            self::ensureIndex('idx_appointments_student_no_show', 'appointments', 'student_no_show_at', false);
            self::ensureIndex('idx_appointments_prof_absence', 'appointments', 'professional_absence_notified_at', false);
        }

        self::createEbooksTablesIfMissing();
        self::addColumnIfMissing('ebooks', 'cover_path', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('ebooks', 'cover_mime_type', 'TEXT NULL', 'VARCHAR(120) NULL');

        self::createProfessionalMaterialsTableIfMissing();
        self::addColumnIfMissing('professional_materials', 'cover_path', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('professional_materials', 'cover_mime_type', 'TEXT NULL', 'VARCHAR(120) NULL');

        self::createQuickLinksTableIfMissing();
        self::createContactsTableIfMissing();
        self::addColumnIfMissing('quick_contacts', 'category', "TEXT NOT NULL DEFAULT 'outros'", "VARCHAR(20) NOT NULL DEFAULT 'outros'");
        self::createProfessionalMessagesTableIfMissing();
        self::addColumnIfMissing('professional_messages', 'sender_deleted_at', 'TEXT NULL', 'DATETIME NULL');
        self::addColumnIfMissing('professional_messages', 'recipient_deleted_at', 'TEXT NULL', 'DATETIME NULL');

        self::createStudentLessonPackagesTableIfMissing();
        self::normalizeStudentLessonPackageBalances();
        self::createBeginnerTrainingTablesIfMissing();
        self::addColumnIfMissing('training_contents', 'cover_path', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('training_contents', 'cover_mime_type', 'TEXT NULL', 'VARCHAR(120) NULL');

        self::addColumnIfMissing('users', 'profile_photo_path', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('users', 'internal_code', 'TEXT NULL', 'VARCHAR(9) NULL');
        self::ensureIndex('idx_users_internal_code', 'users', 'internal_code', true);
        if (self::tableExists('professionals')) {
            self::addColumnIfMissing('professionals', 'internal_code', 'TEXT NULL', 'VARCHAR(9) NULL');
            self::addColumnIfMissing('professionals', 'profile_photo_path', 'TEXT NULL', 'VARCHAR(255) NULL');
            self::ensureIndex('idx_professionals_internal_code', 'professionals', 'internal_code', true);
        }
        if (self::tableExists('beginner_instructors')) {
            self::addColumnIfMissing('beginner_instructors', 'internal_code', 'TEXT NULL', 'VARCHAR(9) NULL');
            self::ensureIndex('idx_beginner_internal_code', 'beginner_instructors', 'internal_code', true);
        }
        self::createVideoLessonTablesIfMissing();
        self::createPaymentCenterTablesIfMissing();
        self::addColumnIfMissing('payment_charges', 'proof_path', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('payment_charges', 'proof_name', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('payment_charges', 'proof_mime', 'TEXT NULL', 'VARCHAR(120) NULL');
        self::addColumnIfMissing('payment_charges', 'proof_size', 'INTEGER NULL', 'BIGINT NULL');
        self::addColumnIfMissing('payment_charges', 'proof_submitted_at', 'TEXT NULL', 'DATETIME NULL');
        PaymentReceipt::ensureTable();
        self::createCommunicationTablesIfMissing();
        self::createProfessionalChatTablesIfMissing();
        self::createStudentAnnouncementsTableIfMissing();
        self::addColumnIfMissing('internal_messages', 'attachment_path', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('internal_messages', 'attachment_name', 'TEXT NULL', 'VARCHAR(255) NULL');
        self::addColumnIfMissing('internal_messages', 'attachment_mime', 'TEXT NULL', 'VARCHAR(120) NULL');
        self::addColumnIfMissing('internal_messages', 'attachment_size', 'INTEGER NULL', 'BIGINT NULL');
        self::addColumnIfMissing('video_lessons', 'created_by_professional_id', 'INTEGER NULL', 'INT NULL');
        self::createEbookGroupAccessTableIfMissing();
        self::createStudentLearningLevelsTableIfMissing();
        self::backfillLegacyStudentLearningLevels();
        self::syncAppointmentInternalContacts();
        self::fillInternalCodes();
    }

    public static function tableExists(string $table): bool
    {
        $pdo = self::pdo();
        if (self::driver() === 'mysql') {
            $stmt = $pdo->prepare('SHOW TABLES LIKE ?');
            $stmt->execute([$table]);
            return (bool)$stmt->fetchColumn();
        }
        $stmt = $pdo->prepare("SELECT name FROM sqlite_master WHERE type='table' AND name=?");
        $stmt->execute([$table]);
        return (bool)$stmt->fetchColumn();
    }

    public static function columnExists(string $table, string $column): bool
    {
        $pdo = self::pdo();
        $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        if (self::driver() === 'mysql') {
            $stmt = $pdo->prepare("SHOW COLUMNS FROM `{$safeTable}` LIKE ?");
            $stmt->execute([$column]);
            return (bool)$stmt->fetch();
        }
        $stmt = $pdo->query("PRAGMA table_info({$safeTable})");
        foreach ($stmt->fetchAll() as $info) {
            if (($info['name'] ?? '') === $column) {
                return true;
            }
        }
        return false;
    }

    private static function addColumnIfMissing(string $table, string $column, string $sqliteDefinition, string $mysqlDefinition): void
    {
        if (!self::tableExists($table) || self::columnExists($table, $column)) {
            return;
        }
        $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $safeColumn = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
        $definition = self::driver() === 'mysql' ? $mysqlDefinition : $sqliteDefinition;
        self::pdo()->exec("ALTER TABLE {$safeTable} ADD COLUMN {$safeColumn} {$definition}");
    }

    private static function ensureIndex(string $name, string $table, string $columns, bool $unique = false): void
    {
        $pdo = self::pdo();
        $safeName = preg_replace('/[^a-zA-Z0-9_]/', '', $name);
        $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $safeColumns = implode(', ', array_map(static fn($c) => preg_replace('/[^a-zA-Z0-9_]/', '', trim($c)), explode(',', $columns)));
        try {
            if (self::driver() === 'mysql') {
                $exists = $pdo->prepare("SHOW INDEX FROM `{$safeTable}` WHERE Key_name = ?");
                $exists->execute([$safeName]);
                if ($exists->fetch()) {
                    return;
                }
                $pdo->exec('CREATE ' . ($unique ? 'UNIQUE ' : '') . "INDEX {$safeName} ON {$safeTable} ({$safeColumns})");
            } else {
                $pdo->exec('CREATE ' . ($unique ? 'UNIQUE ' : '') . "INDEX IF NOT EXISTS {$safeName} ON {$safeTable} ({$safeColumns})");
            }
        } catch (Throwable $e) {
            // Nao bloqueia o sistema por indice duplicado em hospedagens diferentes.
        }
    }

    private static function createTheoreticalLessonsTableIfMissing(): void
    {
        if (self::tableExists('theoretical_lessons')) {
            return;
        }
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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");
        } else {
            self::pdo()->exec("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
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_theory_date ON theoretical_lessons(lesson_date, start_time)');
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_theory_active ON theoretical_lessons(is_active)');
        }
    }

    private static function createEbooksTablesIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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,
                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");
            self::pdo()->exec("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");
        } else {
            self::pdo()->exec("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,
                is_active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            )");
            self::pdo()->exec("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)
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_ebooks_active ON ebooks(is_active)');
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_ebook_access_student ON ebook_student_access(student_id)');
        }
    }


    private static function createProfessionalMaterialsTableIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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,
                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");
        } else {
            self::pdo()->exec("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,
                is_active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_prof_material_professional ON professional_materials(professional_id)');
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_prof_material_active ON professional_materials(is_active)');
        }
    }

    private static function createQuickLinksTableIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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");
        } else {
            self::pdo()->exec("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
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_quick_links_active ON quick_links(is_active)');
        }
    }

    private static function createContactsTableIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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,
                created_at DATETIME NOT NULL,
                INDEX idx_quick_contacts_owner (owner_type, owner_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        } else {
            self::pdo()->exec("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,
                created_at TEXT NOT NULL
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_quick_contacts_owner ON quick_contacts(owner_type, owner_id)');
        }
    }


    private static function createProfessionalMessagesTableIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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");
        } else {
            self::pdo()->exec("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
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_prof_messages_recipient ON professional_messages(recipient_id, created_at)');
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_prof_messages_sender ON professional_messages(sender_id, created_at)');
        }
    }


    private static function createStudentLessonPackagesTableIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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");
        } else {
            self::pdo()->exec("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)
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_student_lesson_packages_student ON student_lesson_packages(student_id)');
        }
    }

    private static function normalizeStudentLessonPackageBalances(): void
    {
        if (!self::tableExists('student_lesson_packages')) {
            return;
        }

        // Compatibilidade com bancos antigos em que os contadores podiam estar nulos.
        // Um saldo nulo fazia a confirmação da aula cair incorretamente em conflito.
        self::pdo()->exec("UPDATE student_lesson_packages
            SET contracted_lessons = CASE WHEN contracted_lessons IS NULL OR contracted_lessons < 0 THEN 0 ELSE contracted_lessons END,
                used_lessons = CASE WHEN used_lessons IS NULL OR used_lessons < 0 THEN 0 ELSE used_lessons END
            WHERE contracted_lessons IS NULL OR used_lessons IS NULL OR contracted_lessons < 0 OR used_lessons < 0");
    }

    private static function createBeginnerTrainingTablesIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("CREATE TABLE IF NOT EXISTS beginner_instructors (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(180) NOT NULL,
                cpf VARCHAR(20) NULL,
                email VARCHAR(190) NOT NULL,
                whatsapp VARCHAR(30) NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                admin_notes TEXT NULL,
                created_at DATETIME NOT NULL,
                UNIQUE KEY uq_beginner_email (email),
                UNIQUE KEY uq_beginner_cpf (cpf),
                INDEX idx_beginner_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            self::pdo()->exec("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,
                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");
            self::pdo()->exec("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");
        } else {
            self::pdo()->exec("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,
                created_at TEXT NOT NULL
            )");
            self::pdo()->exec("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,
                is_active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            )");
            self::pdo()->exec("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)
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_beginner_status ON beginner_instructors(status)');
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_training_contents_active ON training_contents(is_active, content_type)');
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_training_orders_status ON training_orders(status)');
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_training_orders_beginner ON training_orders(beginner_id)');
        }
    }


    private static function fixAvailabilityUniqueIndexes(): void
    {
        if (!self::tableExists('availability_slots')) {
            return;
        }

        // Corrige bancos antigos onde o horário era unico globalmente por start_at.
        // O correto e permitir o mesmo horário para instrutores diferentes,
        // bloqueando duplicado apenas para o mesmo profissional + horário.
        try {
            if (self::driver() === 'mysql') {
                self::fixMysqlAvailabilityIndexes();
            } else {
                self::fixSqliteAvailabilityIndexes();
            }
            self::ensureIndex('uq_slots_professional_start', 'availability_slots', 'professional_id, start_at', true);
            self::ensureIndex('idx_slots_start_at', 'availability_slots', 'start_at', false);
        } catch (Throwable $e) {
            // Nao bloqueia abertura do sistema por causa de indice em hospedagem diferente.
        }
    }

    private static function fixMysqlAvailabilityIndexes(): void
    {
        $pdo = self::pdo();
        $stmt = $pdo->query("SHOW INDEX FROM `availability_slots`");
        $indexes = [];
        foreach ($stmt->fetchAll() as $row) {
            $name = $row['Key_name'] ?? '';
            if ($name === 'PRIMARY') {
                continue;
            }
            $indexes[$name]['unique'] = ((int)($row['Non_unique'] ?? 1) === 0);
            $indexes[$name]['columns'][(int)($row['Seq_in_index'] ?? 0)] = $row['Column_name'] ?? '';
        }
        foreach ($indexes as $name => $info) {
            ksort($info['columns']);
            $columns = array_values($info['columns']);
            if (($info['unique'] ?? false) && $columns === ['start_at']) {
                $safeName = str_replace('`', '', $name);
                $pdo->exec("ALTER TABLE `availability_slots` DROP INDEX `{$safeName}`");
            }
        }
    }

    private static function fixSqliteAvailabilityIndexes(): void
    {
        $pdo = self::pdo();
        $needsRebuild = false;
        foreach ($pdo->query('PRAGMA index_list(availability_slots)')->fetchAll() as $idx) {
            $name = $idx['name'] ?? $idx[1] ?? '';
            $unique = (int)($idx['unique'] ?? $idx[2] ?? 0) === 1;
            if (!$unique || $name === '') {
                continue;
            }
            $safeName = str_replace("'", "''", $name);
            $cols = [];
            foreach ($pdo->query("PRAGMA index_info('{$safeName}')")->fetchAll() as $col) {
                $cols[] = $col['name'] ?? $col[2] ?? '';
            }
            $isCorrectUnique = ($cols === ['professional_id', 'start_at']);
            if ($isCorrectUnique) {
                continue;
            }
            // Qualquer UNIQUE antigo envolvendo start_at pode bloquear outro instrutor no mesmo horário.
            if (in_array('start_at', $cols, true)) {
                $origin = $idx['origin'] ?? $idx[3] ?? '';
                if ($origin === 'c') {
                    $quoted = '"' . str_replace('"', '""', $name) . '"';
                    $pdo->exec('DROP INDEX IF EXISTS ' . $quoted);
                } else {
                    $needsRebuild = true;
                }
            }
        }
        if (!$needsRebuild) {
            return;
        }

        $pdo->exec('PRAGMA foreign_keys = OFF');
        $pdo->beginTransaction();
        try {
            $pdo->exec("CREATE TABLE availability_slots_new (
                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)
            )");
            $pdo->exec("INSERT OR IGNORE INTO availability_slots_new (id, professional_id, start_at, end_at, is_active, created_at)
                SELECT id, professional_id, start_at, end_at, is_active, created_at FROM availability_slots ORDER BY id");
            $pdo->exec('DROP TABLE availability_slots');
            $pdo->exec('ALTER TABLE availability_slots_new RENAME TO availability_slots');
            $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS uq_slots_professional_start ON availability_slots(professional_id, start_at)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_slots_start_at ON availability_slots(start_at)');
            $pdo->commit();
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        } finally {
            $pdo->exec('PRAGMA foreign_keys = ON');
        }
    }


    private static function createVideoLessonTablesIfMissing(): void
    {
        $pdo = self::pdo();
        if (self::driver() === 'mysql') {
            $pdo->exec("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',
                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");
            $pdo->exec("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");
        } else {
            $pdo->exec("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', is_active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL
            )");
            $pdo->exec("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)
            )");
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_video_lessons_active ON video_lessons(is_active, audience)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_video_access_recipient ON video_lesson_access(recipient_type, recipient_id)');
        }
    }

    private static function createPaymentCenterTablesIfMissing(): void
    {
        $pdo = self::pdo();
        if (self::driver() === 'mysql') {
            $pdo->exec("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");
            $pdo->exec("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");
        } else {
            $pdo->exec("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
            )");
            $pdo->exec("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)
            )");
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_payment_banks_active ON payment_banks(is_active)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_payment_charges_recipient ON payment_charges(recipient_type, recipient_id, is_active)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_payment_charges_status ON payment_charges(status)');
        }
    }

    private static function createStudentAnnouncementsTableIfMissing(): void
    {
        if (self::driver() === 'mysql') {
            self::pdo()->exec("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");
        } else {
            self::pdo()->exec("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
            )");
            self::pdo()->exec('CREATE INDEX IF NOT EXISTS idx_student_announcements_active ON student_announcements(is_active, starts_at, ends_at)');
        }
    }

    private static function createCommunicationTablesIfMissing(): void
    {
        $pdo = self::pdo();
        if (self::driver() === 'mysql') {
            $pdo->exec("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");
            $pdo->exec("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");
            $pdo->exec("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");
            $pdo->exec("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");
            $pdo->exec("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");
            $pdo->exec("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");
        } else {
            $pdo->exec("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
            )");
            $pdo->exec("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)
            )");
            $pdo->exec("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
            )");
            $pdo->exec("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)
            )");
            $pdo->exec("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)
            )");
            $pdo->exec("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)
            )");
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_theory_group_instructor ON theory_groups(instructor_id)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_theory_member_student ON theory_group_members(student_id)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_internal_direct ON internal_messages(recipient_type, recipient_id, conversation_type)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_internal_group ON internal_messages(group_id, conversation_type)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_internal_created ON internal_messages(created_at)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_internal_contact_owner ON internal_contacts(owner_type, owner_id)');
        }
    }


    private static function createProfessionalChatTablesIfMissing(): void
    {
        $pdo = self::pdo();
        if (self::driver() === 'mysql') {
            $pdo->exec("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");
            $pdo->exec("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");
        } else {
            $pdo->exec("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
            )");
            $pdo->exec("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)
            )");
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_prof_chat_owner ON professional_chat_groups(owner_professional_id)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_prof_chat_member_prof ON professional_chat_group_members(professional_id)');
        }
    }

    private static function createEbookGroupAccessTableIfMissing(): void
    {
        $pdo = self::pdo();
        if (self::driver() === 'mysql') {
            $pdo->exec("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");
        } else {
            $pdo->exec("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)
            )");
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_ebook_group_group ON ebook_group_access(group_id)');
        }
    }

    private static function createStudentLearningLevelsTableIfMissing(): void
    {
        $pdo = self::pdo();
        if (self::driver() === 'mysql') {
            $pdo->exec("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");
        } else {
            $pdo->exec("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)
            )");
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_learning_student ON student_learning_levels(student_id)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_learning_professional ON student_learning_levels(professional_id)');
        }
    }

    private static function backfillLegacyStudentLearningLevels(): void
    {
        if (!self::tableExists('student_learning_levels') || !self::tableExists('users') || !self::tableExists('professionals')) {
            return;
        }
        $sql = "SELECT u.id AS student_id, u.student_level_professional_id AS professional_id,
                       u.student_level AS level_value, u.student_level_note AS note,
                       u.student_level_updated_at AS updated_at
                FROM users u
                INNER JOIN professionals p ON p.id = u.student_level_professional_id
                WHERE u.role = 'student'
                  AND u.student_level IS NOT NULL
                  AND TRIM(u.student_level) <> ''
                  AND u.student_level_professional_id IS NOT NULL";
        $rows = self::pdo()->query($sql)->fetchAll();
        if (!$rows) {
            return;
        }
        $insertSql = self::driver() === 'mysql'
            ? "INSERT IGNORE INTO student_learning_levels (student_id,professional_id,category_key,level_value,note,updated_at) VALUES (?,?,?,?,?,?)"
            : "INSERT OR IGNORE INTO student_learning_levels (student_id,professional_id,category_key,level_value,note,updated_at) VALUES (?,?,?,?,?,?)";
        $insert = self::pdo()->prepare($insertSql);
        foreach ($rows as $row) {
            $insert->execute([
                (int)$row['student_id'],
                (int)$row['professional_id'],
                'geral',
                (string)$row['level_value'],
                ($row['note'] ?? null) !== '' ? ($row['note'] ?? null) : null,
                !empty($row['updated_at']) ? (string)$row['updated_at'] : date('Y-m-d H:i:s'),
            ]);
        }
    }

    private static function syncAppointmentInternalContacts(): void
    {
        if (!self::tableExists('appointments') || !self::tableExists('internal_contacts')) {
            return;
        }
        $pdo = self::pdo();
        $createdExpr = self::driver() === 'mysql' ? "COALESCE(MIN(a.created_at), NOW())" : "COALESCE(MIN(a.created_at), datetime('now'))";
        if (self::driver() === 'mysql') {
            $pdo->exec("INSERT IGNORE INTO internal_contacts (owner_type,owner_id,contact_type,contact_id,created_at)
                SELECT 'student',a.student_id,'professional',a.professional_id,{$createdExpr}
                FROM appointments a GROUP BY a.student_id,a.professional_id");
            $pdo->exec("INSERT IGNORE INTO internal_contacts (owner_type,owner_id,contact_type,contact_id,created_at)
                SELECT 'professional',a.professional_id,'student',a.student_id,{$createdExpr}
                FROM appointments a GROUP BY a.student_id,a.professional_id");
        } else {
            $pdo->exec("INSERT OR IGNORE INTO internal_contacts (owner_type,owner_id,contact_type,contact_id,created_at)
                SELECT 'student',a.student_id,'professional',a.professional_id,{$createdExpr}
                FROM appointments a GROUP BY a.student_id,a.professional_id");
            $pdo->exec("INSERT OR IGNORE INTO internal_contacts (owner_type,owner_id,contact_type,contact_id,created_at)
                SELECT 'professional',a.professional_id,'student',a.student_id,{$createdExpr}
                FROM appointments a GROUP BY a.student_id,a.professional_id");
        }

        if (self::tableExists('quick_contacts') && self::tableExists('users')) {
            $rows = $pdo->query("SELECT DISTINCT a.professional_id,a.student_id,u.name,u.whatsapp
                FROM appointments a INNER JOIN users u ON u.id=a.student_id
                WHERE u.deleted_at IS NULL AND COALESCE(u.whatsapp,'')<>''")->fetchAll();
            $find = $pdo->prepare("SELECT id FROM quick_contacts WHERE owner_type='professional' AND owner_id=? AND notes=? LIMIT 1");
            $update = $pdo->prepare("UPDATE quick_contacts SET name=?,whatsapp=?,category='alunos' WHERE id=?");
            $insert = $pdo->prepare("INSERT INTO quick_contacts (owner_type,owner_id,name,whatsapp,notes,category,created_at) VALUES ('professional',?,?,?,?, 'alunos',?)");
            foreach ($rows as $row) {
                $note = 'Aluno vinculado automaticamente. ID:' . (int)$row['student_id'];
                $find->execute([(int)$row['professional_id'], $note]);
                $id = (int)($find->fetchColumn() ?: 0);
                if ($id > 0) {
                    $update->execute([(string)$row['name'], only_digits((string)$row['whatsapp']), $id]);
                } else {
                    $insert->execute([(int)$row['professional_id'], (string)$row['name'], only_digits((string)$row['whatsapp']), $note, date('Y-m-d H:i:s')]);
                }
            }
        }
    }

    private static function fillInternalCodes(): void
    {
        $pdo = self::pdo();
        $tables = ['users', 'professionals', 'beginner_instructors'];
        $codeSources = [
            ['users', 'internal_code'],
            ['professionals', 'internal_code'],
            ['beginner_instructors', 'internal_code'],
            ['theory_groups', 'group_code'],
        ];
        foreach ($tables as $table) {
            if (!self::tableExists($table) || !self::columnExists($table, 'internal_code')) {
                continue;
            }
            $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
            $rows = $pdo->query("SELECT id FROM {$safeTable} WHERE internal_code IS NULL OR internal_code = ''")->fetchAll();
            $update = $pdo->prepare("UPDATE {$safeTable} SET internal_code = ? WHERE id = ?");
            foreach ($rows as $row) {
                do {
                    $code = (string)random_int(100000000, 999999999);
                    $exists = false;
                    foreach ($codeSources as [$checkTable, $checkColumn]) {
                        if (!self::tableExists($checkTable) || !self::columnExists($checkTable, $checkColumn)) continue;
                        $safeCheck = preg_replace('/[^a-zA-Z0-9_]/', '', $checkTable);
                        $safeColumn = preg_replace('/[^a-zA-Z0-9_]/', '', $checkColumn);
                        $check = $pdo->prepare("SELECT COUNT(*) FROM {$safeCheck} WHERE {$safeColumn} = ?");
                        $check->execute([$code]);
                        if ((int)$check->fetchColumn() > 0) { $exists = true; break; }
                    }
                } while ($exists);
                $update->execute([$code, (int)$row['id']]);
            }
        }
    }

    private static function fillProfessionalTokens(): void
    {
        $pdo = self::pdo();
        $stmt = $pdo->query("SELECT id FROM professionals WHERE panel_token IS NULL OR panel_token = ''");
        $update = $pdo->prepare('UPDATE professionals SET panel_token = ? WHERE id = ?');
        foreach ($stmt->fetchAll() as $row) {
            $token = bin2hex(random_bytes(24));
            $update->execute([$token, (int)$row['id']]);
        }
    }
}
