-- Uitbreiding 3
-- Woonplaatsen normaliseren, geocoding en Leaflet-kaart voorbereiden

USE ID201584_smoelenboek;

-- --------------------------------------------------------
-- Nieuwe tabel met alle woonplaatsen
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `smoelboek_places` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_place_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- Nieuwe foreign key kolom bij collega's
-- --------------------------------------------------------

ALTER TABLE `smoelboek_colleagues`
  ADD COLUMN `place_id` int DEFAULT NULL AFTER `last_name`;

-- --------------------------------------------------------
-- Bestaande woonplaatsen overnemen naar de nieuwe tabel
-- --------------------------------------------------------

INSERT IGNORE INTO `smoelboek_places` (`name`)
SELECT DISTINCT TRIM(`city`) AS `name`
FROM `smoelboek_colleagues`
WHERE TRIM(`city`) <> '';

-- --------------------------------------------------------
-- Bestaande collega's koppelen aan hun woonplaats
-- --------------------------------------------------------

UPDATE `smoelboek_colleagues` AS c
INNER JOIN `smoelboek_places` AS p
    ON p.`name` = TRIM(c.`city`)
SET c.`place_id` = p.`id`
WHERE TRIM(c.`city`) <> '';

-- --------------------------------------------------------
-- Index en foreign key toevoegen
-- --------------------------------------------------------

ALTER TABLE `smoelboek_colleagues`
  ADD KEY `idx_place_id` (`place_id`),
  ADD CONSTRAINT `fk_colleagues_place`
    FOREIGN KEY (`place_id`) REFERENCES `smoelboek_places` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE;

-- --------------------------------------------------------
-- Oud city-veld verwijderen
-- --------------------------------------------------------

ALTER TABLE `smoelboek_colleagues`
  DROP COLUMN `city`;
