-- ============================================================
-- TaPrestation - Création tables manquantes + corrections
-- Date: 2026-02-07
-- À exécuter sur la BDD de production (tapresr689)
-- ============================================================

-- ============================================================
-- 1. TABLE: auction_bids
-- ============================================================
CREATE TABLE IF NOT EXISTS `auction_bids` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `service_id` BIGINT UNSIGNED NOT NULL,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `prestataire_id` BIGINT UNSIGNED NOT NULL,
    `bid_amount` DECIMAL(10, 2) NOT NULL,
    `currency` VARCHAR(3) NOT NULL DEFAULT 'EUR',
    `message` TEXT NULL,
    `status` ENUM('pending', 'accepted', 'rejected', 'expired') NOT NULL DEFAULT 'pending',
    `expires_at` TIMESTAMP NULL,
    `accepted_at` TIMESTAMP NULL,
    `rejected_at` TIMESTAMP NULL,
    `booking_id` BIGINT UNSIGNED NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    INDEX `auction_bids_service_id_status_index` (`service_id`, `status`),
    INDEX `auction_bids_client_id_status_index` (`client_id`, `status`),
    INDEX `auction_bids_expires_at_index` (`expires_at`),
    CONSTRAINT `auction_bids_service_id_foreign` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE,
    CONSTRAINT `auction_bids_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `auction_bids_prestataire_id_foreign` FOREIGN KEY (`prestataire_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `auction_bids_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- 2. TABLE: delivery_providers
-- ============================================================
CREATE TABLE IF NOT EXISTS `delivery_providers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `api_key` VARCHAR(255) NULL,
    `api_url` VARCHAR(255) NULL,
    `coverage_areas` JSON NULL,
    `base_rate` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    `per_km_rate` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    `delivery_type` ENUM('standard', 'express', 'overnight') NULL,
    `estimated_days` INT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `features` JSON NULL,
    `contact_info` JSON NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    UNIQUE KEY `delivery_providers_name_unique` (`name`),
    UNIQUE KEY `delivery_providers_code_unique` (`code`),
    INDEX `delivery_providers_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- 3. TABLE: delivery_orders (schéma complet aligné avec le Model)
-- ============================================================
CREATE TABLE IF NOT EXISTS `delivery_orders` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `booking_id` BIGINT UNSIGNED NULL,
    `delivery_provider_id` BIGINT UNSIGNED NULL,
    `driver_id` BIGINT UNSIGNED NULL,
    `zone_id` BIGINT UNSIGNED NULL,
    `tracking_number` VARCHAR(255) NOT NULL,
    `reference_number` VARCHAR(255) NULL,
    `status` ENUM('pending', 'confirmed', 'preparing', 'ready_for_pickup', 'driver_assigned', 'picked_up', 'in_transit', 'out_for_delivery', 'delivered', 'failed', 'returned', 'cancelled') NOT NULL DEFAULT 'pending',
    `priority` ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
    `shipping_type` ENUM('standard', 'express', 'same_day', 'overnight') NOT NULL DEFAULT 'standard',
    `weight` DECIMAL(10, 3) NULL,
    `dimensions` JSON NULL,
    `package_count` INT NOT NULL DEFAULT 1,
    `fragile` TINYINT(1) NOT NULL DEFAULT 0,
    `requires_signature` TINYINT(1) NOT NULL DEFAULT 0,
    `shipping_cost` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    `insurance_cost` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    `total_cost` DECIMAL(10, 2) NOT NULL DEFAULT 0,
    -- Pickup info
    `pickup_address` VARCHAR(255) NOT NULL,
    `pickup_city` VARCHAR(255) NULL,
    `pickup_postal_code` VARCHAR(255) NULL,
    `pickup_contact_name` VARCHAR(255) NULL,
    `pickup_contact_phone` VARCHAR(255) NULL,
    `pickup_instructions` TEXT NULL,
    `pickup_lat` DECIMAL(10, 8) NULL,
    `pickup_lng` DECIMAL(11, 8) NULL,
    -- Delivery info
    `delivery_address` VARCHAR(255) NOT NULL,
    `delivery_city` VARCHAR(255) NULL,
    `delivery_postal_code` VARCHAR(255) NULL,
    `delivery_contact_name` VARCHAR(255) NULL,
    `delivery_contact_phone` VARCHAR(255) NULL,
    `delivery_instructions` TEXT NULL,
    `delivery_lat` DECIMAL(10, 8) NULL,
    `delivery_lng` DECIMAL(11, 8) NULL,
    -- Scheduling
    `scheduled_pickup_at` TIMESTAMP NULL,
    `picked_up_at` TIMESTAMP NULL,
    `scheduled_delivery_at` TIMESTAMP NULL,
    `estimated_delivery` TIMESTAMP NULL,
    `delivered_at` TIMESTAMP NULL,
    -- Delivery attempts
    `delivery_attempts` INT NOT NULL DEFAULT 0,
    `max_delivery_attempts` INT NOT NULL DEFAULT 3,
    `last_attempt_at` TIMESTAMP NULL,
    `next_attempt_at` TIMESTAMP NULL,
    `failure_reason` TEXT NULL,
    -- Proof of delivery
    `signature_image` VARCHAR(255) NULL,
    `delivery_photo` VARCHAR(255) NULL,
    `recipient_name` VARCHAR(255) NULL,
    -- Notes & tracking
    `tracking_history` JSON NULL,
    `notes` TEXT NULL,
    `internal_notes` TEXT NULL,
    -- Rating
    `customer_rating` TINYINT UNSIGNED NULL,
    `customer_feedback` TEXT NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    UNIQUE KEY `delivery_orders_tracking_number_unique` (`tracking_number`),
    INDEX `delivery_orders_tracking_status_index` (`tracking_number`, `status`),
    INDEX `delivery_orders_booking_id_index` (`booking_id`),
    INDEX `delivery_orders_driver_id_index` (`driver_id`),
    INDEX `delivery_orders_status_index` (`status`),
    INDEX `delivery_orders_scheduled_delivery_at_index` (`scheduled_delivery_at`),
    CONSTRAINT `delivery_orders_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE,
    CONSTRAINT `delivery_orders_delivery_provider_id_foreign` FOREIGN KEY (`delivery_provider_id`) REFERENCES `delivery_providers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `delivery_orders_driver_id_foreign` FOREIGN KEY (`driver_id`) REFERENCES `delivery_drivers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `delivery_orders_zone_id_foreign` FOREIGN KEY (`zone_id`) REFERENCES `delivery_zones` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- 4. TABLE: address_books
-- ============================================================
CREATE TABLE IF NOT EXISTS `address_books` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `label` VARCHAR(255) NULL,
    `recipient_name` VARCHAR(255) NOT NULL,
    `street` VARCHAR(255) NOT NULL,
    `city` VARCHAR(255) NOT NULL,
    `postal_code` VARCHAR(255) NOT NULL,
    `country` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(255) NOT NULL,
    `latitude` DECIMAL(10, 8) NULL,
    `longitude` DECIMAL(11, 8) NULL,
    `tags` JSON NULL,
    `is_default` TINYINT(1) NOT NULL DEFAULT 0,
    `address_type` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    INDEX `address_books_user_id_is_default_index` (`user_id`, `is_default`),
    INDEX `address_books_created_at_index` (`created_at`),
    CONSTRAINT `address_books_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- 5. TABLE: advertisements
-- ============================================================
CREATE TABLE IF NOT EXISTS `advertisements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `advertiser_id` BIGINT UNSIGNED NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `image_url` VARCHAR(255) NULL,
    `target_url` VARCHAR(255) NULL,
    `category` VARCHAR(255) NULL,
    `status` ENUM('pending', 'approved', 'rejected', 'paused', 'expired') NOT NULL DEFAULT 'pending',
    `approved_at` TIMESTAMP NULL,
    `starts_at` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `daily_budget` INT NULL,
    `total_budget` INT NULL,
    `spent_amount` INT NOT NULL DEFAULT 0,
    `impression_count` INT NOT NULL DEFAULT 0,
    `click_count` INT NOT NULL DEFAULT 0,
    `conversion_count` INT NOT NULL DEFAULT 0,
    `targeting` JSON NULL,
    `metadata` JSON NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    INDEX `advertisements_status_expires_at_index` (`status`, `expires_at`),
    INDEX `advertisements_advertiser_id_index` (`advertiser_id`),
    CONSTRAINT `advertisements_advertiser_id_foreign` FOREIGN KEY (`advertiser_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- 6. TABLE: payment_consents
-- ============================================================
CREATE TABLE IF NOT EXISTS `payment_consents` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `consentable_type` VARCHAR(255) NOT NULL,
    `consentable_id` BIGINT UNSIGNED NOT NULL,
    `consent_type` VARCHAR(255) NOT NULL DEFAULT 'payment_terms',
    `version` VARCHAR(20) NOT NULL DEFAULT 'v1.0',
    `terms_hash` TEXT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT NULL,
    `metadata` JSON NULL,
    `consented_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    INDEX `payment_consents_consentable_index` (`consentable_type`, `consentable_id`),
    INDEX `payment_consents_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- 7. COLONNES buyer_consent sur les tables existantes
-- ============================================================

-- Bookings
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bookings' AND COLUMN_NAME = 'buyer_consent_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `bookings` ADD COLUMN `buyer_consent_at` TIMESTAMP NULL, ADD COLUMN `buyer_consent_ip` VARCHAR(45) NULL, ADD COLUMN `buyer_consent_user_agent` TEXT NULL, ADD COLUMN `buyer_consent_version` VARCHAR(20) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Equipment rental requests
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'equipment_rental_requests' AND COLUMN_NAME = 'buyer_consent_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `equipment_rental_requests` ADD COLUMN `buyer_consent_at` TIMESTAMP NULL, ADD COLUMN `buyer_consent_ip` VARCHAR(45) NULL, ADD COLUMN `buyer_consent_user_agent` TEXT NULL, ADD COLUMN `buyer_consent_version` VARCHAR(20) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Urgent sale purchases
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'urgent_sale_purchases' AND COLUMN_NAME = 'buyer_consent_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `urgent_sale_purchases` ADD COLUMN `buyer_consent_at` TIMESTAMP NULL, ADD COLUMN `buyer_consent_ip` VARCHAR(45) NULL, ADD COLUMN `buyer_consent_user_agent` TEXT NULL, ADD COLUMN `buyer_consent_version` VARCHAR(20) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ============================================================
-- 8. COLONNES rating sur urgent_sale_reservations
-- ============================================================
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'urgent_sale_reservations' AND COLUMN_NAME = 'client_rating');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `urgent_sale_reservations` ADD COLUMN `client_rating` TINYINT UNSIGNED NULL AFTER `completed_at`, ADD COLUMN `client_rating_comment` TEXT NULL AFTER `client_rating`, ADD COLUMN `client_rated_at` TIMESTAMP NULL AFTER `client_rating_comment`, ADD COLUMN `seller_rating` TINYINT UNSIGNED NULL AFTER `client_rated_at`, ADD COLUMN `seller_rating_comment` TEXT NULL AFTER `seller_rating`, ADD COLUMN `seller_rated_at` TIMESTAMP NULL AFTER `seller_rating_comment`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ============================================================
-- 9. Enregistrer la migration consolidée
-- ============================================================
INSERT INTO `migrations` (`migration`, `batch`) VALUES 
('2026_02_07_100000_create_missing_tables_consolidated', (SELECT COALESCE(MAX(batch), 0) + 1 FROM (SELECT batch FROM `migrations`) as m));

-- Fin du script
SELECT 'OK: 6 tables créées, colonnes ajoutées, migration enregistrée.' AS result;
