// =============================================================================
// DDL
// ============================================================================

CREATE TABLE cinema.movie
(
    id bigint NOT NULL DEFAULT nextval('cinema.movie_id_seq'::regclass),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    duration integer NOT NULL,
    CONSTRAINT movie_pkey PRIMARY KEY (id)
)




CREATE TABLE cinema.session
(
    id bigint NOT NULL DEFAULT nextval('cinema.session_id_seq'::regclass),
    start timestamp without time zone NOT NULL,
    price numeric(10,3) NOT NULL,
    movie_id bigint,
    CONSTRAINT session_pkey PRIMARY KEY (id),
    CONSTRAINT fk_cinema_session_movie_id FOREIGN KEY (movie_id)
        REFERENCES cinema.movie (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
CREATE INDEX id_cinema_session_movie_id
ON cinema.session(movie_id)



CREATE TABLE cinema.ticket
(
    id bigint NOT NULL DEFAULT nextval('cinema.ticket_id_seq'::regclass),
    session_id bigint,
    CONSTRAINT ticket_pkey PRIMARY KEY (id),
    CONSTRAINT fk_cinema_ticket_session_id FOREIGN KEY (session_id)
        REFERENCES cinema.session (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)


CREATE INDEX id_cinema_ticket_session_id
ON cinema.ticket(session_id)






// =============================================================================
// ошибки в расписании
// =============================================================================

WITH sessions_interval AS (
    SELECT m.name, s.start,
    s.start + (m.duration::text||'minute')::interval AS end,
    m.duration,
    LEAD(start)  OVER (ORDER BY start) AS next_start,
    LEAD(m.name) OVER (ORDER BY start) AS next_name,
    -(LEAD(start)  OVER (ORDER BY start) - s.start - (m.duration::text||'minute')::interval)  AS overlap
    FROM session s
    LEFT JOIN movie m ON s.movie_id = m.id
)
SELECT *
FROM sessions_interval
WHERE overlap > interval '0 minute'


// =============================================================================
// перерывы 30 минут и более между фильмами
// =============================================================================

WITH sessions_interval AS (
    SELECT m.name, s.start,
    s.start + (m.duration::text||'minute')::interval AS end,
    m.duration,
    LEAD(start) OVER (ORDER BY start) AS next_start,
    LEAD(start) OVER (ORDER BY start) - s.start - (m.duration::text||'minute')::interval  AS break,
    LEAD(m.name)OVER (ORDER BY start) AS next_name
    FROM session s
    LEFT JOIN movie m ON s.movie_id = m.id
)
SELECT *
FROM sessions_interval
WHERE break > interval '30 minute'
ORDER BY break DESC





// =============================================================================
// данные фильму / по всем фильмам
// =============================================================================
(SELECT
m.name AS movie,
SUM(s.price) AS income,
COUNT(t.id) AS users_total,
ROUND(AVG(t.id)::numeric,1)
FROM session s
JOIN movie m ON s.movie_id = m.id
JOIN ticket t ON t.session_id = s.id
GROUP BY movie
ORDER BY income DESC)

UNION ALL SELECT
'ВСЕГО' AS movie,
SUM(s.price) AS income,
COUNT(t.id) AS users_total,
ROUND(AVG(t.id)::numeric,1)
FROM session s

JOIN movie m ON s.movie_id = m.id
JOIN ticket t ON t.session_id = s.id
GROUP BY movie