-- =====================================================
-- MODULE AMBASSADEURS - Tables SQL pour phpMyAdmin
-- Base: taprestation
-- =====================================================

-- 1. Table principale ambassadeurs
CREATE TABLE `ambassadors` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `code` VARCHAR(20) NOT NULL,
    `public_slug` VARCHAR(100) NULL,
    `status` ENUM('draft','active','suspended','blocked','terminated') NOT NULL DEFAULT 'draft',
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `display_name` VARCHAR(200) NULL,
    `email` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(30) NULL,
    `company_name` VARCHAR(255) NULL,
    `siret` VARCHAR(20) NULL,
    `address` VARCHAR(500) NULL,
    `city` VARCHAR(100) NULL,
    `postal_code` VARCHAR(10) NULL,
    `country` VARCHAR(100) NOT NULL DEFAULT 'France',
    `notes` TEXT NULL,
    `created_by_admin_id` BIGINT UNSIGNED NULL,
    `zone_type` ENUM('national','region','department','city_radius') NOT NULL DEFAULT 'national',
    `zone_value` VARCHAR(255) NULL,
    `started_at` DATE NULL,
    `ended_at` DATE NULL,
    `can_create_prestataires` TINYINT(1) NOT NULL DEFAULT 1,
    `can_use_affiliate_link` TINYINT(1) NOT NULL DEFAULT 1,
    `last_login_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ambassadors_code_unique` (`code`),
    UNIQUE KEY `ambassadors_public_slug_unique` (`public_slug`),
    KEY `ambassadors_user_id_foreign` (`user_id`),
    KEY `ambassadors_created_by_admin_id_foreign` (`created_by_admin_id`),
    CONSTRAINT `ambassadors_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `ambassadors_created_by_admin_id_foreign` FOREIGN KEY (`created_by_admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Catégories autorisées par ambassadeur + taux de commission
CREATE TABLE `ambassador_categories` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `category_key` VARCHAR(50) NOT NULL,
    `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `commission_type` ENUM('full_platform_fee','split','fixed_rate','custom') NOT NULL DEFAULT 'split',
    `platform_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `ambassador_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `admin_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ambassador_categories_ambassador_id_category_key_unique` (`ambassador_id`, `category_key`),
    CONSTRAINT `ambassador_categories_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Liens affiliés
CREATE TABLE `ambassador_referral_links` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `code` VARCHAR(30) NOT NULL,
    `slug` VARCHAR(100) NULL,
    `target_type` VARCHAR(50) NOT NULL DEFAULT 'prestataire',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `clicks_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `registrations_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ambassador_referral_links_code_unique` (`code`),
    UNIQUE KEY `ambassador_referral_links_slug_unique` (`slug`),
    KEY `ambassador_referral_links_ambassador_id_foreign` (`ambassador_id`),
    CONSTRAINT `ambassador_referral_links_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Tracking des visites sur les liens affiliés
CREATE TABLE `ambassador_referral_visits` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `referral_link_id` BIGINT UNSIGNED NULL,
    `ip` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `utm_source` VARCHAR(100) NULL,
    `utm_medium` VARCHAR(100) NULL,
    `utm_campaign` VARCHAR(100) NULL,
    `visited_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `converted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ambassador_referral_visits_ambassador_id_foreign` (`ambassador_id`),
    KEY `ambassador_referral_visits_referral_link_id_foreign` (`referral_link_id`),
    CONSTRAINT `ambassador_referral_visits_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE,
    CONSTRAINT `ambassador_referral_visits_referral_link_id_foreign` FOREIGN KEY (`referral_link_id`) REFERENCES `ambassador_referral_links` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Rattachement prestataire <-> ambassadeur
CREATE TABLE `prestataire_ambassador_assignments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `prestataire_id` BIGINT UNSIGNED NOT NULL,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `assignment_source` ENUM('manual_admin','manual_ambassador','affiliate_link') NOT NULL DEFAULT 'manual_ambassador',
    `referral_link_id` BIGINT UNSIGNED NULL,
    `assigned_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `unassigned_at` TIMESTAMP NULL,
    `assigned_by_user_id` BIGINT UNSIGNED NULL,
    `status` ENUM('active','inactive','transferred') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `paa_prestataire_status_index` (`prestataire_id`, `status`),
    KEY `paa_ambassador_status_index` (`ambassador_id`, `status`),
    KEY `paa_referral_link_id_foreign` (`referral_link_id`),
    KEY `paa_assigned_by_user_id_foreign` (`assigned_by_user_id`),
    CONSTRAINT `paa_prestataire_id_foreign` FOREIGN KEY (`prestataire_id`) REFERENCES `prestataires` (`id`) ON DELETE CASCADE,
    CONSTRAINT `paa_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE,
    CONSTRAINT `paa_referral_link_id_foreign` FOREIGN KEY (`referral_link_id`) REFERENCES `ambassador_referral_links` (`id`) ON DELETE SET NULL,
    CONSTRAINT `paa_assigned_by_user_id_foreign` FOREIGN KEY (`assigned_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Lots de paiement ambassadeur (créée AVANT commissions pour la FK)
CREATE TABLE `ambassador_payout_batches` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `period_start` DATE NOT NULL,
    `period_end` DATE NOT NULL,
    `gross_amount` DECIMAL(10,2) NOT NULL,
    `adjustments_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `net_amount` DECIMAL(10,2) NOT NULL,
    `currency` VARCHAR(3) NOT NULL DEFAULT 'eur',
    `status` ENUM('draft','pending','processing','paid','failed','cancelled') NOT NULL DEFAULT 'draft',
    `stripe_payout_reference` VARCHAR(255) NULL,
    `paid_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ambassador_payout_batches_ambassador_status_index` (`ambassador_id`, `status`),
    CONSTRAINT `ambassador_payout_batches_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Commissions ambassadeur
CREATE TABLE `ambassador_commissions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `prestataire_id` BIGINT UNSIGNED NOT NULL,
    `order_type` VARCHAR(50) NOT NULL,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `order_item_id` BIGINT UNSIGNED NULL,
    `category_key` VARCHAR(50) NOT NULL,
    `gross_order_amount` DECIMAL(10,2) NOT NULL,
    `platform_fee_amount` DECIMAL(10,2) NOT NULL,
    `ambassador_commission_amount` DECIMAL(10,2) NOT NULL,
    `admin_commission_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `commission_rule_snapshot` JSON NULL,
    `status` ENUM('draft','pending','validated','payable','paid','cancelled','compensated') NOT NULL DEFAULT 'draft',
    `available_at` TIMESTAMP NULL,
    `paid_at` TIMESTAMP NULL,
    `stripe_transfer_id` VARCHAR(255) NULL,
    `payout_batch_id` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ambassador_commissions_ambassador_status_index` (`ambassador_id`, `status`),
    KEY `ambassador_commissions_prestataire_index` (`prestataire_id`),
    KEY `ambassador_commissions_order_index` (`order_type`, `order_id`),
    KEY `ambassador_commissions_payout_batch_id_foreign` (`payout_batch_id`),
    CONSTRAINT `ambassador_commissions_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE,
    CONSTRAINT `ambassador_commissions_prestataire_id_foreign` FOREIGN KEY (`prestataire_id`) REFERENCES `prestataires` (`id`) ON DELETE CASCADE,
    CONSTRAINT `ambassador_commissions_payout_batch_id_foreign` FOREIGN KEY (`payout_batch_id`) REFERENCES `ambassador_payout_batches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Comptes Stripe ambassadeur
CREATE TABLE `ambassador_stripe_accounts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `stripe_account_id` VARCHAR(255) NULL,
    `onboarding_completed` TINYINT(1) NOT NULL DEFAULT 0,
    `payouts_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `charges_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `requirements_json` JSON NULL,
    `last_synced_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ambassador_stripe_accounts_ambassador_id_unique` (`ambassador_id`),
    CONSTRAINT `ambassador_stripe_accounts_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Journal d'activité ambassadeur
CREATE TABLE `ambassador_activity_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `actor_user_id` BIGINT UNSIGNED NULL,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(100) NULL,
    `entity_id` BIGINT UNSIGNED NULL,
    `old_values_json` JSON NULL,
    `new_values_json` JSON NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ambassador_activity_logs_ambassador_id_foreign` (`ambassador_id`),
    KEY `ambassador_activity_logs_actor_user_id_foreign` (`actor_user_id`),
    CONSTRAINT `ambassador_activity_logs_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE,
    CONSTRAINT `ambassador_activity_logs_actor_user_id_foreign` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
