ALTER TABLE update_packages
    ADD COLUMN IF NOT EXISTS package_key VARCHAR(191) NULL AFTER id,
    ADD COLUMN IF NOT EXISTS version VARCHAR(50) NULL AFTER package_name,
    ADD COLUMN IF NOT EXISTS package_type VARCHAR(50) NULL AFTER version,
    ADD COLUMN IF NOT EXISTS checksum VARCHAR(191) NULL AFTER file_path,
    ADD COLUMN IF NOT EXISTS release_notes LONGTEXT NULL AFTER checksum,
    ADD COLUMN IF NOT EXISTS created_by BIGINT(20) UNSIGNED NULL AFTER release_notes;

SET @bmp15_update_packages_key_index_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'update_packages'
      AND INDEX_NAME = 'uk_update_packages_package_key'
);
SET @bmp15_update_packages_key_index_sql := IF(
    @bmp15_update_packages_key_index_exists = 0,
    'ALTER TABLE update_packages ADD UNIQUE KEY uk_update_packages_package_key (package_key)',
    'SELECT 1'
);
PREPARE bmp15_update_packages_key_index_stmt FROM @bmp15_update_packages_key_index_sql;
EXECUTE bmp15_update_packages_key_index_stmt;
DEALLOCATE PREPARE bmp15_update_packages_key_index_stmt;

SET @bmp15_update_packages_type_index_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'update_packages'
      AND INDEX_NAME = 'idx_update_packages_package_type'
);
SET @bmp15_update_packages_type_index_sql := IF(
    @bmp15_update_packages_type_index_exists = 0,
    'ALTER TABLE update_packages ADD KEY idx_update_packages_package_type (package_type)',
    'SELECT 1'
);
PREPARE bmp15_update_packages_type_index_stmt FROM @bmp15_update_packages_type_index_sql;
EXECUTE bmp15_update_packages_type_index_stmt;
DEALLOCATE PREPARE bmp15_update_packages_type_index_stmt;

ALTER TABLE update_logs
    ADD COLUMN IF NOT EXISTS tenant_id BIGINT(20) UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS update_package_id BIGINT(20) UNSIGNED NULL AFTER tenant_id,
    ADD COLUMN IF NOT EXISTS action VARCHAR(50) NULL AFTER update_package_id,
    ADD COLUMN IF NOT EXISTS message TEXT NULL AFTER status,
    ADD COLUMN IF NOT EXISTS started_at DATETIME NULL AFTER message,
    ADD COLUMN IF NOT EXISTS finished_at DATETIME NULL AFTER started_at,
    ADD COLUMN IF NOT EXISTS created_by BIGINT(20) UNSIGNED NULL AFTER finished_at;

SET @bmp15_update_logs_tenant_index_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'update_logs'
      AND INDEX_NAME = 'idx_update_logs_tenant_id'
);
SET @bmp15_update_logs_tenant_index_sql := IF(
    @bmp15_update_logs_tenant_index_exists = 0,
    'ALTER TABLE update_logs ADD KEY idx_update_logs_tenant_id (tenant_id)',
    'SELECT 1'
);
PREPARE bmp15_update_logs_tenant_index_stmt FROM @bmp15_update_logs_tenant_index_sql;
EXECUTE bmp15_update_logs_tenant_index_stmt;
DEALLOCATE PREPARE bmp15_update_logs_tenant_index_stmt;

SET @bmp15_update_logs_package_index_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'update_logs'
      AND INDEX_NAME = 'idx_update_logs_update_package_id'
);
SET @bmp15_update_logs_package_index_sql := IF(
    @bmp15_update_logs_package_index_exists = 0,
    'ALTER TABLE update_logs ADD KEY idx_update_logs_update_package_id (update_package_id)',
    'SELECT 1'
);
PREPARE bmp15_update_logs_package_index_stmt FROM @bmp15_update_logs_package_index_sql;
EXECUTE bmp15_update_logs_package_index_stmt;
DEALLOCATE PREPARE bmp15_update_logs_package_index_stmt;

SET @bmp15_update_logs_action_index_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'update_logs'
      AND INDEX_NAME = 'idx_update_logs_action'
);
SET @bmp15_update_logs_action_index_sql := IF(
    @bmp15_update_logs_action_index_exists = 0,
    'ALTER TABLE update_logs ADD KEY idx_update_logs_action (action)',
    'SELECT 1'
);
PREPARE bmp15_update_logs_action_index_stmt FROM @bmp15_update_logs_action_index_sql;
EXECUTE bmp15_update_logs_action_index_stmt;
DEALLOCATE PREPARE bmp15_update_logs_action_index_stmt;

SET @bmp15_update_logs_tenant_fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'update_logs'
      AND CONSTRAINT_NAME = 'fk_update_logs_tenant_id'
);
SET @bmp15_update_logs_tenant_fk_sql := IF(
    @bmp15_update_logs_tenant_fk_exists = 0,
    'ALTER TABLE update_logs ADD CONSTRAINT fk_update_logs_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON UPDATE CASCADE ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE bmp15_update_logs_tenant_fk_stmt FROM @bmp15_update_logs_tenant_fk_sql;
EXECUTE bmp15_update_logs_tenant_fk_stmt;
DEALLOCATE PREPARE bmp15_update_logs_tenant_fk_stmt;

SET @bmp15_update_logs_package_fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'update_logs'
      AND CONSTRAINT_NAME = 'fk_update_logs_update_package_id'
);
SET @bmp15_update_logs_package_fk_sql := IF(
    @bmp15_update_logs_package_fk_exists = 0,
    'ALTER TABLE update_logs ADD CONSTRAINT fk_update_logs_update_package_id FOREIGN KEY (update_package_id) REFERENCES update_packages (id) ON UPDATE CASCADE ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE bmp15_update_logs_package_fk_stmt FROM @bmp15_update_logs_package_fk_sql;
EXECUTE bmp15_update_logs_package_fk_stmt;
DEALLOCATE PREPARE bmp15_update_logs_package_fk_stmt;

CREATE TABLE IF NOT EXISTS tenant_update_status (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NOT NULL,
    project_version VARCHAR(50) NOT NULL DEFAULT '1.0.0',
    database_version VARCHAR(50) NOT NULL DEFAULT '1.0.0',
    theme_version VARCHAR(50) NOT NULL DEFAULT '1.0.0',
    component_version VARCHAR(50) NOT NULL DEFAULT '1.0.0',
    last_checked_at DATETIME NULL,
    last_updated_at DATETIME NULL,
    maintenance_mode TINYINT(1) NOT NULL DEFAULT 0,
    maintenance_message TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_tenant_update_status_tenant_id (tenant_id),
    KEY idx_tenant_update_status_maintenance_mode (maintenance_mode),
    CONSTRAINT fk_tenant_update_status_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_windows (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NULL,
    title VARCHAR(191) NOT NULL,
    message TEXT NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'scheduled',
    created_by BIGINT(20) UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_maintenance_windows_tenant_id (tenant_id),
    KEY idx_maintenance_windows_status (status),
    KEY idx_maintenance_windows_starts_at (starts_at),
    CONSTRAINT fk_maintenance_windows_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;