-- ========================================
-- Invoice Number Format Migration
-- ========================================
-- This migration changes invoice numbers from INV-YYYYMMDD-0001 format
-- to simple sequential format: 001, 002, 003, etc.
--
-- IMPORTANT: Choose ONE of the options below based on your needs
-- ========================================

-- ========================================
-- OPTION 1: KEEP EXISTING INVOICES (RECOMMENDED FOR PRODUCTION)
-- ========================================
-- This option keeps all existing invoice numbers as-is
-- Only NEW invoices will use the new format (001, 002, 003...)
-- Existing invoices with old format will remain unchanged
--
-- No migration needed! Just update the API code.
-- The new format will apply only to new invoices.

-- ========================================
-- OPTION 2: MIGRATE EXISTING INVOICES (USE WITH CAUTION)
-- ========================================
-- This option converts ALL existing invoices to new format
-- WARNING: This will change invoice numbers in your database!
-- This may cause issues with:
-- - Previously printed/sent invoices
-- - Customer records
-- - Accounting records
-- - Legal compliance
--
-- Only use this if:
-- 1. You have very few invoices
-- 2. You haven't sent invoices to customers yet
-- 3. You have backups
-- 4. You understand the risks
--
-- To use this option, uncomment the following code:

/*
-- Step 1: Backup your sales table first!
CREATE TABLE sales_backup_before_invoice_migration AS SELECT * FROM sales;

-- Step 2: Update invoice numbers to sequential format
-- This creates a temporary column, assigns new numbers, then replaces the old ones
ALTER TABLE sales ADD COLUMN new_invoice_number VARCHAR(10);

-- Assign sequential numbers based on sale date order
SET @row_number = 0;
UPDATE sales
SET new_invoice_number = LPAD(@row_number := @row_number + 1, 3, '0')
ORDER BY sale_date ASC, id ASC;

-- Replace old invoice numbers with new ones
UPDATE sales SET invoice_number = new_invoice_number;

-- Drop the temporary column
ALTER TABLE sales DROP COLUMN new_invoice_number;

-- Step 3: Verify the migration
SELECT id, customer_name, invoice_number, sale_date FROM sales ORDER BY id;

-- If everything looks good, you can drop the backup:
-- DROP TABLE sales_backup_before_invoice_migration;
*/

-- ========================================
-- OPTION 3: RESET INVOICE NUMBERING (TESTING/DEVELOPMENT ONLY)
-- ========================================
-- This option is ONLY for testing/development databases
-- It deletes ALL sales records and resets numbering
-- DO NOT USE IN PRODUCTION!
--
-- To use this option, uncomment the following code:

/*
-- WARNING: This will DELETE ALL sales records!
-- Only use in testing/development environment
TRUNCATE TABLE sales;

-- Reset auto-increment if needed
ALTER TABLE sales AUTO_INCREMENT = 1;

-- New invoices will now start from 001
*/

-- ========================================
-- Post-Migration Notes:
-- ========================================
-- After choosing and running your option:
-- 1. Test by creating a new sale - it should get invoice number 001 (or next in sequence)
-- 2. Verify the invoice PDF displays the correct format
-- 3. Check that all old invoices (if kept) still work correctly
-- 4. Update any external systems that reference invoice numbers
-- 5. Inform your team about the new format

-- ========================================
-- Rollback Instructions (If needed):
-- ========================================
-- If you used OPTION 2 and need to rollback:
/*
-- Restore from backup
DROP TABLE sales;
RENAME TABLE sales_backup_before_invoice_migration TO sales;
*/

