-- Financial Year Master Table
-- This table allows managing multiple financial years and activating one at a time
-- for viewing and filtering bills

CREATE TABLE IF NOT EXISTS `tbl_financial_year_master` (
  `fy_id` int NOT NULL AUTO_INCREMENT,
  `fy_start_date` date NOT NULL COMMENT 'Financial year start date (typically April 1st)',
  `fy_end_date` date NOT NULL COMMENT 'Financial year end date (typically March 31st)',
  `fy_label` varchar(20) NOT NULL COMMENT 'Financial year label (e.g., 2025-2026)',
  `fy_is_active` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 if this is the active financial year',
  `fy_created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fy_updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`fy_id`),
  UNIQUE KEY `fy_label` (`fy_label`),
  KEY `fy_start_date` (`fy_start_date`),
  KEY `fy_end_date` (`fy_end_date`),
  KEY `fy_is_active` (`fy_is_active`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Financial Year Master for bill filtering';

-- Insert current financial year (2025-2026) as active
INSERT INTO `tbl_financial_year_master` 
(`fy_start_date`, `fy_end_date`, `fy_label`, `fy_is_active`)
VALUES 
('2025-04-01', '2026-03-31', '2025-2026', 1)
ON DUPLICATE KEY UPDATE `fy_is_active` = 1;

-- Add financial_year_id column to cbill table for linking bills to financial year
-- This allows filtering bills by financial year
ALTER TABLE `cbill` 
ADD COLUMN `bl_financial_year_id` int DEFAULT '0' COMMENT 'Links to tbl_financial_year_master.fy_id' AFTER `bl_line`;

-- Add index for faster filtering
ALTER TABLE `cbill` 
ADD KEY `idx_bl_financial_year_id` (`bl_financial_year_id`);

-- Update existing bills to link to the default financial year (2025-2026)
-- Get the fy_id for 2025-2026
UPDATE `cbill` 
SET `bl_financial_year_id` = (SELECT `fy_id` FROM `tbl_financial_year_master` WHERE `fy_label` = '2025-2026' LIMIT 1)
WHERE `bl_financial_year_id` = 0;
