-- Migration 003 — clients enrichis, providers, abonnement
-- À exécuter UNE FOIS via phpMyAdmin > base 'monitoring' > onglet SQL.
-- Si vous voyez "Duplicate column name" ou "Table already exists",
-- c'est que la migration est déjà passée pour cette partie — ignorez.

-- 1. Enrichir la table clients
ALTER TABLE clients
    ADD COLUMN first_name VARCHAR(100) NULL AFTER name,
    ADD COLUMN last_name VARCHAR(100) NULL AFTER first_name,
    ADD COLUMN whatsapp_url VARCHAR(500) NULL AFTER notes;

-- 2. Table providers (hébergeurs et registrars)
CREATE TABLE IF NOT EXISTS providers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    website VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Seed initial des providers (idempotent grâce à ON DUPLICATE KEY UPDATE)
INSERT INTO providers (name, slug, website) VALUES
    ('OVH', 'ovh', 'https://www.ovhcloud.com'),
    ('Hostinger', 'hostinger', 'https://www.hostinger.com'),
    ('Infomaniak', 'infomaniak', 'https://www.infomaniak.com'),
    ('Kinsta', 'kinsta', 'https://kinsta.com'),
    ('o2switch', 'o2switch', 'https://www.o2switch.fr'),
    ('Ionos 1&1', 'ionos', 'https://www.ionos.fr'),
    ('Pressable', 'pressable', 'https://pressable.com'),
    ('WP Engine', 'wpengine', 'https://wpengine.com'),
    ('Gandi', 'gandi', 'https://www.gandi.net'),
    ('Cloudflare', 'cloudflare', 'https://www.cloudflare.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 4. Enrichir la table sites — hébergement
ALTER TABLE sites
    ADD COLUMN hosting_provider_id INT UNSIGNED NULL AFTER admin_url,
    ADD COLUMN hosting_admin_url VARCHAR(500) NULL AFTER hosting_provider_id,
    ADD COLUMN hosting_username VARCHAR(255) NULL AFTER hosting_admin_url,
    ADD COLUMN hosting_password_enc TEXT NULL AFTER hosting_username;

-- 5. Enrichir la table sites — domaine
ALTER TABLE sites
    ADD COLUMN domain_provider_id INT UNSIGNED NULL AFTER hosting_password_enc,
    ADD COLUMN domain_admin_url VARCHAR(500) NULL AFTER domain_provider_id,
    ADD COLUMN domain_username VARCHAR(255) NULL AFTER domain_admin_url,
    ADD COLUMN domain_password_enc TEXT NULL AFTER domain_username;

-- 6. Enrichir la table sites — abonnement
ALTER TABLE sites
    ADD COLUMN subscription_formula VARCHAR(100) NULL,
    ADD COLUMN subscription_cycle ENUM('monthly','yearly') NULL,
    ADD COLUMN subscription_amount_ht DECIMAL(10,2) NULL,
    ADD COLUMN subscription_started_at DATE NULL;

-- 7. Foreign keys (à séparer car les ALTER précédents doivent passer d'abord)
ALTER TABLE sites
    ADD CONSTRAINT fk_sites_hosting_provider FOREIGN KEY (hosting_provider_id) REFERENCES providers(id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_sites_domain_provider FOREIGN KEY (domain_provider_id) REFERENCES providers(id) ON DELETE SET NULL;
