-- Migration 016 — Types d'interface BDD
--
-- Sépare la notion de "type d'interface BDD" (phpMyAdmin OVH, Adminer,
-- phpMyAdmin générique, etc.) de la liste des providers d'hébergement.
--
-- Avant : sites.db_provider_id pointait vers providers (hébergeurs).
-- Après : sites.db_provider_id pointe vers db_providers (interfaces BDD).
--
-- Les valeurs existantes dans db_provider_id sont remises à NULL
-- car la sémantique change. À ressaisir depuis l'onglet Accès.

-- 1. Nouvelle table dédiée
CREATE TABLE IF NOT EXISTS db_providers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    website VARCHAR(255) NOT NULL COMMENT 'URL utilisée pour générer le favicon',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Reset des valeurs existantes (sémantique différente)
UPDATE sites SET db_provider_id = NULL WHERE db_provider_id IS NOT NULL;

-- 3. Drop l'ancienne FK si elle existe (idempotent)
SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'sites'
      AND CONSTRAINT_NAME = 'fk_sites_db_provider'
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @drop_sql := IF(@fk_exists > 0,
    'ALTER TABLE sites DROP FOREIGN KEY fk_sites_db_provider',
    'SELECT "FK fk_sites_db_provider absente, skip" AS info'
);
PREPARE stmt FROM @drop_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. Nouvelle FK vers db_providers (idempotent)
SET @new_fk_exists := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'sites'
      AND CONSTRAINT_NAME = 'fk_sites_db_provider_type'
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @add_sql := IF(@new_fk_exists = 0,
    'ALTER TABLE sites ADD CONSTRAINT fk_sites_db_provider_type FOREIGN KEY (db_provider_id) REFERENCES db_providers(id) ON DELETE SET NULL',
    'SELECT "FK fk_sites_db_provider_type déjà présente, skip" AS info'
);
PREPARE stmt FROM @add_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. Seed : quelques interfaces BDD courantes
INSERT INTO db_providers (name, slug, website) VALUES
    ('phpMyAdmin OVH', 'phpmyadmin-ovh', 'https://www.ovh.com'),
    ('phpMyAdmin', 'phpmyadmin', 'https://www.phpmyadmin.net'),
    ('Adminer', 'adminer', 'https://www.adminer.org'),
    ('cPanel phpMyAdmin', 'cpanel-phpmyadmin', 'https://www.cpanel.net')
ON DUPLICATE KEY UPDATE slug = slug;
