CREATE TABLE objednavka (
id INTEGER NOT NULL,
zarucni_doba INTEGER NOT NULL,
datum_objednani DATE NOT NULL,
zakaznik_id INTEGER NOT NULL,
vyrobek_id INTEGER NOT NULL,
prodavac_id INTEGER NOT NULL
);

ALTER TABLE objednavka ADD CONSTRAINT objednavka_pk PRIMARY KEY ( id );

CREATE TABLE prodavac (
id INTEGER NOT NULL,
jmeno VARCHAR2(30 CHAR) NOT NULL
);

ALTER TABLE prodavac ADD CONSTRAINT prodavac_pk PRIMARY KEY ( id );

CREATE TABLE vyrobek (
id INTEGER NOT NULL,
nazev VARCHAR2(30 CHAR) NOT NULL
);

ALTER TABLE vyrobek ADD CONSTRAINT vyrobek_pk PRIMARY KEY ( id );

CREATE TABLE zakaznik (
id INTEGER NOT NULL,
jmeno VARCHAR2(30 CHAR) NOT NULL
);

ALTER TABLE zakaznik ADD CONSTRAINT zakaznik_pk PRIMARY KEY ( id );

ALTER TABLE objednavka
ADD CONSTRAINT objednavka_prodavac_fk FOREIGN KEY ( prodavac_id )
REFERENCES prodavac ( id );

ALTER TABLE objednavka
ADD CONSTRAINT objednavka_vyrobek_fk FOREIGN KEY ( vyrobek_id )
REFERENCES vyrobek ( id );

ALTER TABLE objednavka
ADD CONSTRAINT objednavka_zakaznik_fk FOREIGN KEY ( zakaznik_id )
REFERENCES zakaznik ( id );

Insert into Prodavac(ID, Jmeno) values (0,'karel');
Insert into Prodavac(ID, Jmeno) values (1,'pepa');
Insert into Prodavac(ID, Jmeno) values (2,'anezka');
Insert into Prodavac(ID, Jmeno) values (3,'urvalek');

Insert into Vyrobek (ID, Nazev) values (0,'sekacek');
Insert into Vyrobek(ID, Nazev) values (1,'tyc');
Insert into Vyrobek(ID, Nazev) values (2,'sekera');
Insert into Vyrobek(ID, Nazev) values (3,'maceta');

Insert into Zakaznik(ID, Jmeno) values (0,'kajinek');
Insert into Zakaznik(ID, Jmeno) values (1,'roubal');
Insert into Zakaznik(ID, Jmeno) values (2,'cerny');
Insert into Zakaznik(ID, Jmeno) values (3,'breburda');
Insert into Zakaznik(ID, Jmeno) values (4,'hubka');

alter session set NLS_DATE_FORMAT = 'YYYYMMDD';
Insert into Objednavka (ID,zarucni_doba,Datum_objednani,zakaznik_ID,vyrobek_ID,prodavac_ID) values (0,'2','20101005',0,0,0);
Insert into Objednavka (ID,zarucni_doba,Datum_objednani,zakaznik_ID,vyrobek_ID,prodavac_ID) values (1,'2','20171005',0,0,1);
Insert into Objednavka (ID,zarucni_doba,Datum_objednani,zakaznik_ID,vyrobek_ID,prodavac_ID) values (2,'2','20101005',1,1,1);
Insert into Objednavka (ID,zarucni_doba,Datum_objednani,zakaznik_ID,vyrobek_ID,prodavac_ID) values (3,'2','20101005',2,2,2);

-- 1) Prodavači bez prodejů a zákazníci bez nákupů
SELECT prodavaci.jmeno AS jmeno_prodavace, zakaznici.jmeno AS jmeno_zakaznika
FROM (
    SELECT prodavac.jmeno
    FROM prodavac
    WHERE prodavac.id NOT IN (SELECT DISTINCT prodavac_id FROM objednavka)
) prodavaci
CROSS JOIN (
    SELECT zakaznik.jmeno
    FROM zakaznik
    WHERE zakaznik.id NOT IN (SELECT DISTINCT zakaznik_id FROM objednavka)
) zakaznici;

-- 2) Poslední výrobek kupovaný každým zákazníkem
SELECT zakaznik.jmeno, vyrobek.nazev
FROM objednavka
JOIN zakaznik ON objednavka.zakaznik_id = zakaznik.id
JOIN vyrobek ON objednavka.vyrobek_id = vyrobek.id
WHERE (objednavka.zakaznik_id, objednavka.datum_objednani) IN (
    SELECT objednavka2.zakaznik_id, MAX(objednavka2.datum_objednani)
    FROM objednavka objednavka2
    GROUP BY objednavka2.zakaznik_id
);

-- 3. Výrobky, které už nejsou v záruce (ale prodaly se)
SELECT DISTINCT v.nazev
FROM vyrobek v
JOIN objednavka o ON v.id = o.vyrobek_id
WHERE ADD_MONTHS(o.datum_objednani, o.zarucni_doba * 12) < SYSDATE

UNION

SELECT nazev
FROM vyrobek
WHERE id NOT IN (SELECT vyrobek_id FROM objednavka);

-- 4) Prodavači, kteří prodali výrobky všem zákazníkům tento rok
SELECT p.jmeno
FROM prodavac p
WHERE NOT EXISTS (
    SELECT z.id 
    FROM zakaznik z
    WHERE NOT EXISTS (
        SELECT o.id 
        FROM objednavka o 
        WHERE o.prodavac_id = p.id 
          AND o.zakaznik_id = z.id
          AND EXTRACT(YEAR FROM o.datum_objednani) = 2026
    )
);