-- Migration: Add manual payment confirmation fields to payments table
-- Run this migration to add support for manual bank transfer payments

ALTER TABLE `payments` 
ADD COLUMN `transactionReference` VARCHAR(255) NULL AFTER `paidAt`,
ADD COLUMN `customerNotes` TEXT NULL AFTER `transactionReference`,
ADD COLUMN `adminNotes` TEXT NULL AFTER `customerNotes`,
ADD COLUMN `confirmedBy` INT NULL AFTER `adminNotes`,
ADD COLUMN `confirmedAt` DATETIME NULL AFTER `confirmedBy`;

-- Add foreign key constraint for confirmedBy
ALTER TABLE `payments`
ADD CONSTRAINT `fk_payments_confirmed_by` 
FOREIGN KEY (`confirmedBy`) REFERENCES `users`(`id`) 
ON DELETE SET NULL ON UPDATE CASCADE;