-- =============================================================================
-- Migration: Add Print Server Cloud Printing Tables
-- Date: December 8, 2025
-- Issue: Cloud Print Server Architecture Implementation
-- =============================================================================
--
-- OVERVIEW:
-- Add tables to support cloud-connected receipt printing:
--   1. printServerPairingCodes - Temporary codes for device registration
--   2. printServerDevices - Registered print server devices with auth tokens
--   3. printQueue - Print job queue for pending/completed print jobs
--
-- PAIRING FLOW:
--   1. Manager App calls generate-pairing-code → gets 6-char code (expires in 5 min)
--   2. Print Server enters code → calls login.php with pairingCode
--   3. API verifies code, creates device, returns permanent authToken
--   4. Print Server stores authToken for future API calls
--
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. printServerPairingCodes - Temporary pairing codes for device registration
-- -----------------------------------------------------------------------------
CREATE TABLE `printServerPairingCodes` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendorId` VARCHAR(50) NOT NULL,
  `pairingCode` VARCHAR(6) NOT NULL,
  `deviceName` VARCHAR(100) NOT NULL DEFAULT 'Print Server',
  `expiresAt` INT(11) UNSIGNED NOT NULL,
  `usedAt` INT(11) UNSIGNED DEFAULT NULL,
  `usedByDeviceId` INT(11) UNSIGNED DEFAULT NULL,
  `createdAt` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pairingcodes_code` (`pairingCode`),
  KEY `idx_pairingcodes_vendor` (`vendorId`),
  KEY `idx_pairingcodes_expires` (`expiresAt`),
  CONSTRAINT `fk_printServerPairingCodes_vendorId` 
    FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- -----------------------------------------------------------------------------
-- 2. printServerDevices - Registered print server devices
-- -----------------------------------------------------------------------------
CREATE TABLE `printServerDevices` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendorId` VARCHAR(50) NOT NULL,
  `deviceName` VARCHAR(100) NOT NULL DEFAULT 'Print Server',
  `authToken` VARCHAR(64) NOT NULL,
  `printerModel` VARCHAR(100) DEFAULT NULL,
  `printerBluetoothName` VARCHAR(100) DEFAULT NULL,
  `lastSeenAt` INT(11) UNSIGNED DEFAULT NULL,
  `lastPrintAt` INT(11) UNSIGNED DEFAULT NULL,
  `isActive` TINYINT(1) NOT NULL DEFAULT 1,
  `createdAt` INT(11) UNSIGNED NOT NULL,
  `updatedAt` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_printserverdevices_token` (`authToken`),
  KEY `idx_printserverdevices_vendor` (`vendorId`),
  CONSTRAINT `fk_printServerDevices_vendorId` 
    FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- -----------------------------------------------------------------------------
-- 3. printQueue - Print job queue
-- -----------------------------------------------------------------------------
CREATE TABLE `printQueue` (
  `id` VARCHAR(36) NOT NULL COMMENT 'UUID job ID',
  `vendorId` VARCHAR(50) NOT NULL,
  `receiptCode` VARCHAR(50) NOT NULL,
  `deviceId` INT(11) UNSIGNED DEFAULT NULL COMMENT 'Target device (NULL = any device for vendor)',
  `status` ENUM('pending', 'printing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
  `priority` ENUM('low', 'normal', 'high') NOT NULL DEFAULT 'normal',
  `copies` TINYINT(3) UNSIGNED NOT NULL DEFAULT 1,
  `createdAt` INT(11) UNSIGNED NOT NULL,
  `claimedAt` INT(11) UNSIGNED DEFAULT NULL COMMENT 'When a device claimed the job',
  `claimedByDeviceId` INT(11) UNSIGNED DEFAULT NULL,
  `completedAt` INT(11) UNSIGNED DEFAULT NULL,
  `errorMessage` VARCHAR(500) DEFAULT NULL,
  `retryCount` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `maxRetries` TINYINT(3) UNSIGNED NOT NULL DEFAULT 3,
  `metadata` JSON DEFAULT NULL COMMENT 'Additional job metadata',
  PRIMARY KEY (`id`),
  KEY `idx_printqueue_vendor_status` (`vendorId`, `status`),
  KEY `idx_printqueue_device` (`deviceId`),
  KEY `idx_printqueue_receipt` (`vendorId`, `receiptCode`),
  KEY `idx_printqueue_claimed` (`claimedByDeviceId`),
  CONSTRAINT `fk_printQueue_vendorId` 
    FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_printQueue_deviceId` 
    FOREIGN KEY (`deviceId`) REFERENCES `printServerDevices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_printQueue_claimedByDeviceId` 
    FOREIGN KEY (`claimedByDeviceId`) REFERENCES `printServerDevices` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- =============================================================================
-- VERIFICATION QUERIES
-- =============================================================================
-- 
-- DESCRIBE printServerPairingCodes;
-- DESCRIBE printServerDevices;
-- DESCRIBE printQueue;
--
-- SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS 
-- WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' 
-- AND TABLE_NAME IN ('printServerPairingCodes', 'printServerDevices', 'printQueue');
--
-- =============================================================================

-- =============================================================================
-- CLEANUP: Remove expired pairing codes (run periodically)
-- =============================================================================
-- DELETE FROM printServerPairingCodes WHERE expiresAt < UNIX_TIMESTAMP();
-- =============================================================================
