100 lines
2.5 KiB
SQL
100 lines
2.5 KiB
SQL
-- Create enums for states (using German names), countries (using ISO codes for i18n), and language levels
|
|
CREATE TYPE app.german_state AS ENUM (
|
|
'Baden-Württemberg',
|
|
'Bayern',
|
|
'Berlin',
|
|
'Brandenburg',
|
|
'Bremen',
|
|
'Hamburg',
|
|
'Hessen',
|
|
'Mecklenburg-Vorpommern',
|
|
'Niedersachsen',
|
|
'Nordrhein-Westfalen',
|
|
'Rheinland-Pfalz',
|
|
'Saarland',
|
|
'Sachsen',
|
|
'Sachsen-Anhalt',
|
|
'Schleswig-Holstein',
|
|
'Thüringen'
|
|
);
|
|
|
|
CREATE TYPE app.education_country AS ENUM (
|
|
'AL', -- Albania
|
|
'BA', -- Bosnia and Herzegovina
|
|
'IN', -- India
|
|
'MX', -- Mexico
|
|
'PH', -- Philippines
|
|
'SY', -- Syria
|
|
'TN', -- Tunisia
|
|
'TR', -- Turkey
|
|
'UA', -- Ukraine
|
|
'VN' -- Vietnam
|
|
);
|
|
|
|
CREATE TYPE app.language_level AS ENUM (
|
|
'A_ONE',
|
|
'A_TWO',
|
|
'B_ONE',
|
|
'B_TWO',
|
|
'C_ONE',
|
|
'C_TWO'
|
|
);
|
|
|
|
CREATE TYPE app.candidate_document_type AS ENUM (
|
|
'education',
|
|
'transcript',
|
|
'license',
|
|
'language',
|
|
'work_experience'
|
|
);
|
|
|
|
CREATE TABLE app.candidate (
|
|
candidate_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
email VARCHAR NOT NULL,
|
|
name VARCHAR NOT NULL,
|
|
|
|
-- Basic info
|
|
state_work_preference app.german_state[],
|
|
open_to_work BOOLEAN,
|
|
living_in_germany BOOLEAN,
|
|
eu_passport BOOLEAN,
|
|
country_education app.education_country,
|
|
|
|
-- License info
|
|
license_provided BOOLEAN,
|
|
|
|
-- Language info
|
|
language_certificate_provided BOOLEAN,
|
|
language_self_assessment_level app.language_level,
|
|
|
|
submitted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
last_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE app.candidate_document (
|
|
document_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
candidate_id UUID NOT NULL REFERENCES app.candidate,
|
|
category app.candidate_document_type NOT NULL,
|
|
title VARCHAR,
|
|
note VARCHAR,
|
|
file_path VARCHAR NOT NULL,
|
|
file_name VARCHAR NOT NULL,
|
|
file_size INTEGER,
|
|
mime_type VARCHAR,
|
|
uploaded BOOLEAN DEFAULT FALSE,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
last_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Add triggers for timestamp updates
|
|
CREATE TRIGGER update_candidate_timestamp
|
|
BEFORE UPDATE ON app.candidate
|
|
FOR EACH ROW EXECUTE PROCEDURE app.update_lastupdated_timestamp();
|
|
|
|
CREATE TRIGGER update_candidate_document_timestamp
|
|
BEFORE UPDATE ON app.candidate_document
|
|
FOR EACH ROW EXECUTE PROCEDURE app.update_lastupdated_timestamp();
|