-- ============================================================
-- Assets Management System 1.0
-- Phase 2: Core Asset Register
-- Run AFTER database/schema.sql + database/seed.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- asset_categories
-- Self-referencing tree covers Category -> Subcategory -> Type
-- (spec section 12) without forcing four rigid tables up front.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asset_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    name_np VARCHAR(150) NULL,
    code VARCHAR(20) NULL COMMENT 'short code for numbering/reporting, optional',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_cat_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_cat_parent FOREIGN KEY (parent_id) REFERENCES asset_categories(id),
    INDEX idx_cat_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- locations
-- Self-referencing tree covers Office/Department/Branch/Section/
-- Store/Room (spec section 6, 76) without a separate table per level.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS locations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    name_np VARCHAR(150) NULL,
    location_type ENUM('office','department','branch','section','store','room','other') NOT NULL DEFAULT 'other',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_loc_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_loc_parent FOREIGN KEY (parent_id) REFERENCES locations(id),
    INDEX idx_loc_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- employees
-- Minimal custodian directory. Deliberately holds only what's
-- needed for asset accountability (spec section 116) — not a
-- general HR module.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    designation VARCHAR(100) NULL,
    location_id INT UNSIGNED NULL,
    phone VARCHAR(30) NULL,
    linked_user_id INT UNSIGNED NULL COMMENT 'optional link if this employee also has a login',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_emp_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_emp_location FOREIGN KEY (location_id) REFERENCES locations(id),
    CONSTRAINT fk_emp_user FOREIGN KEY (linked_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- assets — core asset master record (spec section 14)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS assets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,

    asset_code VARCHAR(40) NOT NULL COMMENT 'e.g. AST-2082-000001, from next_sequence_number()',
    jinsi_number VARCHAR(60) NULL,
    qr_token CHAR(32) NOT NULL COMMENT 'opaque token for public QR view URL',
    barcode_value VARCHAR(60) NULL,
    serial_number VARCHAR(100) NULL,

    name VARCHAR(200) NOT NULL,
    name_np VARCHAR(200) NULL,
    category_id INT UNSIGNED NULL,
    brand VARCHAR(100) NULL,
    model VARCHAR(100) NULL,
    manufacturer VARCHAR(150) NULL,
    specification TEXT NULL,
    color VARCHAR(50) NULL,
    unit VARCHAR(30) NOT NULL DEFAULT 'Piece',
    quantity DECIMAL(12,2) NOT NULL DEFAULT 1,

    condition_status ENUM('New','Excellent','Good','Fair','Poor','Damaged','Unserviceable','Beyond Economic Repair') NOT NULL DEFAULT 'New',
    status ENUM('Draft','Received','Under Inspection','Accepted','Registered','In Store','Issued','Assigned',
                'Transferred','Under Maintenance','Damaged','Missing','Impaired','Disposed','Auction Pending',
                'Auctioned','Written Off','Deregistered') NOT NULL DEFAULT 'Draft',

    ownership_type VARCHAR(50) NULL,
    acquisition_type ENUM('Purchase','Donation','Grant','Transfer','Construction','Manufactured Internally','Lease','Found/Recovered','Other') NOT NULL DEFAULT 'Purchase',
    acquisition_date DATE NULL,
    fiscal_year_id INT UNSIGNED NULL,

    supplier VARCHAR(150) NULL,
    invoice_number VARCHAR(80) NULL,
    voucher_number VARCHAR(80) NULL,
    funding_source VARCHAR(150) NULL,

    original_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
    additional_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
    current_value DECIMAL(18,2) NOT NULL DEFAULT 0,
    useful_life_years SMALLINT UNSIGNED NULL,
    warranty_expiry DATE NULL,

    location_id INT UNSIGNED NULL,
    department_id INT UNSIGNED NULL COMMENT 'references locations.id with location_type=department',
    custodian_employee_id INT UNSIGNED NULL,

    remarks TEXT NULL,

    deleted_at DATETIME NULL,
    deleted_by INT UNSIGNED NULL,
    delete_reason VARCHAR(255) NULL,

    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_asset_code (organization_id, asset_code),
    UNIQUE KEY uq_asset_qr_token (qr_token),
    UNIQUE KEY uq_asset_serial (organization_id, serial_number),

    CONSTRAINT fk_asset_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_asset_category FOREIGN KEY (category_id) REFERENCES asset_categories(id),
    CONSTRAINT fk_asset_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id),
    CONSTRAINT fk_asset_location FOREIGN KEY (location_id) REFERENCES locations(id),
    CONSTRAINT fk_asset_department FOREIGN KEY (department_id) REFERENCES locations(id),
    CONSTRAINT fk_asset_custodian FOREIGN KEY (custodian_employee_id) REFERENCES employees(id),

    INDEX idx_asset_status (status),
    INDEX idx_asset_condition (condition_status),
    INDEX idx_asset_category (category_id),
    INDEX idx_asset_location (location_id),
    INDEX idx_asset_custodian (custodian_employee_id),
    INDEX idx_asset_acq_date (acquisition_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Serial number is only unique when present — a NULL-heavy unique
-- key works fine in MySQL (NULLs don't collide), so no special
-- handling needed here for assets without a serial number.

-- ------------------------------------------------------------
-- asset_documents (spec section 50)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asset_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id INT UNSIGNED NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL COMMENT 'randomized name on disk — never trust original name',
    mime_type VARCHAR(100) NOT NULL,
    file_size INT UNSIGNED NOT NULL,
    document_type VARCHAR(50) NULL COMMENT 'invoice, warranty, photo, other',
    uploaded_by INT UNSIGNED NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_doc_asset FOREIGN KEY (asset_id) REFERENCES assets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- asset_history — append-only lifecycle timeline (spec section 49)
-- Distinct from audit_logs: this is asset-centric and shown on
-- the asset detail page's timeline tab, in plain language.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS asset_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id INT UNSIGNED NOT NULL,
    event_type VARCHAR(50) NOT NULL COMMENT 'registered, updated, status_changed, condition_changed, etc.',
    description VARCHAR(500) NOT NULL,
    old_value VARCHAR(255) NULL,
    new_value VARCHAR(255) NULL,
    recorded_by INT UNSIGNED NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hist_asset FOREIGN KEY (asset_id) REFERENCES assets(id),
    INDEX idx_hist_asset (asset_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;