techfixDB.sql

create table techfix_chat_message
(
    id         binary(16)   not null
        primary key,
    created_at datetime(6)  not null,
    updated_at datetime(6)  not null,
    content    text         null,
    role       varchar(255) not null,
    user_id    binary(16)   not null,
    image_url  longtext     null
)
    engine = InnoDB;

create table techfix_client
(
    id                binary(16)                                                        not null
        primary key,
    created_at        datetime(6)                                                       not null,
    updated_at        datetime(6)                                                       not null,
    address           varchar(255)                                                      not null,
    code              varchar(255)                                                      not null,
    first_name        varchar(255)                                                      not null,
    last_interaction  datetime(6)                                                       null,
    last_name         varchar(255)                                                      not null,
    legal_type        enum ('PARTICULAR', 'PROFESSIONAL', 'ORGANIZATION', 'GOVERNMENT') not null,
    loyalty_points    int                                                               not null,
    notes             text                                                              null,
    phone_number1     varchar(255)                                                      not null,
    phone_number2     varchar(255)                                                      null,
    preferred_contact enum ('EMAIL', 'PHONE', 'SMS') default 'EMAIL'                    not null,
    sentiment_score   float                                                             null,
    tax_number        varchar(255)                                                      null,
    email             varchar(255)                                                      null,
    company_id        binary(16)                                                        not null,
    workshop_id       binary(16)                                                        not null,
    constraint UKa7shm927ox1coqsb9dsmlicgb
        unique (code),
    constraint FK6c0dxn09ki9vvpdnhe8x1c8ys
        foreign key (workshop_id) references techfix_workshop (id),
    constraint FKe4r0vi6h8xhgc9p7xjqkrw0e4
        foreign key (company_id) references techfix_company (id)
)
    engine = InnoDB;

create table techfix_company
(
    id              binary(16)   not null
        primary key,
    created_at      datetime(6)  not null,
    updated_at      datetime(6)  not null,
    contact_email   varchar(255) not null,
    industry        varchar(255) null,
    legal_signature varchar(255) null,
    logo_path       varchar(255) null,
    name            varchar(255) not null,
    phone_number    varchar(255) not null,
    tva             varchar(255) null,
    website         varchar(255) null,
    address         varchar(255) not null,
    stamp_path      varchar(255) null,
    tax_number      varchar(255) not null,
    timezone        varchar(255) not null
)
    engine = InnoDB;

create table techfix_invoice
(
    id             binary(16)                                                                              not null
        primary key,
    created_at     datetime(6)                                                                             not null,
    updated_at     datetime(6)                                                                             not null,
    amount         decimal(10, 2)                                                                          not null,
    discount       decimal(10, 2)                                                                          not null,
    due_date       date                                                                                    not null,
    invoice_number varchar(255)                                                                            not null,
    notes          text                                                                                    null,
    payment_terms  varchar(255)                                                                            not null,
    status         enum ('DRAFT', 'PENDING', 'PARTIALLY_PAID', 'PAID', 'OVERDUE', 'CANCELLED', 'REFUNDED') not null,
    tax            decimal(10, 2)                                                                          not null,
    total          decimal(10, 2)                                                                          not null,
    ticket_id      binary(16)                                                                              not null,
    constraint UKbucwcnca8fjw5g2qfb2o4femq
        unique (invoice_number),
    constraint FKklw2c4p71te4qce90qd3h6hhp
        foreign key (ticket_id) references techfix_ticket (id)
)
    engine = InnoDB;
create table techfix_kpi_aggregations
(
    id                               binary(16)  not null
        primary key,
    created_at                       datetime(6) not null,
    updated_at                       datetime(6) not null,
    average_repair_time              double      not null,
    company_id                       binary(16)  null,
    in_progress_tickets              bigint      not null,
    new_clients                      bigint      not null,
    returning_clients                bigint      not null,
    tech_performance                 double      not null,
    tickets_per_hour                 bigint      not null,
    workshop_efficiency              double      not null,
    workshop_id                      binary(16)  null,
    average_repair_time_for_tech     double      null,
    average_repair_time_for_workshop double      null,
    average_tickets_per_tech         double      null,
    completed_tickets                bigint      null,
    completed_tickets_for_tech       bigint      null,
    completion_ratio                 double      null,
    completion_ratio_for_tech        double      null,
    completion_ratio_for_workshop    double      null,
    tickets_last_hour                bigint      null
)
    engine = InnoDB;

create table techfix_kpi_techs
(
    id                  binary(16)  not null
        primary key,
    created_at          datetime(6) not null,
    updated_at          datetime(6) not null,
    average_repair_time double      null,
    completed_tickets   bigint      null,
    completion_ratio    double      null,
    tech_id             binary(16)  null,
    kpi_aggregation_id  binary(16)  null,
    constraint FKlje4lcy4r8gaiyevmslmniyfy
        foreign key (kpi_aggregation_id) references techfix_kpi_aggregations (id)
)
    engine = InnoDB;

create table techfix_kpi_workshops
(
    id                  binary(16)  not null
        primary key,
    created_at          datetime(6) not null,
    updated_at          datetime(6) not null,
    average_repair_time double      null,
    completion_ratio    double      null,
    workshop_id         binary(16)  null,
    kpi_aggregation_id  binary(16)  null,
    constraint FKtdix3xi6mqu9adqy2jqndcurt
        foreign key (kpi_aggregation_id) references techfix_kpi_aggregations (id)
)
    engine = InnoDB;

create table techfix_notifications
(
    id                  binary(16)                                                                                   not null
        primary key,
    created_at          datetime(6)                                                                                  not null,
    updated_at          datetime(6)                                                                                  not null,
    action_type         enum ('ACKNOWLEDGE', 'CONFIRM_EMAIL_CLIENT', 'GENERATE_INVOICE', 'INFO_ONLY', 'OPEN_TICKET') not null,
    is_read             bit                                                                                          not null,
    message             text                                                                                         not null,
    related_entity_id   binary(16)                                                                                   null,
    related_entity_type varchar(255)                                                                                 null,
    title               varchar(255)                                                                                 not null,
    type                enum ('ERROR', 'INFO', 'SUCCESS', 'WARNING')                                                 not null,
    user_id             binary(16)                                                                                   not null,
    constraint FKbs45xfnrdu5eoap1c2gv8pemj
        foreign key (user_id) references techfix_user (id)
)
    engine = InnoDB;

create table techfix_payment
(
    id              binary(16)                                      not null
        primary key,
    created_at      datetime(6)                                     not null,
    updated_at      datetime(6)                                     not null,
    amount          decimal(10, 2)                                  not null,
    method          enum ('BANK_TRANSFER', 'CARD', 'CASH', 'OTHER') not null,
    notes           text                                            null,
    paid_date       datetime(6)                                     not null,
    transaction_ref varchar(255)                                    null,
    invoice_id      binary(16)                                      not null,
    constraint FKkc14238dwsetxbxofvp0jmgb9
        foreign key (invoice_id) references techfix_invoice (id)
)
    engine = InnoDB;

create table techfix_ticket
(
    id                       binary(16)                                                                                                                          not null
        primary key,
    created_at               datetime(6)                                                                                                                         not null,
    updated_at               datetime(6)                                                                                                                         not null,
    accessories              text                                                                                                                                null,
    actual_completion_date   datetime(6)                                                                                                                         null,
    actual_duration          int                                                                                                                                 null,
    ai_diagnosis             text                                                                                                                                null,
    code                     varchar(255)                                                                                                                        not null,
    designation              varchar(255)                                                                                                                        not null,
    device_model             varchar(255)                                                                                                                        not null,
    device_reference         varchar(255)                                                                                                                        null,
    device_status            enum ('NEW', 'DAMAGED', 'USED')                                                                                                     not null,
    device_type              enum ('PC', 'LAPTOP', 'MOBILE', 'TABLET', 'SERVER', 'NETWORK_EQUIPMENT', 'MEDICAL', 'PRINTER', 'AUDIO_VIDEO', 'APPLIANCE', 'OTHER') not null,
    estimated_cost           decimal(10, 2)                                                                                                                      null,
    estimated_duration       int                                                                                                                                 null,
    expected_completion_date datetime(6)                                                                                                                         null,
    initial_diagnosis        text                                                                                                                                null,
    priority                 enum ('HIGH', 'LOW', 'MEDIUM', 'URGENT')                                                                                            not null,
    problem_description      text                                                                                                                                not null,
    qr_code                  varchar(255)                                                                                                                        not null,
    status                   enum ('TO_DO', 'DIAGNOSED', 'IN_PROGRESS', 'COMPLETED', 'BLOCKED', 'CLOSED', 'CANCELLED', 'AWAITING_PAYMENT', 'PAID')               not null,
    type                     enum ('CUSTOM_WORK', 'UPGRADE', 'INSPECTION', 'INSTALLATION', 'WARRANTY', 'MAINTENANCE')                                            not null,
    warranty_expiry          date                                                                                                                                null,
    assigned_tech_id         binary(16)                                                                                                                          null,
    client_id                binary(16)                                                                                                                          not null,
    workshop_id              binary(16)                                                                                                                          not null,
    total_cost               decimal(38, 2)                                                                                                                      not null,
    completed_at             datetime(6)                                                                                                                         null,
    constraint UKqghq0cyt8a2j6snuq9nff4v1g
        unique (qr_code),
    constraint UKqvg01guakxhywhjoirvx32uit
        unique (code),
    constraint FKhp20cafgrgkp1cm4wy70og3a
        foreign key (workshop_id) references techfix_workshop (id),
    constraint FKkp6gny2dphcgrpw6crjchx0tk
        foreign key (client_id) references techfix_client (id),
    constraint FKt4av8psw2o5wtqnwbn48amrip
        foreign key (assigned_tech_id) references techfix_user (id)
)
    engine = InnoDB;


create table techfix_ticket_line
(
    id          binary(16)            not null
        primary key,
    created_at  datetime(6)           not null,
    updated_at  datetime(6)           not null,
    description varchar(255)          null,
    name        varchar(255)          not null,
    part_number varchar(255)          null,
    quantity    decimal(10, 2)        not null,
    total_price decimal(10, 2)        null,
    type        enum ('ITEM', 'PART') not null,
    unit_price  decimal(10, 2)        null,
    ticket_id   binary(16)            not null,
    constraint FK4farh4criqt3vgmrwx6iq5w7l
        foreign key (ticket_id) references techfix_ticket (id)
)
    engine = InnoDB;

create table techfix_user
(
    id            binary(16)                     not null
        primary key,
    created_at    datetime(6)                    not null,
    updated_at    datetime(6)                    not null,
    email         varchar(255)                   not null,
    first_name    varchar(255)                   not null,
    is_active     bit                            not null,
    last_login    datetime(6)                    null,
    last_name     varchar(255)                   not null,
    password      varchar(255)                   not null,
    phone_number1 varchar(255)                   not null,
    phone_number2 varchar(255)                   null,
    role          enum ('ADMIN', 'CHEF', 'TECH') not null,
    username      varchar(255)                   not null,
    workshop_id   binary(16)                     null,
    profile_image varchar(255)                   null,
    company_id    binary(16)                     null,
    constraint UKeuo0cw7s8fos9wompvcqq8v9q
        unique (username),
    constraint UKo4mn24nk2mikqhijb2yjymv8v
        unique (email),
    constraint FK17m9yuf1m6q7x5oynrfs0kw09
        foreign key (workshop_id) references techfix_workshop (id),
    constraint FKek6nnn4mlnjwvft4m32f8q420
        foreign key (company_id) references techfix_company (id)
            on delete set null
)
    engine = InnoDB;

create index idx_user_email
    on techfix_user (email);

create index idx_user_username
    on techfix_user (username);

create table techfix_work_session
(
    id         binary(16)  not null
        primary key,
    created_at datetime(6) not null,
    updated_at datetime(6) not null,
    end_time   datetime(6) null,
    start_time datetime(6) not null,
    ticket_id  binary(16)  not null,
    constraint FKoo3q0k9uy1n1731igl0fh76cy
        foreign key (ticket_id) references techfix_ticket (id)
)
    engine = InnoDB;

create table techfix_workshop
(
    id             binary(16)   not null
        primary key,
    created_at     datetime(6)  not null,
    updated_at     datetime(6)  not null,
    address        varchar(255) not null,
    business_hours text         not null,
    name           varchar(255) not null,
    signature_path varchar(255) null,
    timezone       varchar(255) not null,
    company_id     binary(16)   not null,
    contact_email  varchar(255) not null,
    phone_number   varchar(255) not null,
    constraint FK2xguted4oibrwrkrcp4cdr1gb
        foreign key (company_id) references techfix_company (id)
)
    engine = InnoDB;

create table ticket_attachments
(
    id         binary(16)   not null
        primary key,
    created_at datetime(6)  not null,
    updated_at datetime(6)  not null,
    file_name  varchar(255) null,
    file_type  varchar(255) not null,
    file_url   varchar(255) not null,
    ticket_id  binary(16)   not null,
    constraint FKtitir80lhp4jwvk0vc8vd4uga
        foreign key (ticket_id) references techfix_ticket (id)
)
    engine = InnoDB;

create table user_specialization
(
    user_id        binary(16)   not null,
    specialization varchar(255) null,
    constraint FK6ds27k9tho2ogcq5858xvmi94
        foreign key (user_id) references techfix_user (id)
)
    engine = InnoDB;

create table workshop_services
(
    workshop_id binary(16)   not null,
    services    varchar(255) null,
    constraint FKl3jpgxo8bgslfkdrt9c307hwt
        foreign key (workshop_id) references techfix_workshop (id)
)
    engine = InnoDB;