-- CreateTable
CREATE TABLE `bookings` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `bookingCode` VARCHAR(40) NOT NULL,
    `customerUserId` BIGINT NOT NULL,
    `serviceZoneId` BIGINT NOT NULL,
    `vehicleCategoryId` BIGINT NOT NULL,
    `assignedDriverId` BIGINT NULL,
    `assignedVehicleId` BIGINT NULL,
    `bookingType` ENUM('INSTANT', 'SCHEDULED', 'RENTAL', 'OUTSTATION_ONE_WAY', 'OUTSTATION_ROUND_TRIP', 'AIRPORT', 'MULTI_STOP') NOT NULL DEFAULT 'INSTANT',
    `status` ENUM('REQUESTED', 'SEARCHING_DRIVER', 'DRIVER_ASSIGNED', 'DRIVER_EN_ROUTE', 'DRIVER_ARRIVED', 'OTP_VERIFIED', 'TRIP_STARTED', 'TRIP_COMPLETED', 'PAYMENT_PENDING', 'PAID', 'CLOSED', 'CANCELLED', 'NO_DRIVER_AVAILABLE', 'PAYMENT_FAILED') NOT NULL DEFAULT 'REQUESTED',
    `pickupAddress` VARCHAR(500) NOT NULL,
    `pickupLat` DECIMAL(10, 7) NOT NULL,
    `pickupLng` DECIMAL(10, 7) NOT NULL,
    `dropAddress` VARCHAR(500) NULL,
    `dropLat` DECIMAL(10, 7) NULL,
    `dropLng` DECIMAL(10, 7) NULL,
    `estimatedDistanceKm` DECIMAL(10, 2) NULL,
    `estimatedDurationMin` INTEGER NULL,
    `estimatedFare` DECIMAL(12, 2) NULL,
    `finalFare` DECIMAL(12, 2) NULL,
    `currency` CHAR(3) NOT NULL DEFAULT 'INR',
    `scheduledAt` DATETIME(3) NULL,
    `requestedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `driverAssignedAt` DATETIME(3) NULL,
    `driverArrivedAt` DATETIME(3) NULL,
    `cancelledAt` DATETIME(3) NULL,
    `completedAt` DATETIME(3) NULL,
    `cancellationReason` VARCHAR(500) NULL,
    `customerNote` VARCHAR(500) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `bookings_publicId_key`(`publicId`),
    UNIQUE INDEX `bookings_bookingCode_key`(`bookingCode`),
    INDEX `bookings_customerUserId_createdAt_idx`(`customerUserId`, `createdAt`),
    INDEX `bookings_status_requestedAt_idx`(`status`, `requestedAt`),
    INDEX `bookings_serviceZoneId_status_idx`(`serviceZoneId`, `status`),
    INDEX `bookings_vehicleCategoryId_status_idx`(`vehicleCategoryId`, `status`),
    INDEX `bookings_assignedDriverId_status_idx`(`assignedDriverId`, `status`),
    INDEX `bookings_scheduledAt_idx`(`scheduledAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `booking_stops` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `bookingId` BIGINT NOT NULL,
    `stopType` ENUM('PICKUP', 'WAYPOINT', 'DROP') NOT NULL,
    `sequenceNo` INTEGER NOT NULL,
    `address` VARCHAR(500) NOT NULL,
    `latitude` DECIMAL(10, 7) NOT NULL,
    `longitude` DECIMAL(10, 7) NOT NULL,
    `reachedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `booking_stops_publicId_key`(`publicId`),
    INDEX `booking_stops_bookingId_stopType_idx`(`bookingId`, `stopType`),
    UNIQUE INDEX `booking_stops_bookingId_sequenceNo_key`(`bookingId`, `sequenceNo`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `driver_offers` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `bookingId` BIGINT NOT NULL,
    `driverId` BIGINT NOT NULL,
    `status` ENUM('PENDING', 'ACCEPTED', 'REJECTED', 'EXPIRED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    `offeredAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `expiresAt` DATETIME(3) NOT NULL,
    `respondedAt` DATETIME(3) NULL,
    `distanceKm` DECIMAL(10, 2) NULL,
    `etaMinutes` INTEGER NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `driver_offers_publicId_key`(`publicId`),
    INDEX `driver_offers_driverId_status_expiresAt_idx`(`driverId`, `status`, `expiresAt`),
    INDEX `driver_offers_bookingId_status_idx`(`bookingId`, `status`),
    UNIQUE INDEX `driver_offers_bookingId_driverId_key`(`bookingId`, `driverId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `trips` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `tripCode` VARCHAR(40) NOT NULL,
    `bookingId` BIGINT NOT NULL,
    `driverId` BIGINT NOT NULL,
    `vehicleId` BIGINT NOT NULL,
    `status` ENUM('CREATED', 'DRIVER_EN_ROUTE', 'DRIVER_ARRIVED', 'OTP_VERIFIED', 'STARTED', 'COMPLETED', 'CANCELLED') NOT NULL DEFAULT 'CREATED',
    `startOtpHash` VARCHAR(255) NULL,
    `otpExpiresAt` DATETIME(3) NULL,
    `otpVerifiedAt` DATETIME(3) NULL,
    `startLat` DECIMAL(10, 7) NULL,
    `startLng` DECIMAL(10, 7) NULL,
    `endLat` DECIMAL(10, 7) NULL,
    `endLng` DECIMAL(10, 7) NULL,
    `actualDistanceKm` DECIMAL(10, 2) NULL,
    `actualDurationMin` INTEGER NULL,
    `startedAt` DATETIME(3) NULL,
    `completedAt` DATETIME(3) NULL,
    `cancelledAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `trips_publicId_key`(`publicId`),
    UNIQUE INDEX `trips_tripCode_key`(`tripCode`),
    UNIQUE INDEX `trips_bookingId_key`(`bookingId`),
    INDEX `trips_driverId_status_idx`(`driverId`, `status`),
    INDEX `trips_status_createdAt_idx`(`status`, `createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `location_events` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `driverId` BIGINT NOT NULL,
    `tripId` BIGINT NULL,
    `eventType` ENUM('DRIVER_ONLINE', 'DRIVER_OFFLINE', 'DRIVER_LOCATION', 'BOOKING_ASSIGNED', 'EN_ROUTE', 'ARRIVED', 'TRIP_STARTED', 'TRIP_LOCATION', 'TRIP_COMPLETED') NOT NULL,
    `latitude` DECIMAL(10, 7) NOT NULL,
    `longitude` DECIMAL(10, 7) NOT NULL,
    `heading` DECIMAL(6, 2) NULL,
    `speedKph` DECIMAL(8, 2) NULL,
    `accuracyM` DECIMAL(8, 2) NULL,
    `recordedAt` DATETIME(3) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `location_events_publicId_key`(`publicId`),
    INDEX `location_events_driverId_recordedAt_idx`(`driverId`, `recordedAt`),
    INDEX `location_events_tripId_recordedAt_idx`(`tripId`, `recordedAt`),
    INDEX `location_events_eventType_recordedAt_idx`(`eventType`, `recordedAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `bookings` ADD CONSTRAINT `bookings_customerUserId_fkey` FOREIGN KEY (`customerUserId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `bookings` ADD CONSTRAINT `bookings_serviceZoneId_fkey` FOREIGN KEY (`serviceZoneId`) REFERENCES `service_zones`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `bookings` ADD CONSTRAINT `bookings_vehicleCategoryId_fkey` FOREIGN KEY (`vehicleCategoryId`) REFERENCES `vehicle_categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `bookings` ADD CONSTRAINT `bookings_assignedDriverId_fkey` FOREIGN KEY (`assignedDriverId`) REFERENCES `drivers`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `bookings` ADD CONSTRAINT `bookings_assignedVehicleId_fkey` FOREIGN KEY (`assignedVehicleId`) REFERENCES `vehicles`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `booking_stops` ADD CONSTRAINT `booking_stops_bookingId_fkey` FOREIGN KEY (`bookingId`) REFERENCES `bookings`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `driver_offers` ADD CONSTRAINT `driver_offers_bookingId_fkey` FOREIGN KEY (`bookingId`) REFERENCES `bookings`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `driver_offers` ADD CONSTRAINT `driver_offers_driverId_fkey` FOREIGN KEY (`driverId`) REFERENCES `drivers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `trips` ADD CONSTRAINT `trips_bookingId_fkey` FOREIGN KEY (`bookingId`) REFERENCES `bookings`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `trips` ADD CONSTRAINT `trips_driverId_fkey` FOREIGN KEY (`driverId`) REFERENCES `drivers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `trips` ADD CONSTRAINT `trips_vehicleId_fkey` FOREIGN KEY (`vehicleId`) REFERENCES `vehicles`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `location_events` ADD CONSTRAINT `location_events_driverId_fkey` FOREIGN KEY (`driverId`) REFERENCES `drivers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `location_events` ADD CONSTRAINT `location_events_tripId_fkey` FOREIGN KEY (`tripId`) REFERENCES `trips`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

