-- Terminal Groups System - Complete SQL Script
-- Execute this script directly in MySQL to create the complete grouping system

-- 1. Create terminal_groups table
CREATE TABLE IF NOT EXISTS terminal_groups (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    group_type VARCHAR(255) NOT NULL DEFAULT 'custom',
    color VARCHAR(255) DEFAULT '#3B82F6',
    icon VARCHAR(255) DEFAULT 'hero-squares-2x2',
    parent_group_id BIGINT,
    is_active BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(255),
    metadata JSON,
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_terminal_groups_name (name),
    KEY idx_terminal_groups_group_type (group_type),
    KEY idx_terminal_groups_parent_group_id (parent_group_id),
    KEY idx_terminal_groups_is_active (is_active),
    
    FOREIGN KEY (parent_group_id) REFERENCES terminal_groups(id) ON DELETE SET NULL
);

-- 2. Create terminal_group_rules table
CREATE TABLE IF NOT EXISTS terminal_group_rules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT NOT NULL,
    rule_name VARCHAR(255) NOT NULL,
    rule_type VARCHAR(255) NOT NULL,
    field_name VARCHAR(255),
    operator VARCHAR(255) NOT NULL,
    value TEXT NOT NULL,
    priority INT DEFAULT 100,
    is_active BOOLEAN DEFAULT TRUE,
    conditions JSON,
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    KEY idx_terminal_group_rules_group_id (group_id),
    KEY idx_terminal_group_rules_rule_type (rule_type),
    KEY idx_terminal_group_rules_field_name (field_name),
    KEY idx_terminal_group_rules_priority (priority),
    KEY idx_terminal_group_rules_is_active (is_active),
    
    FOREIGN KEY (group_id) REFERENCES terminal_groups(id) ON DELETE CASCADE
);

-- 3. Create terminal_group_memberships table
CREATE TABLE IF NOT EXISTS terminal_group_memberships (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    terminal_id BIGINT NOT NULL,
    group_id BIGINT NOT NULL,
    assignment_type VARCHAR(255) NOT NULL,
    assigned_by VARCHAR(255),
    assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    rule_id BIGINT,
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    KEY idx_terminal_group_memberships_terminal_id (terminal_id),
    KEY idx_terminal_group_memberships_group_id (group_id),
    KEY idx_terminal_group_memberships_assignment_type (assignment_type),
    KEY idx_terminal_group_memberships_is_active (is_active),
    KEY terminal_group_membership_composite_idx (terminal_id, group_id, is_active),
    
    FOREIGN KEY (group_id) REFERENCES terminal_groups(id) ON DELETE CASCADE,
    FOREIGN KEY (rule_id) REFERENCES terminal_group_rules(id) ON DELETE SET NULL
);

-- 4. Create merchant_types table
CREATE TABLE IF NOT EXISTS merchant_types (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    code VARCHAR(255) NOT NULL,
    category VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_merchant_types_code (code),
    UNIQUE KEY unique_merchant_types_name (name),
    KEY idx_merchant_types_category (category),
    KEY idx_merchant_types_is_active (is_active)
);

-- 5. Add columns to merchants table (only if they don't exist)
SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND column_name = 'merchant_type_id';
SET @sql = IF(@count = 0, 'ALTER TABLE merchants ADD COLUMN merchant_type_id BIGINT', 'SELECT "merchant_type_id already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND column_name = 'business_category';
SET @sql = IF(@count = 0, 'ALTER TABLE merchants ADD COLUMN business_category VARCHAR(255)', 'SELECT "business_category already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND column_name = 'region';
SET @sql = IF(@count = 0, 'ALTER TABLE merchants ADD COLUMN region VARCHAR(255)', 'SELECT "region already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND column_name = 'tier';
SET @sql = IF(@count = 0, 'ALTER TABLE merchants ADD COLUMN tier VARCHAR(255)', 'SELECT "tier already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6. Add foreign key constraint for merchants.merchant_type_id (only if it doesn't exist)
SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND constraint_name = 'merchants_merchant_type_id_fkey';
SET @sql = IF(@count = 0, 
    'ALTER TABLE merchants ADD CONSTRAINT merchants_merchant_type_id_fkey FOREIGN KEY (merchant_type_id) REFERENCES merchant_types(id) ON DELETE SET NULL',
    'SELECT "Foreign key already exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7. Add indexes for merchants table (only if they don't exist)
SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND index_name = 'idx_merchants_merchant_type_id';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_merchants_merchant_type_id ON merchants(merchant_type_id)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND index_name = 'idx_merchants_business_category';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_merchants_business_category ON merchants(business_category)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND index_name = 'idx_merchants_region';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_merchants_region ON merchants(region)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'merchants' AND index_name = 'idx_merchants_tier';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_merchants_tier ON merchants(tier)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 8. Add columns to tms_terminals table (only if they don't exist)
SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND column_name = 'location_code';
SET @sql = IF(@count = 0, 'ALTER TABLE tms_terminals ADD COLUMN location_code VARCHAR(255)', 'SELECT "location_code already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND column_name = 'deployment_type';
SET @sql = IF(@count = 0, 'ALTER TABLE tms_terminals ADD COLUMN deployment_type VARCHAR(255)', 'SELECT "deployment_type already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND column_name = 'tier';
SET @sql = IF(@count = 0, 'ALTER TABLE tms_terminals ADD COLUMN tier VARCHAR(255)', 'SELECT "tier already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 9. Add foreign key constraint for tms_terminals.merchant_id (only if it doesn't exist)
-- Note: Assuming merchant_id column already exists, just adding the constraint
SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND constraint_name = 'tms_terminals_merchant_id_fkey';
SET @sql = IF(@count = 0, 
    'ALTER TABLE tms_terminals ADD CONSTRAINT tms_terminals_merchant_id_fkey FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE SET NULL',
    'SELECT "Foreign key already exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 10. Add indexes for tms_terminals table (only if they don't exist)
SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND index_name = 'idx_tms_terminals_merchant_id';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_tms_terminals_merchant_id ON tms_terminals(merchant_id)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND index_name = 'idx_tms_terminals_location_code';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_tms_terminals_location_code ON tms_terminals(location_code)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND index_name = 'idx_tms_terminals_deployment_type';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_tms_terminals_deployment_type ON tms_terminals(deployment_type)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = '';
SELECT COUNT(*) INTO @count FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = DATABASE() AND table_name = 'tms_terminals' AND index_name = 'idx_tms_terminals_tier';
SET @sql = IF(@count = 0, 'CREATE INDEX idx_tms_terminals_tier ON tms_terminals(tier)', 'SELECT "Index already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 11. Insert default terminal groups
INSERT IGNORE INTO terminal_groups (name, description, group_type, color, icon, is_active, created_by, inserted_at, updated_at) VALUES
('All Terminals', 'Default group containing all terminals', 'system', '#6B7280', 'hero-computer-desktop', true, 'system', NOW(), NOW()),
('Active Terminals', 'Terminals currently active and operational', 'auto', '#10B981', 'hero-check-circle', true, 'system', NOW(), NOW()),
('Inactive Terminals', 'Terminals that are offline or inactive', 'auto', '#EF4444', 'hero-x-circle', true, 'system', NOW(), NOW()),
('High Volume', 'Terminals processing high transaction volumes', 'auto', '#8B5CF6', 'hero-chart-bar-square', true, 'system', NOW(), NOW());

-- 12. Insert default merchant types
INSERT IGNORE INTO merchant_types (name, description, code, category, is_active, inserted_at, updated_at) VALUES
('Retail Store', 'General retail establishments', 'RETAIL', 'retail', true, NOW(), NOW()),
('Restaurant', 'Food service establishments', 'RESTAURANT', 'food', true, NOW(), NOW()),
('Healthcare', 'Medical and healthcare facilities', 'HEALTHCARE', 'healthcare', true, NOW(), NOW()),
('Gas Station', 'Fuel and convenience stores', 'GAS_STATION', 'automotive', true, NOW(), NOW()),
('E-commerce', 'Online businesses', 'ECOMMERCE', 'digital', true, NOW(), NOW()),
('Small Business', 'Small scale businesses', 'SMB', 'general', true, NOW(), NOW()),
('Enterprise', 'Large enterprise clients', 'ENTERPRISE', 'corporate', true, NOW(), NOW());

-- 13. Insert default rules for auto groups
INSERT IGNORE INTO terminal_group_rules (group_id, rule_name, rule_type, field_name, operator, value, priority, is_active, inserted_at, updated_at)
SELECT 
    tg.id,
    'Active Status Rule',
    'field_match',
    'status',
    'equals',
    'active',
    1,
    true,
    NOW(),
    NOW()
FROM terminal_groups tg WHERE tg.name = 'Active Terminals';

INSERT IGNORE INTO terminal_group_rules (group_id, rule_name, rule_type, field_name, operator, value, priority, is_active, inserted_at, updated_at)
SELECT 
    tg.id,
    'Inactive Status Rule',
    'field_match',
    'status',
    'in',
    'inactive,offline,maintenance',
    1,
    true,
    NOW(),
    NOW()
FROM terminal_groups tg WHERE tg.name = 'Inactive Terminals';

-- 14. Verify the installation
SELECT 'Terminal Groups System Installation Complete' as status;
SELECT COUNT(*) as terminal_groups_count FROM terminal_groups;
SELECT COUNT(*) as merchant_types_count FROM merchant_types;
SELECT COUNT(*) as default_rules_count FROM terminal_group_rules;
