-- WORDLY username + 7-day username-change cooldown
-- Jalankan sekali pada database wordly_db melalui phpMyAdmin.
-- Kompatibel dengan tabel users lama yang memakai display_name (bukan username).

USE wordly_db;

DROP PROCEDURE IF EXISTS wordly_migrate_username_cooldown;
DELIMITER //
CREATE PROCEDURE wordly_migrate_username_cooldown()
BEGIN
  -- Tambahkan kolom username jika database lama belum memilikinya.
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='username'
  ) THEN
    ALTER TABLE users ADD COLUMN username VARCHAR(30) NULL AFTER display_name;
  END IF;

  -- Isi username awal untuk akun lama agar tidak ada nilai NULL/kosong.
  UPDATE users
  SET username=CONCAT('user', id)
  WHERE username IS NULL OR TRIM(username)='';

  -- Pastikan username unik.
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND INDEX_NAME='uq_users_username'
  ) THEN
    CREATE UNIQUE INDEX uq_users_username ON users(username);
  END IF;

  -- Username wajib tersedia untuk akun berikutnya.
  ALTER TABLE users MODIFY COLUMN username VARCHAR(30) NOT NULL;

  -- Tambahkan penanda waktu perubahan username.
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='username_changed_at'
  ) THEN
    ALTER TABLE users ADD COLUMN username_changed_at DATETIME NULL DEFAULT NULL AFTER username;
  END IF;
END//
DELIMITER ;

CALL wordly_migrate_username_cooldown();
DROP PROCEDURE wordly_migrate_username_cooldown;
