-- Migration: Remove shifts and cash_counts tables and shift_id from sales
-- Description: Removes shift functionality from the system
-- Date: 2026-01-16

-- Step 1: Remove shift_id column from sales table
-- This column references shifts table, so we remove it first
-- Note: We need to check and drop the foreign key constraint first
-- The constraint name may vary, so we'll try common names

-- Drop foreign key constraint (if it exists)
SET @constraint_name = (
  SELECT CONSTRAINT_NAME 
  FROM information_schema.KEY_COLUMN_USAGE 
  WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'sales' 
    AND COLUMN_NAME = 'shift_id' 
    AND REFERENCED_TABLE_NAME = 'shifts'
  LIMIT 1
);

SET @sql = IF(@constraint_name IS NOT NULL, 
  CONCAT('ALTER TABLE `sales` DROP FOREIGN KEY `', @constraint_name, '`'), 
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Drop index on shift_id (if it exists)
SET @index_name = (
  SELECT INDEX_NAME 
  FROM information_schema.STATISTICS 
  WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'sales' 
    AND COLUMN_NAME = 'shift_id'
    AND INDEX_NAME != 'PRIMARY'
  LIMIT 1
);

SET @sql = IF(@index_name IS NOT NULL, 
  CONCAT('ALTER TABLE `sales` DROP INDEX `', @index_name, '`'), 
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Drop shift_id column (if it exists)
SET @column_exists = (
  SELECT COUNT(*) 
  FROM information_schema.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'sales' 
    AND COLUMN_NAME = 'shift_id'
);

SET @sql = IF(@column_exists > 0, 
  'ALTER TABLE `sales` DROP COLUMN `shift_id`', 
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 2: Drop cash_counts table (depends on shifts)
DROP TABLE IF EXISTS `cash_counts`;

-- Step 3: Drop shifts table
DROP TABLE IF EXISTS `shifts`;

-- Note: All shift-related models, services, controllers, and routes have been removed from the codebase