-- Master database schema for tenant management

CREATE TABLE IF NOT EXISTS tenants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_key VARCHAR(100) NOT NULL,
    company_name VARCHAR(255) NOT NULL,
    site_name VARCHAR(255) NOT NULL,
    db_host VARCHAR(255) NOT NULL,
    db_name VARCHAR(255) NOT NULL,
    db_user VARCHAR(255) NOT NULL,
    db_pass VARCHAR(255) NOT NULL,
    default_domain VARCHAR(255) NOT NULL,
    default_theme VARCHAR(100) NOT NULL,
    default_language VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    maintenance_mode TINYINT(1) 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_tenants_tenant_key (tenant_key),
    UNIQUE KEY uq_tenants_db_name (db_name),
    UNIQUE KEY uq_tenants_default_domain (default_domain),
    KEY idx_tenants_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tenant records are managed deliberately, so delete is restricted instead of cascaded.
CREATE TABLE IF NOT EXISTS tenant_domains (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    domain VARCHAR(255) NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_domains_domain (domain),
    KEY idx_tenant_domains_tenant_id (tenant_id),
    KEY idx_tenant_domains_status (status),
    CONSTRAINT fk_tenant_domains_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS global_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(150) NOT NULL,
    setting_value LONGTEXT NULL,
    setting_group VARCHAR(100) NOT NULL DEFAULT 'general',
    autoload TINYINT(1) 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_global_settings_setting_key (setting_key),
    KEY idx_global_settings_setting_group (setting_group),
    KEY idx_global_settings_autoload (autoload)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    log_type VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    context LONGTEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_system_logs_log_type (log_type),
    KEY idx_system_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS update_packages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    package_name VARCHAR(255) NOT NULL,
    project VARCHAR(150) NOT NULL,
    project_version VARCHAR(50) NOT NULL,
    database_version VARCHAR(50) NOT NULL,
    theme_version VARCHAR(50) NOT NULL,
    component_version VARCHAR(50) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    manifest_json LONGTEXT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'created',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_update_packages_package_name (package_name),
    KEY idx_update_packages_project (project),
    KEY idx_update_packages_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS update_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    package_name VARCHAR(255) NOT NULL,
    old_project_version VARCHAR(50) NULL,
    new_project_version VARCHAR(50) NULL,
    old_database_version VARCHAR(50) NULL,
    new_database_version VARCHAR(50) NULL,
    status VARCHAR(50) NOT NULL,
    applied_sql_files LONGTEXT NULL,
    changed_files LONGTEXT NULL,
    backup_path VARCHAR(255) NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_update_logs_package_name (package_name),
    KEY idx_update_logs_status (status),
    KEY idx_update_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;