-- CreateTable
CREATE TABLE `coupons` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `description` VARCHAR(500) NULL,
    `couponType` ENUM('FLAT', 'PERCENTAGE') NOT NULL,
    `discountValue` DECIMAL(12, 2) NOT NULL,
    `maximumDiscount` DECIMAL(12, 2) NULL,
    `minimumFare` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `totalUsageLimit` INTEGER NULL,
    `perUserUsageLimit` INTEGER NOT NULL DEFAULT 1,
    `usedCount` INTEGER NOT NULL DEFAULT 0,
    `validFrom` DATETIME(3) NOT NULL,
    `validUntil` DATETIME(3) NOT NULL,
    `status` ENUM('ACTIVE', 'INACTIVE', 'EXPIRED') NOT NULL DEFAULT 'ACTIVE',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `coupons_publicId_key`(`publicId`),
    UNIQUE INDEX `coupons_code_key`(`code`),
    INDEX `coupons_status_validFrom_validUntil_idx`(`status`, `validFrom`, `validUntil`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `coupon_redemptions` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `couponId` BIGINT NOT NULL,
    `userId` BIGINT NOT NULL,
    `bookingId` BIGINT NOT NULL,
    `discountAmount` DECIMAL(12, 2) NOT NULL,
    `redeemedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `coupon_redemptions_publicId_key`(`publicId`),
    INDEX `coupon_redemptions_userId_redeemedAt_idx`(`userId`, `redeemedAt`),
    INDEX `coupon_redemptions_couponId_userId_idx`(`couponId`, `userId`),
    UNIQUE INDEX `coupon_redemptions_couponId_bookingId_key`(`couponId`, `bookingId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ratings` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `bookingId` BIGINT NOT NULL,
    `tripId` BIGINT NULL,
    `givenByUserId` BIGINT NOT NULL,
    `receivedByUserId` BIGINT NOT NULL,
    `score` INTEGER NOT NULL,
    `comment` VARCHAR(1000) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `ratings_publicId_key`(`publicId`),
    INDEX `ratings_receivedByUserId_createdAt_idx`(`receivedByUserId`, `createdAt`),
    INDEX `ratings_tripId_idx`(`tripId`),
    UNIQUE INDEX `ratings_bookingId_givenByUserId_receivedByUserId_key`(`bookingId`, `givenByUserId`, `receivedByUserId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `support_tickets` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `ticketCode` VARCHAR(50) NOT NULL,
    `requesterUserId` BIGINT NOT NULL,
    `bookingId` BIGINT NULL,
    `subject` VARCHAR(200) NOT NULL,
    `description` TEXT NOT NULL,
    `status` ENUM('OPEN', 'IN_PROGRESS', 'WAITING_CUSTOMER', 'RESOLVED', 'CLOSED') NOT NULL DEFAULT 'OPEN',
    `priority` ENUM('LOW', 'NORMAL', 'HIGH', 'URGENT') NOT NULL DEFAULT 'NORMAL',
    `assignedToUserId` BIGINT NULL,
    `resolvedAt` DATETIME(3) NULL,
    `closedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `support_tickets_publicId_key`(`publicId`),
    UNIQUE INDEX `support_tickets_ticketCode_key`(`ticketCode`),
    INDEX `support_tickets_requesterUserId_createdAt_idx`(`requesterUserId`, `createdAt`),
    INDEX `support_tickets_bookingId_idx`(`bookingId`),
    INDEX `support_tickets_status_priority_createdAt_idx`(`status`, `priority`, `createdAt`),
    INDEX `support_tickets_assignedToUserId_idx`(`assignedToUserId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `support_messages` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `ticketId` BIGINT NOT NULL,
    `authorUserId` BIGINT NOT NULL,
    `message` TEXT NOT NULL,
    `isInternalNote` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `support_messages_publicId_key`(`publicId`),
    INDEX `support_messages_ticketId_createdAt_idx`(`ticketId`, `createdAt`),
    INDEX `support_messages_authorUserId_idx`(`authorUserId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `notifications` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `userId` BIGINT NOT NULL,
    `channel` ENUM('IN_APP', 'PUSH', 'SMS', 'EMAIL', 'WHATSAPP') NOT NULL,
    `status` ENUM('QUEUED', 'PROCESSING', 'SENT', 'DELIVERED', 'FAILED', 'CANCELLED') NOT NULL DEFAULT 'QUEUED',
    `templateCode` VARCHAR(100) NULL,
    `subject` VARCHAR(255) NULL,
    `body` TEXT NOT NULL,
    `destination` VARCHAR(255) NULL,
    `provider` VARCHAR(100) NULL,
    `providerMessageId` VARCHAR(191) NULL,
    `idempotencyKey` VARCHAR(191) NULL,
    `queuedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `sentAt` DATETIME(3) NULL,
    `deliveredAt` DATETIME(3) NULL,
    `failedAt` DATETIME(3) NULL,
    `failureMessage` VARCHAR(500) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `notifications_publicId_key`(`publicId`),
    UNIQUE INDEX `notifications_idempotencyKey_key`(`idempotencyKey`),
    INDEX `notifications_userId_createdAt_idx`(`userId`, `createdAt`),
    INDEX `notifications_status_queuedAt_idx`(`status`, `queuedAt`),
    INDEX `notifications_channel_status_idx`(`channel`, `status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `emergency_events` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `emergencyCode` VARCHAR(50) NOT NULL,
    `raisedByUserId` BIGINT NOT NULL,
    `bookingId` BIGINT NULL,
    `tripId` BIGINT NULL,
    `emergencyType` ENUM('SOS', 'ACCIDENT', 'MEDICAL', 'SAFETY', 'VEHICLE_BREAKDOWN', 'OTHER') NOT NULL,
    `status` ENUM('OPEN', 'ACKNOWLEDGED', 'RESPONDING', 'RESOLVED', 'CLOSED') NOT NULL DEFAULT 'OPEN',
    `latitude` DECIMAL(10, 7) NULL,
    `longitude` DECIMAL(10, 7) NULL,
    `description` VARCHAR(1000) NULL,
    `acknowledgedByUserId` BIGINT NULL,
    `acknowledgedAt` DATETIME(3) NULL,
    `resolvedAt` DATETIME(3) NULL,
    `resolutionNote` VARCHAR(1000) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `emergency_events_publicId_key`(`publicId`),
    UNIQUE INDEX `emergency_events_emergencyCode_key`(`emergencyCode`),
    INDEX `emergency_events_raisedByUserId_createdAt_idx`(`raisedByUserId`, `createdAt`),
    INDEX `emergency_events_bookingId_idx`(`bookingId`),
    INDEX `emergency_events_tripId_idx`(`tripId`),
    INDEX `emergency_events_status_emergencyType_createdAt_idx`(`status`, `emergencyType`, `createdAt`),
    INDEX `emergency_events_acknowledgedByUserId_idx`(`acknowledgedByUserId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `coupon_redemptions` ADD CONSTRAINT `coupon_redemptions_couponId_fkey` FOREIGN KEY (`couponId`) REFERENCES `coupons`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `coupon_redemptions` ADD CONSTRAINT `coupon_redemptions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

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

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

-- AddForeignKey
ALTER TABLE `ratings` ADD CONSTRAINT `ratings_givenByUserId_fkey` FOREIGN KEY (`givenByUserId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ratings` ADD CONSTRAINT `ratings_receivedByUserId_fkey` FOREIGN KEY (`receivedByUserId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `support_tickets` ADD CONSTRAINT `support_tickets_requesterUserId_fkey` FOREIGN KEY (`requesterUserId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `support_tickets` ADD CONSTRAINT `support_tickets_bookingId_fkey` FOREIGN KEY (`bookingId`) REFERENCES `bookings`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `support_messages` ADD CONSTRAINT `support_messages_ticketId_fkey` FOREIGN KEY (`ticketId`) REFERENCES `support_tickets`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `support_messages` ADD CONSTRAINT `support_messages_authorUserId_fkey` FOREIGN KEY (`authorUserId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `notifications` ADD CONSTRAINT `notifications_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `emergency_events` ADD CONSTRAINT `emergency_events_raisedByUserId_fkey` FOREIGN KEY (`raisedByUserId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `emergency_events` ADD CONSTRAINT `emergency_events_bookingId_fkey` FOREIGN KEY (`bookingId`) REFERENCES `bookings`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `emergency_events` ADD CONSTRAINT `emergency_events_tripId_fkey` FOREIGN KEY (`tripId`) REFERENCES `trips`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

