Untitled

CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    address VARCHAR(255),
    hire_date DATE NOT NULL,
    department_id INT REFERENCES Departments(department_id)
);


CREATE TABLE Departments (
    department_id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL
);


CREATE TABLE Schedules (
    schedule_id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES Employees(employee_id),
    shift_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    status VARCHAR(20) CHECK (status IN ('Scheduled', 'Completed', 'Cancelled')) DEFAULT 'Scheduled'
);


CREATE TABLE Payroll (
    payroll_id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES Employees(employee_id),
    pay_date DATE NOT NULL,
    hours_worked DECIMAL(5, 2) NOT NULL,
    pay_rate DECIMAL(10, 2) NOT NULL,
    gross_pay DECIMAL(10, 2) NOT NULL,
    net_pay DECIMAL(10, 2) NOT NULL
);


CREATE TABLE Shifts (
    shift_id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES Employees(employee_id),
    name VARCHAR(50) UNIQUE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);