-- ==============================
-- 1. Vytvoření databáze (volitelné)
-- ==============================
-- Pokud již databáze existuje, tento krok přeskoč.
CREATE DATABASE eshop_oddil;
\c eshop_oddil;

-- ======================
-- 2. Výčtové typy (ENUM)
-- ======================
CREATE TYPE user_role AS ENUM ('admin', 'zakaznik');
CREATE TYPE order_status AS ENUM ('nova', 'prijata', 'vyřízena', 'stornovana');

-- ========================
-- 3. Tabulka users (registrovaní)
-- ========================
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role user_role DEFAULT 'zakaznik',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 4. Tabulka guest_orders (anonymní objednávky)
-- =========================
CREATE TABLE guest_orders (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  address TEXT NOT NULL,
  phone TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 5. Tabulka products
-- =========================
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  image TEXT,
  price NUMERIC(10,2) NOT NULL,
  category TEXT,
  customizable_name BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 6. Tabulka orders
-- =========================
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE SET NULL,
  guest_id INT REFERENCES guest_orders(id) ON DELETE SET NULL,
  status order_status DEFAULT 'nova',
  total NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 7. Tabulka order_items
-- =========================
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT REFERENCES products(id) ON DELETE SET NULL,
  qty INT NOT NULL CHECK (qty > 0),
  unit_price NUMERIC(10,2) NOT NULL,
  name_print TEXT  -- jméno pro tisk (např. na batoh)
);

-- =========================
-- 8. Tabulka payments
-- =========================
CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id) ON DELETE CASCADE,
  payment_method TEXT,
  payment_status TEXT,
  transaction_id TEXT,
  paid_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 9. Tabulka promotions (promo kódy)
-- =========================
CREATE TABLE promotions (
  id SERIAL PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  discount_percent INT NOT NULL CHECK (discount_percent >= 0 AND discount_percent <= 100),
  valid_from TIMESTAMP,
  valid_to TIMESTAMP,
  limit_uses INT,  -- NULL znamená neomezeno
  used_count INT DEFAULT 0,
  role_required user_role,  -- použitelný jen pro určitou roli, nebo NULL pro všechny
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 10. Tabulka order_promotions (který kód použit v objednávce)
-- =========================
CREATE TABLE order_promotions (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id) ON DELETE CASCADE,
  promotion_id INT REFERENCES promotions(id) ON DELETE CASCADE
);

-- =========================
-- 11. Testovací data
-- =========================

-- Uživatele
INSERT INTO users (name, email, password_hash, role)
VALUES
  ('Admin Uživateľ', 'admin@example.com', 'hashed_admin_pwd', 'admin'),
  ('Jan Novak', 'jan@example.com', 'hashed_password', 'zakaznik');

-- Produkty
INSERT INTO products (title, description, image, price, category, customizable_name)
VALUES
  ('Batoh klubový', 'Batoh s logem oddílu, možnost potisku jména', '/img/batoh.jpg', 899.00, 'doplnky', TRUE),
  ('Triko oddílové', 'Triko s klubovým potiskem', '/img/triko.jpg', 499.00, 'oblečení', FALSE);

-- Promo kód
INSERT INTO promotions (code, discount_percent, valid_from, valid_to, limit_uses, role_required)
VALUES
  ('KLUB10', 10, NOW(), NOW() + INTERVAL '30 days', NULL, 'zakaznik'),
  ('VIP20', 20, NOW(), NOW() + INTERVAL '7 days', 100, 'zakaznik');

-- Objednávka registrovaného uživatele
INSERT INTO orders (user_id, status, total)
VALUES
  (2, 'nova', 899.00);

-- Položka objednávky (s možností tisku)
INSERT INTO order_items (order_id, product_id, qty, unit_price, name_print)
VALUES
  (1, 1, 1, 899.00, 'Novák');

-- Použitý promo kód pro objednávku
INSERT INTO order_promotions (order_id, promotion_id)
VALUES
  (1, 1);

-- Platba pro objednávku
INSERT INTO payments (order_id, payment_method, payment_status, transaction_id, paid_at)
VALUES
  (1, 'comgate', 'pending', 'TX123456789', NULL);