-- Limbus Monitoring — Schéma initial v0.1
-- Compatible MySQL 5.7+ / MariaDB 10.3+ (offre OVH mutualisé)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- USERS (admins de la plateforme)
-- =====================================================================
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    phone VARCHAR(50) NULL,
    role ENUM('admin','collaborator') NOT NULL DEFAULT 'collaborator',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- CLIENTS
-- =====================================================================
CREATE TABLE IF NOT EXISTS clients (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    email VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    company VARCHAR(255) NULL,
    notes TEXT NULL,
    whatsapp_url VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 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;

INSERT IGNORE 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');

-- =====================================================================
-- SITES (sites WordPress maintenus)
-- =====================================================================
CREATE TABLE IF NOT EXISTS sites (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NULL,
    name VARCHAR(255) NOT NULL,
    url VARCHAR(500) NOT NULL,
    favicon_data MEDIUMTEXT NULL,
    favicon_mime VARCHAR(50) NULL,
    favicon_updated_at TIMESTAMP NULL DEFAULT NULL,
    admin_url VARCHAR(500) NULL,
    hosting_provider_id INT UNSIGNED NULL,
    hosting_admin_url VARCHAR(500) NULL,
    hosting_username VARCHAR(255) NULL,
    hosting_password_enc TEXT NULL,
    domain_provider_id INT UNSIGNED NULL,
    domain_admin_url VARCHAR(500) NULL,
    domain_username VARCHAR(255) NULL,
    domain_password_enc TEXT NULL,
    subscription_formula VARCHAR(100) NULL,
    subscription_cycle ENUM('monthly','yearly') NULL,
    subscription_amount_ht DECIMAL(10,2) NULL,
    subscription_started_at DATE NULL,
    sync_interval_hours TINYINT UNSIGNED NOT NULL DEFAULT 24,
    api_key VARCHAR(64) NOT NULL UNIQUE,
    hmac_secret VARCHAR(128) NOT NULL,
    tier ENUM('free','essential','pro','agency') NOT NULL DEFAULT 'free',
    status ENUM('active','paused','disabled') NOT NULL DEFAULT 'active',
    last_sync_at TIMESTAMP NULL DEFAULT NULL,
    last_sync_status ENUM('ok','warning','critical','no_data') NOT NULL DEFAULT 'no_data',
    health_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    INDEX idx_status (status),
    INDEX idx_last_sync (last_sync_at),
    CONSTRAINT fk_sites_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    CONSTRAINT fk_sites_hosting_provider FOREIGN KEY (hosting_provider_id) REFERENCES providers(id) ON DELETE SET NULL,
    CONSTRAINT fk_sites_domain_provider FOREIGN KEY (domain_provider_id) REFERENCES providers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SITE_SNAPSHOTS (historique des relevés de santé)
-- =====================================================================
CREATE TABLE IF NOT EXISTS site_snapshots (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    wp_version VARCHAR(20) NULL,
    wp_update_available VARCHAR(20) NULL,
    php_version VARCHAR(20) NULL,
    db_version VARCHAR(50) NULL,
    server_software VARCHAR(255) NULL,
    active_theme_name VARCHAR(255) NULL,
    active_theme_version VARCHAR(50) NULL,
    plugins_total SMALLINT UNSIGNED NULL,
    plugins_active SMALLINT UNSIGNED NULL,
    plugins_updates SMALLINT UNSIGNED NULL,
    plugins_data JSON NULL,
    health_checks JSON NULL,
    ssl_valid TINYINT(1) NULL,
    ssl_expires_at DATE NULL,
    response_time_ms INT UNSIGNED NULL,
    health_score TINYINT UNSIGNED NULL,
    raw_payload JSON NULL,
    INDEX idx_site_date (site_id, captured_at),
    CONSTRAINT fk_snapshots_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- AUDIT_LOG (traçabilité des actions)
-- =====================================================================
CREATE TABLE IF NOT EXISTS audit_log (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NULL,
    entity_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
