-- =====================================================================
--  Демо-база інтернет-магазину — курс аналітики даних SEOWORK
--  https://www.seowork.in.ua/kurs-analityky-danykh/
--  MySQL 8.0+  ·  кодування utf8mb4
--  Таблиці: customers, products, orders, order_items
--  Імпорт:  mysql -u root -p < seowork-demo-shop.sql
--       або у MySQL Workbench: Server > Data Import > Import from Self-Contained File
-- =====================================================================
CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop;
SET NAMES utf8mb4;

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  city VARCHAR(60)  NOT NULL
) ENGINE=InnoDB;

CREATE TABLE products (
  id       INT PRIMARY KEY,
  name     VARCHAR(120)   NOT NULL,
  price    DECIMAL(10,2)  NOT NULL,
  category VARCHAR(60)    NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  status      ENUM('completed','pending','cancelled') NOT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  qty        INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders(id),
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

INSERT INTO customers (id,name,city) VALUES
(1,'Олена Ковальчук','Київ'),
(2,'Ігор Бондаренко','Львів'),
(3,'Марія Шевченко','Одеса'),
(4,'Андрій Ткаченко','Харків'),
(5,'Наталія Мельник','Дніпро'),
(6,'Сергій Кравець','Київ'),
(7,'Юлія Поліщук','Львів'),
(8,'Дмитро Савчук','Одеса'),
(9,'Оксана Гнатюк','Київ'),
(10,'Віталій Руденко','Запоріжжя');

INSERT INTO products (id,name,price,category) VALUES
(1,'Смартфон Aurora X',13999.00,'Електроніка'),
(2,'Ноутбук Zenbook 14',32499.00,'Електроніка'),
(3,'Навушники SoundPro',2499.00,'Електроніка'),
(4,'Кавоварка Barista',5499.00,'Побутова техніка'),
(5,'Пилосос CycloneV',7999.00,'Побутова техніка'),
(6,'Куртка зимова Alpine',3299.00,'Одяг'),
(7,'Кросівки RunFast',2199.00,'Одяг'),
(8,'Книга «SQL з нуля»',480.00,'Книги'),
(9,'Книга «Power BI на практиці»',620.00,'Книги'),
(10,'Рюкзак Urban 25L',1299.00,'Аксесуари'),
(11,'Смарт-годинник PulseFit',4599.00,'Електроніка'),
(12,'Настільна лампа LumiDesk',899.00,'Побутова техніка');

INSERT INTO orders (id,customer_id,order_date,amount,status) VALUES
(1,1,'2025-07-03',6438.00,'completed'),
(2,4,'2025-07-08',23997.00,'completed'),
(3,7,'2025-07-13',27994.00,'pending'),
(4,5,'2025-08-10',98737.00,'completed'),
(5,8,'2025-08-15',13797.00,'cancelled'),
(6,1,'2025-08-20',34394.00,'completed'),
(7,4,'2025-08-25',6438.00,'completed'),
(8,9,'2025-09-17',30294.00,'completed'),
(9,2,'2025-09-22',3997.00,'pending'),
(10,5,'2025-09-03',1240.00,'completed'),
(11,3,'2025-10-24',20536.00,'cancelled'),
(12,6,'2025-10-05',20597.00,'completed'),
(13,9,'2025-10-10',4998.00,'completed'),
(14,2,'2025-10-15',30294.00,'completed'),
(15,7,'2025-11-07',2199.00,'pending'),
(16,10,'2025-11-12',100936.00,'completed'),
(17,3,'2025-11-17',19296.00,'cancelled'),
(18,1,'2025-12-14',13999.00,'completed'),
(19,4,'2025-12-19',20437.00,'completed'),
(20,7,'2025-12-24',25296.00,'completed'),
(21,10,'2025-12-05',2199.00,'pending'),
(22,5,'2026-01-21',98737.00,'completed'),
(23,8,'2026-01-26',13797.00,'cancelled'),
(24,1,'2026-01-07',34394.00,'completed'),
(25,9,'2026-02-04',6438.00,'completed'),
(26,2,'2026-02-09',23997.00,'completed'),
(27,5,'2026-02-14',27994.00,'pending'),
(28,8,'2026-02-19',98737.00,'completed'),
(29,3,'2026-03-11',20536.00,'cancelled'),
(30,6,'2026-03-16',20597.00,'completed'),
(31,9,'2026-03-21',4998.00,'completed'),
(32,7,'2026-04-18',30294.00,'completed'),
(33,10,'2026-04-23',3997.00,'pending'),
(34,3,'2026-04-04',1240.00,'completed'),
(35,6,'2026-04-09',20536.00,'cancelled'),
(36,1,'2026-05-25',13999.00,'completed'),
(37,4,'2026-05-06',20437.00,'completed'),
(38,7,'2026-05-11',25296.00,'completed'),
(39,5,'2026-06-08',2199.00,'pending'),
(40,8,'2026-06-13',100936.00,'completed'),
(41,1,'2026-06-18',19296.00,'cancelled'),
(42,4,'2026-06-23',13999.00,'completed');

INSERT INTO order_items (order_id,product_id,qty) VALUES
(1,3,2),
(1,8,3),
(2,5,3),
(3,7,1),
(3,12,2),
(3,5,3),
(4,9,2),
(4,2,3),
(5,11,3),
(6,1,1),
(6,6,2),
(6,11,3),
(7,3,2),
(7,8,3),
(8,5,3),
(8,10,1),
(8,3,2),
(9,7,1),
(9,12,2),
(10,9,2),
(11,11,3),
(11,4,1),
(11,9,2),
(12,1,1),
(12,6,2),
(13,3,2),
(14,5,3),
(14,10,1),
(14,3,2),
(15,7,1),
(16,9,2),
(16,2,3),
(16,7,1),
(17,11,3),
(17,4,1),
(18,1,1),
(19,3,2),
(19,8,3),
(19,1,1),
(20,5,3),
(20,10,1),
(21,7,1),
(22,9,2),
(22,2,3),
(23,11,3),
(24,1,1),
(24,6,2),
(24,11,3),
(25,3,2),
(25,8,3),
(26,5,3),
(27,7,1),
(27,12,2),
(27,5,3),
(28,9,2),
(28,2,3),
(29,11,3),
(29,4,1),
(29,9,2),
(30,1,1),
(30,6,2),
(31,3,2),
(32,5,3),
(32,10,1),
(32,3,2),
(33,7,1),
(33,12,2),
(34,9,2),
(35,11,3),
(35,4,1),
(35,9,2),
(36,1,1),
(37,3,2),
(37,8,3),
(37,1,1),
(38,5,3),
(38,10,1),
(39,7,1),
(40,9,2),
(40,2,3),
(40,7,1),
(41,11,3),
(41,4,1),
(42,1,1);
