-- Refactor `social_feed` and `social_feed_entity` to match project conventions.
--
-- - drop the polymorphic `entity_type_id` discriminator: only media is in scope
--   for the feature (cf. memory `project_social_feeds`). Add back as a real
--   discriminator if/when a new target type appears.
-- - rename `social_feed_entity` → `social_feed_media` and `entity_id` → `media_id`
--   with a real FK to `media(id)` (orphan rows are dropped first).
-- - add `position` (1..N, carousel order) + `created_at` (audit).
-- - `social_feed.id` stays as the share code (VARCHAR(10)): starts at 4 chars,
--   the generator may escalate to longer codes once collisions become frequent.

ALTER TABLE social_feed
  DROP COLUMN entity_type_id;

RENAME TABLE social_feed_entity TO social_feed_media;

-- Drop rows whose target media no longer exists; otherwise the FK below fails.
DELETE sfm FROM social_feed_media sfm
LEFT JOIN media m ON m.id = sfm.entity_id
WHERE m.id IS NULL;

ALTER TABLE social_feed_media
  CHANGE entity_id media_id BINARY(16) NOT NULL,
  ADD COLUMN position SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER media_id,
  ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER position;

-- Backfill `position` for the legacy rows using row_number over each feed.
-- Without a meaningful pre-existing ordering, we use the media_id as a stable
-- but arbitrary tie-breaker — feeds created before this migration end up with
-- a deterministic but possibly non-author-intended order. Two legacy feeds are
-- in scope so this is acceptable.
UPDATE social_feed_media t
JOIN (
  SELECT social_feed_id,
         media_id,
         ROW_NUMBER() OVER (PARTITION BY social_feed_id ORDER BY media_id) AS rn
  FROM social_feed_media
) s ON s.social_feed_id = t.social_feed_id AND s.media_id = t.media_id
SET t.position = s.rn;

ALTER TABLE social_feed_media
  ALTER COLUMN position DROP DEFAULT,
  ADD UNIQUE KEY uk_social_feed_media_position (social_feed_id, position),
  ADD CONSTRAINT fk_social_feed_media_media
      FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE ON UPDATE CASCADE;
