-- ============================================
-- Simple Migration Script - Fix All Missing Columns
-- Run this directly in MySQL/phpMyAdmin
-- ============================================

-- ============================================
-- 1. Add shop_id column to feature_flags table
-- ============================================
-- This allows shop-specific feature flags (e.g., roster enabled per shop)

-- Add shop_id column (will fail gracefully if already exists)
ALTER TABLE `feature_flags` 
ADD COLUMN `shop_id` INT NULL AFTER `id`;

-- Add foreign key constraint (will fail gracefully if already exists)
ALTER TABLE `feature_flags` 
ADD CONSTRAINT `fk_feature_flags_shop` 
FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE;

-- Drop existing unique index on feature_name (needed for shop-specific flags)
-- This will fail if index doesn't exist, but that's OK
DROP INDEX `feature_name` ON `feature_flags`;

-- Add composite index for faster lookups
CREATE INDEX `idx_shop_feature` ON `feature_flags` (`shop_id`, `feature_name`);

-- ============================================
-- 2. Create refresh_tokens table
-- ============================================
-- This table is used for JWT token refresh functionality

CREATE TABLE IF NOT EXISTS `refresh_tokens` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `token` TEXT NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_expires_at` (`expires_at`),
  CONSTRAINT `fk_refresh_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. Verify branches.pin column exists
-- ============================================
-- This should already exist, but adding it just in case

ALTER TABLE `branches` 
ADD COLUMN `pin` VARCHAR(4) DEFAULT NULL;

-- ============================================
-- Migration Complete!
-- ============================================
-- After running this script, restart your PM2 server:
-- pm2 restart server

