-- ACTIVE MIGRATION
-- This file is part of the master replay chain.
-- Safe to include in controlled fresh environment replay.

CREATE TABLE IF NOT EXISTS packages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    package_key VARCHAR(100) NOT NULL,
    package_name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_packages_package_key (package_key),
    KEY idx_packages_is_active (is_active),
    KEY idx_packages_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS package_features (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    package_id BIGINT UNSIGNED NOT NULL,
    feature_key VARCHAR(100) NOT NULL,
    feature_value VARCHAR(255) NOT NULL,
    value_type VARCHAR(50) NOT NULL DEFAULT 'boolean',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_package_features_package_feature (package_id, feature_key),
    KEY idx_package_features_feature_key (feature_key),
    CONSTRAINT fk_package_features_package_id
        FOREIGN KEY (package_id) REFERENCES packages (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tenant_package_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    package_id BIGINT UNSIGNED NOT NULL,
    starts_at DATETIME NULL,
    expires_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    assigned_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_tenant_package_assignments_tenant_id (tenant_id),
    KEY idx_tenant_package_assignments_package_id (package_id),
    KEY idx_tenant_package_assignments_is_active (is_active),
    KEY idx_tenant_package_assignments_dates (starts_at, expires_at),
    KEY idx_tenant_package_assignments_assigned_by (assigned_by),
    CONSTRAINT fk_tenant_package_assignments_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_tenant_package_assignments_package_id
        FOREIGN KEY (package_id) REFERENCES packages (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tenant_usage_cache (
    tenant_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    current_users INT NOT NULL DEFAULT 0,
    current_storage_mb INT NOT NULL DEFAULT 0,
    current_sites INT NOT NULL DEFAULT 0,
    current_domains INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_tenant_usage_cache_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO packages (package_key, package_name, description, is_active, sort_order)
VALUES
    ('lite', 'Lite', 'Giris seviyesi package', 1, 10),
    ('standard', 'Standard', 'Standart package', 1, 20),
    ('advanced', 'Advanced', 'Gelismis package', 1, 30),
    ('professional', 'Professional', 'Profesyonel package', 1, 40)
ON DUPLICATE KEY UPDATE
    package_name = VALUES(package_name),
    description = VALUES(description),
    is_active = VALUES(is_active),
    sort_order = VALUES(sort_order);

INSERT INTO package_features (package_id, feature_key, feature_value, value_type)
SELECT p.id, f.feature_key, f.feature_value, f.value_type
FROM packages p
INNER JOIN (
    SELECT 'lite' AS package_key, 'max_users' AS feature_key, '2' AS feature_value, 'integer' AS value_type
    UNION ALL SELECT 'lite', 'max_storage_mb', '512', 'integer'
    UNION ALL SELECT 'lite', 'max_domains', '1', 'integer'
    UNION ALL SELECT 'lite', 'max_sites', '1', 'integer'
    UNION ALL SELECT 'lite', 'max_pages', '10', 'integer'
    UNION ALL SELECT 'lite', 'max_blocks', '25', 'integer'
    UNION ALL SELECT 'lite', 'custom_css', '0', 'boolean'
    UNION ALL SELECT 'lite', 'custom_js', '0', 'boolean'
    UNION ALL SELECT 'lite', 'seo_module', '0', 'boolean'
    UNION ALL SELECT 'lite', 'blog_module', '0', 'boolean'
    UNION ALL SELECT 'lite', 'ecommerce_module', '0', 'boolean'
    UNION ALL SELECT 'lite', 'analytics_module', '0', 'boolean'
    UNION ALL SELECT 'lite', 'api_access', '0', 'boolean'
    UNION ALL SELECT 'lite', 'export_tools', '0', 'boolean'
    UNION ALL SELECT 'lite', 'backup_tools', '0', 'boolean'
    UNION ALL SELECT 'lite', 'update_center', '0', 'boolean'
    UNION ALL SELECT 'lite', 'maintenance_mode', '1', 'boolean'
    UNION ALL SELECT 'lite', 'web_module', '0', 'boolean'
    UNION ALL SELECT 'lite', 'smtp_access', '0', 'boolean'
    UNION ALL SELECT 'lite', 'sms_access', '0', 'boolean'
    UNION ALL SELECT 'lite', 'block_access', '1', 'boolean'
    UNION ALL SELECT 'lite', 'builder_access', '0', 'boolean'
    UNION ALL SELECT 'lite', 'media_library', '1', 'boolean'

    UNION ALL SELECT 'standard', 'max_users', '5', 'integer'
    UNION ALL SELECT 'standard', 'max_storage_mb', '2048', 'integer'
    UNION ALL SELECT 'standard', 'max_domains', '3', 'integer'
    UNION ALL SELECT 'standard', 'max_sites', '2', 'integer'
    UNION ALL SELECT 'standard', 'max_pages', '50', 'integer'
    UNION ALL SELECT 'standard', 'max_blocks', '100', 'integer'
    UNION ALL SELECT 'standard', 'custom_css', '1', 'boolean'
    UNION ALL SELECT 'standard', 'custom_js', '0', 'boolean'
    UNION ALL SELECT 'standard', 'seo_module', '1', 'boolean'
    UNION ALL SELECT 'standard', 'blog_module', '1', 'boolean'
    UNION ALL SELECT 'standard', 'ecommerce_module', '0', 'boolean'
    UNION ALL SELECT 'standard', 'analytics_module', '1', 'boolean'
    UNION ALL SELECT 'standard', 'api_access', '0', 'boolean'
    UNION ALL SELECT 'standard', 'export_tools', '0', 'boolean'
    UNION ALL SELECT 'standard', 'backup_tools', '1', 'boolean'
    UNION ALL SELECT 'standard', 'update_center', '0', 'boolean'
    UNION ALL SELECT 'standard', 'maintenance_mode', '1', 'boolean'
    UNION ALL SELECT 'standard', 'web_module', '1', 'boolean'
    UNION ALL SELECT 'standard', 'smtp_access', '1', 'boolean'
    UNION ALL SELECT 'standard', 'sms_access', '0', 'boolean'
    UNION ALL SELECT 'standard', 'block_access', '1', 'boolean'
    UNION ALL SELECT 'standard', 'builder_access', '1', 'boolean'
    UNION ALL SELECT 'standard', 'media_library', '1', 'boolean'

    UNION ALL SELECT 'advanced', 'max_users', '15', 'integer'
    UNION ALL SELECT 'advanced', 'max_storage_mb', '5120', 'integer'
    UNION ALL SELECT 'advanced', 'max_domains', '10', 'integer'
    UNION ALL SELECT 'advanced', 'max_sites', '5', 'integer'
    UNION ALL SELECT 'advanced', 'max_pages', '200', 'integer'
    UNION ALL SELECT 'advanced', 'max_blocks', '300', 'integer'
    UNION ALL SELECT 'advanced', 'custom_css', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'custom_js', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'seo_module', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'blog_module', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'ecommerce_module', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'analytics_module', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'api_access', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'export_tools', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'backup_tools', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'update_center', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'maintenance_mode', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'web_module', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'smtp_access', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'sms_access', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'block_access', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'builder_access', '1', 'boolean'
    UNION ALL SELECT 'advanced', 'media_library', '1', 'boolean'

    UNION ALL SELECT 'professional', 'max_users', '9999', 'integer'
    UNION ALL SELECT 'professional', 'max_storage_mb', '20480', 'integer'
    UNION ALL SELECT 'professional', 'max_domains', '999', 'integer'
    UNION ALL SELECT 'professional', 'max_sites', '999', 'integer'
    UNION ALL SELECT 'professional', 'max_pages', '9999', 'integer'
    UNION ALL SELECT 'professional', 'max_blocks', '9999', 'integer'
    UNION ALL SELECT 'professional', 'custom_css', '1', 'boolean'
    UNION ALL SELECT 'professional', 'custom_js', '1', 'boolean'
    UNION ALL SELECT 'professional', 'seo_module', '1', 'boolean'
    UNION ALL SELECT 'professional', 'blog_module', '1', 'boolean'
    UNION ALL SELECT 'professional', 'ecommerce_module', '1', 'boolean'
    UNION ALL SELECT 'professional', 'analytics_module', '1', 'boolean'
    UNION ALL SELECT 'professional', 'api_access', '1', 'boolean'
    UNION ALL SELECT 'professional', 'export_tools', '1', 'boolean'
    UNION ALL SELECT 'professional', 'backup_tools', '1', 'boolean'
    UNION ALL SELECT 'professional', 'update_center', '1', 'boolean'
    UNION ALL SELECT 'professional', 'maintenance_mode', '1', 'boolean'
    UNION ALL SELECT 'professional', 'web_module', '1', 'boolean'
    UNION ALL SELECT 'professional', 'smtp_access', '1', 'boolean'
    UNION ALL SELECT 'professional', 'sms_access', '1', 'boolean'
    UNION ALL SELECT 'professional', 'block_access', '1', 'boolean'
    UNION ALL SELECT 'professional', 'builder_access', '1', 'boolean'
    UNION ALL SELECT 'professional', 'media_library', '1', 'boolean'
) f ON f.package_key = p.package_key
ON DUPLICATE KEY UPDATE
    feature_value = VALUES(feature_value),
    value_type = VALUES(value_type);