-- Petty Cash Module - Database Schema
-- Excel-like Cash Expenses Summary System

-- Petty Cash Periods Table
CREATE TABLE IF NOT EXISTS `petty_cash_periods` (
  `period_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_name` varchar(255) NOT NULL,
  `site_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `opening_balance` decimal(15,2) DEFAULT 0.00,
  `closing_balance` decimal(15,2) DEFAULT 0.00,
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime,
  `locked_by` int(11),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`period_id`),
  KEY `fk_pc_period_site` (`site_id`),
  KEY `fk_pc_period_created_by` (`created_by`),
  KEY `fk_pc_period_locked_by` (`locked_by`),
  KEY `idx_pc_period_month_year` (`month`, `year`),
  KEY `idx_pc_period_dates` (`start_date`, `end_date`),
  CONSTRAINT `fk_pc_period_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_pc_period_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pc_period_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Petty Cash Daily Entries Table
CREATE TABLE IF NOT EXISTS `petty_cash_entries` (
  `entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_id` int(11) NOT NULL,
  `entry_date` date NOT NULL,
  `voucher_no` varchar(100),
  `description_bill` text,
  `total_cash_in_hand` decimal(15,2) DEFAULT 0.00,
  `received_from_finance` decimal(15,2) DEFAULT 0.00,
  `other_receipt` decimal(15,2) DEFAULT 0.00,
  `total_cash_out` decimal(15,2) DEFAULT 0.00,
  `purchase_catering` decimal(15,2) DEFAULT 0.00,
  `purchase_hk` decimal(15,2) DEFAULT 0.00,
  `maintenance` decimal(15,2) DEFAULT 0.00,
  `impres_money_commission` decimal(15,2) DEFAULT 0.00,
  `printing_stationery` decimal(15,2) DEFAULT 0.00,
  `grocery_cutlery_rent_hire` decimal(15,2) DEFAULT 0.00,
  `mobile_tel` decimal(15,2) DEFAULT 0.00,
  `local_conveyance` decimal(15,2) DEFAULT 0.00,
  `courier` decimal(15,2) DEFAULT 0.00,
  `staff_welfare` decimal(15,2) DEFAULT 0.00,
  `other_misc` decimal(15,2) DEFAULT 0.00,
  `decoration_charges` decimal(15,2) DEFAULT 0.00,
  `bank_deposit` decimal(15,2) DEFAULT 0.00,
  `balance_in_hand` decimal(15,2) DEFAULT 0.00,
  `reimbursable_non_reimbursable` varchar(50),
  `sr_no` int(11) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entry_id`),
  KEY `fk_pc_entry_period` (`period_id`),
  KEY `idx_pc_entry_date` (`entry_date`),
  KEY `idx_pc_entry_sr_no` (`sr_no`),
  CONSTRAINT `fk_pc_entry_period` FOREIGN KEY (`period_id`) REFERENCES `petty_cash_periods` (`period_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Petty Cash Expense Headers (Dynamic Headers)
CREATE TABLE IF NOT EXISTS `petty_cash_headers` (
  `header_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_id` int(11) NOT NULL,
  `header_name` varchar(255) NOT NULL,
  `header_type` enum('expense','collection','other') DEFAULT 'expense',
  `header_order` int(11) DEFAULT 0,
  `is_calculation_row` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`header_id`),
  KEY `fk_pc_header_period` (`period_id`),
  CONSTRAINT `fk_pc_header_period` FOREIGN KEY (`period_id`) REFERENCES `petty_cash_periods` (`period_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Petty Cash Period Details (Remarks, Approval, Denomination)
CREATE TABLE IF NOT EXISTS `petty_cash_period_details` (
  `detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_id` int(11) NOT NULL,
  `remarks` text,
  `prepared_by_signature` varchar(255),
  `prepared_by_name` varchar(255),
  `approved_by_signature` varchar(255),
  `approved_by_name` varchar(255),
  `denomination_2000` int(11) DEFAULT 0,
  `denomination_500` int(11) DEFAULT 0,
  `denomination_200` int(11) DEFAULT 0,
  `denomination_100` int(11) DEFAULT 0,
  `denomination_50` int(11) DEFAULT 0,
  `denomination_20` int(11) DEFAULT 0,
  `denomination_10` int(11) DEFAULT 0,
  `denomination_5` int(11) DEFAULT 0,
  `denomination_2` int(11) DEFAULT 0,
  `denomination_1` int(11) DEFAULT 0,
  `total_amount` decimal(15,2) DEFAULT 0.00,
  `difference` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`detail_id`),
  UNIQUE KEY `unique_pc_detail_period` (`period_id`),
  CONSTRAINT `fk_pc_detail_period` FOREIGN KEY (`period_id`) REFERENCES `petty_cash_periods` (`period_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
