create table customers
(
    T_id    int auto_increment
        primary key,
    T_name  varchar(10) not null,
    T_rsex  varchar(4)  null,
    T_phone varchar(20) null
)
    charset = utf8mb3;

create table menu_items
(
    primary key (M_id)
)
    charset = utf8mb3;

create table discount_rules
(
    primary key (M_id),
    constraint discount_rules_menu_items_M_id_fk
        foreign key (M_id) references menu_items (M_id)
            on update cascade on delete cascade
)
    charset = utf8mb3;

create table tables
(
    Cz_id     varchar(10) not null
        primary key,
    Cz_number int         not null,
    Cz_zt     varchar(6)  null
)
    charset = utf8mb3;

create table workers
(
    W_id     varchar(10) not null
        primary key,
    W_name   varchar(10) null,
    W_sex    varchar(4)  null,
    W_stat   int         null,
    W_salary float       null
)
    charset = utf8mb3;

create table orders
(
    O_id  int auto_increment
        primary key,
    T_id  int         not null,
    time  datetime    not null,
    Cz_id varchar(10) not null,
    W_id  varchar(10) not null,
    items text        null,
    constraint orders_customers_T_id_fk
        foreign key (T_id) references customers (T_id),
    constraint orders_tables_Cz_id_fk
        foreign key (Cz_id) references tables (Cz_id),
    constraint orders_workers_W_id_fk
        foreign key (W_id) references workers (W_id)
)
    charset = utf8mb3;

create table bills
(
    B_id         int auto_increment
        primary key,
    O_id         int      null,
    S_price      float    null,
    S_priceafter float    null,
    time         datetime null,
    constraint bills_orders_O_id_fk
        foreign key (O_id) references orders (O_id)
            on update cascade on delete cascade
)
    charset = utf8mb3;

create index Cz_id
    on orders (Cz_id);

create index T_id
    on orders (T_id);

create index W_id
    on orders (W_id);

create definer = root@localhost trigger UpdateTableStatusToFreeAfterDelete
    after delete
    on orders
    for each row
BEGIN
    UPDATE tables SET Cz_zt = '空闲' WHERE Cz_id = OLD.Cz_id;
END;

create definer = root@localhost view customer_orders as
select `c`.`T_id`      AS `Customer_ID`,
       `c`.`T_name`    AS `Customer_Name`,
       `o`.`O_id`      AS `Order_ID`,
       `o`.`time`      AS `Order_Time`,
       `t`.`Cz_id`     AS `Table_ID`,
       `t`.`Cz_number` AS `Table_Number`,
       `w`.`W_id`      AS `Waiter_ID`,
       `w`.`W_name`    AS `Waiter_Name`,
       `o`.`items`     AS `Ordered_Items`
from (((`mydata`.`orders` `o` join `mydata`.`customers` `c` on ((`o`.`T_id` = `c`.`T_id`))) join `mydata`.`tables` `t`
       on ((`o`.`Cz_id` = `t`.`Cz_id`))) join `mydata`.`workers` `w` on ((`o`.`W_id` = `w`.`W_id`)));

create definer = root@localhost view customers_t as
select `mydata`.`customers`.`T_id`    AS `T_id`,
       `mydata`.`customers`.`T_name`  AS `T_name`,
       `mydata`.`customers`.`T_rsex`  AS `T_rsex`,
       `mydata`.`customers`.`T_phone` AS `T_phone`
from `mydata`.`customers`
where (`mydata`.`customers`.`T_rsex` = '女');

create definer = root@localhost view menu_item_sales as
select `m`.`M_id`                                               AS `Menu_Item_ID`,
       `m`.`M_name`                                             AS `Menu_Item_Name`,
       count(0)                                                 AS `Sales_Count`,
       sum((`m`.`M_price` * (1 - coalesce(`d`.`discount`, 0)))) AS `Total_Sales_Amount`
from ((`mydata`.`menu_items` `m` left join `mydata`.`discount_rules` `d`
       on ((`m`.`M_id` = `d`.`M_id`))) join `mydata`.`orders` `o` on ((find_in_set(`m`.`M_id`, `o`.`items`) > 0)))
group by `m`.`M_id`, `m`.`M_name`, `d`.`discount`;

create definer = root@localhost view table_status as
select `mydata`.`tables`.`Cz_id`     AS `Table_ID`,
       `mydata`.`tables`.`Cz_number` AS `Table_Number`,
       `mydata`.`tables`.`Cz_zt`     AS `Status`
from `mydata`.`tables`;