-- Rockview University staff and administrator CVs (PostgreSQL / pgAdmin 4).
-- Run after the application's users table exists.

CREATE TABLE IF NOT EXISTS cv_profiles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    professional_title VARCHAR(180),
    career_objective TEXT,
    professional_summary TEXT,
    phone VARCHAR(80),
    contact_email VARCHAR(255),
    address TEXT,
    photo_url VARCHAR(500),
    website VARCHAR(255),
    linkedin_url VARCHAR(255),
    nationality VARCHAR(120),
    languages TEXT,
    hobbies TEXT,
    date_of_birth DATE,
    marital_status VARCHAR(80),
    place_of_birth VARCHAR(150),
    religion VARCHAR(100),
    gender VARCHAR(40),
    nrc_number VARCHAR(80),
    cv_name VARCHAR(150),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cv_entries (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    section VARCHAR(32) NOT NULL CHECK (section IN ('education','experience','internship','work_learning','certification','project','skill','referee')),
    sort_order INT NOT NULL DEFAULT 0,
    entry_data JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_cv_entries_user_section_order
    ON cv_entries(user_id, section, sort_order, id);
CREATE INDEX IF NOT EXISTS idx_cv_profiles_updated
    ON cv_profiles(updated_at DESC);

ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS hobbies TEXT;

-- Reapply the section constraint when upgrading from an earlier CV package.
ALTER TABLE cv_entries DROP CONSTRAINT IF EXISTS cv_entries_section_check;
ALTER TABLE cv_entries ADD CONSTRAINT cv_entries_section_check CHECK (section IN ('education','experience','internship','work_learning','certification','project','skill','referee'));


-- Optional personal details shown in the supplied CV reference.
ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS date_of_birth DATE;
ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS marital_status VARCHAR(80);
ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS place_of_birth VARCHAR(150);
ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS religion VARCHAR(100);
ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS gender VARCHAR(40);
ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS nrc_number VARCHAR(80);
ALTER TABLE cv_profiles ADD COLUMN IF NOT EXISTS cv_name VARCHAR(150);

-- Admin-visible audit trail for every saved CV revision.
CREATE TABLE IF NOT EXISTS cv_change_log (
    id BIGSERIAL PRIMARY KEY,
    cv_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    changed_by_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    changed_sections TEXT NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_cv_change_log_cv_user_time
    ON cv_change_log(cv_user_id, changed_at DESC);
