CREATE DATABASE IF NOT EXISTS EPMS;
USE EPMS;

CREATE TABLE IF NOT EXISTS `user` (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS department (
  departmentCode VARCHAR(50) PRIMARY KEY,
  departmentName VARCHAR(100) NOT NULL,
  grossSalary DECIMAL(10, 2) NOT NULL
);

CREATE TABLE IF NOT EXISTS employee (
  id INT AUTO_INCREMENT PRIMARY KEY,
  firstName VARCHAR(100) NOT NULL,
  lastName VARCHAR(100) NOT NULL,
  position VARCHAR(100) NOT NULL,
  address VARCHAR(255) NOT NULL,
  telephone VARCHAR(30) NOT NULL,
  gender VARCHAR(20) NOT NULL,
  hiredDate DATE NOT NULL,
  department VARCHAR(50) NOT NULL,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_employee_department
    FOREIGN KEY (department)
    REFERENCES department(departmentCode)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS salary (
  id INT AUTO_INCREMENT PRIMARY KEY,
  grossSalary DECIMAL(10, 2) NOT NULL,
  totalDeduction DECIMAL(10, 2) NOT NULL,
  netSalary DECIMAL(10, 2) NOT NULL,
  month DATE NOT NULL,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
