-- Step 15: Add Cache State Tables for Version-Based Cache Invalidation
-- Run this migration on production database

-- Core cache state tracking table
-- Tracks the current version of each entity type per vendor
CREATE TABLE IF NOT EXISTS cacheState (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendorId VARCHAR(50) NOT NULL,
    entityType VARCHAR(50) NOT NULL,
    version INT UNSIGNED NOT NULL DEFAULT 1,
    lastModifiedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    lastModifiedBy VARCHAR(100) DEFAULT NULL,
    INDEX idx_vendor_entity (vendorId, entityType),
    UNIQUE KEY unique_vendor_entity (vendorId, entityType)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cache invalidation log for debugging and auditing
-- Records every version bump with context about what triggered it
CREATE TABLE IF NOT EXISTS cacheInvalidationLog (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendorId VARCHAR(50) NOT NULL,
    entityType VARCHAR(50) NOT NULL,
    previousVersion INT UNSIGNED NOT NULL,
    newVersion INT UNSIGNED NOT NULL,
    reason VARCHAR(255) DEFAULT NULL,
    triggeredBy VARCHAR(100) DEFAULT NULL,
    triggeredEndpoint VARCHAR(255) DEFAULT NULL,
    additionalContext JSON DEFAULT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_vendor_entity (vendorId, entityType),
    INDEX idx_vendor_created (vendorId, createdAt),
    INDEX idx_created (createdAt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verify tables were created
SELECT 'cacheState' AS tableName, COUNT(*) AS rowCount FROM cacheState
UNION ALL
SELECT 'cacheInvalidationLog' AS tableName, COUNT(*) AS rowCount FROM cacheInvalidationLog;

-- ============================================
-- Entity Types Reference
-- ============================================
-- 
-- STATIC DATA (changes infrequently):
-- - menu           : Items, categories, subcategories, options, addons, modifications
-- - tables         : Tables, table groups, table groupings for reservations  
-- - openingHours   : Opening hours templates, regular days, adjusted days
-- - translations   : Translation updates
-- - vendor         : Vendor settings
-- - discounts      : Discount configurations (menu-level)
--
-- DYNAMIC DATA (changes frequently):
-- - reservations   : Reservations data
-- - onlineOrders   : Online order data
--
-- SESSION DATA (high-frequency updates, per-vendor):
-- - sessionItems      : Session items (both kitchen and waiter handled)
-- - sessionCustomers  : Session customers
-- - sessionPayments   : Session payments
-- - sessionDiscounts  : Session applied discounts
-- - sessionRequests   : Session customer requests
-- - sessionEvents     : Session special events
-- ============================================
