create table bo_permissions
(
    id          int auto_increment
        primary key,
    scope       varchar(50)  not null comment 'Catégorie (ex: users, pos, finance)',
    code        varchar(100) not null comment 'Code unique (ex: users.create)',
    description varchar(255) null,
    constraint code
        unique (code)
)
    collate = utf8mb4_unicode_ci;

create table bo_roles
(
    id          int auto_increment
        primary key,
    name        varchar(50)                          not null comment 'Nom du rôle (ex: Admin, Comptable)',
    description text                                 null,
    created_at  datetime default current_timestamp() null,
    constraint name
        unique (name)
)
    collate = utf8mb4_unicode_ci;

create table bo_role_permissions
(
    role_id       int not null,
    permission_id int not null,
    primary key (role_id, permission_id),
    constraint fk_rp_perm
        foreign key (permission_id) references bo_permissions (id)
            on delete cascade,
    constraint fk_rp_role
        foreign key (role_id) references bo_roles (id)
            on delete cascade
)
    collate = utf8mb4_unicode_ci;

create table bo_users
(
    id                int auto_increment
        primary key,
    email             varchar(255)                            not null,
    password_hash     varchar(255)                            null comment 'Hash bcrypt pour auth locale',
    full_name         varchar(150)                            null,
    is_active         tinyint(1)  default 1                   null,
    is_superuser      tinyint(1)  default 0                   null comment '1 = Accès total sans vérifier les rôles',
    auth_provider     varchar(50) default 'local'             null comment 'local ou oidc',
    role_id           int                                     null,
    two_factor_secret varchar(32)                             null,
    last_login        datetime                                null,
    last_login_ip     varchar(45)                             null,
    created_at        datetime    default current_timestamp() null,
    constraint email
        unique (email),
    constraint fk_user_role
        foreign key (role_id) references bo_roles (id)
            on delete set null
)
    collate = utf8mb4_unicode_ci;

create table bo_audit_logs
(
    id           bigint auto_increment
        primary key,
    user_id      int                                  null,
    action       varchar(50)                          not null comment 'CREATE, UPDATE, DELETE, LOGIN',
    target_table varchar(50)                          not null,
    target_id    varchar(100)                         not null,
    changes      longtext collate utf8mb4_bin         null comment 'Snapshot des modifications'
        check (json_valid(`changes`)),
    ip_address   varchar(45)                          null,
    user_agent   text                                 null,
    timestamp    datetime default current_timestamp() null,
    constraint fk_audit_user
        foreign key (user_id) references bo_users (id)
            on delete set null
)
    collate = utf8mb4_unicode_ci;

create table cashless_operator
(
    cashless_operator_id         int auto_increment
        primary key,
    cashless_operator_name       varchar(100)                                                              not null,
    cashless_operator_pin_hash   varchar(255)                                                              not null comment 'Hashed PIN',
    cashless_operator_role       enum ('server', 'banker', 'manager', 'admin') default 'server'            not null,
    cashless_operator_active     tinyint(1)                                    default 1                   null,
    cashless_operator_created_at timestamp                                     default current_timestamp() null
);

create table cashless_pos
(
    cashless_pos_id                 int auto_increment
        primary key,
    cashless_pos_name               varchar(255)                          not null,
    cashless_pos_desc               varchar(255)                          null,
    cashless_pos_creation_timestamp timestamp default current_timestamp() not null,
    cashless_pos_type               varchar(255)                          null,
    cashless_pos_active             int       default 1                   null
);

create table cashless_product
(
    cashless_product_id        int auto_increment
        primary key,
    cashless_product_name      varchar(255)                    not null,
    cashless_product_desc      varchar(255)                    null,
    cashless_product_amount    int           default 0         not null comment 'In euros and in cents',
    cashless_product_category  varchar(255)                    not null comment 'normal - majority_required',
    cashless_product_color     varchar(20)   default '#3B82F6' null comment 'Hex color for UI',
    cashless_product_vat_rate  decimal(5, 2) default 20.00     null comment 'Tax rate in %',
    cashless_product_image_url varchar(255)                    null,
    cashless_product_active    tinyint(1)    default 1         null
);

create table cashless_pos_product_link
(
    cashless_pos_product_link_id            int auto_increment
        primary key,
    cashless_pos_id                         int           not null,
    cashless_product_id                     int           not null,
    cashless_pos_product_link_display_order int default 0 null comment 'Order on screen',
    constraint cashless_pos_id
        unique (cashless_pos_id, cashless_product_id),
    constraint fk_link_pos
        foreign key (cashless_pos_id) references cashless_pos (cashless_pos_id)
            on delete cascade,
    constraint fk_link_prod
        foreign key (cashless_product_id) references cashless_product (cashless_product_id)
            on delete cascade
);

create table event
(
    event_id   int auto_increment
        primary key,
    event_name varchar(255) not null,
    event_desc varchar(255) null,
    event_date date         null,
    event_time time         null,
    event_type varchar(255) null
);

create table entry_type
(
    entry_type_id       int auto_increment
        primary key,
    entry_type_name     varchar(255) not null,
    entry_type_event_id int          not null,
    constraint entry_type_pk_2
        unique (entry_type_name),
    constraint entry_type_event_event_id_fk
        foreign key (entry_type_event_id) references event (event_id)
            on update cascade
);

create table price
(
    price_id       int auto_increment
        primary key,
    price_name     varchar(255) null,
    price_desc     varchar(255) null,
    price_amount   int          null comment 'In euros and in cents',
    price_type     varchar(255) null comment 'donation - internal - public',
    price_event_id int          not null,
    constraint price_event_event_id_fk
        foreign key (price_event_id) references event (event_id)
);

create table event_price_stock
(
    event_price_stock_id     int auto_increment
        primary key,
    price_id                 int           not null,
    event_id                 int           not null,
    event_price_stock_number int default 0 not null,
    constraint event_price_stock_event_event_id_fk
        foreign key (event_id) references event (event_id)
            on update cascade,
    constraint event_price_stock_price_price_id_fk
        foreign key (price_id) references price (price_id)
            on update cascade
);

create table stripe_transaction
(
    stripe_transaction_id        int auto_increment
        primary key,
    stripe_transaction_timestamp timestamp default current_timestamp() null,
    stripe_transaction_cs        varchar(255)                          not null comment 'Stripe Payment Session ID',
    stripe_transaction_txn       varchar(255)                          null comment 'Stripe Transaction Key',
    stripe_transaction_pi        varchar(255)                          null comment 'Stripe Payment Intenent Key',
    stripe_transaction_ch        varchar(255)                          null comment 'Stripe Charge Key',
    stripe_transaction_re        varchar(255)                          null comment 'Stripe Refund Key'
);

create table banking_txn
(
    banking_txn_id                int auto_increment
        primary key,
    banking_txn_uuid              uuid default uuid() null,
    banking_txn_flow              varchar(255)        null comment 'in --> Payment / out --> refund',
    banking_txn_type              varchar(255)        not null comment 'stripe or cc or cash',
    banking_txn_amount            int                 not null comment 'In euro and in cents',
    banking_txn_fees              int  default 0      not null comment 'In euro and in cents',
    banking_txn_net_amount        int  default 0      not null comment 'In euro and in cents',
    banking_txn_stripe_payment_id int                 null,
    banking_txn_timestamp         timestamp           null,
    banking_txn_client_ip         blob                null,
    banking_txn_signature         blob                null,
    constraint banking_txn_stripe_transaction_stripe_transaction_id_fk
        foreign key (banking_txn_stripe_payment_id) references stripe_transaction (stripe_transaction_id)
            on update cascade
);

create table `order`
(
    order_id             int auto_increment
        primary key,
    order_lastname       varchar(255)                          null,
    order_name           varchar(255)                          null,
    order_amount         int                                   null comment 'Global amount of the order',
    order_ticket_number  int                                   null comment 'Number of ticket into the order',
    order_email          varchar(255)                          null,
    order_timestamp      timestamp default current_timestamp() null,
    order_event_id       int                                   not null,
    order_price_id       int                                   null,
    order_state          varchar(255)                          null comment 'waiting_for_payment - payment_ok - tickets_sent - payment_error - tickets_generated',
    order_type           varchar(255)                          null comment 'online - manual',
    order_banking_txn_id int                                   null,
    constraint order_banking_txn_banking_txn_id_fk
        foreign key (order_banking_txn_id) references banking_txn (banking_txn_id)
            on update cascade,
    constraint order_event_event_id_fk
        foreign key (order_event_id) references event (event_id)
            on update cascade,
    constraint order_price_price_id_fk
        foreign key (order_price_id) references price (price_id)
            on update cascade
);

create table ticket
(
    ticket_id                   int auto_increment
        primary key,
    ticket_uuid                 uuid         not null,
    ticket_readable             varchar(8)   not null,
    ticket_type                 varchar(255) not null comment 'normal - staff',
    ticket_state                varchar(255) null comment 'ok - already_scanned - disabled',
    ticket_first_scan_timestamp timestamp    null,
    ticket_creation_timestamp   timestamp    null,
    ticket_order_id             int          null,
    constraint ticket_order_order_id_fk
        foreign key (ticket_order_id) references `order` (order_id)
);

create table cashless_account
(
    cashless_account_id             int auto_increment
        primary key,
    cashless_account_email          blob                                  null,
    cashless_account_uuid           uuid      default uuid()              not null,
    cashless_account_timestamp      timestamp default current_timestamp() null,
    cashless_account_status         varchar(255)                          not null comment 'activated - disabled',
    cashless_account_balance        int       default 0                   not null comment 'In euro and in cents',
    cashless_account_balance_status varchar(255)                          not null comment 'validated - waiting_for_validation - invalid',
    cashless_account_ticket_id      int                                   null,
    cashless_account_name           blob                                  null,
    constraint cashless_account_pk
        unique (cashless_account_ticket_id),
    constraint cashless_account_pk_2
        unique (cashless_account_uuid),
    constraint cashless_account_ticket_ticket_id_fk
        foreign key (cashless_account_ticket_id) references ticket (ticket_id)
);

create table cashless_card
(
    cashless_card_id                 int auto_increment
        primary key,
    cashless_card_uuid               uuid       default uuid()              not null comment 'UUID of the QRCode/NFC Tag',
    cashless_card_readable           varchar(255)                           not null comment 'Unique readable identifier written on the card',
    cashless_card_creation_timestamp timestamp  default current_timestamp() not null,
    cashless_card_batch_number       int                                    null comment 'Card batch number',
    cashless_card_serial_number      int                                    null comment 'Card sequential number in the batch',
    cashless_card_account_id         int                                    not null,
    cashless_card_active             tinyint(1) default 1                   null,
    constraint cashless_card_pk_2
        unique (cashless_card_uuid),
    constraint cashless_card_pk_3
        unique (cashless_card_batch_number, cashless_card_serial_number),
    constraint cashless_card_pk_4
        unique (cashless_card_readable),
    constraint cashless_card_cashless_account_cashless_account_id_fk
        foreign key (cashless_card_account_id) references cashless_account (cashless_account_id)
);

create table cashless_contact
(
    contact_id             int auto_increment
        primary key,
    contact_owner_id       int                                   not null,
    contact_beneficiary_id int                                   not null,
    contact_created_at     timestamp default current_timestamp() null,
    constraint unique_contact
        unique (contact_owner_id, contact_beneficiary_id),
    constraint cashless_contact_ibfk_1
        foreign key (contact_owner_id) references cashless_account (cashless_account_id)
            on delete cascade,
    constraint cashless_contact_ibfk_2
        foreign key (contact_beneficiary_id) references cashless_account (cashless_account_id)
            on delete cascade
);

create index contact_beneficiary_id
    on cashless_contact (contact_beneficiary_id);

create table cashless_credit_txn
(
    cashless_credit_txn_id             int auto_increment
        primary key,
    cashless_credit_txn_account_id     int          not null,
    cashless_credit_txn_amount         int          not null comment 'In euro and in cents',
    cashless_credit_txn_payment_method varchar(255) not null comment 'stripe - cc - cash',
    cashless_credit_txn_banking_txn_id int          null,
    cashless_operator_id               int          null,
    cashless_pos_id                    int          null,
    constraint cashless_credit_txn_banking_txn_banking_txn_id_fk
        foreign key (cashless_credit_txn_banking_txn_id) references banking_txn (banking_txn_id),
    constraint cashless_credit_txn_cashless_account_cashless_account_id_fk
        foreign key (cashless_credit_txn_account_id) references cashless_account (cashless_account_id),
    constraint fk_credit_operator
        foreign key (cashless_operator_id) references cashless_operator (cashless_operator_id),
    constraint fk_credit_pos
        foreign key (cashless_pos_id) references cashless_pos (cashless_pos_id)
);

create table cashless_txn
(
    cashless_txn_id          int auto_increment
        primary key,
    cashless_txn_uuid        uuid      default uuid()              not null,
    cashless_txn_account_id  int                                   not null,
    cashless_txn_card_id     int                                   null,
    cashless_txn_type        varchar(255)                          not null comment 'debit - credit',
    cashless_txn_pos_id      int                                   null,
    cashless_txn_amount      int                                   not null comment 'In cents and in euros (can be negative)',
    cashless_txn_timestamp   timestamp default current_timestamp() null,
    cashless_txn_signature   blob                                  null,
    cashless_txn_description varchar(255)                          null,
    cashless_operator_id     int                                   null,
    constraint cashless_txn_pk_2
        unique (cashless_txn_uuid),
    constraint cashless_txn_cashless_account_cashless_account_id_fk
        foreign key (cashless_txn_account_id) references cashless_account (cashless_account_id),
    constraint cashless_txn_cashless_card_cashless_card_id_fk
        foreign key (cashless_txn_card_id) references cashless_card (cashless_card_id),
    constraint cashless_txn_cashless_pos_cashless_pos_id_fk
        foreign key (cashless_txn_pos_id) references cashless_pos (cashless_pos_id),
    constraint fk_txn_operator
        foreign key (cashless_operator_id) references cashless_operator (cashless_operator_id)
);

create table cashless_txn_product
(
    cashless_txn_product_id                int auto_increment
        primary key,
    cashless_txn_id                        int                         null,
    cashless_product_id                    int                         null,
    cashless_pos_id                        int                         null,
    cashless_txn_product_quantity          int                         null,
    cashless_txn_product_frozen_unit_price int           default 0     not null comment 'Price at moment of sale',
    cashless_txn_product_frozen_vat_rate   decimal(5, 2) default 20.00 not null,
    constraint cashless_txn_product_cashless_pos_cashless_pos_id_fk
        foreign key (cashless_pos_id) references cashless_pos (cashless_pos_id),
    constraint cashless_txn_product_cashless_product_cashless_product_id_fk
        foreign key (cashless_product_id) references cashless_product (cashless_product_id),
    constraint cashless_txn_product_cashless_txn_cashless_txn_id_fk
        foreign key (cashless_txn_id) references cashless_txn (cashless_txn_id)
);

create table entry
(
    entry_id        int auto_increment
        primary key,
    entry_timestamp timestamp default current_timestamp() not null,
    entry_type_id   int                                   null,
    entry_event_id  int                                   not null,
    entry_ticket_id int                                   null,
    constraint entry_pk
        unique (entry_ticket_id),
    constraint entry_entry_type_entry_type_event_id_fk
        foreign key (entry_type_id) references entry_type (entry_type_event_id)
            on update cascade,
    constraint entry_event_event_id_fk
        foreign key (entry_event_id) references event (event_id)
            on update cascade,
    constraint entry_ticket_ticket_id_fk
        foreign key (entry_ticket_id) references ticket (ticket_id)
            on update cascade
);

create table notification_token
(
    notification_token_id         int auto_increment
        primary key,
    notification_token_account_id int                                     not null,
    notification_token_value      varchar(255)                            not null,
    notification_token_os         varchar(20) default 'unknown'           null,
    notification_token_created_at timestamp   default current_timestamp() null,
    notification_token_updated_at timestamp   default current_timestamp() null on update current_timestamp(),
    constraint unique_token
        unique (notification_token_value),
    constraint fk_notif_account
        foreign key (notification_token_account_id) references cashless_account (cashless_account_id)
            on delete cascade
);

create table safezone_alerts
(
    safezone_alert_id                int auto_increment
        primary key,
    safezone_alert_uuid              varchar(36)                                                        not null,
    safezone_alert_latitude          decimal(10, 8)                                                     null,
    safezone_alert_longitude         decimal(11, 8)                                                     null,
    safezone_alert_title             varchar(150)                                                       not null,
    safezone_alert_description       text                                                               null,
    safezone_alert_timestamp         datetime                               default current_timestamp() null,
    safezone_alert_account_id        int                                                                null,
    safezone_alert_operator_id       int                                                                null,
    safezone_alert_status            enum ('new', 'acknowledged', 'closed') default 'new'               null,
    safezone_alert_ack_operator_id   int                                                                null,
    safezone_alert_ack_timestamp     datetime                                                           null,
    safezone_alert_close_operator_id int                                                                null,
    safezone_alert_close_timestamp   datetime                                                           null,
    safezone_alert_close_comment     text                                                               null,
    constraint safezone_alert_uuid
        unique (safezone_alert_uuid),
    constraint safezone_alerts_ibfk_1
        foreign key (safezone_alert_account_id) references cashless_account (cashless_account_id),
    constraint safezone_alerts_ibfk_2
        foreign key (safezone_alert_operator_id) references cashless_operator (cashless_operator_id),
    constraint safezone_alerts_ibfk_3
        foreign key (safezone_alert_ack_operator_id) references cashless_operator (cashless_operator_id),
    constraint safezone_alerts_ibfk_4
        foreign key (safezone_alert_close_operator_id) references cashless_operator (cashless_operator_id)
);

create index safezone_alert_account_id
    on safezone_alerts (safezone_alert_account_id);

create index safezone_alert_ack_operator_id
    on safezone_alerts (safezone_alert_ack_operator_id);

create index safezone_alert_close_operator_id
    on safezone_alerts (safezone_alert_close_operator_id);

create index safezone_alert_operator_id
    on safezone_alerts (safezone_alert_operator_id);

