CREATE DATABASE IF NOT EXISTS concat
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

use concat;

CREATE TABLE categories (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE clients (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  phone varchar(20) NOT NULL,
  email varchar(255) NOT NULL,
  company varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE departments (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE roles (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE employees (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  email varchar(255) NOT NULL UNIQUE,
  phone varchar(20) NOT NULL UNIQUE,
  code int(11) DEFAULT NULL UNIQUE,
  password varchar(255) NOT NULL,
  image varchar(255) DEFAULT NULL,
  department_id int(11) NOT NULL,
  status enum('active','inactive','terminated') DEFAULT 'active',
  role_id int(11) NOT NULL,
  CONSTRAINT fk_employees_departments FOREIGN KEY (department_id) REFERENCES departments (id) ON UPDATE CASCADE,
  CONSTRAINT fk_employees_roles FOREIGN KEY (role_id) REFERENCES roles (id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE projects (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL COLLATE utf8mb4_general_ci,
  description text DEFAULT NULL COLLATE utf8mb4_general_ci,
  client_id int(11) NOT NULL,
  estimated_deadline datetime DEFAULT NULL,
  added_by int(11) NOT NULL,
  category_id int(11) NOT NULL,
  created_at datetime DEFAULT current_timestamp(),
  status enum('pending','cancelled','completed','processing') COLLATE utf8mb4_general_ci DEFAULT 'pending',
  CONSTRAINT fk_projects_categories FOREIGN KEY (category_id) REFERENCES categories (id) ON UPDATE CASCADE,
  CONSTRAINT fk_projects_clients FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_projects_employee FOREIGN KEY (added_by) REFERENCES employees (id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE media (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  source varchar(255) NOT NULL,
  project_id int(11) NOT NULL,
  CONSTRAINT fk_media_projects FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE projects_departments (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  assigned_at datetime DEFAULT current_timestamp(),
  project_id int(11) NOT NULL,
  department_id int(11) NOT NULL,
  CONSTRAINT fk_projectsdepartments_project FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_projectsdepartments_departments FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE tasks_statuses(
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE tasks (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  assigned_at datetime DEFAULT current_timestamp(),
  last_assigned_to int DEFAULT NULL,
  added_by int(11) NOT NULL,
  estimated_deadline datetime DEFAULT NULL,
  title varchar(255) NOT NULL,
  description text DEFAULT NULL,
  project_id int(11) NOT NULL,
  status_id int(11) NOT NULL,
  CONSTRAINT fk_tasks_last_assigned FOREIGN KEY (last_assigned_to) REFERENCES employees (id) ON UPDATE CASCADE,
  CONSTRAINT fk_tasks_statuses FOREIGN KEY (status_id) REFERENCES tasks_statuses (id) ON UPDATE CASCADE,
  CONSTRAINT fk_tasks_projects FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_tasks_assignedby FOREIGN KEY (added_by) REFERENCES employees (id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE employees_tasks (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  assigned_at datetime DEFAULT current_timestamp(),
  assigned_to int(11) NOT NULL,
  task_id int(11) NOT NULL,
  assigned_by int(11) NOT NULL,
  note text DEFAULT NULL,
  CONSTRAINT fk_employeestasks_assignedby FOREIGN KEY (assigned_by) REFERENCES employees (id) ON UPDATE CASCADE,
  CONSTRAINT fk_employeestasks_assignedto FOREIGN KEY (assigned_to) REFERENCES employees (id) ON UPDATE CASCADE,
  CONSTRAINT fk_employeestasks_tasks FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE tokens (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  token varchar(255) NOT NULL,
  employee_id int(11) NOT NULL,
  created_at datetime DEFAULT current_timestamp(),
  expires_at datetime NOT NULL,
  CONSTRAINT fk_tokens_employees FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;