<?php

use Wibiesana\Padi\Core\DatabaseManager;

return new class {
    public function up(): void
    {
        $db = DatabaseManager::connection();
        
        // 1. companies
        $db->exec("CREATE TABLE IF NOT EXISTS companies (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(150) NOT NULL,
            industry_sector VARCHAR(100) NOT NULL,
            address TEXT NULL,
            contact_person VARCHAR(100) NULL,
            phone VARCHAR(25) NULL,
            email VARCHAR(100) NULL,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            INDEX idx_companies_name (name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 2. mous
        $db->exec("CREATE TABLE IF NOT EXISTS mous (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            company_id INT UNSIGNED NOT NULL,
            mou_number VARCHAR(100) NOT NULL,
            agreement_date DATE NOT NULL,
            start_date DATE NOT NULL,
            expiry_date DATE NOT NULL,
            status VARCHAR(20) DEFAULT 'active',
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
            INDEX idx_mous_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 3. job_vacancies
        $db->exec("CREATE TABLE IF NOT EXISTS job_vacancies (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            company_id INT UNSIGNED NULL,
            title VARCHAR(150) NOT NULL,
            qualifications TEXT NOT NULL,
            application_deadline DATE NOT NULL,
            contact_info VARCHAR(200) NULL,
            location VARCHAR(150) NULL,
            status VARCHAR(20) DEFAULT 'open',
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
            INDEX idx_job_vacancies_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 4. job_applications
        $db->exec("CREATE TABLE IF NOT EXISTS job_applications (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            job_vacancy_id INT UNSIGNED NOT NULL,
            student_id INT UNSIGNED NOT NULL,
            application_date DATE NOT NULL,
            status VARCHAR(20) DEFAULT 'pending',
            resume_path VARCHAR(255) NULL,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (job_vacancy_id) REFERENCES job_vacancies(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
            INDEX idx_job_applications_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 5. internships (PKL)
        $db->exec("CREATE TABLE IF NOT EXISTS internships (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            company_id INT UNSIGNED NOT NULL,
            student_id INT UNSIGNED NOT NULL,
            advisor_teacher_id INT UNSIGNED NULL,
            start_date DATE NOT NULL,
            end_date DATE NOT NULL,
            report_file_path VARCHAR(255) NULL,
            advisor_score DECIMAL(5,2) NULL,
            status VARCHAR(20) DEFAULT 'requested',
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
            FOREIGN KEY (advisor_teacher_id) REFERENCES teacher(id) ON DELETE SET NULL,
            INDEX idx_internships_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 6. industrial_visits (Kunjin)
        $db->exec("CREATE TABLE IF NOT EXISTS industrial_visits (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            company_id INT UNSIGNED NOT NULL,
            teacher_in_charge_id INT UNSIGNED NULL,
            class_name VARCHAR(50) NOT NULL,
            visit_date DATE NOT NULL,
            documentation_path VARCHAR(255) NULL,
            notes TEXT NULL,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
            FOREIGN KEY (teacher_in_charge_id) REFERENCES teacher(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        echo "✓ Hubin, BKK, PKL, and Kunjin tables created successfully\n";
    }

    public function down(): void
    {
        $db = DatabaseManager::connection();
        $db->exec("DROP TABLE IF EXISTS industrial_visits");
        $db->exec("DROP TABLE IF EXISTS internships");
        $db->exec("DROP TABLE IF EXISTS job_applications");
        $db->exec("DROP TABLE IF EXISTS job_vacancies");
        $db->exec("DROP TABLE IF EXISTS mous");
        $db->exec("DROP TABLE IF EXISTS companies");
        
        echo "✓ Hubin, BKK, PKL, and Kunjin tables dropped successfully\n";
    }
};
