-- =============================================================================
-- Migration: Add vendorId to session-related tables
-- Date: December 5, 2025
-- Issue: Trello - "session DB tables missing vendorId"
-- =============================================================================
--
-- PROBLEM:
-- Five session-related tables are missing the `vendorId` column:
--   1. sessionAppliedDiscounts
--   2. sessionCustomerRequests
--   3. sessionCustomers
--   4. sessionPayments
--   5. sessionSpecialEvents
--
-- This causes:
--   - Poor query performance (requires JOIN with `sessions` table to filter by vendor)
--   - Inconsistency with other session tables that already have vendorId
--   - Can't create efficient compound indexes for vendor-scoped queries
--
-- SOLUTION:
-- Add `vendorId` column to all 5 tables with proper indexes and foreign keys.
-- Backfill existing data from the sessions table.
--
-- NOTE: Sessions are ephemeral - they're deleted after the bill is paid.
-- This migration handles any active sessions that exist at migration time.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. sessionAppliedDiscounts
-- -----------------------------------------------------------------------------
ALTER TABLE `sessionAppliedDiscounts`
  ADD COLUMN `vendorId` varchar(50) NOT NULL AFTER `sessionId`;

-- Backfill vendorId from sessions table
UPDATE `sessionAppliedDiscounts` sad
  JOIN `sessions` s ON sad.sessionId = s.id
  SET sad.vendorId = s.vendorId;

-- Add index for vendor-scoped queries
ALTER TABLE `sessionAppliedDiscounts`
  ADD KEY `idx_sessionapplieddiscounts_vendor` (`vendorId`);

-- Add foreign key constraint
ALTER TABLE `sessionAppliedDiscounts`
  ADD CONSTRAINT `fk_sessionAppliedDiscounts_vendorId` 
  FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE;

-- -----------------------------------------------------------------------------
-- 2. sessionCustomerRequests
-- -----------------------------------------------------------------------------
ALTER TABLE `sessionCustomerRequests`
  ADD COLUMN `vendorId` varchar(50) NOT NULL AFTER `sessionId`;

-- Backfill vendorId from sessions table
UPDATE `sessionCustomerRequests` scr
  JOIN `sessions` s ON scr.sessionId = s.id
  SET scr.vendorId = s.vendorId;

-- Add index for vendor-scoped queries
ALTER TABLE `sessionCustomerRequests`
  ADD KEY `idx_sessioncustomerrequests_vendor` (`vendorId`);

-- Add foreign key constraint
ALTER TABLE `sessionCustomerRequests`
  ADD CONSTRAINT `fk_sessionCustomerRequests_vendorId` 
  FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE;

-- -----------------------------------------------------------------------------
-- 3. sessionCustomers
-- -----------------------------------------------------------------------------
ALTER TABLE `sessionCustomers`
  ADD COLUMN `vendorId` varchar(50) NOT NULL AFTER `sessionId`;

-- Backfill vendorId from sessions table
UPDATE `sessionCustomers` sc
  JOIN `sessions` s ON sc.sessionId = s.id
  SET sc.vendorId = s.vendorId;

-- Add index for vendor-scoped queries  
ALTER TABLE `sessionCustomers`
  ADD KEY `idx_sessioncustomers_vendor` (`vendorId`);

-- Add foreign key constraint
ALTER TABLE `sessionCustomers`
  ADD CONSTRAINT `fk_sessionCustomers_vendorId` 
  FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE;

-- -----------------------------------------------------------------------------
-- 4. sessionPayments
-- -----------------------------------------------------------------------------
ALTER TABLE `sessionPayments`
  ADD COLUMN `vendorId` varchar(50) NOT NULL AFTER `sessionId`;

-- Backfill vendorId from sessions table
UPDATE `sessionPayments` sp
  JOIN `sessions` s ON sp.sessionId = s.id
  SET sp.vendorId = s.vendorId;

-- Add index for vendor-scoped queries
ALTER TABLE `sessionPayments`
  ADD KEY `idx_sessionpayments_vendor` (`vendorId`);

-- Add foreign key constraint
ALTER TABLE `sessionPayments`
  ADD CONSTRAINT `fk_sessionPayments_vendorId` 
  FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE;

-- -----------------------------------------------------------------------------
-- 5. sessionSpecialEvents
-- -----------------------------------------------------------------------------
ALTER TABLE `sessionSpecialEvents`
  ADD COLUMN `vendorId` varchar(50) NOT NULL AFTER `sessionId`;

-- Backfill vendorId from sessions table
UPDATE `sessionSpecialEvents` sse
  JOIN `sessions` s ON sse.sessionId = s.id
  SET sse.vendorId = s.vendorId;

-- Add index for vendor-scoped queries
ALTER TABLE `sessionSpecialEvents`
  ADD KEY `idx_sessionspecialevents_vendor` (`vendorId`);

-- Add foreign key constraint
ALTER TABLE `sessionSpecialEvents`
  ADD CONSTRAINT `fk_sessionSpecialEvents_vendorId` 
  FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE;

-- =============================================================================
-- VERIFICATION QUERIES (run after migration to confirm)
-- =============================================================================
-- 
-- Check all tables have vendorId column:
-- DESCRIBE sessionAppliedDiscounts;
-- DESCRIBE sessionCustomerRequests;
-- DESCRIBE sessionCustomers;
-- DESCRIBE sessionPayments;
-- DESCRIBE sessionSpecialEvents;
--
-- Check foreign keys exist:
-- SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS 
-- WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' 
-- AND TABLE_NAME IN ('sessionAppliedDiscounts', 'sessionCustomerRequests', 
--                    'sessionCustomers', 'sessionPayments', 'sessionSpecialEvents')
-- AND CONSTRAINT_NAME LIKE '%vendorId%';
--
-- =============================================================================
