<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateSarprasTables extends Migration
{
    public function up()
    {
        // 1. Users
        $this->forge->addField([
            'id'       => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name'     => ['type' => 'VARCHAR', 'constraint' => 100],
            'username' => ['type' => 'VARCHAR', 'constraint' => 50],
            'password' => ['type' => 'VARCHAR', 'constraint' => 255],
            'role'     => ['type' => 'ENUM', 'constraint' => ['admin', 'user'], 'default' => 'user'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('users', true);

        // 2. Ruangan
        $this->forge->addField([
            'id'           => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'nama_ruangan' => ['type' => 'VARCHAR', 'constraint' => 100],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('ruangan', true);

        // 3. Sumber Dana
        $this->forge->addField([
            'id'          => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'nama_sumber' => ['type' => 'VARCHAR', 'constraint' => 100],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('sumber_dana', true);

        // 4. Jenis Barang
        $this->forge->addField([
            'id'         => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'nama_jenis' => ['type' => 'VARCHAR', 'constraint' => 100],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('jenis_barang', true);

        // 5. Barang
        $this->forge->addField([
            'id'             => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'kode_barang'    => ['type' => 'VARCHAR', 'constraint' => 50],
            'nama_barang'    => ['type' => 'VARCHAR', 'constraint' => 150],
            'id_jenis'       => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'merk'           => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'spesifikasi'    => ['type' => 'TEXT', 'null' => true],
            'jumlah'         => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'harga_satuan'   => ['type' => 'DOUBLE', 'default' => 0],
            'harga_total'    => ['type' => 'DOUBLE', 'default' => 0],
            'id_sumber_dana' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'id_ruangan'     => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'foto'           => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'tahun_perolehan'=> ['type' => 'YEAR', 'null' => true],
            'umur_ekonomis'  => ['type' => 'INT', 'constraint' => 11, 'null' => true], // in years
            'penyusutan_per_tahun' => ['type' => 'DOUBLE', 'default' => 0],
            'kondisi'        => ['type' => 'ENUM', 'constraint' => ['Baik', 'Rusak Ringan', 'Rusak Berat'], 'default' => 'Baik'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        // Foreign keys can be added, but skipping strict constraints for simplicity
        $this->forge->createTable('barang', true);

        // 6. Tanah
        $this->forge->addField([
            'id'                 => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'nama_tanah'         => ['type' => 'VARCHAR', 'constraint' => 150],
            'status_kepemilikan' => ['type' => 'VARCHAR', 'constraint' => 100],
            'luas'               => ['type' => 'DOUBLE', 'default' => 0], // m2
            'lokasi'             => ['type' => 'TEXT'],
            'tahun_perolehan'    => ['type' => 'YEAR', 'null' => true],
            'nilai_perolehan'    => ['type' => 'DOUBLE', 'default' => 0],
            'kenaikan_per_tahun' => ['type' => 'DOUBLE', 'default' => 0], // user requested Kenaikan untuk Tanah
            'nilai_sekarang'     => ['type' => 'DOUBLE', 'default' => 0],
            'kondisi'            => ['type' => 'VARCHAR', 'constraint' => 100],
            'keterangan'         => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('tanah', true);

        // 7. Bangunan
        $this->forge->addField([
            'id'                   => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'nama_bangunan'        => ['type' => 'VARCHAR', 'constraint' => 150],
            'luas_bangunan'        => ['type' => 'DOUBLE', 'default' => 0], // m2
            'jumlah_ruang'         => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'tahun_dibangun'       => ['type' => 'YEAR', 'null' => true],
            'nilai_perolehan'      => ['type' => 'DOUBLE', 'default' => 0],
            'penyusutan_per_tahun' => ['type' => 'DOUBLE', 'default' => 0],
            'nilai_sekarang'       => ['type' => 'DOUBLE', 'default' => 0],
            'kondisi'              => ['type' => 'ENUM', 'constraint' => ['Baik', 'Rusak Ringan', 'Rusak Berat'], 'default' => 'Baik'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('bangunan', true);

        // 8. Buku
        $this->forge->addField([
            'id'             => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'tgl_pengadaan'  => ['type' => 'DATE', 'null' => true],
            'id_sumber_dana' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'judul'          => ['type' => 'VARCHAR', 'constraint' => 200],
            'pengarang'      => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'penerbit'       => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'tahun_terbit'   => ['type' => 'YEAR', 'null' => true],
            'harga_satuan'   => ['type' => 'DOUBLE', 'default' => 0],
            'jumlah'         => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'total_harga'    => ['type' => 'DOUBLE', 'default' => 0],
            'foto'           => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'id_ruangan'     => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('buku', true);

        // 9. Peminjaman
        $this->forge->addField([
            'id'               => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'id_barang'        => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'id_ruangan'       => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'tgl_peminjaman'   => ['type' => 'DATE', 'null' => true],
            'tgl_pengembalian' => ['type' => 'DATE', 'null' => true],
            'biaya'            => ['type' => 'DOUBLE', 'default' => 0],
            'foto'             => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'keterangan'       => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('peminjaman', true);

        // 10. Perbaikan
        $this->forge->addField([
            'id'              => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'id_barang'       => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'id_ruangan'      => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'jenis_perbaikan' => ['type' => 'VARCHAR', 'constraint' => 150],
            'tgl_kerusakan'   => ['type' => 'DATE', 'null' => true],
            'tgl_perbaikan'   => ['type' => 'DATE', 'null' => true],
            'biaya'           => ['type' => 'DOUBLE', 'default' => 0],
            'foto'            => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('perbaikan', true);

        // 11. Pengajuan
        $this->forge->addField([
            'id'              => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'tgl_pengajuan'   => ['type' => 'DATE', 'null' => true],
            'tgl_persetujuan' => ['type' => 'DATE', 'null' => true],
            'nama_barang'     => ['type' => 'VARCHAR', 'constraint' => 150],
            'id_ruangan'      => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'biaya'           => ['type' => 'DOUBLE', 'default' => 0],
            'foto'            => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'keterangan'      => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('pengajuan', true);

        // 12. Penghapusan
        $this->forge->addField([
            'id'              => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'tgl_kerusakan'   => ['type' => 'DATE', 'null' => true],
            'tgl_persetujuan' => ['type' => 'DATE', 'null' => true],
            'id_barang'       => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'id_ruangan'      => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'foto'            => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'keterangan'      => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('penghapusan', true);
    }

    public function down()
    {
        $this->forge->dropTable('penghapusan', true);
        $this->forge->dropTable('pengajuan', true);
        $this->forge->dropTable('perbaikan', true);
        $this->forge->dropTable('peminjaman', true);
        $this->forge->dropTable('buku', true);
        $this->forge->dropTable('bangunan', true);
        $this->forge->dropTable('tanah', true);
        $this->forge->dropTable('barang', true);
        $this->forge->dropTable('jenis_barang', true);
        $this->forge->dropTable('sumber_dana', true);
        $this->forge->dropTable('ruangan', true);
        $this->forge->dropTable('users', true);
    }
}
