-- ============================================================
-- Migration: Multi-Account Meta Support
-- Version: 001
-- Date: 2026-01-26
-- Description: Enable multiple Meta ad accounts per user
-- ============================================================

-- IMPORTANT: Backup your data before running this migration!
-- Run with: mysql -u root -p criah_conecte < 001_multi_account_meta.sql

-- ============================================================
-- Step 1: Remove UNIQUE constraint on user_id
-- This allows multiple connections per user
-- ============================================================
ALTER TABLE `meta_connections` DROP INDEX `uk_meta_connections_user`;

-- ============================================================
-- Step 2: Add new columns for multi-account support
-- ============================================================
ALTER TABLE `meta_connections`
    ADD COLUMN `ad_account_id` VARCHAR(50) NULL AFTER `meta_user_id`,
    ADD COLUMN `ad_account_name` VARCHAR(255) NULL AFTER `ad_account_id`,
    ADD COLUMN `is_active` TINYINT(1) NOT NULL DEFAULT 0 AFTER `ad_account_name`;

-- ============================================================
-- Step 3: Add new indexes for performance
-- - idx_meta_connections_user_active: Fast lookup of active connection
-- - uk_meta_connections_user_account: Prevent duplicate ad accounts per user
-- ============================================================
ALTER TABLE `meta_connections`
    ADD INDEX `idx_meta_connections_user_active` (`user_id`, `is_active`),
    ADD UNIQUE KEY `uk_meta_connections_user_account` (`user_id`, `ad_account_id`);

-- ============================================================
-- Step 4: Set existing connections as active
-- Ensures backward compatibility
-- ============================================================
UPDATE `meta_connections` SET `is_active` = 1 WHERE `id` > 0;

-- ============================================================
-- Verification queries (run manually to verify)
-- ============================================================
-- DESCRIBE meta_connections;
-- SHOW INDEX FROM meta_connections;
-- SELECT * FROM meta_connections;
