USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS fact_asset_allocations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    asset_id INT NOT NULL,
    employee_id INT NULL,
    assigned_to_name VARCHAR(255) NULL,
    allocation_date DATE NOT NULL,
    return_date DATE NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Active',
    notes TEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_fact_asset_allocations_tenant (tenant_id),
    INDEX idx_fact_asset_allocations_asset (tenant_id, asset_id),
    INDEX idx_fact_asset_allocations_employee (employee_id),
    INDEX idx_fact_asset_allocations_status (tenant_id, status)
);