-- ============================================
-- Jobsportal Update - Database Changes
-- ============================================
-- This SQL file contains database updates for:
-- 1. Job Questions Feature
-- 2. Iyzico Payment Gateway
-- 3. Referral System (Company & User Referrals)
-- 4. Incomplete Profile Reminder Tracking
-- Import this file into your database to add these features
-- ============================================

-- ============================================
-- Iyzico Payment Gateway - Add columns to site_settings table
-- ============================================
-- Note: If columns already exist, you may get an error. That's okay - just continue.
ALTER TABLE `site_settings` 
ADD COLUMN `iyzico_api_key` VARCHAR(255) NULL AFTER `paystack_secret`,
ADD COLUMN `iyzico_secret_key` VARCHAR(255) NULL AFTER `iyzico_api_key`,
ADD COLUMN `iyzico_live_sandbox` VARCHAR(10) NULL DEFAULT 'sandbox' AFTER `iyzico_secret_key`,
ADD COLUMN `is_iyzico_active` TINYINT(1) NULL DEFAULT 0 AFTER `iyzico_live_sandbox`;

-- ============================================
-- Job Questions Feature - New Tables
-- ============================================

-- Table: job_questions
-- Stores custom questions that companies can add to their job posts
CREATE TABLE IF NOT EXISTS `job_questions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` bigint(20) UNSIGNED NOT NULL,
  `question_title` varchar(255) NOT NULL,
  `order` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_questions_job_id_index` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: job_question_answers
-- Stores answers provided by jobseekers for the custom questions
CREATE TABLE IF NOT EXISTS `job_question_answers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_question_id` bigint(20) UNSIGNED NOT NULL,
  `job_apply_id` bigint(20) UNSIGNED NOT NULL,
  `answer` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_question_answers_job_question_id_job_apply_id_index` (`job_question_id`, `job_apply_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Referral System - Company & User Referrals
-- ============================================

-- Create referrals table to track all referrals
CREATE TABLE IF NOT EXISTS `referrals` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `referrer_company_id` INT(11) UNSIGNED NOT NULL COMMENT 'Company who referred',
    `referred_company_id` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'Company who registered using referral',
    `referral_code` VARCHAR(50) NOT NULL,
    `referred_email` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Email invited (before registration)',
    `status` ENUM('pending', 'registered', 'rewarded') NOT NULL DEFAULT 'pending',
    `reward_jobs_given` INT(11) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_referral_code` (`referral_code`),
    KEY `idx_referrer_company` (`referrer_company_id`),
    KEY `idx_referred_company` (`referred_company_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_referrals table to track job seeker referrals
CREATE TABLE IF NOT EXISTS `user_referrals` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `referrer_user_id` INT(11) UNSIGNED NOT NULL COMMENT 'User who referred',
    `referred_user_id` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'User who registered using referral',
    `referral_code` VARCHAR(50) NOT NULL,
    `referred_email` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Email invited (before registration)',
    `status` ENUM('pending', 'registered', 'rewarded') NOT NULL DEFAULT 'pending',
    `reward_days_given` INT(11) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_referrer_user` (`referrer_user_id`),
    KEY `idx_referred_user` (`referred_user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_referral_code` (`referral_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add referral settings to site_settings table
ALTER TABLE `site_settings` 
ADD COLUMN IF NOT EXISTS `referral_required_count` INT(11) NOT NULL DEFAULT 3 COMMENT 'Number of successful referrals needed for reward',
ADD COLUMN IF NOT EXISTS `referral_reward_jobs` INT(11) NOT NULL DEFAULT 1 COMMENT 'Number of free jobs given as reward',
ADD COLUMN IF NOT EXISTS `user_referral_required_count` INT(11) NOT NULL DEFAULT 3 COMMENT 'Number of successful referrals needed for reward',
ADD COLUMN IF NOT EXISTS `user_referral_reward_days` INT(11) NOT NULL DEFAULT 7 COMMENT 'Number of featured days given as reward';

-- Add referral tracking columns to companies table
ALTER TABLE `companies` 
ADD COLUMN IF NOT EXISTS `referral_code` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Unique referral code for this company',
ADD COLUMN IF NOT EXISTS `referred_by_company_id` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'Company who referred this company',
ADD COLUMN IF NOT EXISTS `referral_bonus_jobs` INT(11) NOT NULL DEFAULT 0 COMMENT 'Bonus jobs earned from referrals';

-- Add referral tracking columns to users table
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `referral_code` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Unique referral code for this user',
ADD COLUMN IF NOT EXISTS `referred_by_user_id` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'User who referred this user',
ADD COLUMN IF NOT EXISTS `referral_featured_days` INT(11) NOT NULL DEFAULT 0 COMMENT 'Featured days earned from referrals',
ADD COLUMN IF NOT EXISTS `featured_until` TIMESTAMP NULL DEFAULT NULL COMMENT 'Date until profile is featured';

-- ============================================
-- Incomplete Profile Reminder Tracking
-- ============================================
-- Add column to track when incomplete profile reminder was last sent
-- This prevents sending reminders too frequently

ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `incomplete_profile_reminder_sent_at` DATETIME NULL DEFAULT NULL AFTER `account_deletion_requested_at`;

-- ============================================
-- End of Update
-- ============================================
