CREATE TABLE IF NOT EXISTS expenses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    expense_number VARCHAR(40) NOT NULL,
    employee_id BIGINT UNSIGNED NULL,
    deal_id BIGINT UNSIGNED NULL,
    category ENUM('labor', 'tools', 'transport', 'maintenance', 'storage', 'salary', 'other') NOT NULL,
    funding_source ENUM('employee_custody', 'employee_personal', 'company') NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    reimbursed_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NOT NULL,
    description VARCHAR(255) NOT NULL,
    notes TEXT NULL,
    receipt_path VARCHAR(500) NULL,
    receipt_name VARCHAR(255) NULL,
    receipt_mime VARCHAR(100) NULL,
    status ENUM('pending', 'approved', 'partially_reimbursed', 'reimbursed', 'rejected', 'reversed') NOT NULL,
    submitted_by BIGINT UNSIGNED NOT NULL,
    submitted_at DATETIME NOT NULL,
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    review_reason TEXT NULL,
    reversed_by BIGINT UNSIGNED NULL,
    reversed_at DATETIME NULL,
    reversal_reason TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY expenses_number_unique (expense_number),
    KEY expenses_employee_status_index (employee_id, status),
    KEY expenses_deal_date_index (deal_id, submitted_at),
    CONSTRAINT expenses_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT expenses_deal_fk FOREIGN KEY (deal_id) REFERENCES deals(id),
    CONSTRAINT expenses_submitted_by_fk FOREIGN KEY (submitted_by) REFERENCES users(id),
    CONSTRAINT expenses_reviewed_by_fk FOREIGN KEY (reviewed_by) REFERENCES users(id),
    CONSTRAINT expenses_reversed_by_fk FOREIGN KEY (reversed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expense_reimbursements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    expense_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NOT NULL,
    notes TEXT NULL,
    paid_by BIGINT UNSIGNED NOT NULL,
    paid_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    KEY expense_reimbursements_expense_date_index (expense_id, paid_at),
    CONSTRAINT expense_reimbursements_expense_fk FOREIGN KEY (expense_id) REFERENCES expenses(id),
    CONSTRAINT expense_reimbursements_paid_by_fk FOREIGN KEY (paid_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;