-- Create room_booking_deposits table
CREATE TABLE `room_booking_deposits` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `lease_booking_id` bigint(20) UNSIGNED NOT NULL,
    `stock_session_id` bigint(20) UNSIGNED NOT NULL,
    `payment_mode_id` int(11) NOT NULL,
    `amount` decimal(15,2) NOT NULL,
    `description` text NULL,
    `status` enum('pending','allocated','refunded') DEFAULT 'pending',
    `created_by` bigint(20) UNSIGNED NOT NULL,
    `updated_by` bigint(20) UNSIGNED NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `room_booking_deposits_lease_booking_id_foreign` (`lease_booking_id`),
    KEY `room_booking_deposits_stock_session_id_foreign` (`stock_session_id`),
    KEY `room_booking_deposits_payment_mode_id_index` (`payment_mode_id`),
    KEY `room_booking_deposits_created_by_foreign` (`created_by`),
    KEY `room_booking_deposits_updated_by_foreign` (`updated_by`),
    KEY `room_booking_deposits_status_index` (`status`),
    CONSTRAINT `room_booking_deposits_lease_booking_id_foreign` FOREIGN KEY (`lease_booking_id`) REFERENCES `lease_bookings` (`id`) ON DELETE CASCADE,
    CONSTRAINT `room_booking_deposits_stock_session_id_foreign` FOREIGN KEY (`stock_session_id`) REFERENCES `stock_sessions` (`id`) ON DELETE CASCADE,
    CONSTRAINT `room_booking_deposits_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `room_booking_deposits_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 