-- ============================================
-- Comprehensive Migration Script
-- Fixes all missing columns and tables for server database
-- Run this script on your server database to sync with local
-- ============================================

-- ============================================
-- 1. Fix feature_flags table - Add shop_id column for shop-specific feature flags
-- ============================================

-- Check and add shop_id column if it doesn't exist
SET @dbname = DATABASE();
SET @tablename = 'feature_flags';
SET @columnname = 'shop_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT "Column shop_id already exists in feature_flags" AS result;',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' INT NULL AFTER id;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Drop existing unique index on feature_name if it exists (needed for shop-specific flags)
SET @indexname = 'feature_name';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (INDEX_NAME = @indexname)
  ) > 0,
  CONCAT('DROP INDEX ', @indexname, ' ON ', @tablename, ';'),
  'SELECT "Index feature_name does not exist" AS result;'
));
PREPARE dropIndexIfExists FROM @preparedStatement;
EXECUTE dropIndexIfExists;
DEALLOCATE PREPARE dropIndexIfExists;

-- Add foreign key constraint for shop_id (if column was just added)
SET @constraintname = 'fk_feature_flags_shop';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (CONSTRAINT_NAME = @constraintname)
  ) > 0,
  'SELECT "Foreign key fk_feature_flags_shop already exists" AS result;',
  CONCAT('ALTER TABLE ', @tablename, ' ADD CONSTRAINT ', @constraintname, ' FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE;')
));
PREPARE addFkIfNotExists FROM @preparedStatement;
EXECUTE addFkIfNotExists;
DEALLOCATE PREPARE addFkIfNotExists;

-- Add composite index for faster lookups by shop and feature
SET @indexname = 'idx_shop_feature';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (INDEX_NAME = @indexname)
  ) > 0,
  'SELECT "Index idx_shop_feature already exists" AS result;',
  CONCAT('CREATE INDEX ', @indexname, ' ON ', @tablename, ' (shop_id, feature_name);')
));
PREPARE createIndexIfNotExists FROM @preparedStatement;
EXECUTE createIndexIfNotExists;
DEALLOCATE PREPARE createIndexIfNotExists;

-- ============================================
-- 2. Create refresh_tokens table if it doesn't exist
-- ============================================

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 (should already be there, but check anyway)
-- ============================================

SET @tablename = 'branches';
SET @columnname = 'pin';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT "Column pin already exists in branches" AS result;',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(4) DEFAULT NULL;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- ============================================
-- Migration Complete
-- ============================================

SELECT 'Migration completed successfully!' AS status;
SELECT 'Please verify the following:' AS note;
SELECT '1. feature_flags table has shop_id column' AS check1;
SELECT '2. feature_flags table has idx_shop_feature index' AS check2;
SELECT '3. refresh_tokens table exists' AS check3;
SELECT '4. branches table has pin column' AS check4;

