/* ==========================================================
   Baza do ćwiczeń JOIN: cwiczenia_join
   Silnik: MariaDB/MySQL
   Uruchom w phpMyAdmin -> SQL
   ========================================================== */

DROP DATABASE IF EXISTS cwiczenia_join;
CREATE DATABASE cwiczenia_join
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_polish_ci;

USE cwiczenia_join;

-- ===== Słowniki
CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL
);

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  category_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  price_net DECIMAL(10,2) NOT NULL,
  vat_rate DECIMAL(4,2) NOT NULL DEFAULT 0.23,
  CONSTRAINT fk_products_cat
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(60) NOT NULL,
  last_name  VARCHAR(60) NOT NULL,
  email      VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL
);

CREATE TABLE coupons (
  id INT PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(30) NOT NULL UNIQUE,
  discount_pct DECIMAL(5,2) NOT NULL
);

-- ===== Transakcje
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  coupon_id INT NULL,
  status VARCHAR(20) NOT NULL, -- NEW/PAID/SENT/CANCELLED
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_orders_coupon
    FOREIGN KEY (coupon_id) REFERENCES coupons(id)
);

CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL,
  price_net_at_time DECIMAL(10,2) NOT NULL,
  vat_rate_at_time DECIMAL(4,2) NOT NULL,
  CONSTRAINT fk_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_items_product
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE payments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL UNIQUE,
  method VARCHAR(20) NOT NULL, -- card/blik/transfer/cash
  paid_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_payments_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE TABLE shipments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL UNIQUE,
  courier VARCHAR(30) NOT NULL, -- dpd/inpost/ups
  shipped_at DATETIME NOT NULL,
  delivered_at DATETIME NULL,
  CONSTRAINT fk_shipments_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- ===== Dane
INSERT INTO categories (name) VALUES
('Napoje'), ('Jedzenie'), ('Chemia'), ('Elektronika');

INSERT INTO products (category_id, name, price_net, vat_rate) VALUES
(1, 'Kawa 250g',           19.99, 0.23),
(1, 'Herbata 100g',         9.50, 0.23),
(1, 'Woda 1.5L',            2.00, 0.23),
(2, 'Chleb',                4.20, 0.05),
(2, 'Ser 200g',            12.35, 0.05),
(2, 'Czekolada 90g',        6.67, 0.23),
(3, 'Płyn do naczyń',       7.99, 0.23),
(4, 'Mysz komputerowa',    49.00, 0.23),
(4, 'Słuchawki',          120.00, 0.23); -- produkt celowo "nigdy niezamówiony"

INSERT INTO customers (first_name, last_name, email, created_at) VALUES
('Anna',  'Nowak',     'anna.nowak@example.com',  '2026-01-10 10:00:00'),
('Jan',   'Kowalski',  'jan.kowalski@example.com','2026-01-12 12:00:00'),
('Ola',   'Zając',     'ola.zajac@example.com',   '2026-02-01 08:30:00'),
('Piotr', 'Wójcik',    'piotr.wojcik@example.com','2026-02-10 14:00:00'),
('Ewa',   'Lis',       'ewa.lis@example.com',     '2026-03-01 09:15:00'),
('Marek', 'Król',      'marek.krol@example.com',  '2026-03-02 10:00:00'); -- klient bez zamówień

INSERT INTO coupons (code, discount_pct) VALUES
('RABAT10', 10.00),
('VIP20',   20.00),
('NIEUZYTY5', 5.00); -- kupon nigdy nieużyty

INSERT INTO orders (customer_id, coupon_id, status, created_at) VALUES
(1, NULL, 'NEW',       '2026-03-01 10:00:00'),
(1, 1,    'PAID',      '2026-03-02 11:00:00'),
(2, NULL, 'PAID',      '2026-03-02 12:30:00'),
(2, 2,    'SENT',      '2026-03-05 09:00:00'),
(3, NULL, 'CANCELLED', '2026-03-06 16:00:00'),
(4, 1,    'PAID',      '2026-03-07 13:00:00'),
(5, NULL, 'NEW',       '2026-03-08 09:45:00');

INSERT INTO order_items (order_id, product_id, qty, price_net_at_time, vat_rate_at_time) VALUES
(1, 1, 1, 19.99, 0.23),
(1, 4, 2,  4.20, 0.05),

(2, 2, 1,  9.50, 0.23),
(2, 6, 3,  6.67, 0.23),

(3, 8, 1, 49.00, 0.23),
(3, 3, 6,  2.00, 0.23),

(4, 5, 2, 12.35, 0.05),
(4, 7, 1,  7.99, 0.23),

(5, 1, 1, 19.99, 0.23),

(6, 4, 5,  4.20, 0.05),
(6, 3,10,  2.00, 0.23),

(7, 2, 2,  9.50, 0.23);

INSERT INTO payments (order_id, method, paid_at, amount) VALUES
(2, 'blik',     '2026-03-02 11:05:00', 29.51),
(3, 'card',     '2026-03-02 12:35:00', 61.00),
(4, 'transfer', '2026-03-05 09:10:00', 33.69),
(6, 'card',     '2026-03-07 13:05:00', 41.00);

INSERT INTO shipments (order_id, courier, shipped_at, delivered_at) VALUES
(4, 'inpost', '2026-03-05 12:00:00', '2026-03-06 10:00:00');