-- Business Denominations Configuration Schema
-- Allows businesses to customize which denominations appear in cash counting
-- Each business gets their own copy from global defaults that they can customize

-- Create business-specific denominations configuration table
CREATE TABLE IF NOT EXISTS `{business_code}_denominations_config` (
    `config_id` INT PRIMARY KEY AUTO_INCREMENT,
    `denomination_value` DECIMAL(10,2) NOT NULL,
    `currency_code` VARCHAR(3) NOT NULL DEFAULT 'PHP',
    `currency_symbol` VARCHAR(5) NOT NULL DEFAULT '₱',
    `is_coin` BOOLEAN NOT NULL DEFAULT FALSE,
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `display_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `idx_denom_value` (`denomination_value`),
    INDEX `idx_active_order` (`is_active`, `display_order`),
    INDEX `idx_coin_active` (`is_coin`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initialize business denominations from global table during setup
-- This should be run when a business is created
INSERT IGNORE INTO `{business_code}_denominations_config` 
(denomination_value, currency_code, currency_symbol, is_coin, is_active, display_order)
SELECT denomination_value, currency_code, currency_symbol, is_coin, is_active, display_order
FROM default_country_denominations 
WHERE country_code = '{country_code}' AND is_active = 1
ORDER BY display_order;

-- Sample management queries for business admin
-- Enable/disable specific denominations
-- UPDATE `{business_code}_denominations_config` SET is_active = FALSE WHERE denomination_value = 0.01;
-- UPDATE `{business_code}_denominations_config` SET is_active = FALSE WHERE denomination_value = 0.05;

-- Reorder denominations
-- UPDATE `{business_code}_denominations_config` SET display_order = display_order + 10 WHERE is_coin = TRUE;

-- View current configuration
-- SELECT denomination_value, currency_symbol, 
--        CASE WHEN is_coin THEN 'Coin' ELSE 'Bill' END as type,
--        CASE WHEN is_active THEN 'Active' ELSE 'Disabled' END as status,
--        display_order
-- FROM `{business_code}_denominations_config`
-- ORDER BY display_order; 