-- Unit and Province asset registers. Ownership is immutable; loans do not transfer it.
CREATE TABLE asset_register (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_type ENUM('province','unit') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 asset_identifier VARCHAR(80) NOT NULL, asset_type ENUM('jewel','collar','regalia','banner','furniture','key','other') NOT NULL,
 description VARCHAR(500) NOT NULL, condition_state ENUM('excellent','good','fair','poor','damaged','missing','disposed') NOT NULL DEFAULT 'good',
 location_label VARCHAR(200) NULL, acquisition_date DATE NULL, disposal_date DATE NULL, purchase_value DECIMAL(12,2) NULL, insured_value DECIMAL(12,2) NULL,
 key_details_cipher MEDIUMTEXT NULL, review_due_at DATE NULL, visibility ENUM('private_officer','member') NOT NULL DEFAULT 'private_officer',
 status ENUM('active','lost','disposal_pending','disposed','archived') NOT NULL DEFAULT 'active', created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 scope_key VARCHAR(90) GENERATED ALWAYS AS (CONCAT(scope_type,':',province_id,':',IFNULL(unit_id,0))) STORED,
 UNIQUE KEY uq_asset_identifier(scope_key,asset_identifier), KEY idx_asset_scope(scope_type,province_id,unit_id,status,review_due_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT chk_asset_scope CHECK((scope_type='province' AND unit_id IS NULL) OR (scope_type='unit' AND unit_id IS NOT NULL)),
 CONSTRAINT chk_asset_disposal CHECK((status='disposed' AND disposal_date IS NOT NULL) OR status<>'disposed')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE asset_images (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_id BIGINT UNSIGNED NOT NULL, tenant_version_id BIGINT UNSIGNED NULL, province_version_id BIGINT UNSIGNED NULL,
 caption VARCHAR(200) NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_asset_image_version(asset_id,tenant_version_id,province_version_id), FOREIGN KEY(asset_id) REFERENCES asset_register(id) ON DELETE CASCADE,
 FOREIGN KEY(tenant_version_id) REFERENCES tenant_document_versions(id) ON DELETE RESTRICT, FOREIGN KEY(province_version_id) REFERENCES province_document_versions(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id), CONSTRAINT chk_asset_image_version CHECK((tenant_version_id IS NULL)<>(province_version_id IS NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE asset_assignments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_id BIGINT UNSIGNED NOT NULL, custodian_person_id BIGINT UNSIGNED NOT NULL,
 checked_out_at DATETIME NOT NULL, due_at DATETIME NULL, checked_in_at DATETIME NULL, issued_by BIGINT UNSIGNED NOT NULL, checked_in_by BIGINT UNSIGNED NULL,
 custodian_confirmed_at DATETIME NULL, return_confirmed_at DATETIME NULL, handover_note VARCHAR(500) NULL,
 active_asset_id BIGINT UNSIGNED GENERATED ALWAYS AS (IF(checked_in_at IS NULL,asset_id,NULL)) STORED,
 UNIQUE KEY uq_asset_active_assignment(active_asset_id), KEY idx_asset_custodian(custodian_person_id,checked_in_at,due_at),
 FOREIGN KEY(asset_id) REFERENCES asset_register(id) ON DELETE CASCADE, FOREIGN KEY(custodian_person_id) REFERENCES people(id),
 FOREIGN KEY(issued_by) REFERENCES users(id), FOREIGN KEY(checked_in_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE asset_loans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_id BIGINT UNSIGNED NOT NULL, borrowing_unit_id BIGINT UNSIGNED NOT NULL,
 loaned_at DATETIME NOT NULL, due_at DATETIME NULL, returned_at DATETIME NULL, approved_by BIGINT UNSIGNED NOT NULL, received_by BIGINT UNSIGNED NULL,
 active_asset_id BIGINT UNSIGNED GENERATED ALWAYS AS (IF(returned_at IS NULL,asset_id,NULL)) STORED,
 UNIQUE KEY uq_asset_active_loan(active_asset_id), KEY idx_asset_loan_unit(borrowing_unit_id,returned_at,due_at),
 FOREIGN KEY(asset_id) REFERENCES asset_register(id) ON DELETE CASCADE, FOREIGN KEY(borrowing_unit_id) REFERENCES units(id),
 FOREIGN KEY(approved_by) REFERENCES users(id), FOREIGN KEY(received_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE asset_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_id BIGINT UNSIGNED NOT NULL, event_type ENUM('created','updated','image_linked','checked_out','custodian_confirmed','checked_in','return_confirmed','loaned','loan_returned','lost','found','disposal_requested','disposed','archived','published','unpublished') NOT NULL,
 assignment_id BIGINT UNSIGNED NULL, loan_id BIGINT UNSIGNED NULL, change_json TEXT NULL, actor_user_id BIGINT UNSIGNED NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_asset_history(asset_id,occurred_at,id), FOREIGN KEY(asset_id) REFERENCES asset_register(id) ON DELETE CASCADE,
 FOREIGN KEY(assignment_id) REFERENCES asset_assignments(id) ON DELETE SET NULL, FOREIGN KEY(loan_id) REFERENCES asset_loans(id) ON DELETE SET NULL,
 FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Custody evidence is append-only. Corrections are recorded as a later event.
CREATE TRIGGER asset_history_no_update BEFORE UPDATE ON asset_history FOR EACH ROW
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Asset history is append-only';
CREATE TRIGGER asset_history_no_delete BEFORE DELETE ON asset_history FOR EACH ROW
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Asset history is append-only';

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280031','Unit and Province asset registers',SHA2('202609280031_asset_registers_v2',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);