CREATE TABLE IF NOT EXISTS job_cost_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    job_id INT NOT NULL,
    cost_type VARCHAR(40) NOT NULL,
    description VARCHAR(190) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    cost_date DATE NOT NULL,
    source VARCHAR(80) NOT NULL DEFAULT 'manual',
    notes TEXT NULL,
    created_by_user_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE INDEX idx_job_cost_entries_tenant_job_date ON job_cost_entries (tenant_id, job_id, cost_date);
CREATE INDEX idx_job_cost_entries_tenant_type_date ON job_cost_entries (tenant_id, cost_type, cost_date);