-- 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;

-- Create tables
CREATE TABLE Company (
    company_id BIGINT PRIMARY KEY,
    company_no VARCHAR(255) NOT NULL,
    company_name VARCHAR(255) NOT NULL,
    company_representative VARCHAR(255) NOT NULL,
    company_address JSONB,
    company_bank_accounts JSONB,
    company_vat VARCHAR(255),
    company_email VARCHAR(255) NOT NULL,
    company_logo_url VARCHAR(4000),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE "User" (
    user_id BIGINT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    user_email VARCHAR(255) UNIQUE NOT NULL,
    user_role VARCHAR(50) CHECK (user_role IN ('Admin', 'HR', 'Finance Manager')) NOT NULL,
    company_id BIGINT NOT NULL REFERENCES Company(company_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Employee (
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(255) NOT NULL,
    employee_email VARCHAR(255) UNIQUE NOT NULL,
    employee_role VARCHAR(255),
    employee_status VARCHAR(50) CHECK (employee_status IN ('Active', 'Inactive', 'Terminated')) NOT NULL,
    employee_address JSONB,
    employee_iban: VARCHAR(255),
    employee_cv_ref VARCHAR(4000),
    employee_photo_ref 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 Candidate (
    candidate_id BIGINT PRIMARY KEY,
    candidate_name VARCHAR(255) NOT NULL,
    candidate_email VARCHAR(255) UNIQUE NOT NULL,
    candidate_status VARCHAR(50) CHECK (candidate_status IN ('Applied', 'Interview', 'Offered', 'Rejected')) NOT NULL,
    candidate_cv_ref VARCHAR(4000),
    candidate_stage VARCHAR(255),
    candidate_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,
    client_name VARCHAR(255) NOT NULL,
    client_representative VARCHAR(255) NOT NULL,
    client_email VARCHAR(255) UNIQUE NOT NULL,
    client_address JSONB,
    client_vat VARCHAR(255),
    client_logo_url 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,
    invoice_no BIGINT NOT NULL,
    invoice_issue_date DATE NOT NULL,
    invoice_due_date DATE NOT NULL,
    invoice_notes TEXT,
    invoice_status VARCHAR(50) CHECK (invoice_status IN ('Draft', 'Pending', 'Paid', 'Overdue')) NOT NULL,
    invoice_currency VARCHAR(10),
    invoice_taxes DECIMAL(10, 2),
    invoice_quantity_type VARCHAR(50),
    invoice_discount DECIMAL(10, 2),
    invoice_month VARCHAR(50),
    invoice_pdf_ref VARCHAR(4000),
    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 (
    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
);
