-- EN: Clean up objects generated by a previous run of this script
-- HU: A szkript korabbi futasa altal generalt objektumok eltuntetese

DROP TABLE loans CASCADE CONSTRAINTS PURGE;
DROP TABLE videos CASCADE CONSTRAINTS PURGE;
DROP TABLE members CASCADE CONSTRAINTS PURGE;

-- EN: table for members' data
-- HU: Kolcsonzok tablaja
CREATE TABLE members (
    member_id           NUMBER(10)    GENERATED ALWAYS AS IDENTITY (start with 10000) CONSTRAINT member_pk PRIMARY KEY,
    member_code         NVARCHAR2(10) CONSTRAINT member_code_uq UNIQUE NOT NULL,
    Name                NVARCHAR2(30) NOT NULL,
    Address             NVARCHAR2(50) NOT NULL,
    DateofBirth         DATE          NOT NULL,
    DateofCreation      DATE          DEFAULT SYSDATE NOT NULL,
    LevelofMember       NVARCHAR2(6)  NOT NULL,
    CONSTRAINT member_create_ck       CHECK( ADD_MONTHS(DateofBirth, 12*13) <= DateofCreation ),
    -- EN: date of birth has day granularity
    -- HU: a szuletesi datum nap pontossagu
    CONSTRAINT member_dateofbirth_day_ck CHECK( trunc(DateofBirth) = DateofBirth ),
    CONSTRAINT member_level_ck        CHECK( LevelofMember IN ('BASIC', 'SILVER', 'GOLD') )
);

-- EN: table for videos' data
-- HU: Videok tablaja
CREATE TABLE videos (
    video_id            NUMBER(10)    GENERATED ALWAYS AS IDENTITY (start with 20000) CONSTRAINT video_pk PRIMARY KEY,
    video_code          NCHAR(7)      CONSTRAINT video_code_uq UNIQUE NOT NULL,
    Title               NVARCHAR2(50) NOT NULL,
    Appearance          DATE          NOT NULL,
    Director            NVARCHAR2(30),
    Duration            NUMBER(3)     NOT NULL,
    Fee                 NUMBER(6)     DEFAULT 135 NOT NULL,
    Commentline         NVARCHAR2(150),
    Type                NCHAR(1)      NOT NULL,
    CONSTRAINT video_appear_ck    CHECK( Appearance >= TO_DATE('1848-03-15','YYYY-MM-DD') ),
    CONSTRAINT video_duration_ck  CHECK( Duration BETWEEN 15 AND 270 ),
    CONSTRAINT video_feemin_ck    CHECK( Fee >= 50 ),
    CONSTRAINT video_type_ck      CHECK( Type IN ('D', 'B') )
);


-- EN: Table for loans' data
-- HU: Kolcsonzesi adatok tablaja
CREATE TABLE loans (
    loan_id         NUMBER(10)    GENERATED ALWAYS AS IDENTITY CONSTRAINT loan_pk PRIMARY KEY,
    video_id        NOT NULL CONSTRAINT loan_video_fk  REFERENCES videos   ON DELETE CASCADE,
    member_id       NOT NULL CONSTRAINT loan_member_fk REFERENCES members  ON DELETE CASCADE,
    DateofCreation  DATE NOT NULL,
    DateofTurnback  DATE,
    Discount        NUMBER(3) default 0 CONSTRAINT video_discount_ck CHECK(Discount between 0 and 100) NOT NULL,
    CONSTRAINT loan_vmd_uq UNIQUE (video_id, member_id, DateofCreation),
    CONSTRAINT loan_turnback_ck CHECK( DateofTurnback IS NULL OR DateofTurnback >= DateofCreation )
);
comment on column loans.Discount is 'Percentage value of the discount for the particular loan transaction.';

-- EN: Insert members' data
-- HU: Tagok bevitele

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('KK01', 'Kovács Károly',     '9600 Sopron, Ászok u. 11',   TRUNC(ADD_MONTHS( SYSDATE-32, -35*12 )),  ADD_MONTHS( SYSDATE, -16  ), 'BASIC');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('NI01', 'Nagy István',     '7622 Pécs, Malom u. 10',   TRUNC(ADD_MONTHS( SYSDATE-54, -64*12 )),  ADD_MONTHS( SYSDATE, -65  ), 'SILVER');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('KK02', 'Kiss Károly',     '8500 Pápa, Kossuth u. 126',   TRUNC(ADD_MONTHS( SYSDATE, -39*12 )),  ADD_MONTHS( SYSDATE, -26  ), 'BASIC');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('NI02', 'Nagy Istvan',     '7622 Pécs, Malom u. 10',   TRUNC(ADD_MONTHS( SYSDATE-175, -34*12 )),  ADD_MONTHS( SYSDATE, -64  ), 'SILVER');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('KI01', 'Király István',     '2500 Esztergom, Kis-Duna sétány 1.',   TRUNC(ADD_MONTHS( SYSDATE, -33*12 )),  ADD_MONTHS( SYSDATE, -20*12  ), 'BASIC');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('FB01', 'Fehér Barna',     '1117 Budapest, Bogdánfy u. 3.',   TRUNC(ADD_MONTHS( SYSDATE-288, -26*12 )),  ADD_MONTHS( SYSDATE, -27  ), 'GOLD');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('VP01', 'Vörös Piroska',     '1111 Budapest, Lágymányosi u. 11.',   TRUNC(ADD_MONTHS( SYSDATE-23, -28*12 )),  ADD_MONTHS( SYSDATE, -34  ), 'SILVER');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('KK03', 'Könyves Kálmán',     '1092 Budapest, Üllői út 101.',   TRUNC(ADD_MONTHS( SYSDATE-187, -89*12 )),  ADD_MONTHS( SYSDATE, -15  ), 'GOLD');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('EI01', 'Első Imre',     '1005 Budapest, Arany János u. 5.',   TRUNC(ADD_MONTHS( SYSDATE-198, -76*12 )),  ADD_MONTHS( SYSDATE, -11  ), 'BASIC');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('IB01', 'Illés Béla',     '1102 Budapest, Hungária krt. 10.',   TRUNC(ADD_MONTHS( SYSDATE, -35*12 )),  ADD_MONTHS( SYSDATE, -1  ), 'BASIC');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('GB01', 'Bill Gates',     'Seattle Area',                 TRUNC(ADD_MONTHS( SYSDATE, -43*12 )),  ADD_MONTHS( SYSDATE, -2  ), 'SILVER');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('YW01', 'Will B. Yeats',  'Burbank, CA',                   TRUNC(ADD_MONTHS( SYSDATE, -19*12 )),  ADD_MONTHS( SYSDATE, -3  ), 'BASIC');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('OC01', 'Conan O''Bryan', 'NY 47. Str. 4.',               TRUNC(ADD_MONTHS( SYSDATE, -24.6*12)), ADD_MONTHS( SYSDATE, -4  ), 'BASIC');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('GJ01', 'Johnny B. Good', 'NY Broadway 234.',             TRUNC(ADD_MONTHS( SYSDATE, -39*12 )),  ADD_MONTHS( SYSDATE, -5  ), 'BASIC');

UPDATE Members SET DateofBirth=(select dateofbirth from members where member_code='GJ01') where member_code='KK02';

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('KB01', 'Kovács I. Béla', 'Budapest Váci út 123.',        TRUNC(ADD_MONTHS( SYSDATE, -23*12 )),  ADD_MONTHS( SYSDATE, -10 ), 'SILVER');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('CV01', 'Vito Corleone',  'NY 13. Str.',                  TRUNC(ADD_MONTHS( SYSDATE, -64*12 )),  ADD_MONTHS( SYSDATE, -24 ), 'GOLD');

INSERT INTO Members (member_code, Name, Address, DateofBirth, DateofCreation, LevelofMember )
VALUES ('BA01', 'Al Bundy',       'Chicago, IL, next to Marcy',       TRUNC(ADD_MONTHS( SYSDATE, -24*12 )),  ADD_MONTHS( SYSDATE, -25 ), 'GOLD');

-- EN: Insert videos' data
-- HU: Videok bevitele

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('LSTB001', 'Lock, Stock And Two Smoking Barrels',   date'1998-08-28',   'Guy Ritchie', 107, 450, 'Angol, szinkronizált', 'B');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('DGMA001', 'Dogma',   date'1999-05-21',   'Kevin Smith', 130, 450, null, 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('CLER001', 'Clerks',   date'1994-10-19',   'Kevin Smith', 92, 250, 'ff.', 'B');

-- EN: Intentionally has incorrect duration value
-- HU: Az idotartam mezo szandekosan rossz erteku
INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('GLAD001', 'Gladiator',   date'2000-05-05',   'Ridley Scott', 150, 600, 'Oscar-díjas', 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('GLAD092', 'Gladiator',   date'1992-03-06',   'Rowdy Herrington', 101, 250, 'Akció', 'B');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('ENGP001', 'The English Patient',   date'1996-12-06',   'Anthony Minghella', 162, 500, 'Oscar-díjas', 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('KKAM001', 'Kandi Kamera',   date'2015-07-27',   null, 25, 150, 'rejtett felvételek', 'B');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('GODF002', 'The Godfather: Part II',   date'1974-12-20',   'Francis Ford Coppola', 202, 600, 'Oscar-díjas', 'B');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('TLBA099', 'Le Dernier Combat (The Last Battle)',   date'1984-06-12',   'Luc Besson', 92, 600, null, 'B');

-- FIXME: what is this?
-- INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
-- VALUES ('ATLA001', 'Atlantis',            ADD_MONTHS( SYSDATE, -1.5*12 ), 'George Lucas',         120, 500, 'Klasszikus', 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('SUBW001', 'Subway',              date'1985-11-06',   'Luc Besson',           104, 800, NULL, 'B');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('LGBL001', 'Le Grande Bleu',      date'1988-08-19',  'Luc Besson',        168, 200, NULL, 'B');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('TOLO001', 'A tolonc (The Undesirable)',              date'1915-02-25',   'Michael Curtiz',       67, 400, NULL, 'B');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('SPEC007', 'Spectre',                date'2015-11-06',   'Sam Mendes',           148, 600, 'Oscar dijas', 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('ELEM005', 'The Fifth Element',       date'1997-05-01',   'Luc Besson',      126,  600, 'Alapmű', 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('DARC001', 'Jeanne d''Arc',       date'2009-03-04',   'Lauri Timonen',      22,  400, 'Kihagyhatatlan', 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('TRAN001', 'The Transporter',     date'2002-10-11',   'Louis Leterrier, Corey Yuen',      92, 400,  NULL, 'D');

INSERT INTO videos (video_code, Title, Appearance, Director, Duration, Fee, Commentline, Type)
VALUES ('SPAR060', 'Spartacus',     date'1960-11-17',   'Stanley Kubrick',      197, 400,  NULL, 'D');

-- EN: Insert loans' data
-- HU: Kolcsonzesek bevitele

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 12,  NULL
    FROM videos v, members m
   WHERE v.video_code='TRAN001'
     AND m.member_code='BA01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 3,   NULL
    FROM videos v, members m
   WHERE v.video_code='SPEC007'
     AND m.member_code='CV01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 5,   NULL
    FROM videos v, members m
   WHERE v.video_code='LGBL001'
     AND m.member_code='KB01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 1,   NULL
    FROM videos v, members m
   WHERE v.video_code='SUBW001'
     AND m.member_code='GJ01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 254, SYSDATE - 237
    FROM videos v, members m
   WHERE v.video_code='SPEC007'
     AND m.member_code='OC01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 154, SYSDATE - 152
    FROM videos v, members m
   WHERE v.video_code='TRAN001'
     AND m.member_code='KB01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 847, SYSDATE - 746
    FROM videos v, members m
   WHERE v.video_code='TOLO001'
     AND m.member_code='GJ01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 40,  SYSDATE - 39
    FROM videos v, members m
   WHERE v.video_code='SUBW001'
     AND m.member_code='BA01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 83,  SYSDATE - 75
    FROM videos v, members m
   WHERE v.video_code='LGBL001'
     AND m.member_code='CV01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 267,  SYSDATE - 225
    FROM videos v, members m
   WHERE v.video_code='LSTB001'
     AND m.member_code='NI01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 224,  SYSDATE - 183
    FROM videos v, members m
   WHERE v.video_code='LSTB001'
     AND m.member_code='GJ01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 182,  SYSDATE - 120
    FROM videos v, members m
   WHERE v.video_code='LSTB001'
     AND m.member_code='NI02'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 85,  SYSDATE - 75
    FROM videos v, members m
   WHERE v.video_code='LSTB001'
     AND m.member_code='NI01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 312,  SYSDATE - 307
    FROM videos v, members m
   WHERE v.video_code='DGMA001'
     AND m.member_code='KK02'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 245,  SYSDATE - 232
    FROM videos v, members m
   WHERE v.video_code='DGMA001'
     AND m.member_code='VP01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 221,  SYSDATE - 208
    FROM videos v, members m
   WHERE v.video_code='DGMA001'
     AND m.member_code='KK01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 181,  SYSDATE - 179
    FROM videos v, members m
   WHERE v.video_code='DGMA001'
     AND m.member_code='BA01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 43,  SYSDATE - 12
    FROM videos v, members m
   WHERE v.video_code='DGMA001'
     AND m.member_code='NI02'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 483,  SYSDATE - 475
    FROM videos v, members m
   WHERE v.video_code='CLER001'
     AND m.member_code='VP01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 15,  SYSDATE - 5
    FROM videos v, members m
   WHERE v.video_code='CLER001'
     AND m.member_code='EI01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 83,  SYSDATE - 74
    FROM videos v, members m
   WHERE v.video_code='GLAD001'
     AND m.member_code='EI01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 45,  SYSDATE - 25
    FROM videos v, members m
   WHERE v.video_code='GLAD001'
     AND m.member_code='KI01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 12,  SYSDATE - 6
    FROM videos v, members m
   WHERE v.video_code='GLAD001'
     AND m.member_code='FB01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 83,  SYSDATE - 75
    FROM videos v, members m
   WHERE v.video_code='GLAD092'
     AND m.member_code='VP01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 64,  SYSDATE - 32
    FROM videos v, members m
   WHERE v.video_code='ENGP001'
     AND m.member_code='FB01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 25,  SYSDATE - 15
    FROM videos v, members m
   WHERE v.video_code='ENGP001'
     AND m.member_code='NI01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 3,  NULL
    FROM videos v, members m
   WHERE v.video_code='ENGP001'
     AND m.member_code='FB01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 83,  SYSDATE - 75
    FROM videos v, members m
   WHERE v.video_code='KKAM001'
     AND m.member_code='VP01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 82,  SYSDATE - 56
    FROM videos v, members m
   WHERE v.video_code='GODF002'
     AND m.member_code='VP01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 53,  SYSDATE - 22
    FROM videos v, members m
   WHERE v.video_code='GODF002'
     AND m.member_code='KK02'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 20,  SYSDATE - 15
    FROM videos v, members m
   WHERE v.video_code='GODF002'
     AND m.member_code='KI01'
 );

INSERT INTO loans (video_id, member_id, DateofCreation, DateofTurnback)
 (SELECT v.video_id, m.member_id, SYSDATE - 8,  NULL
    FROM videos v, members m
   WHERE v.video_code='GODF002'
     AND m.member_code='GJ01'
 );

COMMIT;
