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

use concat;

CREATE TABLE IF NOT EXISTS departments(
    id int AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS roles(
    id int AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS employees(
    id int AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL UNIQUE,
    code int UNIQUE,
    password VARCHAR(255) NOT NULL,
    image VARCHAR(255),
    department_id int NOT NULL,
    status enum('active', 'inactive', 'terminated') default 'active',
    role_id int NOT NULL,

    CONSTRAINT fk_employees_departments FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_employees_roles FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS clients(
    id int AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255) NOT NULL,
    company VARCHAR(255)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS categories(
    id int PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS projects(
    id int AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description text,
    client_id int NOT NULL,
    estimated_deadline DATETIME,
    added_by int NOT NULL,
    category_id int NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    status enum('pending', 'cancelled', 'completed', 'processing') DEFAULT 'pending',

    CONSTRAINT fk_projects_clients FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_projects_categories FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_projects_employee FOREIGN KEY(added_by) REFERENCES employees(id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS projects_departments(
    id int AUTO_INCREMENT PRIMARY KEY,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    description text,
    project_id int NOT NULL,
    department_id int 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;

CREATE TABLE IF NOT EXISTS tasks(
    id int AUTO_INCREMENT PRIMARY KEY,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    added_by int NOT NULL,
    estimated_deadline DATETIME,
    title VARCHAR(255) NOT NULL,
    description text,
    project_id int NOT NULL,
    status enum('assigned', 'accepted', 'started', 'ended', 'reviewed', 'completed', 'rejected') DEFAULT 'assigned',

    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 DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS employees_tasks(
    id int AUTO_INCREMENT PRIMARY KEY,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    unassigned_at DATETIME,
    assigned_to int NOT NULL,
    task_id int NOT NULL,
    assigned_by int NOT NULL,
    note text,

    CONSTRAINT fk_employeestasks_assignedby FOREIGN KEY(assigned_by) REFERENCES employees(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_employeestasks_tasks FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_employeestasks_assignedto FOREIGN KEY(assigned_to) REFERENCES employees(id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS media(
    id int AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(255) NOT NULL,
    project_id int NOT NULL,

    CONSTRAINT fk_media_projects FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tokens(
    id int AUTO_INCREMENT PRIMARY KEY,
    token VARCHAR(255) NOT NULL UNIQUE,
    employee_id int 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;