-- Migration: thông tin trung tâm trong cấu hình.
-- An toàn khi chạy lại: chỉ tạo bảng/cột còn thiếu, không UPDATE dữ liệu hiện có.
-- Chạy trên database đang phục vụ website trước khi kiểm thử form Cấu hình.

CREATE TABLE IF NOT EXISTS `config_tb` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `center_name` VARCHAR(255) NULL DEFAULT NULL,
    `address` VARCHAR(500) NULL DEFAULT NULL,
    `phone` VARCHAR(50) NULL DEFAULT NULL,
    `email` VARCHAR(255) NULL DEFAULT NULL,
    `website` VARCHAR(255) NULL DEFAULT NULL,
    `logo` MEDIUMTEXT NULL,
    `date_created` DATETIME NULL DEFAULT NULL,
    `date_updated` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hỗ trợ cả trường hợp config_tb đã có từ trước nhưng chưa có các cột mới.
SET @config_columns := DATABASE();

SET @has_center_name := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @config_columns AND TABLE_NAME = 'config_tb' AND COLUMN_NAME = 'center_name');
SET @sql := IF(@has_center_name = 0, 'ALTER TABLE `config_tb` ADD COLUMN `center_name` VARCHAR(255) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_address := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @config_columns AND TABLE_NAME = 'config_tb' AND COLUMN_NAME = 'address');
SET @sql := IF(@has_address = 0, 'ALTER TABLE `config_tb` ADD COLUMN `address` VARCHAR(500) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_phone := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @config_columns AND TABLE_NAME = 'config_tb' AND COLUMN_NAME = 'phone');
SET @sql := IF(@has_phone = 0, 'ALTER TABLE `config_tb` ADD COLUMN `phone` VARCHAR(50) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_email := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @config_columns AND TABLE_NAME = 'config_tb' AND COLUMN_NAME = 'email');
SET @sql := IF(@has_email = 0, 'ALTER TABLE `config_tb` ADD COLUMN `email` VARCHAR(255) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_website := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @config_columns AND TABLE_NAME = 'config_tb' AND COLUMN_NAME = 'website');
SET @sql := IF(@has_website = 0, 'ALTER TABLE `config_tb` ADD COLUMN `website` VARCHAR(255) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_logo := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @config_columns AND TABLE_NAME = 'config_tb' AND COLUMN_NAME = 'logo');
SET @sql := IF(@has_logo = 0, 'ALTER TABLE `config_tb` ADD COLUMN `logo` MEDIUMTEXT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_date_updated := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @config_columns AND TABLE_NAME = 'config_tb' AND COLUMN_NAME = 'date_updated');
SET @sql := IF(@has_date_updated = 0, 'ALTER TABLE `config_tb` ADD COLUMN `date_updated` DATE NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Controller luôn đọc/lưu bản ghi ID 1. Chỉ seed nếu bản ghi chưa tồn tại.
INSERT INTO `config_tb` (`id`)
VALUES (1)
ON DUPLICATE KEY UPDATE `id` = `id`;