-- ============================================================================
-- HRMS Recruitment Module - Database Schema
-- ============================================================================
-- Description: Complete recruitment workflow from job posting to employee onboarding
-- Date: 2025-11-21
-- Author: TAF Development Team
-- ============================================================================

-- 1. Job Postings Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS job_postings (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    job_title VARCHAR(255) NOT NULL,
    job_code VARCHAR(50) UNIQUE NOT NULL,
    department VARCHAR(100) NULL,
    location VARCHAR(200) NULL,
    employment_type VARCHAR(50) DEFAULT 'full_time' CHECK (employment_type IN ('full_time', 'part_time', 'contract', 'temporary', 'internship')),
    experience_level VARCHAR(50) DEFAULT 'mid' CHECK (experience_level IN ('entry', 'mid', 'senior', 'executive')),
    job_description TEXT NOT NULL,
    requirements TEXT NULL,
    responsibilities TEXT NULL,
    qualifications TEXT NULL,
    skills_required JSONB NULL,
    salary_min DECIMAL(15,2) NULL,
    salary_max DECIMAL(15,2) NULL,
    salary_currency VARCHAR(10) DEFAULT 'KES',
    number_of_positions INTEGER DEFAULT 1,
    application_deadline DATE NULL,
    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'closed', 'cancelled', 'filled')),
    is_remote BOOLEAN DEFAULT FALSE,
    is_urgent BOOLEAN DEFAULT FALSE,
    posted_by UUID NULL,
    posted_at TIMESTAMPTZ NULL,
    closed_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_job_posting_posted_by
    FOREIGN KEY (posted_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_job_postings_org ON job_postings(org_id);
CREATE INDEX IF NOT EXISTS idx_job_postings_status ON job_postings(status);
CREATE INDEX IF NOT EXISTS idx_job_postings_deadline ON job_postings(application_deadline);
CREATE INDEX IF NOT EXISTS idx_job_postings_dept ON job_postings(department);

-- 2. Applicants Table (Candidate database)
-- ============================================================================
CREATE TABLE IF NOT EXISTS applicants (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    alternate_phone VARCHAR(50) NULL,
    date_of_birth DATE NULL,
    gender VARCHAR(20) NULL,
    nationality VARCHAR(100) NULL,
    national_id VARCHAR(50) NULL,
    passport_number VARCHAR(50) NULL,
    current_address TEXT NULL,
    permanent_address TEXT NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    country VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,
    linkedin_url VARCHAR(255) NULL,
    portfolio_url VARCHAR(255) NULL,
    current_company VARCHAR(200) NULL,
    current_position VARCHAR(200) NULL,
    current_salary DECIMAL(15,2) NULL,
    expected_salary DECIMAL(15,2) NULL,
    notice_period_days INTEGER NULL,
    total_experience_years DECIMAL(4,1) NULL,
    highest_education VARCHAR(100) NULL,
    skills JSONB NULL,
    languages JSONB NULL,
    resume_file_path VARCHAR(500) NULL,
    cover_letter_file_path VARCHAR(500) NULL,
    photo_file_path VARCHAR(500) NULL,
    source VARCHAR(100) NULL,
    referral_name VARCHAR(200) NULL,
    tags JSONB NULL,
    notes TEXT NULL,
    is_blacklisted BOOLEAN DEFAULT FALSE,
    blacklist_reason TEXT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT unique_applicant_email_org UNIQUE (email, org_id)
);

CREATE INDEX IF NOT EXISTS idx_applicants_org ON applicants(org_id);
CREATE INDEX IF NOT EXISTS idx_applicants_email ON applicants(email);
CREATE INDEX IF NOT EXISTS idx_applicants_name ON applicants(last_name, first_name);
CREATE INDEX IF NOT EXISTS idx_applicants_skills ON applicants USING GIN (skills);
CREATE INDEX IF NOT EXISTS idx_applicants_blacklist ON applicants(is_blacklisted);

-- 3. Job Applications Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS job_applications (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    job_posting_id INTEGER NOT NULL,
    applicant_id INTEGER NOT NULL,
    application_number VARCHAR(50) UNIQUE NOT NULL,
    application_date TIMESTAMPTZ DEFAULT NOW(),
    status VARCHAR(50) DEFAULT 'submitted' CHECK (status IN (
        'submitted', 'screening', 'shortlisted', 'rejected_screening',
        'interview_scheduled', 'interviewed', 'rejected_interview',
        'offer_extended', 'offer_accepted', 'offer_declined',
        'hired', 'withdrawn', 'cancelled'
    )),
    stage VARCHAR(50) DEFAULT 'application' CHECK (stage IN (
        'application', 'screening', 'interview', 'technical_test', 
        'offer', 'onboarding', 'hired', 'rejected'
    )),
    priority VARCHAR(20) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
    applied_position VARCHAR(255) NULL,
    applied_salary DECIMAL(15,2) NULL,
    screening_score DECIMAL(5,2) NULL,
    screening_notes TEXT NULL,
    screening_by UUID NULL,
    screening_date TIMESTAMPTZ NULL,
    overall_rating DECIMAL(3,2) NULL,
    recruiter_notes TEXT NULL,
    rejection_reason TEXT NULL,
    rejected_by UUID NULL,
    rejected_at TIMESTAMPTZ NULL,
    assigned_to UUID NULL,
    interview_count INTEGER DEFAULT 0,
    offer_id INTEGER NULL,
    hired_as_employee_id INTEGER NULL,
    source_channel VARCHAR(100) NULL,
    referral_source VARCHAR(200) NULL,
    custom_fields JSONB NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_application_job_posting
    FOREIGN KEY (job_posting_id) REFERENCES job_postings(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_application_applicant
    FOREIGN KEY (applicant_id) REFERENCES applicants(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_application_screening_by
    FOREIGN KEY (screening_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT fk_application_rejected_by
    FOREIGN KEY (rejected_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT fk_application_assigned_to
    FOREIGN KEY (assigned_to) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT unique_applicant_job_application
    UNIQUE (job_posting_id, applicant_id)
);

CREATE INDEX IF NOT EXISTS idx_applications_org ON job_applications(org_id);
CREATE INDEX IF NOT EXISTS idx_applications_job ON job_applications(job_posting_id);
CREATE INDEX IF NOT EXISTS idx_applications_applicant ON job_applications(applicant_id);
CREATE INDEX IF NOT EXISTS idx_applications_status ON job_applications(status);
CREATE INDEX IF NOT EXISTS idx_applications_stage ON job_applications(stage);
CREATE INDEX IF NOT EXISTS idx_applications_assigned ON job_applications(assigned_to);
CREATE INDEX IF NOT EXISTS idx_applications_date ON job_applications(application_date);

-- 4. Interview Schedules Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS interview_schedules (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    application_id INTEGER NOT NULL,
    interview_type VARCHAR(50) DEFAULT 'phone' CHECK (interview_type IN (
        'phone', 'video', 'in_person', 'technical', 'panel', 'final'
    )),
    interview_round INTEGER DEFAULT 1,
    scheduled_date DATE NOT NULL,
    scheduled_time TIME NOT NULL,
    duration_minutes INTEGER DEFAULT 60,
    location VARCHAR(200) NULL,
    meeting_link VARCHAR(500) NULL,
    interviewer_ids JSONB NULL,
    interview_panel JSONB NULL,
    status VARCHAR(50) DEFAULT 'scheduled' CHECK (status IN (
        'scheduled', 'confirmed', 'rescheduled', 'in_progress', 
        'completed', 'cancelled', 'no_show'
    )),
    agenda TEXT NULL,
    notes TEXT NULL,
    reminder_sent BOOLEAN DEFAULT FALSE,
    reminder_sent_at TIMESTAMPTZ NULL,
    confirmation_status VARCHAR(50) DEFAULT 'pending' CHECK (confirmation_status IN (
        'pending', 'confirmed', 'declined', 'tentative'
    )),
    confirmed_at TIMESTAMPTZ NULL,
    cancelled_reason TEXT NULL,
    rescheduled_from_id INTEGER NULL,
    scheduled_by UUID NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_interview_application
    FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_interview_scheduled_by
    FOREIGN KEY (scheduled_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT fk_interview_rescheduled_from
    FOREIGN KEY (rescheduled_from_id) REFERENCES interview_schedules(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_interviews_application ON interview_schedules(application_id);
CREATE INDEX IF NOT EXISTS idx_interviews_date ON interview_schedules(scheduled_date, scheduled_time);
CREATE INDEX IF NOT EXISTS idx_interviews_status ON interview_schedules(status);
CREATE INDEX IF NOT EXISTS idx_interviews_type ON interview_schedules(interview_type);

-- 5. Interview Feedback Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS interview_feedback (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    interview_id INTEGER NOT NULL,
    interviewer_id UUID NOT NULL,
    overall_rating DECIMAL(3,2) NULL CHECK (overall_rating >= 0 AND overall_rating <= 5),
    technical_skills_rating DECIMAL(3,2) NULL,
    communication_rating DECIMAL(3,2) NULL,
    cultural_fit_rating DECIMAL(3,2) NULL,
    problem_solving_rating DECIMAL(3,2) NULL,
    experience_rating DECIMAL(3,2) NULL,
    strengths TEXT NULL,
    weaknesses TEXT NULL,
    feedback_notes TEXT NULL,
    recommendation VARCHAR(50) DEFAULT 'undecided' CHECK (recommendation IN (
        'strong_hire', 'hire', 'undecided', 'no_hire', 'strong_no_hire'
    )),
    next_steps TEXT NULL,
    custom_ratings JSONB NULL,
    submitted_at TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_feedback_interview
    FOREIGN KEY (interview_id) REFERENCES interview_schedules(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_feedback_interviewer
    FOREIGN KEY (interviewer_id) REFERENCES users(user_id) ON DELETE CASCADE,
    
    CONSTRAINT unique_interviewer_feedback
    UNIQUE (interview_id, interviewer_id)
);

CREATE INDEX IF NOT EXISTS idx_feedback_interview ON interview_feedback(interview_id);
CREATE INDEX IF NOT EXISTS idx_feedback_interviewer ON interview_feedback(interviewer_id);
CREATE INDEX IF NOT EXISTS idx_feedback_rating ON interview_feedback(overall_rating);

-- 6. Offer Letters Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS offer_letters (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    application_id INTEGER NOT NULL,
    offer_number VARCHAR(50) UNIQUE NOT NULL,
    position_title VARCHAR(255) NOT NULL,
    department VARCHAR(100) NULL,
    reporting_to VARCHAR(200) NULL,
    employment_type VARCHAR(50) DEFAULT 'full_time',
    start_date DATE NULL,
    salary_amount DECIMAL(15,2) NOT NULL,
    salary_currency VARCHAR(10) DEFAULT 'KES',
    salary_frequency VARCHAR(20) DEFAULT 'monthly' CHECK (salary_frequency IN ('hourly', 'daily', 'weekly', 'monthly', 'annually')),
    benefits JSONB NULL,
    allowances JSONB NULL,
    bonuses JSONB NULL,
    probation_period_months INTEGER DEFAULT 3,
    notice_period_days INTEGER DEFAULT 30,
    working_hours VARCHAR(100) NULL,
    work_location VARCHAR(200) NULL,
    offer_valid_until DATE NULL,
    offer_letter_template TEXT NULL,
    offer_letter_file_path VARCHAR(500) NULL,
    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN (
        'draft', 'sent', 'viewed', 'accepted', 'declined', 'expired', 'withdrawn'
    )),
    sent_at TIMESTAMPTZ NULL,
    sent_by UUID NULL,
    viewed_at TIMESTAMPTZ NULL,
    response_date TIMESTAMPTZ NULL,
    acceptance_signature TEXT NULL,
    decline_reason TEXT NULL,
    special_conditions TEXT NULL,
    notes TEXT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_offer_application
    FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_offer_sent_by
    FOREIGN KEY (sent_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_offers_application ON offer_letters(application_id);
CREATE INDEX IF NOT EXISTS idx_offers_status ON offer_letters(status);
CREATE INDEX IF NOT EXISTS idx_offers_valid ON offer_letters(offer_valid_until);

-- 7. Employee Onboarding Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS employee_onboarding (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    application_id INTEGER NULL,
    offer_id INTEGER NULL,
    employee_id INTEGER NULL,
    onboarding_status VARCHAR(50) DEFAULT 'pending' CHECK (onboarding_status IN (
        'pending', 'in_progress', 'completed', 'cancelled'
    )),
    start_date DATE NULL,
    expected_completion_date DATE NULL,
    actual_completion_date DATE NULL,
    assigned_buddy_id UUID NULL,
    assigned_hr_id UUID NULL,
    completion_percentage DECIMAL(5,2) DEFAULT 0.00,
    documents_submitted BOOLEAN DEFAULT FALSE,
    equipment_assigned BOOLEAN DEFAULT FALSE,
    access_granted BOOLEAN DEFAULT FALSE,
    training_scheduled BOOLEAN DEFAULT FALSE,
    notes TEXT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_onboarding_application
    FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE SET NULL,
    
    CONSTRAINT fk_onboarding_offer
    FOREIGN KEY (offer_id) REFERENCES offer_letters(id) ON DELETE SET NULL,
    
    CONSTRAINT fk_onboarding_buddy
    FOREIGN KEY (assigned_buddy_id) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT fk_onboarding_hr
    FOREIGN KEY (assigned_hr_id) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_onboarding_application ON employee_onboarding(application_id);
CREATE INDEX IF NOT EXISTS idx_onboarding_employee ON employee_onboarding(employee_id);
CREATE INDEX IF NOT EXISTS idx_onboarding_status ON employee_onboarding(onboarding_status);

-- 8. Onboarding Tasks Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS onboarding_tasks (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    onboarding_id INTEGER NOT NULL,
    task_name VARCHAR(255) NOT NULL,
    task_description TEXT NULL,
    task_category VARCHAR(100) NULL,
    assigned_to UUID NULL,
    due_date DATE NULL,
    priority VARCHAR(20) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'pending', 'in_progress', 'completed', 'skipped', 'blocked'
    )),
    completion_date TIMESTAMPTZ NULL,
    completion_notes TEXT NULL,
    dependencies JSONB NULL,
    checklist_items JSONB NULL,
    attachments JSONB NULL,
    order_index INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_task_onboarding
    FOREIGN KEY (onboarding_id) REFERENCES employee_onboarding(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_task_assigned_to
    FOREIGN KEY (assigned_to) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_onboarding_tasks_onboarding ON onboarding_tasks(onboarding_id);
CREATE INDEX IF NOT EXISTS idx_onboarding_tasks_assigned ON onboarding_tasks(assigned_to);
CREATE INDEX IF NOT EXISTS idx_onboarding_tasks_status ON onboarding_tasks(status);
CREATE INDEX IF NOT EXISTS idx_onboarding_tasks_due ON onboarding_tasks(due_date);

-- 9. Application Documents Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS application_documents (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    application_id INTEGER NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    document_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INTEGER NULL,
    mime_type VARCHAR(100) NULL,
    uploaded_by UUID NULL,
    uploaded_at TIMESTAMPTZ DEFAULT NOW(),
    is_verified BOOLEAN DEFAULT FALSE,
    verified_by UUID NULL,
    verified_at TIMESTAMPTZ NULL,
    notes TEXT NULL,
    
    CONSTRAINT fk_app_doc_application
    FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_app_doc_uploaded_by
    FOREIGN KEY (uploaded_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT fk_app_doc_verified_by
    FOREIGN KEY (verified_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_app_docs_application ON application_documents(application_id);
CREATE INDEX IF NOT EXISTS idx_app_docs_type ON application_documents(document_type);

-- 10. Application Timeline/Activity Log Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS application_timeline (
    id SERIAL PRIMARY KEY,
    application_id INTEGER NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_title VARCHAR(255) NOT NULL,
    event_description TEXT NULL,
    old_value VARCHAR(255) NULL,
    new_value VARCHAR(255) NULL,
    performed_by UUID NULL,
    event_timestamp TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB NULL,
    
    CONSTRAINT fk_timeline_application
    FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_timeline_performed_by
    FOREIGN KEY (performed_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_timeline_application ON application_timeline(application_id);
CREATE INDEX IF NOT EXISTS idx_timeline_timestamp ON application_timeline(event_timestamp);
CREATE INDEX IF NOT EXISTS idx_timeline_type ON application_timeline(event_type);

-- 11. Create Views for Reporting
-- ============================================================================

-- Active Job Postings with Application Count
CREATE OR REPLACE VIEW active_job_postings_summary AS
SELECT 
    jp.id,
    jp.job_code,
    jp.job_title,
    jp.department,
    jp.location,
    jp.employment_type,
    jp.status,
    jp.application_deadline,
    jp.number_of_positions,
    COUNT(DISTINCT ja.id) AS total_applications,
    COUNT(DISTINCT CASE WHEN ja.stage = 'interview' THEN ja.id END) AS interviews_count,
    COUNT(DISTINCT CASE WHEN ja.status = 'offer_extended' THEN ja.id END) AS offers_count,
    COUNT(DISTINCT CASE WHEN ja.status = 'hired' THEN ja.id END) AS hired_count,
    jp.posted_at,
    jp.org_id,
    jp.tenant_id
FROM job_postings jp
LEFT JOIN job_applications ja ON jp.id = ja.job_posting_id
WHERE jp.status IN ('published', 'filled')
GROUP BY jp.id, jp.job_code, jp.job_title, jp.department, jp.location, 
         jp.employment_type, jp.status, jp.application_deadline, 
         jp.number_of_positions, jp.posted_at, jp.org_id, jp.tenant_id;

-- Application Pipeline Summary
CREATE OR REPLACE VIEW application_pipeline_summary AS
SELECT 
    ja.org_id,
    ja.stage,
    ja.status,
    COUNT(*) AS count,
    AVG(ja.overall_rating) AS avg_rating,
    MIN(ja.application_date) AS oldest_application,
    MAX(ja.application_date) AS newest_application
FROM job_applications ja
GROUP BY ja.org_id, ja.stage, ja.status;

-- Complete
SELECT 'HRMS Recruitment Schema Created Successfully' AS status;
