-- ============================================
-- COMPLETE REFERRAL SYSTEM SETUP
-- Run this to set up the referral system from scratch
-- ============================================

-- Step 1: Add referralCode column to users table (if it doesn't exist)
-- Check if column exists first
SET @column_exists = (
  SELECT COUNT(*) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'users' 
  AND COLUMN_NAME = 'referralCode'
);

SET @sql = IF(@column_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `referralCode` VARCHAR(50) NULL UNIQUE AFTER `contactNumber`',
  'SELECT "referralCode column already exists" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 2: Add referredByCode column to users table (if it doesn't exist)
SET @column_exists = (
  SELECT COUNT(*) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'users' 
  AND COLUMN_NAME = 'referredByCode'
);

SET @sql = IF(@column_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `referredByCode` VARCHAR(50) NULL AFTER `referralCode`',
  'SELECT "referredByCode column already exists" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 3: Create referrals table (if it doesn't exist)
-- NOTE: referralCode is NOT UNIQUE - same code can be used by multiple people
CREATE TABLE IF NOT EXISTS `referrals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `referrerId` INT NOT NULL,
  `referrerType` ENUM('customer', 'supplier') NOT NULL,
  `referredCustomerId` INT NOT NULL,
  `referralCode` VARCHAR(50) NOT NULL,
  `status` ENUM('pending', 'active', 'completed', 'cancelled') DEFAULT 'pending',
  `totalEarnings` DECIMAL(10, 2) DEFAULT 0,
  `totalOrders` INT DEFAULT 0,
  `totalOrderValue` DECIMAL(10, 2) DEFAULT 0,
  `commissionRate` DECIMAL(5, 2) DEFAULT 0,
  `firstOrderCompleted` BOOLEAN DEFAULT FALSE,
  `firstOrderRewardPaid` BOOLEAN DEFAULT FALSE,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_referrer` (`referrerId`),
  INDEX `idx_referred_customer` (`referredCustomerId`),
  INDEX `idx_referral_code` (`referralCode`),
  FOREIGN KEY (`referrerId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referredCustomerId`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 4: Create referral_rewards table (if it doesn't exist)
CREATE TABLE IF NOT EXISTS `referral_rewards` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `referralId` INT NOT NULL,
  `orderId` INT NULL,
  `rewardType` ENUM('first_order', 'commission', 'bonus') NOT NULL,
  `orderAmount` DECIMAL(10, 2) NOT NULL,
  `commissionRate` DECIMAL(5, 2) NOT NULL,
  `rewardAmount` DECIMAL(10, 2) NOT NULL,
  `status` ENUM('pending', 'paid', 'cancelled') DEFAULT 'pending',
  `paidAt` DATETIME NULL,
  `paidToWallet` BOOLEAN DEFAULT FALSE,
  `notes` TEXT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_referral` (`referralId`),
  INDEX `idx_order` (`orderId`),
  INDEX `idx_status` (`status`),
  FOREIGN KEY (`referralId`) REFERENCES `referrals`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 5: Verify tables were created
SELECT 'Referral system setup complete!' AS status;
SELECT COUNT(*) as referrals_table_exists FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'referrals';
SELECT COUNT(*) as referral_rewards_table_exists FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'referral_rewards';

-- Step 6: Check columns in users table
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'users' 
AND COLUMN_NAME IN ('referralCode', 'referredByCode');