-- 表的构建
-- 创建area表
CREATE TABLE `area` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '区域编号',
  `name` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '区域名称',
  `surplus` int NOT NULL COMMENT '剩余车位',
  `price` double(11,2) NOT NULL COMMENT '车位价格',
  PRIMARY KEY (`id`),
  KEY `area` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE area
ADD CONSTRAINT pname CHECK (name in ("A","B","C"));
-- 创建车辆信息表
CREATE TABLE `cars` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
  `licensePlate` varchar(7) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '车牌号',
  `ownerId` int NOT NULL COMMENT '车主编号',
  PRIMARY KEY (`id`),
  KEY `ownerId` (`ownerId`),
  KEY `licensePlate` (`licensePlate`),
  CONSTRAINT `ownerId` FOREIGN KEY (`ownerId`) REFERENCES `owner` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 创建owner表
CREATE TABLE `owner` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '车主编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '车主姓名',
  `tel` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户电话',
  `money` double(11,2) NOT NULL COMMENT '消费总额',
  `level` int NOT NULL COMMENT '会员等级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 创建park表
CREATE TABLE `park` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '停车位编号',
  `area` varchar(255) NOT NULL COMMENT '所在区域',
  `number` int NOT NULL COMMENT '车位号',
  `state` int NOT NULL COMMENT '停车状态（0为未停，1为已停）',
  PRIMARY KEY (`id`),
  KEY `area` (`area`),
  CONSTRAINT `area` FOREIGN KEY (`area`) REFERENCES `area` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER table park
ADD CONSTRAINT statename CHECK (state in ("1","0"));

-- 创建parking表
CREATE TABLE `parking` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '停车信息编号',
  `licensePlate` varchar(7) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '车牌号',
  `in` datetime NOT NULL COMMENT '进库时间',
  `parkId` int NOT NULL COMMENT '停车位编号',
  `money` double(11,2) DEFAULT NULL COMMENT '金额',
  `out` datetime DEFAULT NULL COMMENT '出库时间',
  PRIMARY KEY (`id`),
  KEY `licensePlate` (`licensePlate`),
  KEY `park` (`parkId`),
  CONSTRAINT `licensePlate` FOREIGN KEY (`licensePlate`) REFERENCES `cars` (`licensePlate`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `park` FOREIGN KEY (`parkId`) REFERENCES `park` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建user表
CREATE TABLE `user` (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '账户名',
  `password` varchar(255) NOT NULL COMMENT '账户密码',
  `anthority` int NOT NULL COMMENT '权限（0为超级管理员、1为操作员）',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建vip表
CREATE TABLE `vip` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Vip编号',
  `level` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '等级名称',
  `discount` double(11,2) NOT NULL COMMENT '折扣',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- 触发器的编写

-- 车主信息生成
CREATE TRIGGER ownermessage
BEFORE INSERT ON owner
FOR EACH ROW
BEGIN 
SET new.money = 0;
SET new.level = 1;
END;
DROP TRIGGER ownermessage

-- 车主会员的升级
CREATE TRIGGER ownerupdate
BEFORE UPDATE on owner
FOR EACH ROW
BEGIN 
IF new.money>99 AND new.money<499 THEN SET new.level=2;
ELSEIF new.money>499 THEN SET new.level=3;
ELSEIF new.money<100 THEN SET new.level=1;
END IF;
END
DROP TRIGGER ownerupdate;

-- 自动填写进入时间，更改车位状态
CREATE TRIGGER inserttimeandupdatestate
BEFORE INSERT on parking
FOR EACH ROW
BEGIN
DECLARE pid INT;
SET new.in = NOW();
SET pid = new.parkId;
UPDATE park
SET state = 1
WHERE id = pid;
END
DROP TRIGGER inserttimeandupdatestate

-- 修改area中的剩余车辆
CREATE TRIGGER updatesurplus
BEFORE UPDATE on park
FOR EACH ROW
BEGIN
DECLARE name1 varchar(1);
SET name1 = old.area;
IF old.state = 0 THEN
UPDATE area
SET surplus = surplus - 1
WHERE name = name1;
ELSEIF old.state = 1 THEN
UPDATE area
SET surplus = surplus + 1
WHERE name = name1;
end IF;
END
DROP TRIGGER updatesurplus;

-- 当parking表更新时，计算money并修改park对应的state
CREATE TRIGGER updatestate
BEFORE UPDATE on parking
FOR EACH ROW
BEGIN
DECLARE pid INT;
SET pid = old.parkId;
UPDATE park
SET state = 0
WHERE id = pid;
END
DROP TRIGGER  updatestate

CREATE TRIGGER updatestate1
BEFORE UPDATE on parking
FOR EACH ROW
BEGIN
DECLARE pid INT;
SET pid = new.parkId;
UPDATE park
SET state = 1
WHERE id = pid;
END
DROP TRIGGER  updatestate1

-- parking表更新时，计算金额，并将金额加入到车组信息表中；
CREATE TRIGGER getmoney
BEFORE UPDATE on parking
FOR EACH ROW
BEGIN
DECLARE pid INT;
DECLARE hour INT;
DECLARE area1 VARCHAR(255);
DECLARE price1 DOUBLE;
DECLARE owner1 INT;
DECLARE level1 INT;
DECLARE discount1 double;
DECLARE money1 double;
SET pid = old.parkId;
SET hour = TIMESTAMPDIFF(HOUR,new.in,new.out);
SELECT area  from park where id =old.parkId INTO area1;
SELECT price from area WHERE name = area1 INTO price1  ;
SELECT ownerId from cars WHERE licensePlate = old.licensePlate INTO owner1;
SET level1 = (SELECT  level from `owner` WHERE id = owner1 );
SET discount1 = (SELECT discount from vip where id = level1);
SET new.money = hour*price1*discount1;
SET money1 = hour*price1*discount1;
UPDATE `owner`
SET money = money+money1 where id = owner1 ;
END
DROP TRIGGER getmoney

-- 


-- 存储过程的编写

-- for循环插入park表（存储过程）
DELIMITER $$
CREATE PROCEDURE autoinsertinfo()
BEGIN
	DECLARE i int DEFAULT 1 ;
	WHILE (i<21)DO
		INSERT INTO park(area,number,state) VALUES("A",i,0);
		SET i = i+1;
	END WHILE;
SET i = 1;
WHILE(i<101)DO
INSERT INTO park(area,number,state) VALUES("B",i,0);
		SET i = i+1;
END WHILE;
SET i = 1;
WHILE(i<501)DO
INSERT INTO park(area,number,state) VALUES("C",i,0);
		SET i = i+1;
END WHILE;
END
$$
DELIMITER

CALL autoinsertinfo();
DROP PROCEDURE autoinsertinfo;
-- demo
-- UPDATE `parking` SET `out`='2022-01-05 17:24:24' WHERE (`id`='20')
-- UPDATE parking SET out = '2022-01-05 ' WHERE id = 20