-- create table visitor (
--     id number constraint user_pk primary key,
--     nickname varchar2(50) not null,
--     email varchar2(50) not null,
--     address varchar2(100),
--     country varchar2(3)
-- );
-- /

-- alter table visitor add constraint user_nickname_uq unique (nickname);
-- alter table visitor add constraint user_email_uq unique (email);

-- insert into visitor (id, nickname, email,address, country) values (1, 'jozin1', 'hehe@a.pl', 'Jozefitwo', 'PL') ;
-- insert into visitor (id, nickname, email,address, country) values (2, 'heniek2', 'gmail@a.pl', 'Krakow', 'EN') ;
-- insert into visitor (id, nickname, email,address, country) values (3, 'grzymaal', 'grzyma@a.pl', 'Jaslo', 'GB') ;


-- select * from visitor;

-- create table customer (
--     id number constraint customer_pk primary key,
--     firstname varchar2(50) not null,
--     lastname varchar2(50) not null,
--     email varchar2(100) not null,
--     password_hash varchar2(255) not null,
--     address varchar2(255),
--     city varchar2(50),
--     country varchar2(3),
--     zip_code varchar2(10),
--     bio varchar2(500),
--     created_at timestamp not null
-- );
-- /

-- alter table customer add constraint customer_email_uq unique (email);

-- insert into customer (id, firstname, lastname, email, password_hash, address, city, country, zip_code, bio, created_at) values (1, 'Pat', 'Świerszcz', 'pat@gmail.com', 'h3jk12h3k12h3', 'Zakliczyn 15', 'Wieś', 'PL', '31-222', 'lorem ipsum', TIMESTAMP '1997-01-31 09:26:50.12') ;
-- insert into customer (id, firstname, lastname, email, password_hash, address, city, country, zip_code, bio, created_at) values (2, 'Dom', 'Zreb', 'dom@gmail.com', 'h3213jk12h3k12h3', '3 Maja', 'Jaslo', 'EN', '31-555', 'lorem ipsum 1', TIMESTAMP '1996-01-31 09:26:50.12') ;
-- insert into customer (id, firstname, lastname, email, password_hash, address, city, country, zip_code, bio, created_at) values (3, 'Mar', 'Kam', 'kam@gmail.com', 'h3jk12123h3k12h3', 'Dluga', 'Tymbark', 'GB', '31-999', 'lorem ipsum 2', TIMESTAMP '1998-01-31 09:26:50.12') ;
-- insert into customer (id, firstname, lastname, email, password_hash, address, city, country, zip_code, bio, created_at) values (4, 'Other', 'Other', 'grzyma@a.pl', 'h3jk12123h33213k12h3', 'Otherska', 'Othercity', 'PL', '31-000', 'lorem ipsum other', TIMESTAMP '1999-01-31 09:26:50.12') ;


-- select * from customer;

-- CREATE TABLE stage_customer
-- (
--   id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) primary key,
--   name VARCHAR(50) NOT NULL,
--   email varchar2(100) not null,
--   country varchar2(3)
-- );

-- drop table stage_customer;

-- create table stage_customer (
--     id number constraint customer_pk primary key,
--     firstname varchar2(50) not null,
--     lastname varchar2(50) not null,
--     email varchar2(100) not null,
--     password_hash varchar2(255) not null,
--     address varchar2(255),
--     city varchar2(50),
--     country varchar2(3),
--     zip_code varchar2(10),
--     bio varchar2(500),
--     created_at timestamp not null
-- );
-- /

-- alter table customer add constraint customer_email_uq unique (email);

-- SELECT nickname, email, country from visitor
-- union
-- SELECT firstname, email, country from customer

-- select * from customer;

-- create table film (
--     id number constraint film_pk primary key,
--     name varchar2(50) not null
-- );

-- create table actor (
--     id number constraint actor_pk primary key,
--     name varchar2(50) not null
-- );

-- insert into actor (id, name) values (1, 'DiCaprio');
-- insert into actor (id, name) values (2, 'Hopkins');
-- insert into actor (id, name) values (3, 'Snipes');

-- select * from actor;

-- create table actor2 (
--     id number constraint actor2_pk primary key,
--     name varchar2(50) not null
-- );

-- insert into actor2 (id, name) values (1, 'Angelina Jolie');
-- insert into actor2 (id, name) values (2, 'Bradd Pitt');
-- insert into actor2 (id, name) values (3, 'Jackie Chan');

-- select * from actor2;

-- create table film_actor (
--     film_id number,
--     actor_id number
-- );

-- alter table film_actor
-- add constraint fk_film_film_actor
-- foreign key (film_id) references film(id);

-- alter table film_actor
-- add constraint fk_actor_film_actor
-- foreign key (actor_id) references actor(id);

-- insert into film_actor (film_id, actor_id) values(1, 2);
-- insert into film_actor (film_id, actor_id) values(2, 1);
-- insert into film_actor (film_id, actor_id) values(3, 1);
-- insert into film_actor (film_id, actor_id) values(1, 3);
-- insert into film_actor (film_id, actor_id) values(2, 2);
-- insert into film_actor (film_id, actor_id) values(3, 1);

-- select * from film_actor;

-- create table movie_cast (
--     movie_id number not null,
--     actor_id number not null
-- );

-- alter table movie_cast
-- add constraint fk_movie_movie_cast
-- foreign key (movie_id) references movie(id);

-- alter table movie_cast
-- add constraint fk_actor_movie_cast
-- foreign key (actor_id) references actor2(id);

-- insert into movie_cast (movie_id, actor_id) values(1, 2);
-- insert into movie_cast (movie_id, actor_id) values(2, 3);
-- insert into movie_cast (movie_id, actor_id) values(3, 1);
-- insert into movie_cast (movie_id, actor_id) values(3, 2);
-- insert into movie_cast (movie_id, actor_id) values(2, 1);
-- insert into movie_cast (movie_id, actor_id) values(4, 3);


-- create table movie (
--     id number constraint movie_pk primary key,
--     title varchar2(50) not null
-- );

-- insert into movie (id, title) values(1, 'Skazani na shawshank');
-- insert into movie (id, title) values(2, 'Nietykalni');
-- insert into movie (id, title) values(4, 'Harry Potter');


-- insert into film (id, name) values(1, 'Harry Potter');
-- insert into film (id, name) values(2, 'Incepcja');
-- insert into film (id, name) values(3, 'Pasiasta pizama');

-- select * from movie;

-- select id one_id, NULL second_id, name FROM film f
-- where not exists (select 1 from movie m
--                   where f.name = m.title)
-- union
-- select NULL, id, title from movie;

-- select id one_id, NULL second_id, name FROM actor a
-- where not exists (select 1 from actor2 a2
--                   where a.name = a2.name)
-- union
-- select NULL, id, name from actor2;

-- select * from film_actor;

-- select email, (firstname || ' ' || lastname) name, country FROM customer c
-- where not exists (select 1 from visitor v
--                   where c.email = v.email)
-- union
-- select email, nickname, country from visitor

-- create table stage_film
-- (
--   id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) primary key,
--   one_id integer,
--   second_id integer,
--   name VARCHAR(50) NOT NULL
-- );

-- CREATE SEQUENCE stage_film_seq
--   START WITH 1
--   INCREMENT BY 1
--   CACHE 100;

-- insert into stage_film (one_id, second_id, name)
-- select id one_id, NULL second_id, name FROM film f
-- where not exists (select 1 from movie m
--                   where f.name = m.title)
-- union
-- select NULL, id, title from movie;

-- select * from stage_film;

-- create table stage_actor
-- (
--   id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) primary key,
--   one_id integer,
--   second_id integer,
--   name VARCHAR(50) NOT NULL
-- );

-- insert into stage_actor(one_id, second_id, name)
-- select id one_id, NULL second_id, name FROM actor a
-- where not exists (select 1 from actor2 a2
--                   where a.name = a2.name)
-- union
-- select NULL, id, name from actor2;

-- select * from stage_actor;

-- select * from actor;
-- select * from actor2;

-- select * from film;
-- select * from movie;

-- create table category (
--     ID number constraint category_pk primary key,
--     NAME varchar2(50) not null
-- );

-- alter table CATEGORY add constraint category_name_uq unique (NAME);

-- create table film_category (
--     film_id number,
--     category_id number
-- );


-- alter table film_category
-- add constraint fk_film_film_category
-- foreign key (film_id) references film(id);

-- alter table film_category
-- add constraint fk_category_film_category
-- foreign key (category_id) references category(id);

-- insert into category (id, name) values (1, 'Psychologiczny');
-- insert into category (id, name) values (2, 'Thriller');
-- insert into category (id, name) values (3, 'Wojenny');
-- insert into category (id, name) values (4, 'Obyczajowy');

-- select * from category;

-- create table genre (
--     id number constraint genre_pk primary key,
--     name varchar2(50) not null
-- );

-- create table movie_genres (
--     movie_id number not null,
--     genre_id number not null
-- );

-- alter table movie_genres
-- add constraint fk_movie_movie_genres
-- foreign key (movie_id) references movie(id);

-- alter table movie_genres
-- add constraint fk_genre_movie_genres
-- foreign key (genre_id) references genre(id);

-- insert into genre (id, name) values (1, 'Dla dzieci');
-- insert into genre (id, name) values (2, 'Sci-Fi');
-- insert into genre (id, name) values (3, 'Psychologiczny');
-- insert into genre (id, name) values (4, 'Romans');

-- select * from genre;

-- select * from stage_film;
-- select * from stage_actor;
-- select * from film_actor;
-- select * from movie_cast;

-- insert into film_category (film_id, category_id) values (1, 4);
-- insert into film_category (film_id, category_id) values (2, 1);
-- insert into film_category (film_id, category_id) values (3, 3);
-- insert into film_category (film_id, category_id) values (1, 1);
-- insert into film_category (film_id, category_id) values (2, 2);

-- select * from film_category;

-- insert into movie_genres (movie_id, genre_id) values (1, 2);
-- insert into movie_genres (movie_id, genre_id) values (2, 3);
-- insert into movie_genres (movie_id, genre_id) values (3, 3);
-- insert into movie_genres (movie_id, genre_id) values (4, 1);
-- insert into movie_genres (movie_id, genre_id) values (2, 1);
-- insert into movie_genres (movie_id, genre_id) values (3, 2);


-- select * from movie_genres;

-- create table stage_category
-- (
--   id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) primary key,
--   cat_id integer,
--   gen_id integer,
--   name VARCHAR(50) NOT NULL
-- );

-- alter table stage_category add constraint stage_category_name_uq unique (name);

-- insert into stage_category(cat_id, gen_id, name)
-- select id cat_id, NULL gen_id, name FROM category c
-- where not exists (select 1 from genre g
--                   where c.name = g.name)
-- union
-- select NULL, id, name from genre;

select * from stage_film;
select * from stage_category;
select * from film_category;
select * from movie_genres;

select stage_film.id film_id, stage_category.id category_id, stage_film.name, stage_category.name from film_category
join stage_film on stage_film.one_id = film_category.film_id
join stage_category on stage_category.cat_id = film_category.category_id
union all
select stage_film.id, stage_category.id, stage_film.name, stage_category.name from movie_genres
join stage_film on stage_film.second_id = movie_genres.movie_id
join stage_category on stage_category.gen_id = movie_genres.genre_id;



-- select stage_film.id film_id, stage_actor.id actor_id, stage_film.name,stage_actor.name from film_actor
-- join stage_film on stage_film.one_id = film_actor.film_id
-- join stage_actor on stage_actor.one_id = film_actor.actor_id
-- union all
-- select stage_film.id film_id, stage_actor.id actor_id, stage_film.name,stage_actor.name from movie_cast
-- join stage_film on stage_film.second_id = movie_cast.movie_id
-- join stage_actor on stage_actor.second_id = movie_cast.actor_id;