-- job_seeker_profiles was missing columns the job-seeker app code has always
-- queried (full_name, location, summary, skills, active_application_count,
-- status), so profile save/submit and job applications failed at the SQL
-- layer with "column does not exist".
ALTER TABLE job_seeker_profiles
    ADD COLUMN IF NOT EXISTS full_name VARCHAR(255),
    ADD COLUMN IF NOT EXISTS location VARCHAR(255),
    ADD COLUMN IF NOT EXISTS summary TEXT,
    ADD COLUMN IF NOT EXISTS skills TEXT[] DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS active_application_count INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS status VARCHAR(50) NOT NULL DEFAULT 'DRAFT';

-- ApplicationRepository has always queried `job_applications`, but only the
-- differently-shaped `applications` table (job_seeker_id-keyed) was ever
-- created, so every apply-to-job attempt failed with "relation does not
-- exist".
CREATE TABLE IF NOT EXISTS job_applications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    applicant_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    cover_note TEXT,
    status VARCHAR(50) NOT NULL DEFAULT 'APPLIED',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(job_id, applicant_user_id)
);

CREATE INDEX IF NOT EXISTS idx_job_applications_job_id ON job_applications(job_id);
CREATE INDEX IF NOT EXISTS idx_job_applications_applicant_user_id ON job_applications(applicant_user_id);
CREATE INDEX IF NOT EXISTS idx_job_applications_status ON job_applications(status);

-- Human-readable reference number (NXT-JOB-YY-000001), same scheme as
-- verifications/payments/etc — kept here instead of in
-- 20260719120000_reference_numbers.up.sql because that migration ran before
-- this table existed and unconditionally ALTERing a nonexistent table broke
-- the whole migration chain.
CREATE SEQUENCE IF NOT EXISTS job_application_number_seq;
ALTER TABLE job_applications ADD COLUMN IF NOT EXISTS reference_number VARCHAR;

CREATE OR REPLACE FUNCTION set_job_application_reference_number() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.reference_number IS NULL THEN
    NEW.reference_number := 'NXT-JOB-' || to_char(NOW(), 'YY') || '-' || lpad(nextval('job_application_number_seq')::text, 6, '0');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_job_applications_reference_number ON job_applications;
CREATE TRIGGER trg_job_applications_reference_number
  BEFORE INSERT ON job_applications
  FOR EACH ROW EXECUTE FUNCTION set_job_application_reference_number();

DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT id, created_at FROM job_applications WHERE reference_number IS NULL ORDER BY created_at, id LOOP
    UPDATE job_applications SET reference_number = 'NXT-JOB-' || to_char(r.created_at, 'YY') || '-' || lpad(nextval('job_application_number_seq')::text, 6, '0') WHERE id = r.id;
  END LOOP;
END $$;

ALTER TABLE job_applications ALTER COLUMN reference_number SET NOT NULL;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'job_applications_reference_number_key') THEN
    ALTER TABLE job_applications ADD CONSTRAINT job_applications_reference_number_key UNIQUE (reference_number);
  END IF;
END $$;

-- job_seeker_documents backs JobSeekerRepository::create_document/list_documents/
-- delete_document, referenced from apps/job_seekers but never migrated in.
CREATE TABLE IF NOT EXISTS job_seeker_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_seeker_id UUID NOT NULL REFERENCES job_seeker_profiles(id) ON DELETE CASCADE,
    document_type VARCHAR(100) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_job_seeker_documents_job_seeker_id ON job_seeker_documents(job_seeker_id);
