-- Create Expense Category table
CREATE TABLE IF NOT EXISTS `expense_category_cat` (
  `cat_ID` int(11) NOT NULL AUTO_INCREMENT,
  `cat_Name` varchar(100) NOT NULL,
  `cat_Description` text,
  `cat_Active` tinyint(1) DEFAULT 1,
  `cat_DateCreated` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cat_ID`),
  INDEX `idx_active` (`cat_Active`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create Expenses table
CREATE TABLE IF NOT EXISTS `expense_exp` (
  `exp_ID` int(11) NOT NULL AUTO_INCREMENT,
  `exp_Date` date NOT NULL,
  `exp_Amount` decimal(10,2) NOT NULL,
  `exp_Category` int(11) NOT NULL,
  `exp_Fund` int(11),
  `exp_Vendor` varchar(255),
  `exp_Description` text,
  `exp_Reference` varchar(100),
  `exp_Status` enum('Pending','Approved','Rejected','Paid') DEFAULT 'Pending',
  `exp_PaymentMethod` varchar(50),
  `exp_Notes` text,
  `exp_EnteredBy` int(11),
  `exp_ApprovedBy` int(11),
  `exp_DateCreated` timestamp DEFAULT CURRENT_TIMESTAMP,
  `exp_DateLastEdited` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`exp_ID`),
  KEY `idx_date` (`exp_Date`),
  KEY `idx_category` (`exp_Category`),
  KEY `idx_status` (`exp_Status`),
  KEY `idx_fund` (`exp_Fund`),
  KEY `fk_entered_by` (`exp_EnteredBy`),
  KEY `fk_approved_by` (`exp_ApprovedBy`),
  CONSTRAINT `fk_exp_category` FOREIGN KEY (`exp_Category`) REFERENCES `expense_category_cat` (`cat_ID`),
  CONSTRAINT `fk_exp_fund` FOREIGN KEY (`exp_Fund`) REFERENCES `donationfund_fun` (`fun_ID`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_entered_by` FOREIGN KEY (`exp_EnteredBy`) REFERENCES `user_usr` (`usr_ID`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_approved_by` FOREIGN KEY (`exp_ApprovedBy`) REFERENCES `user_usr` (`usr_ID`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default expense categories
INSERT INTO `expense_category_cat` (`cat_Name`, `cat_Description`, `cat_Active`) VALUES
('Utilities', 'Electricity, water, gas, internet, phone', 1),
('Salaries & Wages', 'Employee salaries and wages', 1),
('Office Supplies', 'Paper, pens, office equipment', 1),
('Maintenance & Repairs', 'Building and equipment maintenance', 1),
('Programs & Events', 'Church programs, events, activities', 1),
('Travel & Transportation', 'Vehicle expenses, travel costs', 1),
('Professional Services', 'Consulting, legal, accounting services', 1),
('Insurance', 'Building, liability, vehicle insurance', 1),
('Education & Training', 'Staff development, seminars, books', 1),
('Miscellaneous', 'Other expenses not fitting above categories', 1);
