-- Migration 005 — URL de connexion des providers + zone DNS sur les sites
-- À exécuter UNE FOIS via phpMyAdmin > base 'monitoring' > onglet SQL.
-- "Duplicate column name" / "Duplicate key" sur certaines lignes = la migration a déjà partiellement passé, ignorer.

-- 1) Ajouter login_url aux providers
ALTER TABLE providers
    ADD COLUMN login_url VARCHAR(500) NULL AFTER website;

-- 2) Pré-remplir les login_url pour les providers seed
UPDATE providers SET login_url = 'https://www.ovhcloud.com/manager/' WHERE slug = 'ovh' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://hpanel.hostinger.com/' WHERE slug = 'hostinger' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://manager.infomaniak.com/' WHERE slug = 'infomaniak' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://my.kinsta.com/' WHERE slug = 'kinsta' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://www.o2switch.fr/cpanel/' WHERE slug = 'o2switch' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://login.ionos.fr/' WHERE slug = 'ionos' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://my.pressable.com/' WHERE slug = 'pressable' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://my.wpengine.com/' WHERE slug = 'wpengine' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://id.gandi.net/' WHERE slug = 'gandi' AND login_url IS NULL;
UPDATE providers SET login_url = 'https://dash.cloudflare.com/' WHERE slug = 'cloudflare' AND login_url IS NULL;

-- 3) Ajouter le bloc DNS aux sites
ALTER TABLE sites
    ADD COLUMN dns_provider_id INT UNSIGNED NULL AFTER domain_password_enc,
    ADD COLUMN dns_admin_url VARCHAR(500) NULL AFTER dns_provider_id,
    ADD COLUMN dns_username VARCHAR(255) NULL AFTER dns_admin_url,
    ADD COLUMN dns_password_enc TEXT NULL AFTER dns_username;

-- 4) Foreign key DNS
ALTER TABLE sites
    ADD CONSTRAINT fk_sites_dns_provider FOREIGN KEY (dns_provider_id) REFERENCES providers(id) ON DELETE SET NULL;
