-- ============================================================
--  UPDATE v2.0 - Nâng cấp DB cũ, KHÔNG xoá bảng, KHÔNG xoá dữ liệu
--  Chạy file này trong phpMyAdmin nếu bạn đã import bản cũ trước đó.
-- ============================================================
SET NAMES utf8mb4;

DELIMITER $$

DROP PROCEDURE IF EXISTS add_col_if_missing $$
CREATE PROCEDURE add_col_if_missing(
    IN p_table VARCHAR(64),
    IN p_col VARCHAR(64),
    IN p_def TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_col
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_col, '` ', p_def);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END $$

DROP PROCEDURE IF EXISTS add_idx_if_missing $$
CREATE PROCEDURE add_idx_if_missing(
    IN p_table VARCHAR(64),
    IN p_idx VARCHAR(64),
    IN p_def TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND INDEX_NAME = p_idx
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD ', p_def);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END $$

DELIMITER ;

ALTER TABLE `items` MODIFY COLUMN `external_id` VARCHAR(191) NULL;
CALL add_col_if_missing('items','avatar_url','VARCHAR(1000) NULL AFTER `last_data`');
CALL add_col_if_missing('items','followers_count','BIGINT NULL AFTER `avatar_url`');
CALL add_col_if_missing('items','following_count','BIGINT NULL AFTER `followers_count`');
CALL add_col_if_missing('items','likes_count','BIGINT NULL AFTER `following_count`');
CALL add_col_if_missing('items','videos_count','BIGINT NULL AFTER `likes_count`');
CALL add_col_if_missing('items','last_reason','VARCHAR(500) NULL AFTER `videos_count`');
CALL add_col_if_missing('items','http_code','INT NULL AFTER `last_reason`');
CALL add_col_if_missing('items','next_check_at','DATETIME NULL AFTER `http_code`');
CALL add_col_if_missing('items','die_at','DATETIME NULL AFTER `next_check_at`');
CALL add_col_if_missing('items','last_profile_sync_at','DATETIME NULL AFTER `die_at`');

CALL add_idx_if_missing('items','idx_next_check','INDEX `idx_next_check` (`status`,`next_check_at`,`last_checked`)');
CALL add_idx_if_missing('items','idx_user_platform_external_v2','INDEX `idx_user_platform_external_v2` (`user_id`,`platform`,`external_id`)');

-- Gợi ý kiểm tra trùng trước khi tự thêm UNIQUE:
-- SELECT user_id, platform, external_id, COUNT(*) c
-- FROM items
-- WHERE status <> 'deleted' AND external_id IS NOT NULL
-- GROUP BY user_id, platform, external_id
-- HAVING c > 1;
-- Nếu không có dòng trùng, bạn có thể chạy thêm:
-- ALTER TABLE items ADD UNIQUE KEY uk_user_platform_external (user_id, platform, external_id);

DROP PROCEDURE IF EXISTS add_col_if_missing;
DROP PROCEDURE IF EXISTS add_idx_if_missing;