-- Migration 006 — Synchronisation des abonnements WooCommerce Subscriptions
-- À exécuter UNE FOIS via phpMyAdmin > base 'monitoring' > onglet SQL.

-- 1) Sites marchands : flag pour désigner les sites Limbus qui poussent leurs abos vers la plateforme
ALTER TABLE sites
    ADD COLUMN is_subscription_source TINYINT(1) NOT NULL DEFAULT 0 AFTER url,
    ADD COLUMN subscription_id INT UNSIGNED NULL AFTER subscription_amount_ht;

-- 2) Table des abonnements (un par client/site monitoré)
CREATE TABLE IF NOT EXISTS subscriptions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_site_id INT UNSIGNED NOT NULL COMMENT 'Site marchand qui héberge cet abo',
    wc_subscription_id INT UNSIGNED NOT NULL COMMENT 'ID WooCommerce de l abonnement sur le site marchand',
    customer_email VARCHAR(255) NOT NULL,
    customer_name VARCHAR(255) NULL,
    product_name VARCHAR(255) NULL COMMENT 'Nom du produit abonnement (ex : Maintenance Premium)',
    status VARCHAR(40) NOT NULL DEFAULT 'pending' COMMENT 'active, on-hold, cancelled, expired, pending, pending-cancel',
    billing_period VARCHAR(20) NULL COMMENT 'month, year, week, day',
    billing_interval INT UNSIGNED NULL DEFAULT 1 COMMENT 'Tous les X periods (ex: tous les 3 mois = period=month, interval=3)',
    total_ht DECIMAL(10, 2) NULL,
    total_ttc DECIMAL(10, 2) NULL,
    currency VARCHAR(3) NULL DEFAULT 'EUR',
    start_date DATE NULL,
    next_payment_date DATETIME NULL,
    end_date DATETIME NULL,
    last_synced_at DATETIME NULL,
    UNIQUE KEY uniq_source_wc (source_site_id, wc_subscription_id),
    KEY idx_email (customer_email),
    KEY idx_status (status),
    CONSTRAINT fk_sub_source FOREIGN KEY (source_site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3) Table des commandes/transactions liées aux abonnements
CREATE TABLE IF NOT EXISTS subscription_orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subscription_id INT UNSIGNED NOT NULL,
    source_site_id INT UNSIGNED NOT NULL,
    wc_order_id INT UNSIGNED NOT NULL,
    order_number VARCHAR(40) NULL,
    order_date DATETIME NOT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'pending' COMMENT 'completed, processing, failed, refunded, cancelled, on-hold, pending',
    total_ht DECIMAL(10, 2) NULL,
    total_ttc DECIMAL(10, 2) NULL,
    currency VARCHAR(3) NULL DEFAULT 'EUR',
    payment_method VARCHAR(80) NULL,
    is_renewal TINYINT(1) NOT NULL DEFAULT 0,
    last_synced_at DATETIME NULL,
    UNIQUE KEY uniq_source_wc_order (source_site_id, wc_order_id),
    KEY idx_subscription (subscription_id),
    KEY idx_order_date (order_date),
    KEY idx_status (status),
    CONSTRAINT fk_subord_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
    CONSTRAINT fk_subord_source FOREIGN KEY (source_site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4) FK liant sites.subscription_id → subscriptions.id (ON DELETE SET NULL pour éviter de casser un site)
ALTER TABLE sites
    ADD CONSTRAINT fk_sites_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL;
