-- WORDLY PROFILE SYNC MIGRATION (IDEMPOTENT)
-- Jalankan pada database wordly_db. Aman dijalankan ulang; tidak membuat kolom ganda.
SET @has_avatar_data = (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_settings' AND COLUMN_NAME = 'avatar_data'
);
SET @sql_avatar_data = IF(@has_avatar_data = 0,
  'ALTER TABLE user_settings ADD COLUMN avatar_data MEDIUMTEXT NULL',
  'SELECT "avatar_data sudah tersedia"');
PREPARE stmt_avatar_data FROM @sql_avatar_data;
EXECUTE stmt_avatar_data;
DEALLOCATE PREPARE stmt_avatar_data;

SET @has_avatar_zoom = (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_settings' AND COLUMN_NAME = 'avatar_zoom'
);
SET @sql_avatar_zoom = IF(@has_avatar_zoom = 0,
  'ALTER TABLE user_settings ADD COLUMN avatar_zoom DECIMAL(3,2) NOT NULL DEFAULT 1.00',
  'SELECT "avatar_zoom sudah tersedia"');
PREPARE stmt_avatar_zoom FROM @sql_avatar_zoom;
EXECUTE stmt_avatar_zoom;
DEALLOCATE PREPARE stmt_avatar_zoom;

-- Akun baru mulai dari 0 XP, 0 Coins, 0 streak, dan Level 1.
-- Data progress akun lama sengaja tidak dihapus.

-- Reset satu kali untuk menghapus angka demo/seed pada akun yang sudah ada.
-- Marker membuat reset tidak berulang saat file migrasi di-import lagi.
CREATE TABLE IF NOT EXISTS wordly_migrations (
  migration_key VARCHAR(100) PRIMARY KEY,
  applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT IGNORE INTO wordly_migrations(migration_key) VALUES ('profile_progress_zero_start_v1');
SET @wordly_do_reset = ROW_COUNT();
UPDATE user_progress
SET xp=0, coins=0, streak_days=0, current_level=1
WHERE @wordly_do_reset = 1;
DELETE FROM user_dashboard_state WHERE @wordly_do_reset = 1;
