init.sql3.12 kB
-- Use the prospectio database
\c prospectio;
-- Create companies table
CREATE TABLE IF NOT EXISTS companies (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT,
industry TEXT,
compatibility TEXT,
source TEXT,
location TEXT,
size TEXT,
revenue TEXT,
website TEXT,
description TEXT,
opportunities TEXT[],
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Create jobs table
CREATE TABLE IF NOT EXISTS jobs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
date_creation TIMESTAMP WITH TIME ZONE,
description TEXT,
job_title TEXT,
location TEXT,
salary TEXT,
job_seniority TEXT,
job_type TEXT,
sectors TEXT,
apply_url TEXT[],
compatibility_score INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Create contacts table
CREATE TABLE IF NOT EXISTS contacts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
job_id UUID REFERENCES jobs(id) ON DELETE CASCADE,
name TEXT,
email TEXT[],
title TEXT,
phone TEXT,
profile_url TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Create profiles table
CREATE TABLE IF NOT EXISTS profile (
id SERIAL PRIMARY KEY,
job_title TEXT,
location TEXT,
bio TEXT,
work_experience JSON,
technos TEXT[],
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_jobs_company_id ON jobs(company_id);
CREATE INDEX IF NOT EXISTS idx_contacts_company_id ON contacts(company_id);
CREATE INDEX IF NOT EXISTS idx_contacts_job_id ON contacts(job_id);
CREATE INDEX IF NOT EXISTS idx_companies_name ON companies(name);
CREATE INDEX IF NOT EXISTS idx_jobs_title ON jobs(job_title);
CREATE INDEX IF NOT EXISTS idx_contacts_email ON contacts(email);
-- Create function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ language 'plpgsql';
-- Create triggers to automatically update updated_at column
CREATE TRIGGER update_companies_updated_at
BEFORE UPDATE ON companies
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_jobs_updated_at
BEFORE UPDATE ON jobs
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_contacts_updated_at
BEFORE UPDATE ON contacts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_profiles_updated_at
BEFORE UPDATE ON profile
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();