-- ============================================================
-- migration_v4.sql — Replace username columns with user IDs
-- Run once on the database
-- ============================================================

-- 1. kol_entries: specialist (username) → specialist_id (CHAR 36)
ALTER TABLE kol_entries
  ADD COLUMN specialist_id CHAR(36) DEFAULT NULL AFTER specialist;

UPDATE kol_entries e
  INNER JOIN users u ON u.un = e.specialist
  SET e.specialist_id = u.id;

ALTER TABLE kol_entries DROP INDEX idx_specialist;
ALTER TABLE kol_entries DROP COLUMN specialist;
ALTER TABLE kol_entries ADD INDEX idx_specialist_id (specialist_id);
ALTER TABLE kol_entries ADD CONSTRAINT fk_kol_specialist FOREIGN KEY (specialist_id) REFERENCES users(id) ON DELETE SET NULL;


-- 2. video_entries: artist_un (username) → artist_id (CHAR 36)
ALTER TABLE video_entries
  ADD COLUMN artist_id CHAR(36) DEFAULT NULL AFTER artist_un;

UPDATE video_entries v
  INNER JOIN users u ON u.un = v.artist_un
  SET v.artist_id = u.id;

ALTER TABLE video_entries DROP INDEX idx_artist_un;
ALTER TABLE video_entries DROP COLUMN artist_un;
ALTER TABLE video_entries ADD INDEX idx_artist_id (artist_id);
ALTER TABLE video_entries ADD CONSTRAINT fk_vid_artist FOREIGN KEY (artist_id) REFERENCES users(id) ON DELETE SET NULL;


-- 3. kpi_actuals: specialist_un (username) → specialist_id (CHAR 36)
ALTER TABLE kpi_actuals
  ADD COLUMN specialist_id CHAR(36) DEFAULT NULL AFTER specialist_un;

UPDATE kpi_actuals ka
  INNER JOIN users u ON u.un = ka.specialist_un
  SET ka.specialist_id = u.id;

ALTER TABLE kpi_actuals DROP INDEX uq_actual;
ALTER TABLE kpi_actuals DROP COLUMN specialist_un;
ALTER TABLE kpi_actuals ADD UNIQUE KEY uq_actual (kpi_set_id, specialist_id);
ALTER TABLE kpi_actuals ADD CONSTRAINT fk_kpi_specialist FOREIGN KEY (specialist_id) REFERENCES users(id) ON DELETE SET NULL;