---

CREATE DATABASE IF NOT EXISTS document_flow
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE document_flow;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500)
) ENGINE=InnoDB;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    position VARCHAR(100),
    department_id INT,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
) ENGINE=InnoDB;

CREATE TABLE document_types (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500)
) ENGINE=InnoDB;

CREATE TABLE document_statuses (
    status_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(300)
) ENGINE=InnoDB;

CREATE TABLE documents (
    document_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    creation_date DATE,
    content TEXT,
    type_id INT,
    status_id INT,
    author_id INT,
    CONSTRAINT fk_document_type
        FOREIGN KEY (type_id)
        REFERENCES document_types(type_id),
    CONSTRAINT fk_document_status
        FOREIGN KEY (status_id)
        REFERENCES document_statuses(status_id),
    CONSTRAINT fk_document_author
        FOREIGN KEY (author_id)
        REFERENCES employees(employee_id)
) ENGINE=InnoDB;

CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(500) NOT NULL,
    creation_date DATE,
    due_date DATE,
    document_id INT,
    employee_id INT,
    CONSTRAINT fk_task_document
        FOREIGN KEY (document_id)
        REFERENCES documents(document_id),
    CONSTRAINT fk_task_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
) ENGINE=InnoDB;

CREATE TABLE document_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    action_date DATETIME,
    action_description VARCHAR(250),
    document_id INT,
    employee_id INT,
    CONSTRAINT fk_history_document
        FOREIGN KEY (document_id)
        REFERENCES documents(document_id),
    CONSTRAINT fk_history_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
) ENGINE=InnoDB;