CREATE TABLE IF NOT EXISTS `expenses` (
    `expense_id` INT PRIMARY KEY AUTO_INCREMENT,
    `branch_id` INT NOT NULL,
    `expense_category` VARCHAR(100) NOT NULL,
    `expense_description` TEXT NULL,
    `expense_amount` DECIMAL(10,2) NOT NULL,
    `expense_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `receipt_number` VARCHAR(50) DEFAULT NULL,
    `payment_method` ENUM('cash', 'bank_transfer', 'check', 'credit_card') DEFAULT 'cash',
    `notes` TEXT DEFAULT NULL,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`branch_id`) REFERENCES `branches`(`branch_id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_branch_date` (`branch_id`, `expense_date`),
    INDEX `idx_category` (`expense_category`),
    INDEX `idx_date` (`expense_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample expense categories for reference
INSERT INTO `expenses` (`branch_id`, `expense_category`, `expense_description`, `expense_amount`, `expense_date`, `payment_method`, `created_by`) VALUES
(1, 'Utilities', 'Electricity Bill - December 2024', 2500.00, NOW(), 'bank_transfer', 1),
(1, 'Office Supplies', 'Receipt papers and cleaning supplies', 850.00, NOW(), 'cash', 1),
(1, 'Marketing', 'Facebook Ads Campaign', 1200.00, DATE_SUB(NOW(), INTERVAL 1 DAY), 'credit_card', 1); 