-- Migration: Link Payments to Registrations Instead of Users
-- Date: 2025-11-05
-- Description: Refactor payment system to link to registrations for better tracking and automatic approval

-- Step 1: Drop existing foreign key constraint on user_id
ALTER TABLE `payments` DROP FOREIGN KEY `payments_ibfk_1`;

-- Step 2: Add registration_id column (nullable initially for existing data)
ALTER TABLE `payments` 
ADD COLUMN `registration_id` CHAR(36) DEFAULT NULL COMMENT 'Registration this payment is for' AFTER `id`;

-- Step 3: Add index and foreign key for registration_id
ALTER TABLE `payments` 
ADD KEY `idx_registration_id` (`registration_id`),
ADD CONSTRAINT `fk_payments_registration` FOREIGN KEY (`registration_id`) REFERENCES `registrations` (`id`) ON DELETE CASCADE;

-- Step 4: Make user_id nullable (derived from registration.user_id)
ALTER TABLE `payments` 
MODIFY COLUMN `user_id` CHAR(36) DEFAULT NULL COMMENT 'User who made payment (denormalized from registration)';

-- Step 5: Add index for stripe_payment_intent_id for faster lookups
ALTER TABLE `payments` 
ADD UNIQUE KEY `unique_stripe_intent` (`stripe_payment_intent_id`) COMMENT 'Ensure each Stripe PaymentIntent is recorded once';

-- Step 6: Add created_at and updated_at timestamps
ALTER TABLE `payments` 
ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `stripe_payment_intent_id`,
ADD COLUMN `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- Step 7: Add payment metadata columns
ALTER TABLE `payments` 
ADD COLUMN `metadata` JSON DEFAULT NULL COMMENT 'Additional payment metadata (promo_code, stage_id, etc.)' AFTER `updated_at`;

-- Step 8: Update existing payments to link to user (fallback, will need manual correction)
-- This is safe because payments table is likely empty or very small
-- If you have existing payments, you'll need to manually link them to registrations

-- Step 9: Create index for efficient payment status queries
CREATE INDEX `idx_payments_status` ON `payments` (`status`, `created_at`);

-- Step 10: Create index for user payment history queries
CREATE INDEX `idx_payments_user` ON `payments` (`user_id`, `created_at`);

-- Migration completed successfully
-- Note: After this migration, payments MUST have a registration_id
-- The payment intent creation will now require stage_id and optional promo_code
