🔗 JOIN – tabulu savienošana

-- Lietotāji
CREATE TABLE users (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80)  NOT NULL,
    email    VARCHAR(255) NOT NULL
);

-- Kategorijas
CREATE TABLE categories (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Produkti (FK uz categories)
CREATE TABLE products (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255)   NOT NULL,
    price       DECIMAL(10,2)  NOT NULL,
    category_id INT UNSIGNED   NULL,          -- NULL = bez kategorijas
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Pasūtījumi
CREATE TABLE orders (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    total      DECIMAL(10,2) NOT NULL,
    status     ENUM('gaida','apstrādē','nosūtīts','atcelts') DEFAULT 'gaida',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Pasūtījuma pozīcijas
CREATE TABLE order_items (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    qty        INT NOT NULL DEFAULT 1,
    price      DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Produkti ar kategorijas nosaukumu (tikai tie, kuriem IR kategorija)
SELECT
    p.id,
    p.name        AS produkts,
    p.price,
    c.name        AS kategorija
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

-- Pasūtījumi ar lietotāja vārdu
SELECT
    o.id          AS pasūtījums,
    u.username    AS lietotājs,
    u.email,
    o.total,
    o.status,
    o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC;

-- Triju tabulu JOIN
SELECT
    o.id          AS pasūtījums,
    u.username,
    p.name        AS produkts,
    oi.qty,
    oi.price,
    oi.qty * oi.price AS rindu_summa
FROM orders o
INNER JOIN users       u  ON o.user_id    = u.id
INNER JOIN order_items oi ON oi.order_id  = o.id
INNER JOIN products    p  ON oi.product_id = p.id
WHERE o.status = 'nosūtīts'
ORDER BY o.id, p.name;

-- Četru tabulu JOIN
SELECT
    o.id,
    u.username,
    p.name        AS produkts,
    c.name        AS kategorija,
    oi.qty,
    oi.price
FROM orders o
INNER JOIN users       u  ON o.user_id     = u.id
INNER JOIN order_items oi ON oi.order_id   = o.id
INNER JOIN products    p  ON oi.product_id = p.id
LEFT  JOIN categories  c  ON p.category_id = c.id;

-- LEFT JOIN: VISI produkti, arī bez kategorijas
SELECT
    p.name,
    p.price,
    c.name AS kategorija   -- NULL, ja nav kategorijas
FROM products p
LEFT JOIN categories c ON p.category_id = c.id;

-- Atrast produktus BEZ kategorijas (NULL pārbaude)
SELECT p.name, p.price
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE c.id IS NULL;   -- Tikai tie, kur JOIN neatrada atbilstību

-- Visi lietotāji ar vai bez pasūtījumiem
SELECT
    u.username,
    COUNT(o.id)    AS pasūtījumu_skaits,
    COALESCE(SUM(o.total), 0) AS kopā
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY pasūtījumu_skaits DESC;

-- Atrast lietotājus, kas NEKAD nav pasūtījuši
SELECT u.username, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- RIGHT JOIN: reti izmanto – var aizstāt ar LEFT JOIN no otras puses
-- Visas kategorijas, arī tukšās (bez produktiem)
SELECT c.name AS kategorija, COUNT(p.id) AS produkti
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY produkti DESC;
-- Ekvivalents LEFT JOIN:
-- FROM categories c LEFT JOIN products p ON ...

-- Subquery WHERE IN – produkti no noteiktām kategorijām
SELECT name, price
FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE name LIKE '%Elektro%'
);

-- Subquery WHERE NOT IN
SELECT name FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

-- EXISTS – pārbaudīt, vai subquery atgriež kaut ko
SELECT u.username
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.total > 500
);

-- NOT EXISTS
SELECT u.username
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Scalar subquery (atgriež vienu vērtību)
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS vid_cena,
    price / (SELECT AVG(price) FROM products) * 100 AS procenti_no_videjā
FROM products
ORDER BY price DESC;

-- Derived table (subquery no FROM)
SELECT kategorija, COUNT(*) AS produktu_skaits
FROM (
    SELECT c.name AS kategorija
    FROM products p
    INNER JOIN categories c ON p.category_id = c.id
    WHERE p.price > 100
) AS dārgi_produkti
GROUP BY kategorija;

-- UNION apvieno divu SELECT rezultātus (noņem dublikātus)
-- Kolonnu skaitam un tipiem jābūt vienādiem!

SELECT username AS virsraksts, 'Lietotājs' AS tips FROM users
UNION
SELECT name, 'Produkts' FROM products;

-- UNION ALL – saglabā dublikātus (ātrāk)
SELECT email FROM newsletter_subscribers
UNION ALL
SELECT email FROM users;

-- UNION ar ORDER BY (tikai pēc pēdējā SELECT)
SELECT name, price FROM products WHERE category_id = 1
UNION
SELECT name, price FROM products WHERE category_id = 2
ORDER BY price DESC;

-- Piemērs: apvienot žurnāla tabulas pa gadiem
SELECT id, created_at, amount FROM transactions_2024
UNION ALL
SELECT id, created_at, amount FROM transactions_2025
ORDER BY created_at DESC
LIMIT 50;