create table kehu 
(memberNo  char(18) primary key,
memberName char(8) not null,
memberPassword char(8) not null,
membersex char(3) not null,
membertel char(8) null,
memberaddr varchar(30) null,
yizushuliang int not null check((yizushuliang<7) and (yizushuliang>0))
);



create table pinzhong
(pinzhongNo char(8) primary key
,pinzhongName char(8) not null
,dailyRental Decimal(4,2) not null
);



create table video
(videoNo char(8) primary key 
,videoName char(20) not null
,price number(4,2) not null
,publishtime char(4) not null
,director char(10) not null
,mainactor char(10) not null
,leibie char(10) not null
,publishaddr char(10) not null
,amount int 
,pinzhongNo  char(8)
,constraint fk foreign key(pinzhongNo) references pinzhong(pinzhongNo)
);

create table zujiejilu
(zujieNO char(8) primary key,
videoNo char(8),
memberNo char(18),
zujieamount int,
guihuanamount int default 0 ,
dateout date default sysdate,
datereturn date default sysdate+7 ,
yajin decimal(5,2),
zhuangtai varchar(20) default '未付款',
shishou decimal(5,2) default 0.00,
foreign key(videoNo) references video(videoNo),
foreign key(memberNo) references kehu(memberNo)
);


create table yuyuejilu
(orderNo char(8) primary key ,
videoNo char(8),
memberNo  char(8),
orderamount int,
foreign key(videoNo) references video(videoNo),
foreign key(memberNo) references kehu(memberNo)
);

CREATE OR REPLACE TRIGGER TrigInsertOnZuJie
BEFORE INSERT OR UPDATE ON zujiejilu
FOR EACH ROW
DECLARE
    vVideoPrice Video.price%TYPE;
    vDailyRental pinzhong.dailyrental%TYPE;
    vCurrentTime DATE;
BEGIN
    IF inserting THEN
        SELECT price INTO vVideoPrice FROM Video WHERE VideoNo = :NEW.VIDEONO;
        :NEW.YAJIN := vVideoPrice * 2*:new.zujieamount;
    ELSE
        CASE :NEW.Zhuangtai
            WHEN '已付款' THEN
                 :New.shishou := :Old.yajin;
            WHEN '提前归还' THEN
                 SELECT SYSDATE INTO vCurrentTime FROM dual;
                 SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo);
                 :New.Datereturn := vCurrentTime;
                 :new.guihuanamount:=:new.zujieamount;
                 :NEW.shishou:=(vCurrentTime-:NEW.dateout)*vDailyRental*:new.zujieamount;
            WHEN '按期归还' THEN
               SELECT SYSDATE INTO vCurrentTime FROM dual;
               SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo);
               :NEW.shishou := (:new.datereturn-:new.dateout)*vDailyRental*:new.zujieamount;
               :new.guihuanamount:=:new.zujieamount;
            WHEN '超期归还' THEN
                 SELECT SYSDATE INTO vCurrentTime FROM dual;
                 SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo);
                 :new.shishou:=(7*vDailyRental+(vCurrentTime-:new.dateout-7)*vDailyRental*1.5)*:new.zujieamount;
                 :new.datereturn := vCurrentTime;
                 :new.guihuanamount:=:new.zujieamount;
           when '损坏丢失' then
                  SELECT SYSDATE INTO vCurrentTime FROM dual;
                 :new.shishou:=:old.yajin;
                 :new.datereturn := vCurrentTime;
        END CASE;
    END IF;
END TrigInsertOnZuJie;