-- Gintaro Kelias Database Initialization
-- Built by Tomas Kazlauskas, 2022

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    image VARCHAR(255) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    author VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    role ENUM('admin', 'customer', 'wholesale') DEFAULT 'customer'
);

CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Seed products: Baltic amber jewelry
INSERT INTO products (name, description, price) VALUES
('Saulyte Karoliai', 'Handcrafted Baltic amber necklace with honey-colored cabochon beads. Each bead polished by hand in our Klaipeda workshop. 45cm length with sterling silver clasp.', 89.00),
('Gintarine Apyranke', 'Multi-strand amber bracelet featuring cognac and cherry amber. Elastic band fits most wrists. Stones sourced from the Curonian Spit coast.', 45.00),
('Juru Auskarai', 'Drop earrings with sea-green amber and silver wire wrap. These rare color stones contain 40-million-year-old botanical inclusions visible under magnification.', 120.00),
('Meduza Pakabukas', 'Statement pendant with a large Baltic amber cabochon containing a preserved insect inclusion. Sterling silver bezel setting. Comes with a certificate of authenticity.', 195.00),
('Vasaros Rinkinys', 'Summer collection set: matching necklace, bracelet, and earrings in butterscotch amber. Gift boxed with care instructions. Perfect for the holiday season.', 210.00),
('Gintaro Ziedas', 'Adjustable ring with oval amber stone in antique bronze setting. Classic design popular with our European buyers. One size fits most.', 38.00),
('Baltijos Karoliai', 'Raw amber chip necklace in mixed colors -- honey, cognac, cherry, and milky white. Natural shapes, not machine-polished. A piece of the Baltic Sea.', 55.00),
('Klasikine Sagute', 'Vintage-style amber brooch with filigree silver frame. Inspired by traditional Lithuanian jewelry designs from the Amber Museum collection in Palanga.', 75.00),
('Vaikiska Apyranke', 'Children''s amber teething bracelet. Small polished honey amber beads on elastic cord. Traditional Lithuanian remedy, popular with our Japanese customers.', 22.00),
('Meno Pakabukas', 'Art piece pendant: large irregular amber with visible plant inclusions, wrapped in oxidized silver wire. Each piece is unique -- no two are alike.', 165.00);

-- Seed users including admin with weak credentials
INSERT INTO users (username, password_hash, email, role) VALUES
('admin', MD5('admin'), 'tomas@gintarokelias.lt', 'admin'),
('ruta', MD5('gintaras2022'), 'ruta@gintarokelias.lt', 'admin'),
('anna_mueller', MD5('shopping123'), 'anna.mueller@example.de', 'customer'),
('sophie_dupont', MD5('ambre2024'), 'sophie.dupont@example.fr', 'customer'),
('tanaka_yuki', MD5('kohaku99'), 'tanaka.yuki@example.jp', 'customer'),
('wholesale_kaunas', MD5('bulk_order_2023'), 'orders@kaunascraft.lt', 'wholesale'),
('wholesale_berlin', MD5('amber_import'), 'info@berlinamber.de', 'wholesale'),
('wholesale_lyon', MD5('bijoux_ambre'), 'achat@lyonambre.fr', 'wholesale');

-- Seed reviews (clean, no XSS)
INSERT INTO reviews (product_id, author, content, created_at) VALUES
(1, 'Anna M.', 'Beautiful necklace! The amber beads have such a warm glow. I wear it every day. Shipping to Germany was fast.', '2026-08-15 14:30:00'),
(1, 'Sophie D.', 'The craftsmanship is excellent. You can tell these are handmade with care. Will order more for Christmas gifts.', '2026-09-02 10:15:00'),
(3, 'Yuki T.', 'The green amber is stunning. I can actually see tiny plant fragments inside when I hold them up to the light. A treasure from the Baltic.', '2026-07-20 08:45:00'),
(5, 'Anna M.', 'Bought the summer set as a birthday gift for my mother. She was so happy she cried. The gift box is beautiful too.', '2026-08-28 16:00:00'),
(4, 'Pierre L.', 'The insect inclusion is fascinating. Ruta included a handwritten note about the amber''s origin. Personal touch you don''t get from big shops.', '2026-09-10 11:30:00'),
(9, 'Yuki T.', 'Ordered three for my nieces. Very popular in Japan for teething. The amber is genuine and the elastic is strong.', '2026-07-05 09:00:00');

-- Seed orders
INSERT INTO orders (user_id, total, status, created_at) VALUES
(3, 89.00, 'delivered', '2026-08-14 10:00:00'),
(3, 210.00, 'delivered', '2026-08-27 14:30:00'),
(4, 120.00, 'shipped', '2026-09-01 09:15:00'),
(5, 66.00, 'delivered', '2026-07-04 07:30:00'),
(6, 450.00, 'processing', '2026-09-12 11:00:00'),
(7, 355.00, 'shipped', '2026-09-08 08:45:00'),
(8, 280.00, 'pending', '2026-09-15 14:00:00');