— ==========================================
— 1. DATABASE CREATION
— ==========================================
— Note: Run this first, then connect to mono_db to run the rest.
CREATE DATABASE mono_db;

— ==========================================
— 2. EXTENSIONS & UTILITY FUNCTIONS
— ==========================================
— Required for gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS “pgcrypto”;

— Function to handle automatic updated_at timestamps
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ language ‘plpgsql’;

— ==========================================
— 3. TABLE DEFINITIONS
— ==========================================

— Table: public.cv_users
CREATE TABLE IF NOT EXISTS public.cv_users (
id SERIAL PRIMARY KEY,
email character varying(255) NOT NULL UNIQUE,
password_hash text NOT NULL,
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

— Table: public.refresh_tokens
CREATE TABLE IF NOT EXISTS public.refresh_tokens (
id SERIAL PRIMARY KEY,
user_id integer REFERENCES public.cv_users(id) ON DELETE CASCADE,
token text NOT NULL,
expires_at timestamp without time zone NOT NULL,
revoked boolean DEFAULT false
);

— Table: public.token_usage
CREATE TABLE IF NOT EXISTS public.token_usage (
id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
user_email character varying(255) NOT NULL,
operation character varying(100) NOT NULL,
input_tokens integer NOT NULL DEFAULT 0,
output_tokens integer NOT NULL DEFAULT 0,
cost_usd numeric(12,8) NOT NULL DEFAULT 0,
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

— Index for token_usage
CREATE INDEX IF NOT EXISTS idx_token_usage_user_email ON public.token_usage (user_email);

— Table: public.job_posts
CREATE TABLE IF NOT EXISTS public.job_posts (
id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
job_title character varying(255) NOT NULL,
company character varying(255) NOT NULL,
job_type character varying(100) NOT NULL,
location character varying(255) NOT NULL,
description text,
qualifications jsonb,
responsibilities jsonb,
benefits jsonb,
posted_by character varying(255) DEFAULT ‘HR Admin’,
status character varying(50) DEFAULT ‘active’,
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
gender character varying(10) DEFAULT ‘Any’,
skipped_count integer DEFAULT 0
);

— Trigger for job_posts
CREATE TRIGGER update_job_posts_updated_at
BEFORE UPDATE ON public.job_posts
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

— Table: public.applicants
CREATE TABLE IF NOT EXISTS public.applicants (
id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
job_id character varying(255) NOT NULL,
job_title character varying(255) NOT NULL,
name character varying(255) NOT NULL,
email character varying(255) NOT NULL,
phone character varying(50),
cv_text text,
personal_info jsonb,
skills jsonb,
experience jsonb,
education jsonb,
summary text,
status character varying(50) DEFAULT ‘pending’,
extraction_status character varying(50) DEFAULT ‘processing’,
applied_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

— Indexes for applicants
CREATE INDEX IF NOT EXISTS idx_applicants_email ON public.applicants (email);
CREATE INDEX IF NOT EXISTS idx_applicants_job_id ON public.applicants (job_id);
CREATE INDEX IF NOT EXISTS idx_applicants_job_title ON public.applicants (job_title);
CREATE INDEX IF NOT EXISTS idx_applicants_status ON public.applicants (status);

— Trigger for applicants
CREATE TRIGGER update_applicants_updated_at
BEFORE UPDATE ON public.applicants
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();