CREATE TABLE actors (
    id_actors           NUMBER(3) NOT NULL,
    actors_first_name   CLOB NOT NULL,
    actors_last_name    CLOB NOT NULL,
    actors_email        VARCHAR2(30 CHAR)
);

ALTER TABLE actors ADD CONSTRAINT actors_pk PRIMARY KEY ( id_actors );

CREATE TABLE awards (
    awards_name        VARCHAR2(30),
    play_id_play       NUMBER(3) NOT NULL,
    actors_id_actors   NUMBER(3) NOT NULL
);

CREATE TABLE play (
    id_play         NUMBER(3) NOT NULL,
    play_name       CLOB NOT NULL,
    play_duration   NUMBER(3) NOT NULL,
    play_author     CLOB NOT NULL,
    play_genre      CLOB NOT NULL,
    play_language   CLOB NOT NULL
);

ALTER TABLE play ADD CONSTRAINT play_pk PRIMARY KEY ( id_play );

CREATE TABLE producer (
    id_producer           NUMBER(3) NOT NULL,
    producer_first_name   CLOB NOT NULL,
    producer_last_name    CLOB NOT NULL,
    producer_email        VARCHAR2(30 CHAR),
    producer_phone        NUMBER(11),
    play_id_play          NUMBER(3) NOT NULL
);

ALTER TABLE producer ADD CONSTRAINT producer_pk PRIMARY KEY ( id_producer );

CREATE TABLE room (
    id_room              NUMBER(3) NOT NULL,
    room_capacity        NUMBER(3) NOT NULL,
    room_name            CLOB NOT NULL,
    theatre_id_theatre   NUMBER(3) NOT NULL
);

ALTER TABLE room ADD CONSTRAINT room_pk PRIMARY KEY ( id_room );

CREATE TABLE seats (
    seat_number    NUMBER(3) NOT NULL,
    seat_row       NUMBER(2) NOT NULL,
    room_id_room   NUMBER(3) NOT NULL
);

ALTER TABLE seats ADD CONSTRAINT seats_pk PRIMARY KEY ( seat_number );

CREATE TABLE showings (
    id_showings    NUMBER(3) NOT NULL,
    price          NUMBER(2,2) NOT NULL,
    date_time      DATE NOT NULL,
    room_id_room   NUMBER(3) NOT NULL,
    play_id_play   NUMBER(3) NOT NULL
);

ALTER TABLE showings ADD CONSTRAINT showings_pk PRIMARY KEY ( id_showings );

CREATE TABLE theatre (
    id_theatre         NUMBER(3) NOT NULL,
    theatre_name       CLOB NOT NULL,
    theatre_address    VARCHAR2(30 CHAR) NOT NULL,
    theatre_phone      NUMBER(11) NOT NULL,
    theatre_capacity   NUMBER(3) NOT NULL
);

ALTER TABLE theatre ADD CONSTRAINT theatre_pk PRIMARY KEY ( id_theatre );

CREATE TABLE ticket_status (
    id_ticket     NUMBER(3) NOT NULL,
    ticket_name   CLOB NOT NULL
);

ALTER TABLE ticket_status ADD CONSTRAINT ticket_status_pk PRIMARY KEY ( id_ticket );

CREATE TABLE tickets (
    ticket_number             NUMBER(3) NOT NULL,
    seats_seat_number         NUMBER(3) NOT NULL,
    seats_seat_row            NUMBER NOT NULL,
    ticket_status_id_ticket   NUMBER(3) NOT NULL,
    visitors_id_visitor       NUMBER(3) NOT NULL
);

ALTER TABLE tickets ADD CONSTRAINT tickets_pk PRIMARY KEY ( ticket_number );

CREATE TABLE visitors (
    id_visitor           NUMBER(3) NOT NULL,
    visitor_first_name   CLOB NOT NULL,
    visitor_last_name    CLOB NOT NULL,
    visitor_address      VARCHAR2(30 CHAR),
    visitor_phone        NUMBER(11)
);

ALTER TABLE visitors ADD CONSTRAINT visitors_pk PRIMARY KEY ( id_visitor );

ALTER TABLE awards
    ADD CONSTRAINT awards_actors_fk FOREIGN KEY ( actors_id_actors )
        REFERENCES actors ( id_actors );

ALTER TABLE awards
    ADD CONSTRAINT awards_play_fk FOREIGN KEY ( play_id_play )
        REFERENCES play ( id_play );

ALTER TABLE producer
    ADD CONSTRAINT producer_play_fk FOREIGN KEY ( play_id_play )
        REFERENCES play ( id_play );

ALTER TABLE room
    ADD CONSTRAINT room_theatre_fk FOREIGN KEY ( theatre_id_theatre )
        REFERENCES theatre ( id_theatre );

ALTER TABLE seats
    ADD CONSTRAINT seats_room_fk FOREIGN KEY ( room_id_room )
        REFERENCES room ( id_room );

ALTER TABLE showings
    ADD CONSTRAINT showings_play_fk FOREIGN KEY ( play_id_play )
        REFERENCES play ( id_play );

ALTER TABLE showings
    ADD CONSTRAINT showings_room_fk FOREIGN KEY ( room_id_room )
        REFERENCES room ( id_room );

ALTER TABLE tickets
    ADD CONSTRAINT tickets_seats_fk FOREIGN KEY ( seats_seat_number )
        REFERENCES seats ( seat_number );

ALTER TABLE tickets
    ADD CONSTRAINT tickets_ticket_status_fk FOREIGN KEY ( ticket_status_id_ticket )
        REFERENCES ticket_status ( id_ticket );

ALTER TABLE tickets
    ADD CONSTRAINT tickets_visitors_fk FOREIGN KEY ( visitors_id_visitor )
        REFERENCES visitors ( id_visitor );