-- Expense Management Module Tables

-- ========================================
-- Table: expense_category_cat
-- Purpose: Defines expense categories (Utilities, Salaries, Supplies, etc.)
-- ========================================
CREATE TABLE IF NOT EXISTS `expense_category_cat` (
  `cat_ID` tinyint(3) NOT NULL auto_increment,
  `cat_Name` varchar(50) NOT NULL,
  `cat_Description` varchar(255),
  `cat_Active` enum('true','false') NOT NULL default 'true',
  `cat_Order` int(11) NOT NULL DEFAULT 0,
  `cat_CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cat_ID`),
  UNIQUE KEY `cat_Name` (`cat_Name`)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci AUTO_INCREMENT=1;

-- ========================================
-- Table: expense_exp
-- Purpose: Records all expenses/withdrawals from church accounts
-- ========================================
CREATE TABLE IF NOT EXISTS `expense_exp` (
  `exp_ID` mediumint(9) unsigned NOT NULL auto_increment,
  `exp_Date` date NOT NULL,
  `exp_Amount` decimal(8,2) NOT NULL,
  `exp_Category` tinyint(3) NOT NULL,
  `exp_Fund` tinyint(3),
  `exp_Vendor` varchar(100),
  `exp_Description` text,
  `exp_Reference` varchar(50),
  `exp_Status` enum('Pending','Approved','Rejected','Paid') NOT NULL default 'Pending',
  `exp_PaymentMethod` enum('Check','Cash','Credit Card','Bank Transfer','Other') NOT NULL default 'Check',
  `exp_Notes` text,
  `exp_EnteredBy` mediumint(9) unsigned NOT NULL,
  `exp_ApprovedBy` mediumint(9) unsigned,
  `exp_CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `exp_DateLastEdited` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`exp_ID`),
  KEY `exp_Date` (`exp_Date`),
  KEY `exp_Category` (`exp_Category`),
  KEY `exp_Fund` (`exp_Fund`),
  KEY `exp_Status` (`exp_Status`)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci AUTO_INCREMENT=1;

-- ========================================
-- Table: expense_budget_bud
-- Purpose: Tracks budget allocations by category and fund
-- ========================================
CREATE TABLE IF NOT EXISTS `expense_budget_bud` (
  `bud_ID` mediumint(9) unsigned NOT NULL auto_increment,
  `bud_Year` year NOT NULL,
  `bud_Category` tinyint(3) NOT NULL,
  `bud_Fund` tinyint(3),
  `bud_Amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `bud_Notes` text,
  `bud_CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `bud_DateLastEdited` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bud_ID`),
  UNIQUE KEY `bud_Year_Category_Fund` (`bud_Year`, `bud_Category`, `bud_Fund`),
  KEY `bud_Category` (`bud_Category`),
  KEY `bud_Fund` (`bud_Fund`)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci AUTO_INCREMENT=1;

-- ========================================
-- Table: expense_approval_log_eal
-- Purpose: Audit trail for expense approvals/rejections
-- ========================================
CREATE TABLE IF NOT EXISTS `expense_approval_log_eal` (
  `eal_ID` int(11) NOT NULL auto_increment,
  `eal_ExpenseID` mediumint(9) unsigned NOT NULL,
  `eal_Action` enum('Submitted','Approved','Rejected','Paid') NOT NULL,
  `eal_UserId` mediumint(9) unsigned NOT NULL,
  `eal_Notes` text,
  `eal_Timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`eal_ID`),
  KEY `eal_ExpenseID` (`eal_ExpenseID`),
  KEY `eal_Timestamp` (`eal_Timestamp`)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci AUTO_INCREMENT=1;

-- ========================================
-- Insert Default Expense Categories
-- ========================================
INSERT INTO `expense_category_cat` (`cat_Name`, `cat_Description`, `cat_Order`) VALUES
  ('Utilities', 'Electric, Gas, Water, Internet', 10),
  ('Salaries', 'Staff and Payroll', 20),
  ('Supplies', 'Office and Building Supplies', 30),
  ('Maintenance', 'Building Maintenance and Repairs', 40),
  ('Programs', 'Youth, Adult, and Children Programs', 50),
  ('Outreach', 'Missions and Community Outreach', 60),
  ('Insurance', 'Building and Liability Insurance', 70),
  ('Transportation', 'Fuel and Vehicle Maintenance', 80),
  ('Professional Services', 'Accounting, Legal, Consulting', 90),
  ('Equipment', 'Computers, Equipment, Technology', 100),
  ('Other', 'Miscellaneous Expenses', 999);
