-- Migration script to convert global cash_denominations to branch-specific tables

-- First, ensure the global table exists and has the correct structure
CREATE TABLE IF NOT EXISTS `cash_denominations` (
    `denomination_id` INT PRIMARY KEY AUTO_INCREMENT,
    `denomination_type` ENUM('bill', 'coin') NOT NULL,
    `denomination_value` DECIMAL(10,2) NOT NULL,
    `denomination_name` VARCHAR(50) NOT NULL,
    `sort_order` INT NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `country_code` VARCHAR(3) NOT NULL DEFAULT 'PHP',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_denomination` (`country_code`, `denomination_value`, `denomination_type`)
);

-- Insert default denominations if the table is empty
INSERT IGNORE INTO `cash_denominations` 
(denomination_type, denomination_value, denomination_name, sort_order, country_code) 
VALUES
-- Bills (highest to lowest)
('bill', 1000.00, '₱1000 Bill', 1, 'PHP'),
('bill', 500.00, '₱500 Bill', 2, 'PHP'),
('bill', 200.00, '₱200 Bill', 3, 'PHP'),
('bill', 100.00, '₱100 Bill', 4, 'PHP'),
('bill', 50.00, '₱50 Bill', 5, 'PHP'),
('bill', 20.00, '₱20 Bill', 6, 'PHP'),

-- Coins (highest to lowest)
('coin', 20.00, '₱20 Coin', 7, 'PHP'),
('coin', 10.00, '₱10 Coin', 8, 'PHP'),
('coin', 5.00, '₱5 Coin', 9, 'PHP'),
('coin', 1.00, '₱1 Coin', 10, 'PHP');

-- Create a procedure to copy denominations to a branch-specific table
DELIMITER //

CREATE PROCEDURE migrate_cash_denominations_to_branch(IN branch_code VARCHAR(10))
BEGIN
    SET @create_table = CONCAT('
        CREATE TABLE IF NOT EXISTS `', branch_code, '_cash_denominations` (
            `denomination_id` INT PRIMARY KEY AUTO_INCREMENT,
            `denomination_type` ENUM(\'bill\', \'coin\') NOT NULL,
            `denomination_value` DECIMAL(10,2) NOT NULL,
            `denomination_name` VARCHAR(50) NOT NULL,
            `sort_order` INT NOT NULL,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `country_code` VARCHAR(3) NOT NULL DEFAULT \'PHP\',
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY `unique_denomination` (`country_code`, `denomination_value`, `denomination_type`),
            INDEX `idx_active_sort` (`is_active`, `sort_order`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ');
    
    SET @copy_data = CONCAT('
        INSERT IGNORE INTO `', branch_code, '_cash_denominations`
        (denomination_type, denomination_value, denomination_name, sort_order, is_active, country_code)
        SELECT 
            denomination_type,
            denomination_value,
            denomination_name,
            sort_order,
            is_active,
            country_code
        FROM cash_denominations
    ');
    
    PREPARE create_stmt FROM @create_table;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;
    
    PREPARE copy_stmt FROM @copy_data;
    EXECUTE copy_stmt;
    DEALLOCATE PREPARE copy_stmt;
END //

DELIMITER ;

-- Example usage:
-- CALL migrate_cash_denominations_to_branch('branch1');
-- CALL migrate_cash_denominations_to_branch('branch2'); 