-- Makes the set of platforms admin-editable (admin/platforms.php) instead of
-- a fixed enum, and splits the old generic "playstation" value into
-- "playstation4" / "playstation5", adding "switch2" alongside "switch".
--
-- Assumes no existing `keys`/`redemptions` rows use the old generic
-- 'playstation' value — true for this project's own installs at the time
-- this migration was written. If you do have such rows, reassign them to
-- 'playstation4' or 'playstation5' by hand before running this, e.g.:
--   UPDATE `keys` SET platform = 'playstation4' WHERE platform = 'playstation';
--   UPDATE redemptions SET platform = 'playstation4' WHERE platform = 'playstation';

CREATE TABLE IF NOT EXISTS platforms (
    slug        VARCHAR(32) NOT NULL PRIMARY KEY,
    label       VARCHAR(64) NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO platforms (slug, label, sort_order, created_at) VALUES
    ('steam', 'Steam', 0, NOW()),
    ('xbox', 'Xbox', 1, NOW()),
    ('playstation4', 'PlayStation 4', 2, NOW()),
    ('playstation5', 'PlayStation 5', 3, NOW()),
    ('switch', 'Switch', 4, NOW()),
    ('switch2', 'Switch 2', 5, NOW());

ALTER TABLE `keys` MODIFY platform VARCHAR(32) NOT NULL;
ALTER TABLE `keys` ADD CONSTRAINT fk_key_platform FOREIGN KEY (platform) REFERENCES platforms(slug) ON DELETE CASCADE;

ALTER TABLE redemptions MODIFY platform VARCHAR(32) NOT NULL;
