DDL: kreiranje.sql

-- Drop tables if they exist
DROP TABLE IF EXISTS LineItem CASCADE;
DROP TABLE IF EXISTS Invoice CASCADE;
DROP TABLE IF EXISTS EmailTemplate CASCADE;
DROP TABLE IF EXISTS Employee CASCADE;
DROP TABLE IF EXISTS Candidate CASCADE;
DROP TABLE IF EXISTS Client CASCADE;
DROP TABLE IF EXISTS User CASCADE;
DROP TABLE IF EXISTS Company CASCADE;

-- Drop schema if it exists
DROP SCHEMA IF EXISTS project CASCADE;

-- Create schema
CREATE SCHEMA project;

-- Create tables
CREATE TABLE Company (
    company_id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(4000),
    vat VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE User (
    user_id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(50) CHECK (role IN ('Admin', 'HR', 'Finance Manager')) NOT NULL,
    company_id BIGINT NOT NULL REFERENCES Company(company_id)
);

CREATE TABLE Employee (
    employee_id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    status VARCHAR(50) CHECK (status IN ('Active', 'Inactive', 'Terminated')) NOT NULL,
    iban VARCHAR(255),
    cv VARCHAR(4000),
    photo VARCHAR(4000),
    notes TEXT,
    project VARCHAR(255),
    company_id BIGINT NOT NULL REFERENCES Company(company_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Candidate (
    candidate_id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    status VARCHAR(50) CHECK (status IN ('Applied', 'Interview', 'Offered', 'Rejected')) NOT NULL,
    cv VARCHAR(4000),
    interview_stage VARCHAR(255),
    notes TEXT,
    company_id BIGINT NOT NULL REFERENCES Company(company_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Client (
    client_id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    representative VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address VARCHAR(4000),
    vat VARCHAR(255),
    logo VARCHAR(4000),
    company_id BIGINT NOT NULL REFERENCES Company(company_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Invoice (
    invoice_id BIGINT PRIMARY KEY,
    from VARCHAR(255) NOT NULL,
    to VARCHAR(255) NOT NULL,
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    notes TEXT,
    status VARCHAR(50) CHECK (status IN ('Draft', 'Pending', 'Paid', 'Overdue')) NOT NULL,
    company_id BIGINT NOT NULL REFERENCES Company(company_id),
    client_id BIGINT NOT NULL REFERENCES Client(client_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE LineItem (
    line_item_id BIGINT PRIMARY KEY,
    description TEXT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0),
    currency VARCHAR(10) NOT NULL,
    total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
    invoice_id BIGINT NOT NULL REFERENCES Invoice(invoice_id)
);

CREATE TABLE EmailTemplate (
    template_id BIGINT PRIMARY KEY,
    type VARCHAR(50) CHECK (type IN ('New Invoice', 'Invoice Overdue', 'Welcome Employee', 'Candidate Interview Offer', 'Candidate Rejected', 'Client Welcome')) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    company_id BIGINT NOT NULL REFERENCES Company(company_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);