-- CreateTable
CREATE TABLE `ShippingProvider` (
    `id` VARCHAR(191) NOT NULL,
    `providerName` VARCHAR(100) NOT NULL,
    `displayName` VARCHAR(150) NOT NULL,
    `isEnabled` BOOLEAN NOT NULL DEFAULT true,
    `environment` VARCHAR(191) NOT NULL DEFAULT 'sandbox',
    `apiEmail` VARCHAR(255) NULL,
    `apiPassword` TEXT NULL,
    `apiToken` TEXT NULL,
    `clientId` VARCHAR(255) NULL,
    `clientSecret` TEXT NULL,
    `apiKey` VARCHAR(255) NULL,
    `secretKey` TEXT NULL,
    `pickupLocation` VARCHAR(255) NULL,
    `warehouseName` VARCHAR(150) NULL,
    `warehouseAddress` TEXT NULL,
    `contactPerson` VARCHAR(150) NULL,
    `phone` VARCHAR(50) NULL,
    `gstNumber` VARCHAR(50) NULL,
    `pincode` VARCHAR(20) NULL,
    `callbackUrl` TEXT NULL,
    `webhookUrl` TEXT NULL,
    `trackingUrl` TEXT NULL,
    `priority` INTEGER NOT NULL DEFAULT 0,
    `defaultProvider` BOOLEAN NOT NULL DEFAULT false,
    `sortOrder` INTEGER NOT NULL DEFAULT 0,
    `encryptedCredentials` JSON NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `ShippingProvider_providerName_key`(`providerName`),
    INDEX `ShippingProvider_isEnabled_idx`(`isEnabled`),
    INDEX `ShippingProvider_sortOrder_idx`(`sortOrder`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ShippingOrder` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `providerId` VARCHAR(191) NOT NULL,
    `shipmentId` VARCHAR(100) NULL,
    `awbNumber` VARCHAR(100) NULL,
    `courierName` VARCHAR(150) NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'PENDING',
    `estimatedDelivery` DATETIME(3) NULL,
    `shippingCharges` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `weight` DECIMAL(8, 2) NOT NULL DEFAULT 0.50,
    `dimensionsLength` INTEGER NOT NULL DEFAULT 10,
    `dimensionsWidth` INTEGER NOT NULL DEFAULT 10,
    `dimensionsHeight` INTEGER NOT NULL DEFAULT 10,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `ShippingOrder_orderId_key`(`orderId`),
    UNIQUE INDEX `ShippingOrder_shipmentId_key`(`shipmentId`),
    UNIQUE INDEX `ShippingOrder_awbNumber_key`(`awbNumber`),
    INDEX `ShippingOrder_providerId_idx`(`providerId`),
    INDEX `ShippingOrder_status_idx`(`status`),
    INDEX `ShippingOrder_awbNumber_idx`(`awbNumber`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ShipmentTracking` (
    `id` VARCHAR(191) NOT NULL,
    `shippingOrderId` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL,
    `location` VARCHAR(255) NULL,
    `eventDetails` TEXT NULL,
    `eventTime` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `ShipmentTracking_shippingOrderId_idx`(`shippingOrderId`),
    INDEX `ShipmentTracking_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ShipmentWebhookLog` (
    `id` VARCHAR(191) NOT NULL,
    `provider` VARCHAR(100) NOT NULL,
    `awbNumber` VARCHAR(100) NULL,
    `payload` JSON NOT NULL,
    `processedStatus` VARCHAR(191) NOT NULL DEFAULT 'PENDING',
    `errorMessage` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `ShipmentWebhookLog_provider_idx`(`provider`),
    INDEX `ShipmentWebhookLog_awbNumber_idx`(`awbNumber`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `PickupRequest` (
    `id` VARCHAR(191) NOT NULL,
    `shippingOrderId` VARCHAR(191) NOT NULL,
    `pickupToken` VARCHAR(150) NULL,
    `pickupTime` DATETIME(3) NOT NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'SCHEDULED',
    `history` JSON NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `PickupRequest_shippingOrderId_idx`(`shippingOrderId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ShippingAudit` (
    `id` VARCHAR(191) NOT NULL,
    `providerId` VARCHAR(191) NULL,
    `action` VARCHAR(191) NOT NULL,
    `performedBy` VARCHAR(191) NOT NULL,
    `details` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `ShippingAudit_providerId_idx`(`providerId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ShippingLabel` (
    `id` VARCHAR(191) NOT NULL,
    `shippingOrderId` VARCHAR(191) NOT NULL,
    `labelUrl` TEXT NOT NULL,
    `format` VARCHAR(191) NOT NULL DEFAULT 'PDF',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `ShippingLabel_shippingOrderId_idx`(`shippingOrderId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ShippingInvoice` (
    `id` VARCHAR(191) NOT NULL,
    `shippingOrderId` VARCHAR(191) NOT NULL,
    `invoiceUrl` TEXT NOT NULL,
    `invoiceType` VARCHAR(191) NOT NULL DEFAULT 'COMMERCIAL',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `ShippingInvoice_shippingOrderId_idx`(`shippingOrderId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Warehouse` (
    `id` VARCHAR(191) NOT NULL,
    `warehouseName` VARCHAR(150) NOT NULL,
    `street` TEXT NOT NULL,
    `city` VARCHAR(100) NOT NULL,
    `state` VARCHAR(100) NOT NULL,
    `pincode` VARCHAR(20) NOT NULL,
    `contactPerson` VARCHAR(150) NOT NULL,
    `phone` VARCHAR(50) NOT NULL,
    `gstNumber` VARCHAR(50) NULL,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Warehouse_warehouseName_key`(`warehouseName`),
    INDEX `Warehouse_isActive_idx`(`isActive`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `ShippingOrder` ADD CONSTRAINT `ShippingOrder_orderId_fkey` FOREIGN KEY (`orderId`) REFERENCES `Order`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ShippingOrder` ADD CONSTRAINT `ShippingOrder_providerId_fkey` FOREIGN KEY (`providerId`) REFERENCES `ShippingProvider`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ShipmentTracking` ADD CONSTRAINT `ShipmentTracking_shippingOrderId_fkey` FOREIGN KEY (`shippingOrderId`) REFERENCES `ShippingOrder`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `PickupRequest` ADD CONSTRAINT `PickupRequest_shippingOrderId_fkey` FOREIGN KEY (`shippingOrderId`) REFERENCES `ShippingOrder`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ShippingAudit` ADD CONSTRAINT `ShippingAudit_providerId_fkey` FOREIGN KEY (`providerId`) REFERENCES `ShippingProvider`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ShippingLabel` ADD CONSTRAINT `ShippingLabel_shippingOrderId_fkey` FOREIGN KEY (`shippingOrderId`) REFERENCES `ShippingOrder`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ShippingInvoice` ADD CONSTRAINT `ShippingInvoice_shippingOrderId_fkey` FOREIGN KEY (`shippingOrderId`) REFERENCES `ShippingOrder`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;