-- ============================================
-- SQL pour créer les tables Ambassador
-- À importer directement dans phpMyAdmin
-- ============================================

-- Table principale des ambassadeurs
CREATE TABLE IF NOT EXISTS `ambassadors` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `referral_code` VARCHAR(32) NOT NULL,
    `status` ENUM('active', 'suspended', 'inactive') NOT NULL DEFAULT 'active',
    `phone` VARCHAR(20) NULL,
    `city` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `stripe_account_id` VARCHAR(255) NULL,
    `stripe_account_status` VARCHAR(50) NULL,
    `total_commission_earned` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `total_commission_paid` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ambassadors_user_id_unique` (`user_id`),
    UNIQUE KEY `ambassadors_referral_code_unique` (`referral_code`),
    CONSTRAINT `ambassadors_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Visites des liens de parrainage
CREATE TABLE IF NOT EXISTS `ambassador_referral_visits` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(500) NULL,
    `referer_url` VARCHAR(500) NULL,
    `converted` TINYINT(1) NOT NULL DEFAULT 0,
    `converted_prestataire_id` BIGINT UNSIGNED NULL,
    `visited_at` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`),
    KEY `ambassador_referral_visits_ambassador_id_index` (`ambassador_id`),
    CONSTRAINT `ambassador_referral_visits_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE,
    CONSTRAINT `ambassador_referral_visits_converted_prestataire_id_foreign` FOREIGN KEY (`converted_prestataire_id`) REFERENCES `prestataires` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assignations prestataire-ambassadeur
CREATE TABLE IF NOT EXISTS `prestataire_ambassador_assignments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `prestataire_id` BIGINT UNSIGNED NOT NULL,
    `source` ENUM('referral_link', 'manual_creation', 'admin_assigned') NOT NULL,
    `assigned_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `prestataire_ambassador_assignments_prestataire_id_unique` (`prestataire_id`),
    KEY `prestataire_ambassador_assignments_ambassador_id_index` (`ambassador_id`),
    CONSTRAINT `prestataire_ambassador_assignments_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE,
    CONSTRAINT `prestataire_ambassador_assignments_prestataire_id_foreign` FOREIGN KEY (`prestataire_id`) REFERENCES `prestataires` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Commissions des ambassadeurs
CREATE TABLE IF NOT EXISTS `ambassador_commissions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `prestataire_id` BIGINT UNSIGNED NOT NULL,
    `escrow_transaction_id` BIGINT UNSIGNED NULL,
    `booking_id` BIGINT UNSIGNED NULL,
    `order_type` VARCHAR(30) NOT NULL,
    `base_amount` DECIMAL(12, 2) NOT NULL,
    `commission_rate` DECIMAL(5, 2) NOT NULL,
    `commission_amount` DECIMAL(12, 2) NOT NULL,
    `status` ENUM('pending', 'approved', 'paid', 'cancelled') NOT NULL DEFAULT 'pending',
    `paid_at` TIMESTAMP NULL,
    `payout_batch_id` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ambassador_commissions_ambassador_id_index` (`ambassador_id`),
    KEY `ambassador_commissions_prestataire_id_index` (`prestataire_id`),
    KEY `ambassador_commissions_escrow_transaction_id_index` (`escrow_transaction_id`),
    KEY `ambassador_commissions_status_index` (`status`),
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lots de paiement des ambassadeurs
CREATE TABLE IF NOT EXISTS `ambassador_payout_batches` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `total_amount` DECIMAL(12, 2) NOT NULL,
    `stripe_transfer_id` VARCHAR(255) NULL,
    `status` ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    `processed_at` TIMESTAMP NULL,
    `notes` TEXT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ambassador_payout_batches_ambassador_id_index` (`ambassador_id`),
    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;

-- Ajouter la FK payout_batch_id sur ambassador_commissions
ALTER TABLE `ambassador_commissions`
    ADD CONSTRAINT `ambassador_commissions_payout_batch_id_foreign` FOREIGN KEY (`payout_batch_id`) REFERENCES `ambassador_payout_batches` (`id`) ON DELETE SET NULL;

-- Logs d'activité des ambassadeurs
CREATE TABLE IF NOT EXISTS `ambassador_activity_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ambassador_id` BIGINT UNSIGNED NOT NULL,
    `type` VARCHAR(50) NOT NULL,
    `description` VARCHAR(500) NOT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ambassador_activity_logs_ambassador_id_created_at_index` (`ambassador_id`, `created_at`),
    CONSTRAINT `ambassador_activity_logs_ambassador_id_foreign` FOREIGN KEY (`ambassador_id`) REFERENCES `ambassadors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
