-- -- rem drop tables
-- drop table customer;
-- drop table movie;
-- drop table actor;
-- drop table theater;
-- drop table schedule;
-- drop table ticketing;
-- rem create tables

create table CUSTOMER(
    account_id varchar2(20) not null,
    password varchar2(20) not null,
    name varchar2(20),
    age number,
    sex varchar2(6),
    CONSTRAINT pk_customer primary key (account_id)
);

create table MOVIE(
    mid varchar(20) not null,
    movie_name varchar2(50) not null,
    director varchar2(10) not null,
    release_date date,
    running_time number,
    movie_rating varchar(5),
    movie_state varchar(10),
    CONSTRAINT pk_movie primary key (mid)
);

create table ACTOR(
    actor_name varchar(10) not null,
    mid varchar(50) not null,
    CONSTRAINT fk_movie foreign key (mid) references  MOVIE (mid) ON DELETE cascade,
    CONSTRAINT pk_actor primary key (actor_name, mid)
);

create table THEATER(
    tname varchar(10) not null,
    now_movie varchar(10),
    theater_state varchar(10),
    total_seats number,
    CONSTRAINT pk_theater primary key (tname),
    CONSTRAINT fk_movie_theater foreign key (now_movie) references MOVIE (mid) ON DELETE cascade
);

create table MOVIESCHEDULE(
    schedule_date date,
    time timestamp,
    tname varchar(10) not null,
    mid varchar(20) not null,
    sid varchar(10) not null,
    CONSTRAINT pk_schedule primary key (sid),
    CONSTRAINT fk_schedule_movie foreign key (mid) references MOVIE (mid) on delete cascade,
    CONSTRAINT fk_schedule_theater foreign key (tname) references theater (tname) on delete cascade
);
create table TICKETING(
    id varchar(20) not null,
    rc_date date,
    seats varchar(10),
    status varchar(10),
    account_id varchar2(20) not null,
    schedule_id varchar(20) not null,
    CONSTRAINT ticketing_id primary key (id),
    CONSTRAINT fk_ticketing_account foreign key (account_id) references CUSTOMER (account_id) ON DELETE cascade,
    CONSTRAINT fk_ticketing_schedule foreign key (schedule_id) references MOVIESchedule (sid) ON DELETE cascade
);

commit;
select *
from TICKETING;