create database parkinglot;
USE parkinglot;
create table parkingspotinformation
(
    parking_spot_id  int            not null
        primary key,
    spot_type        varchar(100)   null,
    occupancy_status int            null,
    location         varchar(255)   null,
    fee_standard     decimal(10, 2) null
)
    engine = InnoDB;

create table userinformation
(
    user_id         int          not null
        primary key,
    name            varchar(255) not null,
    telephone       varchar(11)  null,
    user_type       varchar(100) null,
    parking_spot_id int          null,
    address         varchar(255) null,
    gender          varchar(10)  null,
    constraint parking_spot_id
        unique (parking_spot_id),
    constraint userinformation_ibfk_1
        foreign key (parking_spot_id) references parkingspotinformation (parking_spot_id)
)
    engine = InnoDB;

create table accountinformation
(
    account_id     int          not null
        primary key,
    user_id        int          null,
    account_type   varchar(100) null,
    account_status varchar(50)  null,
    name           varchar(255) null,
    password       varchar(255) null,
    constraint user_id
        unique (user_id),
    constraint accountinformation_ibfk_1
        foreign key (user_id) references userinformation (user_id)
)
    engine = InnoDB;

create table icinformation
(
    user_id     int            null,
    card_number varchar(20)    not null
        primary key,
    password    varchar(255)   null,
    balance     decimal(10, 2) null,
    constraint user_id
        unique (user_id),
    constraint icinformation_ibfk_1
        foreign key (user_id) references userinformation (user_id)
)
    engine = InnoDB;
DELIMITER //
create definer = root@localhost trigger update_fee_standard
    after update
    on userinformation
    for each row
BEGIN
    IF NEW.user_type = 'vip' AND OLD.user_type = 'user' THEN
        UPDATE parkingspotinformation
        SET fee_standard = 0.75
        WHERE parking_spot_id = NEW.parking_spot_id;
    END IF;
END;
//

DELIMITER ;
create table vehicleinformation
(
    car_license varchar(20)  not null
        primary key,
    user_id     int          null,
    brand       varchar(100) null,
    model       varchar(100) null,
    constraint user_id
        unique (user_id),
    constraint vehicleinformation_ibfk_1
        foreign key (user_id) references userinformation (user_id)
)
    engine = InnoDB;

create table parkingrecord
(
    record_id       int auto_increment
        primary key,
    parking_spot_id int            null,
    start_time      datetime       null,
    parking_fee     decimal(10, 2) null,
    end_time        datetime       null,
    car_license     varchar(20)    null,
    constraint parkingrecord_ibfk_1
        foreign key (parking_spot_id) references parkingspotinformation (parking_spot_id),
    constraint parkingrecord_ibfk_2
        foreign key (car_license) references vehicleinformation (car_license)
)
    engine = InnoDB;

create index car_license
    on parkingrecord (car_license);

create index parking_spot_id
    on parkingrecord (parking_spot_id);
DELIMITER //
create definer = root@localhost trigger calculate_and_deduct_parking_fee
    before update
    on parkingrecord
    for each row
BEGIN
    DECLARE spot_fee DECIMAL(10, 2);
    DECLARE hours_parked INT;

    IF NEW.end_time IS NOT NULL AND OLD.end_time IS NULL THEN
        -- 获取停车费率
        SELECT fee_standard INTO spot_fee FROM parkingspotinformation WHERE parking_spot_id = NEW.parking_spot_id;

        -- 计算停车时间（小时）
        SET hours_parked = GREATEST(CEIL(TIMESTAMPDIFF(MINUTE, NEW.start_time, NEW.end_time) / 60), 1);

        -- 更新停车费用
        SET NEW.parking_fee = hours_parked * spot_fee;

        -- 扣除停车费用
        UPDATE icinformation
        SET balance = balance - NEW.parking_fee
        WHERE user_id = (SELECT user_id FROM userinformation WHERE parking_spot_id = NEW.parking_spot_id);
    END IF;
END;
//

DELIMITER ;
DELIMITER //
create definer = root@localhost trigger calculate_parking_fee
    before update
    on parkingrecord
    for each row
BEGIN
    IF NEW.end_time IS NOT NULL AND OLD.end_time IS NULL THEN
        SET @hours_parked = GREATEST(CEIL(TIMESTAMPDIFF(MINUTE, NEW.start_time, NEW.end_time) / 60), 1);

        SET @spot_fee = (SELECT fee_standard FROM parkingspotinformation WHERE parking_spot_id = NEW.parking_spot_id);

        SET NEW.parking_fee = @hours_parked * @spot_fee;
    END IF;
END;
//

DELIMITER ;
create definer = root@localhost view parking_record_view as
select `pr`.`record_id`       AS `record_id`,
       `pr`.`parking_spot_id` AS `parking_spot_id`,
       `pr`.`start_time`      AS `start_time`,
       `pr`.`parking_fee`     AS `parking_fee`,
       `pr`.`end_time`        AS `end_time`,
       `pr`.`car_license`     AS `car_license`,
       `psi`.`location`       AS `location`
from (`parkinglot`.`parkingrecord` `pr` left join `parkinglot`.`parkingspotinformation` `psi`
      on ((`pr`.`parking_spot_id` = `psi`.`parking_spot_id`)));

create definer = root@localhost view userparkingtotalfees as
select `u`.`name` AS `user_name`, sum(`pr`.`parking_fee`) AS `total_parking_fees`
from (`parkinglot`.`userinformation` `u` left join `parkinglot`.`parkingrecord` `pr`
      on ((`u`.`parking_spot_id` = `pr`.`parking_spot_id`)))
group by `u`.`name`;

create definer = root@localhost view vehicleuserview as
select `vi`.`car_license` AS `car_license`,
       `vi`.`brand`       AS `brand`,
       `vi`.`model`       AS `model`,
       `ui`.`name`        AS `name`,
       `ui`.`telephone`   AS `telephone`,
       `ui`.`address`     AS `address`
from (`parkinglot`.`vehicleinformation` `vi` join `parkinglot`.`userinformation` `ui`
      on ((`vi`.`user_id` = `ui`.`user_id`)));

