-- ============================================
-- Financial Year Bill Numbering Setup
-- ============================================
-- This file contains all SQL changes needed for
-- financial year-based bill numbering (April 1 - March 31)
-- ============================================

-- Step 1: Create financial year bill sequence table
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';

-- Step 2: Insert current financial year (2025-2026)
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`;

-- Step 3: Remove UNIQUE constraint from cbill.bl_no
-- This allows bill numbers to restart each financial year
ALTER TABLE `cbill` DROP INDEX `bl_no`;

-- Step 4: Add regular index for performance (non-unique)
ALTER TABLE `cbill` ADD INDEX `idx_bl_no` (`bl_no`);

-- ============================================
-- Usage Instructions:
-- ============================================
-- 1. Run this SQL file in your database
-- 2. The generatebill.php has been updated to use
--    financial year-based bill numbering automatically
-- 3. Bill numbers will reset to 1 every April 1st
-- ============================================

-- ============================================
-- Optional: View current financial year status
-- ============================================
-- SELECT * FROM tbl_financial_year_bill_seq ORDER BY financial_year_start DESC;

-- ============================================
-- Optional: Reset bill counter for a financial year
-- ============================================
-- UPDATE tbl_financial_year_bill_seq 
-- SET current_bill_no = 0 
-- WHERE year_label = '2025-2026';
