-- ============================================================
-- Assets Management System 1.0
-- Phase 4: Asset Operations — Assignment, Transfer, Return, Maintenance
-- Run AFTER database/migrations/003_phase3_procurement.sql + 003_phase3_seed.sql
--
-- Design note: full source/destination approval chains (spec section 29)
-- and a formal maintenance request→approval→completion pipeline (section
-- 30) are represented here in a single-table, status-driven form rather
-- than as separate request/approval entities — consistent with how
-- Phase 3's purchase order used one table with a status column instead
-- of a separate approval log. A dedicated multi-step approval engine
-- (spec section 64) can layer on top of these status columns later
-- without a schema change.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- asset_assignments (spec section 26)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asset_assignments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    asset_id INT UNSIGNED NOT NULL,
    assignment_number VARCHAR(40) NOT NULL,
    custodian_employee_id INT UNSIGNED NOT NULL,
    location_id INT UNSIGNED NULL,
    issue_date DATE NOT NULL,
    expected_return_date DATE NULL,
    actual_return_date DATE NULL,
    condition_at_issue VARCHAR(50) NOT NULL,
    status ENUM('Active','Returned') NOT NULL DEFAULT 'Active',
    issued_by INT UNSIGNED NULL,
    remarks TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_assignment_number (organization_id, assignment_number),
    CONSTRAINT fk_asn_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_asn_asset FOREIGN KEY (asset_id) REFERENCES assets(id),
    CONSTRAINT fk_asn_custodian FOREIGN KEY (custodian_employee_id) REFERENCES employees(id),
    CONSTRAINT fk_asn_location FOREIGN KEY (location_id) REFERENCES locations(id),
    INDEX idx_asn_asset (asset_id),
    INDEX idx_asn_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- asset_transfers (spec section 29)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asset_transfers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    asset_id INT UNSIGNED NOT NULL,
    transfer_number VARCHAR(40) NOT NULL,
    transfer_type ENUM('Department','Location','Employee','Office','Inter-unit') NOT NULL DEFAULT 'Location',
    from_location_id INT UNSIGNED NULL,
    to_location_id INT UNSIGNED NULL,
    from_custodian_id INT UNSIGNED NULL,
    to_custodian_id INT UNSIGNED NULL,
    transfer_date DATE NOT NULL,
    reason VARCHAR(255) NULL,
    status ENUM('Pending','Completed','Cancelled') NOT NULL DEFAULT 'Pending',
    requested_by INT UNSIGNED NULL,
    completed_by INT UNSIGNED NULL,
    completed_at DATETIME NULL,
    remarks TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_transfer_number (organization_id, transfer_number),
    CONSTRAINT fk_trf_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_trf_asset FOREIGN KEY (asset_id) REFERENCES assets(id),
    CONSTRAINT fk_trf_from_loc FOREIGN KEY (from_location_id) REFERENCES locations(id),
    CONSTRAINT fk_trf_to_loc FOREIGN KEY (to_location_id) REFERENCES locations(id),
    CONSTRAINT fk_trf_from_cust FOREIGN KEY (from_custodian_id) REFERENCES employees(id),
    CONSTRAINT fk_trf_to_cust FOREIGN KEY (to_custodian_id) REFERENCES employees(id),
    INDEX idx_trf_asset (asset_id),
    INDEX idx_trf_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- asset_returns (spec section 28)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asset_returns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    asset_id INT UNSIGNED NOT NULL,
    assignment_id INT UNSIGNED NOT NULL,
    return_number VARCHAR(40) NOT NULL,
    return_date DATE NOT NULL,
    condition_at_return VARCHAR(50) NOT NULL,
    damage_notes VARCHAR(255) NULL,
    missing_accessories VARCHAR(255) NULL,
    disposition ENUM('Back to Store','Reassigned Immediately','Sent to Maintenance') NOT NULL DEFAULT 'Back to Store',
    received_by INT UNSIGNED NULL,
    remarks TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_return_number (organization_id, return_number),
    CONSTRAINT fk_ret_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_ret_asset FOREIGN KEY (asset_id) REFERENCES assets(id),
    CONSTRAINT fk_ret_assignment FOREIGN KEY (assignment_id) REFERENCES asset_assignments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- asset_maintenance (spec sections 30-31)
-- Single table spans request -> approval -> completion via `status`,
-- consistent with the Phase 3 purchase-order pattern.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asset_maintenance (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    asset_id INT UNSIGNED NOT NULL,
    maintenance_number VARCHAR(40) NOT NULL,
    problem_description TEXT NOT NULL,
    reported_date DATE NOT NULL,
    priority ENUM('Low','Medium','High','Critical') NOT NULL DEFAULT 'Medium',
    status ENUM('Requested','Approved','In Progress','Completed','Cancelled') NOT NULL DEFAULT 'Requested',
    technician_name VARCHAR(150) NULL,
    vendor_name VARCHAR(150) NULL,
    estimated_cost DECIMAL(18,2) NULL,
    actual_cost DECIMAL(18,2) NULL,
    parts_replaced VARCHAR(255) NULL,
    service_date DATE NULL,
    warranty_covered TINYINT(1) NOT NULL DEFAULT 0,
    downtime_days SMALLINT UNSIGNED NULL,
    next_maintenance_date DATE NULL,
    reported_by INT UNSIGNED NULL,
    completed_by INT UNSIGNED NULL,
    remarks TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_maintenance_number (organization_id, maintenance_number),
    CONSTRAINT fk_mnt_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_mnt_asset FOREIGN KEY (asset_id) REFERENCES assets(id),
    INDEX idx_mnt_asset (asset_id),
    INDEX idx_mnt_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;