-- Table to track bill number sequence per financial year
-- Financial Year: April 1st to March 31st

CREATE TABLE IF NOT EXISTS `tbl_financial_year_bill_seq` (
  `seq_id` int(11) NOT NULL AUTO_INCREMENT,
  `financial_year_start` date NOT NULL COMMENT 'Financial year start date (April 1st)',
  `financial_year_end` date NOT NULL COMMENT 'Financial year end date (March 31st)',
  `year_label` varchar(20) NOT NULL COMMENT 'Financial year label (e.g., 2025-2026)',
  `current_bill_no` int(11) NOT NULL DEFAULT '0' COMMENT 'Current bill number counter',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`seq_id`),
  UNIQUE KEY `year_label` (`year_label`),
  KEY `financial_year_start` (`financial_year_start`),
  KEY `financial_year_end` (`financial_year_end`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Track bill number sequence per financial year';

-- Insert initial financial year if not exists
-- This will be updated automatically each year
INSERT INTO `tbl_financial_year_bill_seq` 
(`financial_year_start`, `financial_year_end`, `year_label`, `current_bill_no`)
VALUES 
('2025-04-01', '2026-03-31', '2025-2026', 0)
ON DUPLICATE KEY UPDATE `year_label`=`year_label`;
