-- @database: main
-- Feature « mes réseaux sociaux » (self-service).
--
-- Modèle retenu : on stocke le HANDLE et on reconstruit l'URL au read via un
-- `url_template` porté par le catalogue `social` (robuste + sûr : jamais d'URL
-- arbitraire rendue telle quelle). Exception : `website` (kind='url'), qui
-- stocke l'URL complète.

-- =====================================================================
-- 1) Catalogue `social` : discriminant + template + activation + ordre
-- =====================================================================
ALTER TABLE `social`
  ADD COLUMN `kind`         ENUM('handle','url') NOT NULL DEFAULT 'handle' AFTER `slug`,
  ADD COLUMN `url_template` VARCHAR(120) NULL DEFAULT NULL AFTER `kind`,
  ADD COLUMN `is_active`    TINYINT(1) NOT NULL DEFAULT 1 AFTER `name`,
  ADD COLUMN `position`     SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER `is_active`;

-- Correction des typos de slug (le slug est désormais l'identité API publique).
UPDATE `social` SET `slug` = 'dailymotion' WHERE `slug` = 'daylimotion';
UPDATE `social` SET `slug` = 'soundcloud'  WHERE `slug` = 'souncloud';

-- Seed : kind, template et ordre d'affichage par réseau.
UPDATE `social` SET `kind`='url',    `url_template`=NULL,                                     `position`=0   WHERE `slug`='website';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.instagram.com/{handle}',     `position`=10  WHERE `slug`='instagram';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.facebook.com/{handle}',      `position`=20  WHERE `slug`='facebook';
UPDATE `social` SET `kind`='handle', `url_template`='https://x.com/{handle}',                 `position`=30  WHERE `slug`='twitter';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.tiktok.com/@{handle}',       `position`=40  WHERE `slug`='tiktok';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.youtube.com/@{handle}',      `position`=50  WHERE `slug`='youtube';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.linkedin.com/in/{handle}',   `position`=60  WHERE `slug`='linkedin';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.pinterest.com/{handle}',     `position`=70  WHERE `slug`='pinterest';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.snapchat.com/add/{handle}',  `position`=80  WHERE `slug`='snapchat';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.reddit.com/user/{handle}',   `position`=90  WHERE `slug`='reddit';
UPDATE `social` SET `kind`='handle', `url_template`='https://{handle}.tumblr.com',            `position`=100 WHERE `slug`='tumblr';
UPDATE `social` SET `kind`='handle', `url_template`='https://vimeo.com/{handle}',             `position`=110 WHERE `slug`='vimeo';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.dailymotion.com/{handle}',   `position`=120 WHERE `slug`='dailymotion';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.flickr.com/people/{handle}', `position`=130 WHERE `slug`='flickr';
UPDATE `social` SET `kind`='handle', `url_template`='https://500px.com/p/{handle}',           `position`=140 WHERE `slug`='500px';
UPDATE `social` SET `kind`='handle', `url_template`='https://soundcloud.com/{handle}',        `position`=150 WHERE `slug`='soundcloud';
UPDATE `social` SET `kind`='handle', `url_template`='https://open.spotify.com/user/{handle}', `position`=160 WHERE `slug`='spotify';
UPDATE `social` SET `kind`='handle', `url_template`='https://www.xing.com/profile/{handle}',  `position`=170 WHERE `slug`='xing';
-- Viadeo : réseau défunt, on le garde pour l'historique mais on le retire du catalogue.
UPDATE `social` SET `kind`='handle', `url_template`=NULL, `is_active`=0,                       `position`=180 WHERE `slug`='viadeo';

-- Le slug devient identifiant public : on garantit son unicité.
ALTER TABLE `social`
  ADD UNIQUE KEY `uq_social_slug` (`slug`);

-- =====================================================================
-- 2) `user_social` : url -> handle, FK manquante, ordre perso
-- =====================================================================
ALTER TABLE `user_social`
  CHANGE COLUMN `url` `handle` VARCHAR(255) NULL DEFAULT NULL,
  ADD COLUMN `position` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `handle`;

-- Backfill best-effort des lignes legacy (URL complète -> handle) : on ne garde
-- que le dernier segment de chemin, sans `@` de tête. Les réseaux `url`
-- (website) conservent leur URL telle quelle.
UPDATE `user_social` us
  JOIN `social` s ON s.`id` = us.`social_id`
   SET us.`handle` = TRIM(LEADING '@' FROM
         SUBSTRING_INDEX(TRIM(TRAILING '/' FROM us.`handle`), '/', -1))
 WHERE s.`kind` = 'handle'
   AND us.`handle` IS NOT NULL
   AND us.`handle` <> ''
   AND us.`handle` LIKE '%/%';

-- On purge les handles vides (données legacy inexploitables) avant NOT NULL.
DELETE FROM `user_social` WHERE `handle` IS NULL OR TRIM(`handle`) = '';

ALTER TABLE `user_social`
  MODIFY COLUMN `handle` VARCHAR(255) NOT NULL;

-- FK vers le catalogue (absente jusqu'ici) : intégrité référentielle.
ALTER TABLE `user_social`
  ADD CONSTRAINT `fk_user_social_social`
      FOREIGN KEY (`social_id`) REFERENCES `social` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE;
