HR and employee directory

CREATE TYPE leave_status AS ENUM ('requested', 'approved', 'rejected', 'cancelled');

CREATE TABLE departments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE,
    parent_id uuid
);
CREATE INDEX departments_parent_id_idx ON departments (parent_id);

CREATE TABLE positions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    title text NOT NULL,
    department_id uuid NOT NULL,
    level int DEFAULT 1 NOT NULL
);
CREATE INDEX positions_department_id_idx ON positions (department_id);

CREATE TABLE employees (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    full_name text NOT NULL,
    position_id uuid NOT NULL,
    manager_id uuid,
    hired_on date NOT NULL,
    left_on date
);
CREATE INDEX employees_manager_idx ON employees (manager_id);
CREATE INDEX employees_position_id_idx ON employees (position_id);

CREATE TABLE leave_types (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE,
    paid boolean DEFAULT true NOT NULL,
    annual_days int DEFAULT 0 NOT NULL
);

CREATE TABLE leave_balances (
    employee_id uuid NOT NULL,
    leave_type_id uuid NOT NULL,
    year int NOT NULL,
    days_remaining numeric(5,1) NOT NULL,
    PRIMARY KEY (employee_id, leave_type_id, year)
);
CREATE INDEX leave_balances_leave_type_id_idx ON leave_balances (leave_type_id);

CREATE TABLE leave_requests (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    employee_id uuid NOT NULL,
    leave_type_id uuid NOT NULL,
    approver_id uuid,
    starts_on date NOT NULL,
    ends_on date NOT NULL,
    status leave_status DEFAULT 'requested' NOT NULL,
    CHECK (ends_on >= starts_on)
);
CREATE INDEX leave_requests_employee_id_idx ON leave_requests (employee_id);
CREATE INDEX leave_requests_leave_type_id_idx ON leave_requests (leave_type_id);
CREATE INDEX leave_requests_approver_id_idx ON leave_requests (approver_id);

ALTER TABLE departments ADD FOREIGN KEY (parent_id) REFERENCES departments (id) ON DELETE SET NULL;

ALTER TABLE positions ADD FOREIGN KEY (department_id) REFERENCES departments (id);

ALTER TABLE employees ADD FOREIGN KEY (position_id) REFERENCES positions (id);

ALTER TABLE employees ADD FOREIGN KEY (manager_id) REFERENCES employees (id) ON DELETE SET NULL;

ALTER TABLE leave_balances ADD FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE;

ALTER TABLE leave_balances ADD FOREIGN KEY (leave_type_id) REFERENCES leave_types (id);

ALTER TABLE leave_requests ADD FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE;

ALTER TABLE leave_requests ADD FOREIGN KEY (leave_type_id) REFERENCES leave_types (id);

ALTER TABLE leave_requests ADD FOREIGN KEY (approver_id) REFERENCES employees (id) ON DELETE SET NULL;