Job board and applicant tracking

CREATE TYPE posting_status AS ENUM ('draft', 'open', 'paused', 'filled', 'closed');

CREATE TABLE employers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    website text
);

CREATE TABLE jobs (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    employer_id uuid NOT NULL,
    title text NOT NULL,
    location text,
    remote boolean DEFAULT false NOT NULL,
    salary_min int,
    salary_max int,
    status posting_status DEFAULT 'draft' NOT NULL,
    posted_at timestamptz
);
CREATE INDEX jobs_employer_id_idx ON jobs (employer_id);

CREATE TABLE candidates (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    full_name text NOT NULL,
    resume_url text
);

CREATE TABLE stages (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    employer_id uuid NOT NULL,
    name text NOT NULL,
    position int NOT NULL,
    UNIQUE (employer_id, position)
);

CREATE TABLE applications (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    job_id uuid NOT NULL,
    candidate_id uuid NOT NULL,
    stage_id uuid NOT NULL,
    applied_at timestamptz DEFAULT now() NOT NULL,
    UNIQUE (job_id, candidate_id)
);
CREATE INDEX applications_candidate_id_idx ON applications (candidate_id);
CREATE INDEX applications_stage_id_idx ON applications (stage_id);

CREATE TABLE interviews (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    application_id uuid NOT NULL,
    scheduled_at timestamptz NOT NULL,
    interviewer text NOT NULL,
    rating int,
    notes text,
    CHECK (rating between 1 and 5)
);
CREATE INDEX interviews_application_id_idx ON interviews (application_id);

CREATE TABLE skills (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE job_skills (
    job_id uuid NOT NULL,
    skill_id uuid NOT NULL,
    PRIMARY KEY (job_id, skill_id)
);
CREATE INDEX job_skills_skill_id_idx ON job_skills (skill_id);

ALTER TABLE jobs ADD FOREIGN KEY (employer_id) REFERENCES employers (id) ON DELETE CASCADE;

ALTER TABLE stages ADD FOREIGN KEY (employer_id) REFERENCES employers (id) ON DELETE CASCADE;

ALTER TABLE applications ADD FOREIGN KEY (job_id) REFERENCES jobs (id) ON DELETE CASCADE;

ALTER TABLE applications ADD FOREIGN KEY (candidate_id) REFERENCES candidates (id) ON DELETE CASCADE;

ALTER TABLE applications ADD FOREIGN KEY (stage_id) REFERENCES stages (id);

ALTER TABLE interviews ADD FOREIGN KEY (application_id) REFERENCES applications (id) ON DELETE CASCADE;

ALTER TABLE job_skills ADD FOREIGN KEY (job_id) REFERENCES jobs (id) ON DELETE CASCADE;

ALTER TABLE job_skills ADD FOREIGN KEY (skill_id) REFERENCES skills (id) ON DELETE CASCADE;