CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','warehouse','technician','reporter') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE warehouses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(30) NOT NULL UNIQUE,
    location VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(60) NOT NULL UNIQUE,
    name VARCHAR(180) NOT NULL,
    item_type ENUM('laptop','pc','device','sparepart') NOT NULL DEFAULT 'sparepart',
    category_id BIGINT UNSIGNED NULL,
    brand VARCHAR(100) NULL,
    model VARCHAR(120) NULL,
    unit VARCHAR(30) NOT NULL DEFAULT 'unit',
    serial_number VARCHAR(120) NULL UNIQUE,
    device_name VARCHAR(150) NULL,
    product_number VARCHAR(120) NULL,
    bios_version VARCHAR(120) NULL,
    processor VARCHAR(180) NULL,
    storage_spec VARCHAR(180) NULL,
    installed_ram VARCHAR(100) NULL,
    device_id VARCHAR(180) NULL,
    product_id VARCHAR(180) NULL,
    invoice_number VARCHAR(100) NULL,
    accurate_mr_number VARCHAR(100) NULL,
    asset_status ENUM('Tersedia','Dipinjamkan','Diserahkan','Dalam Perbaikan','Rusak','Hilang','Dihapuskan','Dikembalikan') NOT NULL DEFAULT 'Tersedia',
    min_stock DECIMAL(12,2) NOT NULL DEFAULT 0,
    unit_cost DECIMAL(15,2) NOT NULL DEFAULT 0,
    purchased_on DATE NULL,
    warranty_until DATE NULL,
    notes TEXT NULL,
    is_archived TINYINT(1) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_items_type_status (item_type, asset_status),
    INDEX idx_items_name (name),
    CONSTRAINT fk_items_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_items_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE item_stocks (
    item_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(12,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (item_id, warehouse_id),
    CONSTRAINT fk_stock_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE RESTRICT,
    CONSTRAINT fk_stock_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE RESTRICT,
    CONSTRAINT chk_stock_nonnegative CHECK (quantity >= 0)
) ENGINE=InnoDB;

CREATE TABLE stock_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference_no VARCHAR(40) NOT NULL UNIQUE,
    item_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('Masuk','Keluar','Transfer','Penyesuaian') NOT NULL,
    quantity DECIMAL(12,2) NOT NULL,
    from_warehouse_id BIGINT UNSIGNED NULL,
    to_warehouse_id BIGINT UNSIGNED NULL,
    reason VARCHAR(255) NOT NULL,
    related_reference VARCHAR(100) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stock_tx_date (created_at),
    CONSTRAINT fk_tx_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE RESTRICT,
    CONSTRAINT fk_tx_from FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(id) ON DELETE RESTRICT,
    CONSTRAINT fk_tx_to FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(id) ON DELETE RESTRICT,
    CONSTRAINT fk_tx_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT chk_tx_quantity CHECK (quantity > 0)
) ENGINE=InnoDB;

CREATE TABLE work_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    work_no VARCHAR(40) NOT NULL UNIQUE,
    title VARCHAR(180) NOT NULL,
    customer_name VARCHAR(150) NULL,
    project_location VARCHAR(180) NULL,
    technician_id BIGINT UNSIGNED NULL,
    scheduled_at DATETIME NULL,
    status ENUM('Baru','Dijadwalkan','Dikerjakan','Ditunda','Selesai') NOT NULL DEFAULT 'Baru',
    priority ENUM('Rendah','Normal','Tinggi','Mendesak') NOT NULL DEFAULT 'Normal',
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_work_status_date (status, scheduled_at),
    CONSTRAINT fk_work_technician FOREIGN KEY (technician_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_work_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE loans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT UNSIGNED NOT NULL,
    borrower_name VARCHAR(150) NOT NULL,
    department VARCHAR(120) NULL,
    location VARCHAR(180) NULL,
    purpose VARCHAR(255) NULL,
    borrowed_at DATETIME NOT NULL,
    due_at DATETIME NULL,
    returned_at DATETIME NULL,
    status ENUM('Dipinjamkan','Diserahkan','Dalam Perbaikan','Rusak','Hilang','Dihapuskan','Dikembalikan') NOT NULL DEFAULT 'Dipinjamkan',
    handover_condition VARCHAR(255) NULL,
    return_condition VARCHAR(255) NULL,
    accessories TEXT NULL,
    notes TEXT NULL,
    approved_by BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    active_item_id BIGINT UNSIGNED GENERATED ALWAYS AS (
        CASE WHEN status <> 'Dikembalikan' AND status <> 'Dihapuskan' THEN item_id ELSE NULL END
    ) STORED,
    UNIQUE KEY uq_active_loan_item (active_item_id),
    INDEX idx_loans_borrower_due (borrower_name, due_at),
    CONSTRAINT fk_loan_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_loan_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE maintenance_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(180) NOT NULL,
    scheduled_on DATE NOT NULL,
    completed_on DATE NULL,
    status ENUM('Terjadwal','Selesai','Ditunda') NOT NULL DEFAULT 'Terjadwal',
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_maintenance_schedule (status, scheduled_on),
    CONSTRAINT fk_maintenance_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE RESTRICT,
    CONSTRAINT fk_maintenance_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    outcome VARCHAR(30) NOT NULL DEFAULT 'success',
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_created (created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    attempted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_attempts (username, ip_address, attempted_at)
) ENGINE=InnoDB;