-- 1) Ensure the new table exists
CREATE TABLE IF NOT EXISTS levelrewards (
  guild_id BIGINT NOT NULL,
  levelreq INT NOT NULL,
  role BIGINT NOT NULL,
  PRIMARY KEY (guild_id, levelreq)
);

-- 2) Backfill from legacy columns (only rows that have both values)
INSERT INTO levelrewards (guild_id, levelreq, role)
SELECT guild_id, levelreq, role
FROM levelsettings
WHERE role IS NOT NULL
  AND levelreq IS NOT NULL
ON CONFLICT (guild_id, levelreq)
DO UPDATE SET role = EXCLUDED.role;

SELECT
  (SELECT COUNT(*)
   FROM levelsettings
   WHERE role IS NOT NULL AND levelreq IS NOT NULL) AS legacy_reward_rows,
  (SELECT COUNT(*)
   FROM levelrewards) AS levelrewards_rows;

   SELECT ls.guild_id, ls.levelreq, ls.role
FROM levelsettings ls
LEFT JOIN levelrewards lr
  ON lr.guild_id = ls.guild_id AND lr.levelreq = ls.levelreq
WHERE ls.role IS NOT NULL
  AND ls.levelreq IS NOT NULL
  AND lr.guild_id IS NULL;