-- Client Approval System Migration for cPanel/Production
-- Run this SQL script to add the approval fields to your production database

-- Add approval fields to the users table
ALTER TABLE `users` 
ADD COLUMN `approval_reason` TEXT NULL COMMENT 'Reason for approval',
ADD COLUMN `approved_at` DATETIME NULL,
ADD COLUMN `approved_by_id` CHAR(32) NULL,
ADD COLUMN `rejected_at` DATETIME NULL,
ADD COLUMN `rejected_by_id` CHAR(32) NULL,
ADD COLUMN `rejection_reason` TEXT NULL COMMENT 'Reason for rejection';

-- Add foreign key constraints for approved_by
ALTER TABLE `users`
ADD CONSTRAINT `users_approved_by_id_fk`
FOREIGN KEY (`approved_by_id`) 
REFERENCES `users`(`id`) 
ON DELETE SET NULL;

-- Add foreign key constraints for rejected_by
ALTER TABLE `users`
ADD CONSTRAINT `users_rejected_by_id_fk`
FOREIGN KEY (`rejected_by_id`) 
REFERENCES `users`(`id`) 
ON DELETE SET NULL;

-- Update the status field default and choices (if needed)
-- ALTER TABLE `users` MODIFY COLUMN `status` VARCHAR(20) DEFAULT 'pending_approval';

-- Note: The status field update may vary depending on your current schema
-- Adjust the above ALTER statement based on your table structure