database.sql

#drop schema Translation;
create schema Translation;
use Translation;


CREATE TABLE translators (
    id int primary key not null auto_increment,
    email varchar(255) UNIQUE NOT NULL,
    pwd Text NOT NULL
);

CREATE TABLE companies (
	id int primary key not null auto_increment,
    name_company varchar(255) not null,
    created_by int,
    foreign key (created_by) references translators(id)
);

CREATE TABLE models (
    id int primary key not null auto_increment,
    name_model VARCHAR(255) NOT NULL,
    base_model VARCHAR(100), -- e.g., T5, MarianMT
    company_id INT,
    trained_at TIMESTAMP,
    status VARCHAR(50) DEFAULT 'untrained', -- or 'trained'
    foreign key (company_id) REFERENCES companies(id)
);

CREATE TABLE documents ( -- This is the translated document
    id int primary key not null auto_increment,
    name_document VARCHAR(255),
    based_on VARCHAR(255), --  original document 
    tokens_file VARCHAR(255),
	file_path TEXT, -- Path to the documents
    model_id INT,
    foreign key (model_id) references models(id)
);

CREATE TABLE segments (
    document_id INT NOT NULL,         -- Part of the composite primary key
    unit_id INT NOT NULL,             -- User-provided ID, part of the composite primary key
    original_text TEXT,
    translated_text TEXT,
    reviewed_text TEXT,               -- Optional: post-edited by user
    status_segment ENUM('NotStarted', 'ManuallyConfirmed', 'Edited') DEFAULT 'NotStarted', -- <--- IMPORTANT: REPLACE WITH YOUR ACTUAL STATES
    comment_segment TEXT,
    PRIMARY KEY (document_id, unit_id), -- Defines the composite primary key
    FOREIGN KEY (document_id) REFERENCES documents(id)
);

insert into translators (email, pwd) values ('test@idisc.com', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4');
insert into companies (name_company, created_by) values ('Mitsubishi', 1);
insert into models (name_model, base_model, company_id) values ('Car Manuals', 'MarianMT', 1);