-- ============================================================================
-- SYSTÈME ESCROW COMPLET - TAPRESTATION
-- À exécuter dans phpMyAdmin
-- Date: 11 janvier 2026
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. TABLE ESCROW_TRANSACTIONS (Transactions sécurisées)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `escrow_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID du client (acheteur)',
    `prestataire_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID du prestataire (vendeur)',
    `escrowable_type` VARCHAR(255) NOT NULL COMMENT 'Type: App\\Models\\UrgentSalePurchase, Booking, etc.',
    `escrowable_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID de l\'objet lié',
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Montant total bloqué',
    `deposit_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Montant caution (locations)',
    `platform_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Commission plateforme',
    `released_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Montant déjà libéré',
    `currency` VARCHAR(3) NOT NULL DEFAULT 'EUR',
    `status` ENUM('pending','held','partial','released','refunded','partially_refunded','disputed','dispute_review','cancelled') NOT NULL DEFAULT 'pending',
    `stripe_payment_intent_id` VARCHAR(255) NULL COMMENT 'ID PaymentIntent Stripe',
    `stripe_transfer_id` VARCHAR(255) NULL COMMENT 'ID Transfer Stripe (après libération)',
    `held_at` TIMESTAMP NULL COMMENT 'Date de blocage des fonds',
    `auto_release_at` TIMESTAMP NULL COMMENT 'Date de libération automatique',
    `released_at` TIMESTAMP NULL COMMENT 'Date de libération effective',
    `refunded_at` TIMESTAMP NULL COMMENT 'Date de remboursement',
    `client_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
    `client_confirmed_at` TIMESTAMP NULL,
    `prestataire_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
    `prestataire_confirmed_at` TIMESTAMP NULL,
    `notes` TEXT NULL,
    `metadata` JSON NULL COMMENT 'Données additionnelles (JSON)',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_escrow_client` (`client_id`),
    INDEX `idx_escrow_prestataire` (`prestataire_id`),
    INDEX `idx_escrow_status` (`status`),
    INDEX `idx_escrow_auto_release` (`auto_release_at`),
    INDEX `idx_escrow_escrowable` (`escrowable_type`, `escrowable_id`),
    INDEX `idx_escrow_stripe_pi` (`stripe_payment_intent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 2. TABLE ESCROW_DISPUTES (Litiges)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `escrow_disputes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `escrow_id` BIGINT UNSIGNED NOT NULL COMMENT 'ID de la transaction escrow',
    `opened_by` BIGINT UNSIGNED NOT NULL COMMENT 'User ID qui a ouvert le litige',
    `opened_by_role` ENUM('client','prestataire') NOT NULL DEFAULT 'client',
    `reason` ENUM('non_conformity','not_received','damaged','service_not_provided','quality_issue','other') NOT NULL,
    `description` TEXT NOT NULL,
    `evidence` JSON NULL COMMENT 'Chemins des fichiers preuves',
    `status` ENUM('open','under_review','resolved','closed','auto_split') NOT NULL DEFAULT 'open',
    `resolution` ENUM('refund_full','refund_partial','release_full','release_partial','split') NULL,
    `resolution_amount` DECIMAL(10,2) NULL COMMENT 'Montant de la résolution',
    `resolution_notes` TEXT NULL,
    `resolved_by` BIGINT UNSIGNED NULL COMMENT 'Admin qui a résolu',
    `resolved_at` TIMESTAMP NULL,
    `auto_split_at` TIMESTAMP NULL COMMENT 'Date du split automatique J+7',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_dispute_escrow` (`escrow_id`),
    INDEX `idx_dispute_status` (`status`),
    INDEX `idx_dispute_opened_by` (`opened_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 3. TABLE SHIPMENTS (Expéditions pour ventes urgentes)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `shipments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `escrow_id` BIGINT UNSIGNED NOT NULL,
    `tracking_number` VARCHAR(255) NULL,
    `carrier` VARCHAR(100) NULL COMMENT 'Transporteur: colissimo, mondial_relay, chronopost, etc.',
    `carrier_name` VARCHAR(255) NULL,
    `tracking_url` VARCHAR(500) NULL,
    `status` ENUM('pending','shipped','in_transit','delivered','returned','lost') NOT NULL DEFAULT 'pending',
    `shipped_at` TIMESTAMP NULL,
    `delivered_at` TIMESTAMP NULL,
    `notes` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_shipment_escrow` (`escrow_id`),
    INDEX `idx_shipment_tracking` (`tracking_number`),
    INDEX `idx_shipment_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 4. TABLE FINANCE_LEDGER (Journal financier)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `finance_ledger` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(50) NOT NULL COMMENT 'escrow_hold, escrow_release, escrow_refund, commission, payout, etc.',
    `reference_id` BIGINT UNSIGNED NULL COMMENT 'ID de référence (escrow_id, booking_id, etc.)',
    `reference_type` VARCHAR(100) NULL COMMENT 'Type de référence',
    `user_id` BIGINT UNSIGNED NULL,
    `prestataire_id` BIGINT UNSIGNED NULL,
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Positif = crédit, Négatif = débit',
    `balance_after` DECIMAL(10,2) NULL COMMENT 'Solde après opération',
    `currency` VARCHAR(3) NOT NULL DEFAULT 'EUR',
    `notes` TEXT NULL,
    `meta` JSON NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ledger_type` (`type`),
    INDEX `idx_ledger_user` (`user_id`),
    INDEX `idx_ledger_prestataire` (`prestataire_id`),
    INDEX `idx_ledger_reference` (`reference_type`, `reference_id`),
    INDEX `idx_ledger_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE `urgent_sale_purchases` ADD COLUMN `escrow_id` BIGINT UNSIGNED NULL AFTER `payment_transaction_id`, ADD INDEX `idx_usp_escrow` (`escrow_id`)',
    'SELECT "Column escrow_id already exists in urgent_sale_purchases"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql2 = IF(@balance_exists = 0, 
    'ALTER TABLE `prestataires` ADD COLUMN `balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT "Solde disponible"',
    'SELECT "Column balance already exists in prestataires"'
);

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- ----------------------------------------------------------------------------
-- 7. TABLE MUTUAL_RATINGS (Notes mutuelles client/prestataire)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mutual_ratings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `rater_user_id` BIGINT UNSIGNED NOT NULL COMMENT 'Celui qui note',
    `rated_user_id` BIGINT UNSIGNED NOT NULL COMMENT 'Celui qui est noté',
    `escrow_id` BIGINT UNSIGNED NULL,
    `ratable_type` VARCHAR(255) NULL COMMENT 'Type: Booking, UrgentSalePurchase, etc.',
    `ratable_id` BIGINT UNSIGNED NULL,
    `rating` TINYINT UNSIGNED NOT NULL COMMENT '1-5 étoiles',
    `comment` TEXT NULL,
    `would_recommend` TINYINT(1) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_rating` (`rater_user_id`, `ratable_type`, `ratable_id`),
    INDEX `idx_rating_rated` (`rated_user_id`),
    INDEX `idx_rating_escrow` (`escrow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 8. TABLE PAYMENT_CONSENTS (Consentements RGPD)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_consents` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `consentable_type` VARCHAR(255) NOT NULL,
    `consentable_id` BIGINT UNSIGNED NOT NULL,
    `terms_version` VARCHAR(20) NOT NULL DEFAULT 'v1.0',
    `consent_given_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_consent_user` (`user_id`),
    INDEX `idx_consent_consentable` (`consentable_type`, `consentable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- FIN DU SCRIPT
-- ----------------------------------------------------------------------------
SELECT 'Installation du système Escrow terminée !' AS message;
