-- =====================================================================
-- 0028_asset_custody.sql — object-creator (9 Jul 2026, mandat owner):
-- "Pinjam Aset" — custody karyawan atas aset perusahaan (kendaraan/laptop/
-- HP dst). Kasus pertama = pinjam kendaraan, tapi skema generik (bukan
-- vehicle_id/vehicle-only) supaya laptop/HP dst tinggal pakai tabel yang
-- sama (asset_id -> assets, tanpa kolom khusus kendaraan wajib).
--
-- odometer_out/odometer_in HANYA relevan utk kendaraan (asset ber-
-- meter_type='km' biasanya) — dibiarkan NULL bebas utk aset non-kendaraan,
-- TIDAK divalidasi terhadap meter_type (keputusan desain: form tidak
-- memblokir input odometer utk aset lain, sekadar kosongkan bila tak
-- relevan; anti-over-engineering utk v1).
--
-- Integrasi 2 arah (OBJECT-STANDARD §2 "penyebutan object tanpa link =
-- BUG"): riwayat custody wajib tampil di AssetDetailPage (asset.php, card
-- "Riwayat peminjaman") DAN HrEmployeeDetailPage (employee.php, card "Aset
-- yang dipegang") — lihat perubahan Page terkait di commit yang sama.
--
-- Seed dummy pakai asset id=21 (Mobil Operasional Avanza, AST-000021,
-- meter_type='km', dari 0004_seed_demo.sql) + employee id 1 & 3 (dari
-- 0010_seed_hr_hk.sql, dijamin ada di property_id=1) — bukan representasi
-- fakta "siapa yang benar-benar pakai mobil", murni DUMMY agar halaman
-- langsung hidup (konvensi object-creator).
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;
SET default_storage_engine = InnoDB;

CREATE TABLE asset_custody (
    id                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id           INT UNSIGNED NOT NULL,
    asset_id              INT UNSIGNED NOT NULL,
    employee_id           INT UNSIGNED NOT NULL,
    purpose               VARCHAR(255) NOT NULL COMMENT 'keperluan pinjam',
    checkout_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expected_return_date  DATE NULL,
    returned_at           DATETIME NULL,
    condition_out         VARCHAR(255) NOT NULL COMMENT 'kondisi aset saat keluar',
    condition_in          VARCHAR(255) NULL COMMENT 'kondisi aset saat kembali',
    odometer_out          INT UNSIGNED NULL COMMENT 'HANYA relevan kendaraan; kosong utk aset lain',
    odometer_in           INT UNSIGNED NULL,
    status                ENUM('dipegang','dikembalikan') NOT NULL DEFAULT 'dipegang',
    created_by            INT UNSIGNED NULL COMMENT 'user yang mencatat checkout (FK users)',
    notes                 TEXT NULL,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_asset_custody_property (property_id),
    KEY idx_asset_custody_asset (property_id, asset_id),
    KEY idx_asset_custody_employee (property_id, employee_id),
    KEY idx_asset_custody_status (property_id, status),
    CONSTRAINT fk_asset_custody_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_asset_custody_asset FOREIGN KEY (asset_id) REFERENCES assets(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_asset_custody_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_asset_custody_created_by FOREIGN KEY (created_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SEED DUMMY (property_id=1). Asset 21 = Avanza operasional (kendaraan).
-- ---------------------------------------------------------------------

-- #1 — SUDAH DIKEMBALIKAN (riwayat).
INSERT INTO asset_custody
    (property_id, asset_id, employee_id, purpose, checkout_at, expected_return_date,
     returned_at, condition_out, condition_in, odometer_out, odometer_in, status, created_by, notes)
VALUES
    (1, 21, 3, 'DUMMY — antar-jemput tamu ke Bandara Ngurah Rai', '2026-07-01 08:00:00', '2026-07-01',
     '2026-07-01 15:30:00', 'DUMMY — kondisi baik, BBM full, tidak ada lecet', 'DUMMY — kondisi baik, BBM 1/2, tidak ada lecet baru',
     45000, 45120, 'dikembalikan', 1, 'DUMMY — seed asset-custody (0028)');

-- #2 — SEDANG DIPEGANG (aktif, belum dikembalikan).
INSERT INTO asset_custody
    (property_id, asset_id, employee_id, purpose, checkout_at, expected_return_date,
     condition_out, odometer_out, status, created_by, notes)
VALUES
    (1, 21, 1, 'DUMMY — belanja keperluan operasional ke kota', NOW() - INTERVAL 2 DAY, CURDATE() + INTERVAL 1 DAY,
     'DUMMY — kondisi baik, BBM full, tidak ada lecet', 45210, 'dipegang', 1, 'DUMMY — seed asset-custody (0028)');