-- Create expenses table WITHOUT foreign key constraints
-- Foreign keys will be added after verifying column types

DROP TABLE IF EXISTS `expenses_expense`;

CREATE TABLE `expenses_expense` (
    `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`),
    KEY `expenses_expense_branch_id_e5e8a5f3` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verify table was created
SELECT 'Table created successfully!' AS status;
DESCRIBE expenses_expense;