-- Direct SQL to add the referred_by column to clients table
-- Run this directly in your MySQL database

USE dextragr_will_software_demo;

-- Check if column exists and add it if it doesn't
SET @column_exists = (
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_name = 'clients'
    AND column_name = 'referred_by'
    AND table_schema = DATABASE()
);

SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE clients ADD COLUMN referred_by BIGINT UNSIGNED NULL AFTER assigned_staff_id;',
    'SELECT "Column already exists";'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add foreign key constraint if it doesn't exist
SET @constraint_exists = (
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE constraint_name = 'clients_referred_by_foreign'
    AND table_name = 'clients'
    AND table_schema = DATABASE()
);

SET @sql = IF(@constraint_exists = 0, 
    'ALTER TABLE clients ADD CONSTRAINT clients_referred_by_foreign FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE;',
    'SELECT "Constraint already exists";'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verify the column was added
DESCRIBE clients;