-- LP System V2 migration 0001: V1 schema (58 tables) ported to MariaDB + V2 platform tables.
-- GENERATED by scripts/generate-migration.ts from prisma/schema. Do not edit by hand.
-- Every table states ENGINE, charset and collation explicitly; nothing relies on server or database defaults.
-- Every statement can be re-run safely (IF NOT EXISTS / INSERT IGNORE) so an interrupted migration always resumes.
-- CreateTable
CREATE TABLE IF NOT EXISTS `DocumentSequence` (
    `name` VARCHAR(64) NOT NULL,
    `value` INTEGER NOT NULL DEFAULT 0,
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `IdempotencyKey` (
    `key` VARCHAR(128) NOT NULL,
    `userId` VARCHAR(191) NULL,
    `scope` VARCHAR(64) NOT NULL,
    `requestHash` CHAR(64) NOT NULL,
    `response` JSON NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `IdempotencyKey_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `SystemMeta` (
    `key` VARCHAR(64) NOT NULL,
    `value` TEXT NOT NULL,
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `User` (
    `id` VARCHAR(191) NOT NULL,
    `name` TEXT NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `passwordHash` TEXT NULL,
    `role` ENUM('ADMIN', 'STAFF', 'PRODUCTION', 'CUSTOMER') NOT NULL,
    `phone` TEXT NULL,
    `title` TEXT NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `lastActiveAt` DATETIME(3) NULL,
    `mustChangePassword` BOOLEAN NOT NULL DEFAULT false,
    `sessionVersion` INTEGER NOT NULL DEFAULT 0,
    `failedLoginCount` INTEGER NOT NULL DEFAULT 0,
    `lockoutStage` INTEGER NOT NULL DEFAULT 0,
    `lockedUntil` DATETIME(3) NULL,
    `googleConnectedAt` DATETIME(3) NULL,
    `facebookConnectedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `User_email_key`(`email`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `StaffPermission` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `permission` ENUM('INQUIRY_VIEW', 'INQUIRY_HANDLE', 'INQUIRY_MODIFY', 'INQUIRY_CANCEL', 'QUOTATION_VIEW', 'QUOTATION_CREATE', 'QUOTATION_EDIT', 'QUOTATION_SEND', 'QUOTATION_HANDLE_MODIFICATION', 'QUOTATION_APPROVE_REJECT', 'QUOTATION_CANCEL', 'ORDER_VIEW', 'ORDER_CREATE', 'ORDER_MODIFY', 'ORDER_HANDLE_MODIFICATION', 'ORDER_UPDATE_STATUS', 'ORDER_CANCEL', 'ORDER_BACKDATE', 'PAYMENT_VIEW', 'PAYMENT_RECORD', 'PAYMENT_VERIFY', 'PAYMENT_REJECT', 'PAYMENT_EDIT', 'PAYMENT_REFUND', 'PAYMENT_BACKDATE', 'PRODUCTION_VIEW', 'PRODUCTION_UPDATE_STAGE', 'PRODUCTION_MARK_STAGE_COMPLETE', 'PRODUCTION_MARK_COMPLETE', 'DESIGN_VIEW', 'DESIGN_MANAGE', 'FULFILLMENT_VIEW', 'FULFILLMENT_SCHEDULE_PICKUP', 'FULFILLMENT_SCHEDULE_DELIVERY', 'FULFILLMENT_UPDATE_DELIVERY_STATUS', 'FULFILLMENT_MARK_DELIVERED', 'FULFILLMENT_MARK_INSTALLED', 'REWARDS_VIEW', 'REWARDS_PROCESS_REDEMPTION', 'REWARDS_MANAGE_CONFIG', 'COMMUNICATION_VIEW', 'COMMUNICATION_SEND', 'COMMUNICATION_MANAGE', 'COMMUNICATION_TRANSFER', 'COMMUNICATION_ASSIGN', 'COMMUNICATION_GROUP', 'COMMUNICATION_ATTACHMENT', 'COMMUNICATION_REFERENCE_TRANSACTION', 'COMMUNICATION_SEARCH_CUSTOMER', 'MESSENGER_DISPATCH', 'REPORTS_VIEW', 'REPORTS_EXPORT', 'EXPENSE_VIEW', 'EXPENSE_MANAGE', 'COST_VIEW', 'CUSTOMER_VIEW', 'CUSTOMER_CREATE', 'CUSTOMER_EDIT', 'CUSTOMER_ACTIVATE_DEACTIVATE', 'USER_VIEW', 'USER_CREATE', 'USER_EDIT', 'USER_ACTIVATE_DEACTIVATE', 'USER_MANAGE_PERMISSIONS', 'USER_RESET_PASSWORD', 'DOCUMENT_VIEW', 'DOCUMENT_SHARE', 'DOCUMENT_DOWNLOAD', 'DOCUMENT_REVOKE', 'ORDER_TRACKING_MANAGE', 'FORM_VIEW', 'FORM_CREATE', 'FORM_MANAGE_LINK', 'FORM_EDIT', 'FORM_REOPEN', 'FORM_ITEM_UNLOCK_OVERRIDE', 'SOA_VIEW', 'SOA_GENERATE', 'SOA_SHARE', 'SOA_REVOKE', 'EMAIL_LOG_VIEW', 'SERVICE_VIEW', 'SERVICE_MANAGE', 'SUPPLIER_VIEW', 'SUPPLIER_MANAGE', 'PURCHASE_MANAGE', 'INVENTORY_COST_VIEW') NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `StaffPermission_userId_permission_key`(`userId`, `permission`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `PasswordResetToken` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `token` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin NOT NULL,
    `expiresAt` DATETIME(3) NOT NULL,
    `usedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `PasswordResetToken_token_key`(`token`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Customer` (
    `id` VARCHAR(191) NOT NULL,
    `displayId` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NULL,
    `name` VARCHAR(191) NOT NULL,
    `companyName` TEXT NULL,
    `email` VARCHAR(191) NULL,
    `contactNumber` VARCHAR(191) NULL,
    `address` TEXT NULL,
    `facebookUrl` TEXT NULL,
    `profileImageUrl` TEXT NULL,
    `isQualifiedForTerms` BOOLEAN NOT NULL DEFAULT false,
    `rewardPointsBalance` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Customer_displayId_key`(`displayId`),
    UNIQUE INDEX `Customer_userId_key`(`userId`),
    INDEX `Customer_name_idx`(`name`),
    INDEX `Customer_email_idx`(`email`),
    INDEX `Customer_contactNumber_idx`(`contactNumber`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Inquiry` (
    `id` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `description` TEXT NOT NULL,
    `desiredProduct` TEXT NOT NULL,
    `serviceId` VARCHAR(191) NULL,
    `specs` JSON NULL,
    `roughQty` INTEGER NULL,
    `roughQtyUnit` TEXT NULL,
    `status` ENUM('NEW', 'QUOTED', 'CLOSED', 'CANCELLED') NOT NULL DEFAULT 'NEW',
    `cancelledAt` DATETIME(3) NULL,
    `cancelledById` VARCHAR(191) NULL,
    `cancelReason` TEXT NULL,
    `statusBeforeCancel` ENUM('NEW', 'QUOTED', 'CLOSED', 'CANCELLED') NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Quotation` (
    `id` VARCHAR(191) NOT NULL,
    `inquiryId` VARCHAR(191) NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `quoteNumber` VARCHAR(191) NOT NULL,
    `status` ENUM('DRAFT', 'SENT', 'APPROVED', 'REJECTED', 'REVISION_REQUESTED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    `validUntil` DATETIME(3) NULL,
    `total` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `subtotal` DECIMAL(12, 2) NULL,
    `discountType` ENUM('PERCENTAGE', 'FIXED') NOT NULL DEFAULT 'PERCENTAGE',
    `discountValue` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `discountAmount` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `discountLabel` TEXT NULL,
    `taxPct` DECIMAL(5, 2) NOT NULL DEFAULT 0,
    `taxAmount` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `isInstant` BOOLEAN NOT NULL DEFAULT false,
    `rejectReason` TEXT NULL,
    `notes` TEXT NULL,
    `createdById` VARCHAR(191) NULL,
    `cancelledById` VARCHAR(191) NULL,
    `cancelReason` TEXT NULL,
    `cancelledAt` DATETIME(3) NULL,
    `statusBeforeCancel` ENUM('DRAFT', 'SENT', 'APPROVED', 'REJECTED', 'REVISION_REQUESTED', 'CANCELLED') NULL,
    `approvedByStaffId` VARCHAR(191) NULL,
    `approvalBypassReason` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Quotation_quoteNumber_key`(`quoteNumber`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `QuotationRevisionRequest` (
    `id` VARCHAR(191) NOT NULL,
    `quotationId` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `message` TEXT NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `QuotationLineItem` (
    `id` VARCHAR(191) NOT NULL,
    `quotationId` VARCHAR(191) NOT NULL,
    `productType` TEXT NOT NULL,
    `serviceId` VARCHAR(191) NULL,
    `specs` JSON NULL,
    `description` TEXT NOT NULL,
    `qty` INTEGER NOT NULL,
    `unit` TEXT NULL,
    `unitPrice` DECIMAL(12, 2) NOT NULL,
    `notes` TEXT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `OrderLineItem` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `productType` TEXT NOT NULL,
    `serviceId` VARCHAR(191) NULL,
    `specs` JSON NULL,
    `description` TEXT NOT NULL,
    `qty` INTEGER NOT NULL,
    `unit` TEXT NULL,
    `unitPrice` DECIMAL(12, 2) NOT NULL,
    `notes` TEXT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Order` (
    `id` VARCHAR(191) NOT NULL,
    `quotationId` VARCHAR(191) NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `orderNumber` VARCHAR(191) NOT NULL,
    `status` ENUM('OPEN', 'IN_PRODUCTION', 'FULFILLING', 'COMPLETED', 'CANCELLED') NOT NULL DEFAULT 'OPEN',
    `paymentTermType` ENUM('STANDARD_PARTIAL', 'APPROVED_TERMS') NOT NULL DEFAULT 'STANDARD_PARTIAL',
    `termsApprovedBy` TEXT NULL,
    `termsReason` TEXT NULL,
    `requiredPartialPct` INTEGER NOT NULL DEFAULT 50,
    `totalAmount` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `subtotal` DECIMAL(12, 2) NULL,
    `discountType` ENUM('PERCENTAGE', 'FIXED') NOT NULL DEFAULT 'PERCENTAGE',
    `discountValue` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `discountAmount` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `discountLabel` TEXT NULL,
    `taxPct` DECIMAL(5, 2) NOT NULL DEFAULT 0,
    `taxAmount` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `releaseException` BOOLEAN NOT NULL DEFAULT false,
    `releaseExceptionBy` TEXT NULL,
    `releaseExceptionReason` TEXT NULL,
    `dueDate` DATETIME(3) NULL,
    `notes` TEXT NULL,
    `isHistorical` BOOLEAN NOT NULL DEFAULT false,
    `historicalOrderType` ENUM('PENDING_PRODUCTION', 'ALREADY_RELEASED') NULL,
    `historicalNotes` TEXT NULL,
    `cancelledAt` DATETIME(3) NULL,
    `cancelledById` VARCHAR(191) NULL,
    `cancelReason` TEXT NULL,
    `statusBeforeCancel` ENUM('OPEN', 'IN_PRODUCTION', 'FULFILLING', 'COMPLETED', 'CANCELLED') NULL,
    `completedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `orderDate` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `estimatedProductionCostSnapshot` DECIMAL(12, 2) NULL,
    `costSnapshotFullyConfigured` BOOLEAN NOT NULL DEFAULT false,
    `costSnapshotTakenAt` DATETIME(3) NULL,

    UNIQUE INDEX `Order_orderNumber_key`(`orderNumber`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `WorkflowTemplate` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `WorkflowTemplate_name_key`(`name`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Service` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `description` TEXT NULL,
    `category` TEXT NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `workflowTemplateId` VARCHAR(191) NULL,
    `specFields` JSON NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `pricingMethod` ENUM('NONE', 'PER_PIECE', 'FIXED', 'PER_SET', 'PER_AREA') NOT NULL DEFAULT 'NONE',
    `basePrice` DECIMAL(12, 2) NULL,
    `minQuantity` INTEGER NULL,
    `instantQuoteEnabled` BOOLEAN NOT NULL DEFAULT false,
    `productionCost` DECIMAL(12, 2) NULL,
    `targetMarginPct` DECIMAL(5, 2) NULL,

    UNIQUE INDEX `Service_name_key`(`name`),
    INDEX `Service_active_idx`(`active`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `ServiceBOMMaterial` (
    `id` VARCHAR(191) NOT NULL,
    `serviceId` VARCHAR(191) NOT NULL,
    `inventoryItemId` VARCHAR(191) NOT NULL,
    `consumptionPerUnit` DECIMAL(12, 4) NOT NULL,
    `wastePercent` DECIMAL(5, 2) NULL,
    `sortOrder` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `ServiceBOMMaterial_serviceId_idx`(`serviceId`),
    INDEX `ServiceBOMMaterial_inventoryItemId_idx`(`inventoryItemId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `ServiceCostComponent` (
    `id` VARCHAR(191) NOT NULL,
    `serviceId` VARCHAR(191) NOT NULL,
    `category` ENUM('LABOR', 'MACHINE', 'FINISHING', 'OTHER') NOT NULL,
    `label` TEXT NOT NULL,
    `basis` ENUM('PER_UNIT', 'PER_HOUR', 'FLAT') NOT NULL,
    `rate` DECIMAL(12, 4) NOT NULL,
    `estimatedHours` DECIMAL(8, 2) NULL,
    `sortOrder` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `ServiceCostComponent_serviceId_idx`(`serviceId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Pricelist` (
    `id` VARCHAR(191) NOT NULL,
    `serviceId` VARCHAR(191) NOT NULL,
    `minQty` INTEGER NOT NULL,
    `maxQty` INTEGER NULL,
    `pricePerUnit` DECIMAL(12, 2) NULL,
    `discountPercent` DECIMAL(5, 2) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `Pricelist_serviceId_idx`(`serviceId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Promotion` (
    `id` VARCHAR(191) NOT NULL,
    `name` TEXT NOT NULL,
    `serviceId` VARCHAR(191) NULL,
    `startDate` DATETIME(3) NULL,
    `endDate` DATETIME(3) NULL,
    `minQty` INTEGER NULL,
    `maxQty` INTEGER NULL,
    `percentDiscount` DECIMAL(5, 2) NULL,
    `fixedDiscount` DECIMAL(12, 2) NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `Promotion_serviceId_idx`(`serviceId`),
    INDEX `Promotion_active_idx`(`active`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `WorkflowStage` (
    `id` VARCHAR(191) NOT NULL,
    `templateId` VARCHAR(191) NOT NULL,
    `name` TEXT NOT NULL,
    `order` INTEGER NOT NULL,
    `isQCStage` BOOLEAN NOT NULL DEFAULT false,
    `isInstallStage` BOOLEAN NOT NULL DEFAULT false,
    `isDesignStage` BOOLEAN NOT NULL DEFAULT false,

    UNIQUE INDEX `WorkflowStage_templateId_order_key`(`templateId`, `order`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `JobOrder` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `joNumber` VARCHAR(191) NOT NULL,
    `productType` TEXT NOT NULL,
    `serviceId` VARCHAR(191) NULL,
    `specs` JSON NULL,
    `description` TEXT NOT NULL,
    `quantity` INTEGER NOT NULL,
    `workflowTemplateId` VARCHAR(191) NOT NULL,
    `currentStageOrder` INTEGER NOT NULL DEFAULT 1,
    `status` ENUM('ON_HOLD', 'IN_PROGRESS', 'QC', 'REWORK', 'READY', 'RELEASED', 'COMPLETED') NOT NULL DEFAULT 'ON_HOLD',
    `priority` ENUM('LOW', 'MEDIUM', 'HIGH') NOT NULL DEFAULT 'MEDIUM',
    `deadline` DATETIME(3) NULL,
    `productionInstructions` TEXT NULL,
    `completedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `JobOrder_orderId_joNumber_key`(`orderId`, `joNumber`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `JobOrderStageLog` (
    `id` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NOT NULL,
    `stageName` TEXT NOT NULL,
    `stageOrder` INTEGER NOT NULL,
    `isDesignStage` BOOLEAN NOT NULL DEFAULT false,
    `status` ENUM('READY', 'IN_PROGRESS', 'COMPLETED') NOT NULL DEFAULT 'READY',
    `assignedToId` VARCHAR(191) NULL,
    `startedAt` DATETIME(3) NULL,
    `completedAt` DATETIME(3) NULL,
    `notes` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `File` (
    `id` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NULL,
    `uploadedById` VARCHAR(191) NOT NULL,
    `category` ENUM('CUSTOMER_FILE', 'DESIGN_DRAFT', 'APPROVED_DESIGN', 'PRODUCTION_FILE', 'QC_EVIDENCE', 'PAYMENT_PROOF', 'DELIVERY_PROOF') NOT NULL,
    `version` INTEGER NOT NULL DEFAULT 1,
    `isApproved` BOOLEAN NOT NULL DEFAULT false,
    `filename` TEXT NOT NULL,
    `path` TEXT NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Payment` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `method` ENUM('CASH', 'BANK_TRANSFER', 'GCASH', 'MAYA', 'CHEQUE', 'VOUCHER', 'OTHER', 'FUND_TRANSFER', 'E_WALLET', 'QR_PAYMENT', 'DEBIT_CARD', 'CREDIT_CARD', 'APPLE_PAY') NOT NULL,
    `proofFilePath` TEXT NULL,
    `voucherId` VARCHAR(191) NULL,
    `status` ENUM('PENDING', 'CONFIRMED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
    `referenceNumber` TEXT NULL,
    `paymentDate` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `recordedById` VARCHAR(191) NOT NULL,
    `notes` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `isHistorical` BOOLEAN NOT NULL DEFAULT false,

    UNIQUE INDEX `Payment_voucherId_key`(`voucherId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `QCResult` (
    `id` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NOT NULL,
    `stageName` TEXT NOT NULL,
    `inspectorId` VARCHAR(191) NOT NULL,
    `result` ENUM('PASS', 'FAIL') NOT NULL,
    `quantityChecked` INTEGER NOT NULL,
    `quantityFailed` INTEGER NOT NULL DEFAULT 0,
    `defectNotes` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `ReworkRecord` (
    `id` VARCHAR(191) NOT NULL,
    `qcResultId` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NOT NULL,
    `defectDescription` TEXT NOT NULL,
    `quantityAffected` INTEGER NOT NULL,
    `assignedStage` TEXT NOT NULL,
    `assignedToId` VARCHAR(191) NULL,
    `status` ENUM('OPEN', 'IN_PROGRESS', 'DONE') NOT NULL DEFAULT 'OPEN',
    `reworkQcResultId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `ReworkRecord_qcResultId_key`(`qcResultId`),
    UNIQUE INDEX `ReworkRecord_reworkQcResultId_key`(`reworkQcResultId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `InventoryItem` (
    `id` VARCHAR(191) NOT NULL,
    `sku` VARCHAR(191) NOT NULL,
    `name` TEXT NOT NULL,
    `unit` TEXT NOT NULL,
    `reorderThreshold` INTEGER NOT NULL DEFAULT 0,
    `currentQty` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `InventoryItem_sku_key`(`sku`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Supplier` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `contactPerson` TEXT NULL,
    `phone` TEXT NULL,
    `email` TEXT NULL,
    `address` TEXT NULL,
    `taxId` TEXT NULL,
    `paymentTerms` TEXT NULL,
    `notes` TEXT NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Supplier_name_key`(`name`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `SupplyLot` (
    `id` VARCHAR(191) NOT NULL,
    `inventoryItemId` VARCHAR(191) NOT NULL,
    `lotCode` VARCHAR(191) NOT NULL,
    `receivedDate` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `receivedQty` INTEGER NOT NULL,
    `remainingQty` INTEGER NOT NULL,
    `supplierName` TEXT NULL,
    `supplierId` VARCHAR(191) NULL,
    `unitCost` DECIMAL(12, 4) NULL,
    `invoiceNumber` TEXT NULL,
    `paymentMethod` ENUM('CASH', 'BANK_TRANSFER', 'GCASH', 'MAYA', 'CHEQUE', 'VOUCHER', 'OTHER', 'FUND_TRANSFER', 'E_WALLET', 'QR_PAYMENT', 'DEBIT_CARD', 'CREDIT_CARD', 'APPLE_PAY') NULL,
    `referenceNumber` TEXT NULL,
    `notes` TEXT NULL,
    `cancelledAt` DATETIME(3) NULL,
    `cancelledById` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `SupplyLot_lotCode_key`(`lotCode`),
    INDEX `SupplyLot_supplierId_idx`(`supplierId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `InventoryMovement` (
    `id` VARCHAR(191) NOT NULL,
    `supplyLotId` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NULL,
    `type` ENUM('RECEIVE', 'ALLOCATE', 'CONSUME', 'REJECT', 'WASTE', 'ADJUST', 'CANCEL', 'CONSUME_REVERSAL') NOT NULL,
    `qty` INTEGER NOT NULL,
    `createdById` VARCHAR(191) NOT NULL,
    `notes` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `consumptionRecordId` VARCHAR(191) NULL,

    INDEX `InventoryMovement_consumptionRecordId_idx`(`consumptionRecordId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `JobOrderMaterialConsumption` (
    `id` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NOT NULL,
    `inventoryItemId` VARCHAR(191) NOT NULL,
    `expectedQty` DECIMAL(12, 4) NULL,
    `actualQty` DECIMAL(12, 4) NOT NULL,
    `unitCostSnapshot` DECIMAL(12, 4) NULL,
    `totalCostSnapshot` DECIMAL(12, 2) NULL,
    `varianceReason` TEXT NULL,
    `notes` TEXT NULL,
    `reversedAt` DATETIME(3) NULL,
    `reversedById` VARCHAR(191) NULL,
    `createdById` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `JobOrderMaterialConsumption_jobOrderId_idx`(`jobOrderId`),
    INDEX `JobOrderMaterialConsumption_inventoryItemId_idx`(`inventoryItemId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Fulfillment` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NULL,
    `method` ENUM('PICKUP', 'DELIVERY', 'INSTALLATION') NOT NULL,
    `status` ENUM('SCHEDULED', 'BOOKED', 'IN_TRANSIT', 'DELIVERED', 'INSTALLED', 'RECEIVED', 'CANCELLED') NOT NULL DEFAULT 'SCHEDULED',
    `scheduledDate` DATETIME(3) NULL,
    `trackingNumber` TEXT NULL,
    `courier` TEXT NULL,
    `proofFilePath` TEXT NULL,
    `completedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `RewardRule` (
    `id` VARCHAR(191) NOT NULL,
    `name` TEXT NOT NULL,
    `pointsPerCurrencyUnit` DECIMAL(8, 4) NOT NULL DEFAULT 0,
    `currencyUnit` DECIMAL(12, 2) NOT NULL DEFAULT 100,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `RewardTransaction` (
    `id` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NULL,
    `points` INTEGER NOT NULL,
    `type` ENUM('EARN', 'REDEEM') NOT NULL,
    `description` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `RedemptionTier` (
    `id` VARCHAR(191) NOT NULL,
    `pointsCost` INTEGER NOT NULL,
    `voucherValue` INTEGER NOT NULL,
    `minimumSpend` INTEGER NOT NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Voucher` (
    `id` VARCHAR(191) NOT NULL,
    `code` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `tierId` VARCHAR(191) NULL,
    `value` INTEGER NOT NULL,
    `minimumSpend` INTEGER NOT NULL,
    `status` ENUM('AVAILABLE', 'USED') NOT NULL DEFAULT 'AVAILABLE',
    `rewardTransactionId` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `Voucher_code_key`(`code`),
    UNIQUE INDEX `Voucher_rewardTransactionId_key`(`rewardTransactionId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Conversation` (
    `id` VARCHAR(191) NOT NULL,
    `type` ENUM('CUSTOMER', 'CUSTOMER_GROUP', 'PRIVATE', 'GROUP') NOT NULL DEFAULT 'CUSTOMER',
    `title` TEXT NULL,
    `customerId` VARCHAR(191) NULL,
    `subjectType` ENUM('INQUIRY', 'QUOTATION', 'ORDER', 'JOB_ORDER', 'GENERAL') NULL,
    `inquiryId` VARCHAR(191) NULL,
    `quotationId` VARCHAR(191) NULL,
    `orderId` VARCHAR(191) NULL,
    `jobOrderId` VARCHAR(191) NULL,
    `assignedStaffId` VARCHAR(191) NULL,
    `assignedAt` DATETIME(3) NULL,
    `lastCustomerMessageAt` DATETIME(3) NULL,
    `lastReminderSentAt` DATETIME(3) NULL,
    `createdById` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `ConversationParticipant` (
    `id` VARCHAR(191) NOT NULL,
    `conversationId` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `addedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `ConversationParticipant_conversationId_userId_key`(`conversationId`, `userId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `ConversationRead` (
    `id` VARCHAR(191) NOT NULL,
    `conversationId` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `lastReadAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `ConversationRead_conversationId_userId_key`(`conversationId`, `userId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Message` (
    `id` VARCHAR(191) NOT NULL,
    `conversationId` VARCHAR(191) NOT NULL,
    `senderId` VARCHAR(191) NOT NULL,
    `type` ENUM('TEXT', 'SYSTEM') NOT NULL DEFAULT 'TEXT',
    `body` TEXT NOT NULL DEFAULT '',
    `attachmentPath` TEXT NULL,
    `attachmentName` TEXT NULL,
    `attachmentMime` TEXT NULL,
    `attachmentSize` INTEGER NULL,
    `refType` ENUM('INQUIRY', 'QUOTATION', 'JOB_ORDER') NULL,
    `refInquiryId` VARCHAR(191) NULL,
    `refQuotationId` VARCHAR(191) NULL,
    `refJobOrderId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `Notification` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `type` TEXT NOT NULL,
    `message` TEXT NOT NULL,
    `link` TEXT NULL,
    `read` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `AuditLog` (
    `id` VARCHAR(191) NOT NULL,
    `actorId` VARCHAR(191) NULL,
    `action` TEXT NOT NULL,
    `entityType` VARCHAR(191) NOT NULL,
    `entityId` VARCHAR(191) NOT NULL,
    `changes` JSON NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `requestId` VARCHAR(64) NULL,
    `ip` VARCHAR(45) NULL,
    `userAgent` TEXT NULL,
    `actorRole` VARCHAR(16) NULL,

    INDEX `AuditLog_entityType_entityId_idx`(`entityType`, `entityId`),
    INDEX `AuditLog_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `BusinessSettings` (
    `id` VARCHAR(191) NOT NULL DEFAULT 'default',
    `businessName` TEXT NOT NULL DEFAULT 'LP Printing',
    `tagline` TEXT NULL,
    `description` TEXT NULL,
    `logoPath` TEXT NULL,
    `faviconPath` TEXT NULL,
    `contactNumber` TEXT NULL,
    `email` TEXT NULL,
    `facebookUrl` TEXT NULL,
    `website` TEXT NULL,
    `addressLine` TEXT NULL,
    `city` TEXT NULL,
    `province` TEXT NULL,
    `postalCode` TEXT NULL,
    `assignmentMode` ENUM('MANUAL', 'AUTOMATIC', 'MANUAL_WITH_AUTO_FALLBACK') NOT NULL DEFAULT 'MANUAL',
    `autoAssignGraphicArtist` BOOLEAN NOT NULL DEFAULT false,
    `paymentInstructions` TEXT NULL,
    `emailEnabled` BOOLEAN NOT NULL DEFAULT false,
    `emailProvider` ENUM('GMAIL', 'YAHOO', 'OUTLOOK', 'CUSTOM_SMTP') NULL,
    `emailSenderName` TEXT NULL,
    `emailSenderAddress` TEXT NULL,
    `emailSmtpHost` TEXT NULL,
    `emailSmtpPort` INTEGER NULL,
    `emailSmtpSecure` BOOLEAN NOT NULL DEFAULT true,
    `emailSmtpUsername` TEXT NULL,
    `emailSmtpPasswordEnc` TEXT NULL,
    `emailEventSettings` JSON NOT NULL,
    `emailLastTestAt` DATETIME(3) NULL,
    `emailLastTestOk` BOOLEAN NULL,
    `messengerEnabled` BOOLEAN NOT NULL DEFAULT false,
    `messengerPageId` TEXT NULL,
    `messengerPageAccessTokenEnc` TEXT NULL,
    `messengerVerifyToken` TEXT NULL,
    `messengerAppSecretEnc` TEXT NULL,
    `messengerEventSettings` JSON NOT NULL,
    `googleClientId` TEXT NULL,
    `googleClientSecretEnc` TEXT NULL,
    `facebookClientId` TEXT NULL,
    `facebookClientSecretEnc` TEXT NULL,
    `activeTheme` TEXT NOT NULL DEFAULT '2026',
    `themeColorOverrides` JSON NOT NULL,
    `themeFontFamily` TEXT NOT NULL DEFAULT 'montserrat',
    `timezone` TEXT NOT NULL DEFAULT 'Asia/Manila',
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `OrderTrackingLink` (
    `id` VARCHAR(191) NOT NULL,
    `token` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `createdById` VARCHAR(191) NOT NULL,
    `expiresAt` DATETIME(3) NULL,
    `revokedAt` DATETIME(3) NULL,
    `viewCount` INTEGER NOT NULL DEFAULT 0,
    `lastViewedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `OrderTrackingLink_token_key`(`token`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `CustomerForm` (
    `id` VARCHAR(191) NOT NULL,
    `jobOrderId` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `formType` TEXT NOT NULL,
    `title` TEXT NOT NULL,
    `instructions` TEXT NULL,
    `status` ENUM('OPEN', 'SUBMITTED') NOT NULL DEFAULT 'OPEN',
    `deadline` DATETIME(3) NULL,
    `notes` TEXT NULL,
    `submittedAt` DATETIME(3) NULL,
    `lastReopenedAt` DATETIME(3) NULL,
    `lastReopenedById` VARCHAR(191) NULL,
    `lastReopenReason` TEXT NULL,
    `createdById` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `CustomerForm_jobOrderId_key`(`jobOrderId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `CustomerFormItem` (
    `id` VARCHAR(191) NOT NULL,
    `formId` VARCHAR(191) NOT NULL,
    `sortOrder` INTEGER NOT NULL,
    `name` TEXT NOT NULL,
    `specs` JSON NULL,
    `notes` TEXT NULL,
    `qty` INTEGER NOT NULL,
    `printed` BOOLEAN NOT NULL DEFAULT false,
    `printedAt` DATETIME(3) NULL,
    `printedById` VARCHAR(191) NULL,
    `qcChecked` BOOLEAN NOT NULL DEFAULT false,
    `qcCheckedAt` DATETIME(3) NULL,
    `qcCheckedById` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `CustomerFormLink` (
    `id` VARCHAR(191) NOT NULL,
    `token` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin NOT NULL,
    `formId` VARCHAR(191) NOT NULL,
    `createdById` VARCHAR(191) NOT NULL,
    `expiresAt` DATETIME(3) NULL,
    `revokedAt` DATETIME(3) NULL,
    `viewCount` INTEGER NOT NULL DEFAULT 0,
    `lastViewedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `CustomerFormLink_token_key`(`token`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `CustomerFormDelivery` (
    `id` VARCHAR(191) NOT NULL,
    `formId` VARCHAR(191) NOT NULL,
    `method` ENUM('EMAIL', 'MESSENGER', 'DIRECT_LINK', 'SMS') NOT NULL,
    `recipient` TEXT NOT NULL,
    `deliveredById` VARCHAR(191) NULL,
    `status` ENUM('SENT', 'DELIVERED', 'COPIED', 'PENDING', 'FAILED') NOT NULL,
    `detail` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `CustomerFormOrder` (
    `id` VARCHAR(191) NOT NULL,
    `formId` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `addedById` VARCHAR(191) NOT NULL,
    `note` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `DocumentShareLink` (
    `id` VARCHAR(191) NOT NULL,
    `token` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin NOT NULL,
    `quotationId` VARCHAR(191) NULL,
    `orderId` VARCHAR(191) NULL,
    `jobOrderId` VARCHAR(191) NULL,
    `statementId` VARCHAR(191) NULL,
    `accessLevel` ENUM('VIEW_ONLY', 'VIEW_DOWNLOAD') NOT NULL DEFAULT 'VIEW_ONLY',
    `createdById` VARCHAR(191) NOT NULL,
    `expiresAt` DATETIME(3) NULL,
    `revokedAt` DATETIME(3) NULL,
    `viewCount` INTEGER NOT NULL DEFAULT 0,
    `lastViewedAt` DATETIME(3) NULL,
    `downloadCount` INTEGER NOT NULL DEFAULT 0,
    `lastDownloadedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `DocumentShareLink_token_key`(`token`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `ExpenseCategory` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `description` TEXT NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `ExpenseCategory_name_key`(`name`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `OperatingExpense` (
    `id` VARCHAR(191) NOT NULL,
    `expenseNumber` VARCHAR(191) NOT NULL,
    `expenseDate` DATETIME(3) NOT NULL,
    `categoryId` VARCHAR(191) NOT NULL,
    `description` TEXT NOT NULL,
    `payee` TEXT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `paymentMethod` ENUM('CASH', 'BANK_TRANSFER', 'GCASH', 'MAYA', 'CHEQUE', 'VOUCHER', 'OTHER', 'FUND_TRANSFER', 'E_WALLET', 'QR_PAYMENT', 'DEBIT_CARD', 'CREDIT_CARD', 'APPLE_PAY') NOT NULL,
    `referenceNumber` TEXT NULL,
    `notes` TEXT NULL,
    `createdById` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `voidedAt` DATETIME(3) NULL,
    `voidedById` VARCHAR(191) NULL,

    UNIQUE INDEX `OperatingExpense_expenseNumber_key`(`expenseNumber`),
    INDEX `OperatingExpense_expenseDate_idx`(`expenseDate`),
    INDEX `OperatingExpense_categoryId_idx`(`categoryId`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `AccountAdjustment` (
    `id` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NULL,
    `type` ENUM('CHARGE', 'CREDIT') NOT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `description` TEXT NOT NULL,
    `createdById` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `StatementOfAccount` (
    `id` VARCHAR(191) NOT NULL,
    `statementNumber` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `periodStart` DATETIME(3) NOT NULL,
    `periodEnd` DATETIME(3) NOT NULL,
    `openingBalance` DECIMAL(12, 2) NOT NULL,
    `totalCharges` DECIMAL(12, 2) NOT NULL,
    `totalPayments` DECIMAL(12, 2) NOT NULL,
    `adjustments` DECIMAL(12, 2) NOT NULL DEFAULT 0,
    `outstandingBalance` DECIMAL(12, 2) NOT NULL,
    `generatedById` VARCHAR(191) NULL,
    `generatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `StatementOfAccount_statementNumber_key`(`statementNumber`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `StatementSchedule` (
    `id` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `dayOfMonth` INTEGER NOT NULL DEFAULT 1,
    `deliveryMethod` ENUM('EMAIL') NOT NULL DEFAULT 'EMAIL',
    `onlyIfOutstanding` BOOLEAN NOT NULL DEFAULT true,
    `enabled` BOOLEAN NOT NULL DEFAULT false,
    `createdById` VARCHAR(191) NOT NULL,
    `lastRunAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `EmailLog` (
    `id` VARCHAR(191) NOT NULL,
    `recipientEmail` TEXT NOT NULL,
    `subject` TEXT NOT NULL,
    `bodyHtml` TEXT NOT NULL DEFAULT '',
    `eventType` TEXT NOT NULL,
    `relatedType` TEXT NULL,
    `relatedId` TEXT NULL,
    `status` ENUM('QUEUED', 'SENDING', 'SENT', 'FAILED') NOT NULL DEFAULT 'QUEUED',
    `failureReason` TEXT NULL,
    `attemptCount` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `sentAt` DATETIME(3) NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `EmailTemplate` (
    `id` VARCHAR(191) NOT NULL,
    `key` VARCHAR(191) NOT NULL,
    `subject` TEXT NOT NULL,
    `bodyHtml` TEXT NOT NULL,
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `EmailTemplate_key_key`(`key`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `MessengerConnection` (
    `id` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `optinRef` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin NOT NULL,
    `psid` TEXT NULL,
    `connected` BOOLEAN NOT NULL DEFAULT false,
    `connectedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `MessengerConnection_customerId_key`(`customerId`),
    UNIQUE INDEX `MessengerConnection_optinRef_key`(`optinRef`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `MessengerLog` (
    `id` VARCHAR(191) NOT NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `message` TEXT NOT NULL,
    `eventType` TEXT NOT NULL,
    `relatedType` TEXT NULL,
    `relatedId` TEXT NULL,
    `status` ENUM('QUEUED', 'SENDING', 'SENT', 'FAILED', 'SKIPPED') NOT NULL DEFAULT 'QUEUED',
    `failureReason` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `sentAt` DATETIME(3) NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `InstalledTheme` (
    `id` VARCHAR(191) NOT NULL,
    `slug` VARCHAR(191) NOT NULL,
    `name` TEXT NOT NULL,
    `version` TEXT NOT NULL,
    `author` TEXT NULL,
    `description` TEXT NULL,
    `installedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `installedById` VARCHAR(191) NULL,

    UNIQUE INDEX `InstalledTheme_slug_key`(`slug`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `InstalledPlugin` (
    `id` VARCHAR(191) NOT NULL,
    `slug` VARCHAR(191) NOT NULL,
    `name` TEXT NOT NULL,
    `version` TEXT NOT NULL,
    `author` TEXT NULL,
    `description` TEXT NULL,
    `active` BOOLEAN NOT NULL DEFAULT false,
    `installedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `installedById` VARCHAR(191) NULL,

    UNIQUE INDEX `InstalledPlugin_slug_key`(`slug`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- AddForeignKey
ALTER TABLE `StaffPermission` ADD CONSTRAINT `StaffPermission_userId_fkey` FOREIGN KEY IF NOT EXISTS (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `PasswordResetToken` ADD CONSTRAINT `PasswordResetToken_userId_fkey` FOREIGN KEY IF NOT EXISTS (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Customer` ADD CONSTRAINT `Customer_userId_fkey` FOREIGN KEY IF NOT EXISTS (`userId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Inquiry` ADD CONSTRAINT `Inquiry_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Inquiry` ADD CONSTRAINT `Inquiry_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Inquiry` ADD CONSTRAINT `Inquiry_cancelledById_fkey` FOREIGN KEY IF NOT EXISTS (`cancelledById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Quotation` ADD CONSTRAINT `Quotation_inquiryId_fkey` FOREIGN KEY IF NOT EXISTS (`inquiryId`) REFERENCES `Inquiry`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Quotation` ADD CONSTRAINT `Quotation_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Quotation` ADD CONSTRAINT `Quotation_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Quotation` ADD CONSTRAINT `Quotation_cancelledById_fkey` FOREIGN KEY IF NOT EXISTS (`cancelledById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Quotation` ADD CONSTRAINT `Quotation_approvedByStaffId_fkey` FOREIGN KEY IF NOT EXISTS (`approvedByStaffId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `QuotationRevisionRequest` ADD CONSTRAINT `QuotationRevisionRequest_quotationId_fkey` FOREIGN KEY IF NOT EXISTS (`quotationId`) REFERENCES `Quotation`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `QuotationRevisionRequest` ADD CONSTRAINT `QuotationRevisionRequest_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `QuotationLineItem` ADD CONSTRAINT `QuotationLineItem_quotationId_fkey` FOREIGN KEY IF NOT EXISTS (`quotationId`) REFERENCES `Quotation`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `QuotationLineItem` ADD CONSTRAINT `QuotationLineItem_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `OrderLineItem` ADD CONSTRAINT `OrderLineItem_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `OrderLineItem` ADD CONSTRAINT `OrderLineItem_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Order` ADD CONSTRAINT `Order_quotationId_fkey` FOREIGN KEY IF NOT EXISTS (`quotationId`) REFERENCES `Quotation`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Order` ADD CONSTRAINT `Order_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Order` ADD CONSTRAINT `Order_cancelledById_fkey` FOREIGN KEY IF NOT EXISTS (`cancelledById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Service` ADD CONSTRAINT `Service_workflowTemplateId_fkey` FOREIGN KEY IF NOT EXISTS (`workflowTemplateId`) REFERENCES `WorkflowTemplate`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ServiceBOMMaterial` ADD CONSTRAINT `ServiceBOMMaterial_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ServiceBOMMaterial` ADD CONSTRAINT `ServiceBOMMaterial_inventoryItemId_fkey` FOREIGN KEY IF NOT EXISTS (`inventoryItemId`) REFERENCES `InventoryItem`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ServiceCostComponent` ADD CONSTRAINT `ServiceCostComponent_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Pricelist` ADD CONSTRAINT `Pricelist_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Promotion` ADD CONSTRAINT `Promotion_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `WorkflowStage` ADD CONSTRAINT `WorkflowStage_templateId_fkey` FOREIGN KEY IF NOT EXISTS (`templateId`) REFERENCES `WorkflowTemplate`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrder` ADD CONSTRAINT `JobOrder_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrder` ADD CONSTRAINT `JobOrder_serviceId_fkey` FOREIGN KEY IF NOT EXISTS (`serviceId`) REFERENCES `Service`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrder` ADD CONSTRAINT `JobOrder_workflowTemplateId_fkey` FOREIGN KEY IF NOT EXISTS (`workflowTemplateId`) REFERENCES `WorkflowTemplate`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrderStageLog` ADD CONSTRAINT `JobOrderStageLog_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrderStageLog` ADD CONSTRAINT `JobOrderStageLog_assignedToId_fkey` FOREIGN KEY IF NOT EXISTS (`assignedToId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `File` ADD CONSTRAINT `File_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `File` ADD CONSTRAINT `File_uploadedById_fkey` FOREIGN KEY IF NOT EXISTS (`uploadedById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Payment` ADD CONSTRAINT `Payment_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Payment` ADD CONSTRAINT `Payment_voucherId_fkey` FOREIGN KEY IF NOT EXISTS (`voucherId`) REFERENCES `Voucher`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Payment` ADD CONSTRAINT `Payment_recordedById_fkey` FOREIGN KEY IF NOT EXISTS (`recordedById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `QCResult` ADD CONSTRAINT `QCResult_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `QCResult` ADD CONSTRAINT `QCResult_inspectorId_fkey` FOREIGN KEY IF NOT EXISTS (`inspectorId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ReworkRecord` ADD CONSTRAINT `ReworkRecord_qcResultId_fkey` FOREIGN KEY IF NOT EXISTS (`qcResultId`) REFERENCES `QCResult`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ReworkRecord` ADD CONSTRAINT `ReworkRecord_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ReworkRecord` ADD CONSTRAINT `ReworkRecord_assignedToId_fkey` FOREIGN KEY IF NOT EXISTS (`assignedToId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `SupplyLot` ADD CONSTRAINT `SupplyLot_inventoryItemId_fkey` FOREIGN KEY IF NOT EXISTS (`inventoryItemId`) REFERENCES `InventoryItem`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `SupplyLot` ADD CONSTRAINT `SupplyLot_supplierId_fkey` FOREIGN KEY IF NOT EXISTS (`supplierId`) REFERENCES `Supplier`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `SupplyLot` ADD CONSTRAINT `SupplyLot_cancelledById_fkey` FOREIGN KEY IF NOT EXISTS (`cancelledById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `InventoryMovement` ADD CONSTRAINT `InventoryMovement_supplyLotId_fkey` FOREIGN KEY IF NOT EXISTS (`supplyLotId`) REFERENCES `SupplyLot`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `InventoryMovement` ADD CONSTRAINT `InventoryMovement_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `InventoryMovement` ADD CONSTRAINT `InventoryMovement_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `InventoryMovement` ADD CONSTRAINT `InventoryMovement_consumptionRecordId_fkey` FOREIGN KEY IF NOT EXISTS (`consumptionRecordId`) REFERENCES `JobOrderMaterialConsumption`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrderMaterialConsumption` ADD CONSTRAINT `JobOrderMaterialConsumption_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrderMaterialConsumption` ADD CONSTRAINT `JobOrderMaterialConsumption_inventoryItemId_fkey` FOREIGN KEY IF NOT EXISTS (`inventoryItemId`) REFERENCES `InventoryItem`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrderMaterialConsumption` ADD CONSTRAINT `JobOrderMaterialConsumption_reversedById_fkey` FOREIGN KEY IF NOT EXISTS (`reversedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobOrderMaterialConsumption` ADD CONSTRAINT `JobOrderMaterialConsumption_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Fulfillment` ADD CONSTRAINT `Fulfillment_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Fulfillment` ADD CONSTRAINT `Fulfillment_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `RewardTransaction` ADD CONSTRAINT `RewardTransaction_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `RewardTransaction` ADD CONSTRAINT `RewardTransaction_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Voucher` ADD CONSTRAINT `Voucher_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Voucher` ADD CONSTRAINT `Voucher_tierId_fkey` FOREIGN KEY IF NOT EXISTS (`tierId`) REFERENCES `RedemptionTier`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Voucher` ADD CONSTRAINT `Voucher_rewardTransactionId_fkey` FOREIGN KEY IF NOT EXISTS (`rewardTransactionId`) REFERENCES `RewardTransaction`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Conversation` ADD CONSTRAINT `Conversation_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Conversation` ADD CONSTRAINT `Conversation_inquiryId_fkey` FOREIGN KEY IF NOT EXISTS (`inquiryId`) REFERENCES `Inquiry`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Conversation` ADD CONSTRAINT `Conversation_quotationId_fkey` FOREIGN KEY IF NOT EXISTS (`quotationId`) REFERENCES `Quotation`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Conversation` ADD CONSTRAINT `Conversation_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Conversation` ADD CONSTRAINT `Conversation_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Conversation` ADD CONSTRAINT `Conversation_assignedStaffId_fkey` FOREIGN KEY IF NOT EXISTS (`assignedStaffId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Conversation` ADD CONSTRAINT `Conversation_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ConversationParticipant` ADD CONSTRAINT `ConversationParticipant_conversationId_fkey` FOREIGN KEY IF NOT EXISTS (`conversationId`) REFERENCES `Conversation`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ConversationParticipant` ADD CONSTRAINT `ConversationParticipant_userId_fkey` FOREIGN KEY IF NOT EXISTS (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ConversationRead` ADD CONSTRAINT `ConversationRead_conversationId_fkey` FOREIGN KEY IF NOT EXISTS (`conversationId`) REFERENCES `Conversation`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `ConversationRead` ADD CONSTRAINT `ConversationRead_userId_fkey` FOREIGN KEY IF NOT EXISTS (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Message` ADD CONSTRAINT `Message_conversationId_fkey` FOREIGN KEY IF NOT EXISTS (`conversationId`) REFERENCES `Conversation`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Message` ADD CONSTRAINT `Message_senderId_fkey` FOREIGN KEY IF NOT EXISTS (`senderId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Message` ADD CONSTRAINT `Message_refInquiryId_fkey` FOREIGN KEY IF NOT EXISTS (`refInquiryId`) REFERENCES `Inquiry`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Message` ADD CONSTRAINT `Message_refQuotationId_fkey` FOREIGN KEY IF NOT EXISTS (`refQuotationId`) REFERENCES `Quotation`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Message` ADD CONSTRAINT `Message_refJobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`refJobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Notification` ADD CONSTRAINT `Notification_userId_fkey` FOREIGN KEY IF NOT EXISTS (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `AuditLog` ADD CONSTRAINT `AuditLog_actorId_fkey` FOREIGN KEY IF NOT EXISTS (`actorId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `OrderTrackingLink` ADD CONSTRAINT `OrderTrackingLink_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `OrderTrackingLink` ADD CONSTRAINT `OrderTrackingLink_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerForm` ADD CONSTRAINT `CustomerForm_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerForm` ADD CONSTRAINT `CustomerForm_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerForm` ADD CONSTRAINT `CustomerForm_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerForm` ADD CONSTRAINT `CustomerForm_lastReopenedById_fkey` FOREIGN KEY IF NOT EXISTS (`lastReopenedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerForm` ADD CONSTRAINT `CustomerForm_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormItem` ADD CONSTRAINT `CustomerFormItem_formId_fkey` FOREIGN KEY IF NOT EXISTS (`formId`) REFERENCES `CustomerForm`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormItem` ADD CONSTRAINT `CustomerFormItem_printedById_fkey` FOREIGN KEY IF NOT EXISTS (`printedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormItem` ADD CONSTRAINT `CustomerFormItem_qcCheckedById_fkey` FOREIGN KEY IF NOT EXISTS (`qcCheckedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormLink` ADD CONSTRAINT `CustomerFormLink_formId_fkey` FOREIGN KEY IF NOT EXISTS (`formId`) REFERENCES `CustomerForm`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormLink` ADD CONSTRAINT `CustomerFormLink_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormDelivery` ADD CONSTRAINT `CustomerFormDelivery_formId_fkey` FOREIGN KEY IF NOT EXISTS (`formId`) REFERENCES `CustomerForm`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormDelivery` ADD CONSTRAINT `CustomerFormDelivery_deliveredById_fkey` FOREIGN KEY IF NOT EXISTS (`deliveredById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormOrder` ADD CONSTRAINT `CustomerFormOrder_formId_fkey` FOREIGN KEY IF NOT EXISTS (`formId`) REFERENCES `CustomerForm`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormOrder` ADD CONSTRAINT `CustomerFormOrder_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `CustomerFormOrder` ADD CONSTRAINT `CustomerFormOrder_addedById_fkey` FOREIGN KEY IF NOT EXISTS (`addedById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `DocumentShareLink` ADD CONSTRAINT `DocumentShareLink_quotationId_fkey` FOREIGN KEY IF NOT EXISTS (`quotationId`) REFERENCES `Quotation`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `DocumentShareLink` ADD CONSTRAINT `DocumentShareLink_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `DocumentShareLink` ADD CONSTRAINT `DocumentShareLink_jobOrderId_fkey` FOREIGN KEY IF NOT EXISTS (`jobOrderId`) REFERENCES `JobOrder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `DocumentShareLink` ADD CONSTRAINT `DocumentShareLink_statementId_fkey` FOREIGN KEY IF NOT EXISTS (`statementId`) REFERENCES `StatementOfAccount`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `DocumentShareLink` ADD CONSTRAINT `DocumentShareLink_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `OperatingExpense` ADD CONSTRAINT `OperatingExpense_categoryId_fkey` FOREIGN KEY IF NOT EXISTS (`categoryId`) REFERENCES `ExpenseCategory`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `OperatingExpense` ADD CONSTRAINT `OperatingExpense_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `OperatingExpense` ADD CONSTRAINT `OperatingExpense_voidedById_fkey` FOREIGN KEY IF NOT EXISTS (`voidedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `AccountAdjustment` ADD CONSTRAINT `AccountAdjustment_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `AccountAdjustment` ADD CONSTRAINT `AccountAdjustment_orderId_fkey` FOREIGN KEY IF NOT EXISTS (`orderId`) REFERENCES `Order`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `AccountAdjustment` ADD CONSTRAINT `AccountAdjustment_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `StatementOfAccount` ADD CONSTRAINT `StatementOfAccount_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `StatementOfAccount` ADD CONSTRAINT `StatementOfAccount_generatedById_fkey` FOREIGN KEY IF NOT EXISTS (`generatedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `StatementSchedule` ADD CONSTRAINT `StatementSchedule_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `StatementSchedule` ADD CONSTRAINT `StatementSchedule_createdById_fkey` FOREIGN KEY IF NOT EXISTS (`createdById`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `MessengerConnection` ADD CONSTRAINT `MessengerConnection_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `MessengerLog` ADD CONSTRAINT `MessengerLog_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `InstalledTheme` ADD CONSTRAINT `InstalledTheme_installedById_fkey` FOREIGN KEY IF NOT EXISTS (`installedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `InstalledPlugin` ADD CONSTRAINT `InstalledPlugin_installedById_fkey` FOREIGN KEY IF NOT EXISTS (`installedById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- C-06: SQL-only unique constraint (Prisma relation shape unchanged)
CREATE UNIQUE INDEX IF NOT EXISTS `Order_quotationId_key` ON `Order`(`quotationId`);

-- C-08: fixed counters. Period counters (EXPENSE:YYYY, SOA:YYYY-MM, LOT:...) are created on first use.
INSERT IGNORE INTO `DocumentSequence` (`name`, `value`, `updatedAt`) VALUES ('TRANSACTION', 0, CURRENT_TIMESTAMP(3)), ('CUSTOMER', 0, CURRENT_TIMESTAMP(3));

INSERT IGNORE INTO `SystemMeta` (`key`, `value`, `updatedAt`) VALUES ('schema_version', '1', CURRENT_TIMESTAMP(3));