<?php

use Wibiesana\Padi\Core\DatabaseManager;

return new class {
    public function up(): void
    {
        $db = DatabaseManager::connection();
        
        // 1. sarpras_items
        $db->exec("CREATE TABLE IF NOT EXISTS sarpras_items (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            kode_barang VARCHAR(50) UNIQUE NOT NULL,
            nama_barang VARCHAR(100) NOT NULL,
            kategori VARCHAR(50) NOT NULL,
            spesifikasi TEXT NULL,
            kondisi VARCHAR(50) DEFAULT 'baik',
            status VARCHAR(20) DEFAULT 'tersedia',
            lokasi_ruang VARCHAR(100) NULL,
            lokasi_rak VARCHAR(100) NULL,
            stok_total INT DEFAULT 1,
            stok_tersedia INT DEFAULT 1,
            gambar VARCHAR(255) NULL,
            qr_code_string VARCHAR(255) NULL,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            INDEX idx_sarpras_items_kode (kode_barang),
            INDEX idx_sarpras_items_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 2. sarpras_peminjam
        $db->exec("CREATE TABLE IF NOT EXISTS sarpras_peminjam (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            tipe_peminjam VARCHAR(20) NOT NULL,
            id_identitas VARCHAR(50) NOT NULL UNIQUE,
            nama_lengkap VARCHAR(100) NOT NULL,
            kelas VARCHAR(50) NULL,
            jurusan VARCHAR(100) NULL,
            nomor_telepon 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_sarpras_peminjam_identitas (id_identitas)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 3. sarpras_petugas
        $db->exec("CREATE TABLE IF NOT EXISTS sarpras_petugas (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNSIGNED NOT NULL UNIQUE,
            nip VARCHAR(50) NULL UNIQUE,
            nama_lengkap VARCHAR(100) NOT NULL,
            jabatan VARCHAR(100) NULL,
            kontak VARCHAR(25) NULL,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // 4. sarpras_peminjaman
        $db->exec("CREATE TABLE IF NOT EXISTS sarpras_peminjaman (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            kode_transaksi VARCHAR(50) UNIQUE NOT NULL,
            item_id INT UNSIGNED NOT NULL,
            peminjam_id INT UNSIGNED NOT NULL,
            petugas_id INT UNSIGNED NOT NULL,
            tanggal_pinjam DATE NOT NULL,
            tanggal_rencana_kembali DATE NOT NULL,
            tanggal_kembali DATE NULL,
            jumlah_pinjam INT DEFAULT 1,
            keperluan TEXT NULL,
            status VARCHAR(20) DEFAULT 'dipinjam',
            denda DECIMAL(10, 2) DEFAULT 0.00,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NULL,
            updated_at DATETIME NULL,
            FOREIGN KEY (item_id) REFERENCES sarpras_items(id) ON DELETE CASCADE,
            FOREIGN KEY (peminjam_id) REFERENCES sarpras_peminjam(id) ON DELETE CASCADE,
            FOREIGN KEY (petugas_id) REFERENCES sarpras_petugas(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        echo "✓ Sarpras tables created successfully\n";
    }

    public function down(): void
    {
        $db = DatabaseManager::connection();
        $db->exec("DROP TABLE IF EXISTS sarpras_peminjaman");
        $db->exec("DROP TABLE IF EXISTS sarpras_petugas");
        $db->exec("DROP TABLE IF EXISTS sarpras_peminjam");
        $db->exec("DROP TABLE IF EXISTS sarpras_items");
        echo "✓ Sarpras tables dropped successfully\n";
    }
};