-- Create expenses table
CREATE TABLE IF NOT EXISTS `expenses` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `title` varchar(200) NOT NULL,
    `description` longtext,
    `category` varchar(50) NOT NULL,
    `amount` decimal(15,2) NOT NULL,
    `expense_date` date NOT NULL,
    `payment_method` varchar(50) NOT NULL,
    `paid_to` varchar(200) NOT NULL,
    `reference_number` varchar(100),
    `receipt_path` varchar(100),
    `status` varchar(20) NOT NULL DEFAULT 'pending',
    `notes` longtext,
    `created_at` datetime(6) NOT NULL,
    `updated_at` datetime(6) NOT NULL,
    `approved_at` datetime(6),
    `rejection_reason` longtext,
    `branch_id` char(32) NOT NULL,
    `loan_id` char(32),
    `staff_id` char(32) NOT NULL,
    `approved_by_id` char(32),
    PRIMARY KEY (`id`),
    KEY `expenses_branch__f377ac_idx` (`branch_id`, `expense_date`),
    KEY `expenses_status_0e583e_idx` (`status`, `expense_date`),
    KEY `expenses_categor_a8c11b_idx` (`category`, `expense_date`),
    KEY `expenses_staff_i_a58db8_idx` (`staff_id`, `expense_date`),
    KEY `expenses_expense_loan_id_c8e5e8a5` (`loan_id`),
    KEY `expenses_expense_staff_id_a58db8f8` (`staff_id`),
    KEY `expenses_expense_approved_by_id_f377ac9a` (`approved_by_id`),
    CONSTRAINT `expenses_expense_approved_by_id_f377ac9a_fk_users_customuser_id` 
        FOREIGN KEY (`approved_by_id`) REFERENCES `users_customuser` (`id`),
    CONSTRAINT `expenses_expense_branch_id_e5e8a5f3_fk_branch_system_branch_id` 
        FOREIGN KEY (`branch_id`) REFERENCES `branch_system_branch` (`id`),
    CONSTRAINT `expenses_expense_loan_id_c8e5e8a5_fk_loans_loan_id` 
        FOREIGN KEY (`loan_id`) REFERENCES `loans_loan` (`id`),
    CONSTRAINT `expenses_expense_staff_id_a58db8f8_fk_users_customuser_id` 
        FOREIGN KEY (`staff_id`) REFERENCES `users_customuser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;