-- =================================================================
-- SYSTÈME ESCROW & PROTECTION PAIEMENTS
-- Script complet pour phpMyAdmin - MySQL 5.7+ compatible
-- Date: 2 Janvier 2026
-- 
-- INSTRUCTIONS:
-- 1. Copiez TOUT ce fichier
-- 2. Collez dans phpMyAdmin > SQL
-- 3. Cliquez "Exécuter"
-- 4. Ignorez les erreurs "Duplicate column" (colonne existe déjà)
-- =================================================================

-- =============================================
-- PARTIE 1: COLONNES SUPPLEMENTAIRES
-- =============================================

-- Prestataires: mode de paiement
ALTER TABLE `prestataires` 
ADD COLUMN `payment_mode` ENUM('escrow', 'direct') NOT NULL DEFAULT 'escrow';

-- Equipment rental: statut caution
ALTER TABLE `equipment_rental_requests` 
ADD COLUMN `deposit_status` ENUM('pending', 'held', 'released', 'retained') DEFAULT 'pending';

-- Services: consentement
ALTER TABLE `services` ADD COLUMN `payment_consent_at` TIMESTAMP NULL;
ALTER TABLE `services` ADD COLUMN `payment_consent_ip` VARCHAR(45) NULL;
ALTER TABLE `services` ADD COLUMN `payment_consent_user_agent` TEXT NULL;

-- Equipment: consentement
ALTER TABLE `equipment` ADD COLUMN `payment_consent_at` TIMESTAMP NULL;
ALTER TABLE `equipment` ADD COLUMN `payment_consent_ip` VARCHAR(45) NULL;
ALTER TABLE `equipment` ADD COLUMN `payment_consent_user_agent` TEXT NULL;

-- Urgent sales: consentement
ALTER TABLE `urgent_sales` ADD COLUMN `payment_consent_at` TIMESTAMP NULL;
ALTER TABLE `urgent_sales` ADD COLUMN `payment_consent_ip` VARCHAR(45) NULL;
ALTER TABLE `urgent_sales` ADD COLUMN `payment_consent_user_agent` TEXT NULL;

-- =============================================
-- PARTIE 2: TABLE ESCROW_TRANSACTIONS
-- =============================================

CREATE TABLE IF NOT EXISTS `escrow_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `escrowable_type` VARCHAR(255) NOT NULL,
    `escrowable_id` BIGINT UNSIGNED NOT NULL,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `prestataire_id` BIGINT UNSIGNED NOT NULL,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `deposit_amount` DECIMAL(10,2) DEFAULT 0.00,
    `remaining_amount` DECIMAL(10,2) DEFAULT 0.00,
    `security_deposit` DECIMAL(10,2) DEFAULT 0.00,
    `commission_rate` DECIMAL(5,2) DEFAULT 20.00,
    `commission_amount` DECIMAL(10,2) DEFAULT 0.00,
    `prestataire_amount` DECIMAL(10,2) DEFAULT 0.00,
    `currency` VARCHAR(3) DEFAULT 'EUR',
    `status` ENUM('pending','held','partial','released','refunded','disputed','cancelled') NOT NULL DEFAULT 'pending',
    `stripe_payment_intent_id` VARCHAR(255) NULL,
    `stripe_transfer_id` VARCHAR(255) NULL,
    `stripe_refund_id` VARCHAR(255) NULL,
    `paid_at` TIMESTAMP NULL,
    `released_at` TIMESTAMP NULL,
    `refunded_at` TIMESTAMP NULL,
    `auto_release_at` TIMESTAMP NULL,
    `disputed_at` TIMESTAMP NULL,
    `client_confirmed_at` TIMESTAMP NULL,
    `prestataire_confirmed_at` TIMESTAMP NULL,
    `notes` TEXT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `escrow_escrowable_idx` (`escrowable_type`, `escrowable_id`),
    KEY `escrow_client_idx` (`client_id`),
    KEY `escrow_prestataire_idx` (`prestataire_id`),
    KEY `escrow_status_idx` (`status`),
    KEY `escrow_auto_release_idx` (`auto_release_at`),
    KEY `escrow_stripe_pi_idx` (`stripe_payment_intent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PARTIE 3: TABLE ESCROW_DISPUTES
-- =============================================

CREATE TABLE IF NOT EXISTS `escrow_disputes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `escrow_transaction_id` BIGINT UNSIGNED NOT NULL,
    `opened_by` ENUM('client', 'prestataire') NOT NULL,
    `opener_user_id` BIGINT UNSIGNED NOT NULL,
    `reason` ENUM('service_not_provided','quality_issue','late_delivery','item_damaged','item_not_returned','wrong_item','communication_issue','price_dispute','other') NOT NULL,
    `description` TEXT NOT NULL,
    `evidence` JSON NULL,
    `status` ENUM('open','under_review','resolved','escalated','closed') NOT NULL DEFAULT 'open',
    `resolution` ENUM('refund_full','refund_partial','release_full','release_partial','split','cancelled') NULL,
    `resolution_amount` DECIMAL(10,2) NULL,
    `resolution_notes` TEXT NULL,
    `resolved_by` BIGINT UNSIGNED NULL,
    `resolved_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `dispute_escrow_idx` (`escrow_transaction_id`),
    KEY `dispute_opener_idx` (`opener_user_id`),
    KEY `dispute_status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PARTIE 4: TABLE MUTUAL_RATINGS
-- =============================================

CREATE TABLE IF NOT EXISTS `mutual_ratings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `escrow_transaction_id` BIGINT UNSIGNED NOT NULL,
    `ratable_type` VARCHAR(255) NOT NULL,
    `ratable_id` BIGINT UNSIGNED NOT NULL,
    `rater_type` ENUM('client', 'prestataire') NOT NULL,
    `rater_user_id` BIGINT UNSIGNED NOT NULL,
    `rated_user_id` BIGINT UNSIGNED NOT NULL,
    `overall_rating` TINYINT UNSIGNED NOT NULL,
    `communication_rating` TINYINT UNSIGNED NULL,
    `punctuality_rating` TINYINT UNSIGNED NULL,
    `quality_rating` TINYINT UNSIGNED NULL,
    `value_rating` TINYINT UNSIGNED NULL,
    `comment` TEXT NULL,
    `is_public` TINYINT(1) DEFAULT 1,
    `response` TEXT NULL,
    `response_at` TIMESTAMP NULL,
    `is_approved` TINYINT(1) DEFAULT 1,
    `moderated_at` TIMESTAMP NULL,
    `moderated_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_rating` (`escrow_transaction_id`, `rater_user_id`),
    KEY `rating_escrow_idx` (`escrow_transaction_id`),
    KEY `rating_rater_idx` (`rater_user_id`),
    KEY `rating_rated_idx` (`rated_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PARTIE 5: TABLE SHIPMENTS
-- =============================================

CREATE TABLE IF NOT EXISTS `shipments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `shippable_type` VARCHAR(255) NOT NULL,
    `shippable_id` BIGINT UNSIGNED NOT NULL,
    `escrow_transaction_id` BIGINT UNSIGNED NULL,
    `type` ENUM('outbound', 'return') NOT NULL,
    `carrier` VARCHAR(100) NULL,
    `tracking_number` VARCHAR(255) NULL,
    `tracking_url` VARCHAR(500) NULL,
    `sender_name` VARCHAR(255) NULL,
    `sender_address` TEXT NULL,
    `sender_phone` VARCHAR(20) NULL,
    `recipient_name` VARCHAR(255) NULL,
    `recipient_address` TEXT NULL,
    `recipient_phone` VARCHAR(20) NULL,
    `status` ENUM('pending','shipped','in_transit','out_for_delivery','delivered','returned','failed') NOT NULL DEFAULT 'pending',
    `shipped_at` TIMESTAMP NULL,
    `estimated_delivery` DATE NULL,
    `delivered_at` TIMESTAMP NULL,
    `inspection_status` ENUM('pending', 'passed', 'failed', 'partial') NULL,
    `inspection_notes` TEXT NULL,
    `inspection_photos` JSON NULL,
    `inspected_at` TIMESTAMP NULL,
    `shipping_cost` DECIMAL(10,2) DEFAULT 0.00,
    `paid_by` ENUM('client', 'prestataire', 'platform') DEFAULT 'client',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `shipment_shippable_idx` (`shippable_type`, `shippable_id`),
    KEY `shipment_escrow_idx` (`escrow_transaction_id`),
    KEY `shipment_tracking_idx` (`tracking_number`),
    KEY `shipment_status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PARTIE 6: TABLE URGENT_SALE_PURCHASES
-- =============================================

CREATE TABLE IF NOT EXISTS `urgent_sale_purchases` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `urgent_sale_id` BIGINT UNSIGNED NOT NULL,
    `buyer_id` BIGINT UNSIGNED NOT NULL,
    `prestataire_id` BIGINT UNSIGNED NOT NULL,
    `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
    `unit_price` DECIMAL(10,2) NOT NULL,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `status` ENUM('pending','paid','shipped','delivered','completed','cancelled','refunded') NOT NULL DEFAULT 'pending',
    `delivery_method` ENUM('pickup', 'shipping', 'hand_delivery') NOT NULL DEFAULT 'pickup',
    `delivery_address` TEXT NULL,
    `delivery_notes` TEXT NULL,
    `payment_method` ENUM('online', 'cash', 'hand') NOT NULL DEFAULT 'online',
    `stripe_payment_intent_id` VARCHAR(255) NULL,
    `paid_at` TIMESTAMP NULL,
    `buyer_confirmed_at` TIMESTAMP NULL,
    `seller_confirmed_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `usp_urgent_sale_idx` (`urgent_sale_id`),
    KEY `usp_buyer_idx` (`buyer_id`),
    KEY `usp_prestataire_idx` (`prestataire_id`),
    KEY `usp_status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- FIN DU SCRIPT
-- =============================================
-- 
-- FONCTIONNEMENT:
-- 1. Client paie -> Argent bloque dans Stripe (status = held)
-- 2. Prestataire realise la prestation
-- 3. Client confirme OU 48h passent -> Argent libere
-- 4. En cas de probleme -> Ouverture d un litige
--
-- Commission: 20% prelevee lors de la liberation
-- =============================================
