---

-- ============================================================
-- Скрипт создания БД для системы заказов (3НФ)
-- MySQL 8.0+
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Удаляем существующую БД если есть
DROP DATABASE IF EXISTS shop_db;

CREATE DATABASE shop_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE shop_db;

-- ============================================================
-- 1. ROLES — Роли сотрудников (администратор, менеджер, клиент)
-- ============================================================
CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- 2. USERS — Пользователи (сотрудники и клиенты)
-- ============================================================
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    login VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 3. UNITS — Единицы измерения (шт, кг, л, уп)
-- ============================================================
CREATE TABLE units (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    short_name VARCHAR(10) DEFAULT NULL
) ENGINE=InnoDB;

-- ============================================================
-- 4. SUPPLIERS — Поставщики
-- ============================================================
CREATE TABLE suppliers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- 5. MANUFACTURERS — Производители
-- ============================================================
CREATE TABLE manufacturers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(100) DEFAULT NULL,
    website VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- 6. CATEGORIES — Категории товаров
-- ============================================================
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    parent_id INT UNSIGNED DEFAULT NULL,
    description TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 7. PRODUCTS — Товары (основная сущность)
-- ============================================================
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    article VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    photo VARCHAR(500) DEFAULT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    active_discount DECIMAL(5, 2) DEFAULT 0.00 COMMENT 'Действующая скидка в %',
    quantity_in_stock INT UNSIGNED NOT NULL DEFAULT 0,
    unit_id INT UNSIGNED NOT NULL,
    supplier_id INT UNSIGNED DEFAULT NULL,
    manufacturer_id INT UNSIGNED DEFAULT NULL,
    category_id INT UNSIGNED DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (unit_id) REFERENCES units(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 8. PICKUP_POINTS — Пункты выдачи
-- ============================================================
CREATE TABLE pickup_points (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    address VARCHAR(500) NOT NULL UNIQUE,
    city VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    work_hours VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- 9. ORDERS — Заказы (основная шапка заказа)
-- ============================================================
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE COMMENT 'Номер заказа',
    user_id INT UNSIGNED NOT NULL COMMENT 'ФИО авторизованного клиента',
    pickup_point_id INT UNSIGNED NOT NULL COMMENT 'Адрес пункта выдачи',
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    delivery_date DATETIME DEFAULT NULL,
    pickup_code VARCHAR(20) NOT NULL COMMENT 'Код для получения',
    status ENUM('new', 'processing', 'shipped', 'ready', 'delivered', 'cancelled') NOT NULL DEFAULT 'new',
    total_amount DECIMAL(12, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (pickup_point_id) REFERENCES pickup_points(id)
) ENGINE=InnoDB;

-- ============================================================
-- 10. ORDER_ITEMS — Состав заказа (позиции)
-- ============================================================
CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    product_article VARCHAR(50) NOT NULL COMMENT 'Артикул заказа/товара',
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    price_at_order DECIMAL(10, 2) NOT NULL COMMENT 'Цена на момент заказа',
    discount_amount DECIMAL(10, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    UNIQUE KEY unique_order_product (order_id, product_id)
) ENGINE=InnoDB;

-- ============================================================
-- Начальные данные (seed)
-- ============================================================

-- Роли
INSERT INTO roles (name, description) VALUES
('admin', 'Администратор системы'),
('manager', 'Менеджер'),
('client', 'Клиент');

-- Единицы измерения
INSERT INTO units (name, short_name) VALUES
('Штука', 'шт'),
('Килограмм', 'кг'),
('Литр', 'л'),
('Упаковка', 'уп'),
('Метр', 'м');

-- Категории
INSERT INTO categories (name, description) VALUES
('Электроника', 'Бытовая и цифровая техника'),
('Одежда', 'Мужская, женская, детская одежда'),
('Продукты', 'Продукты питания');

-- Поставщики
INSERT INTO suppliers (name, contact_person, phone) VALUES
('ООО "ТехноОпт"', 'Иванов И.И.', '+7 (495) 123-45-67'),
('ИП Петров', 'Петров П.П.', '+7 (900) 111-22-33');

-- Производители
INSERT INTO manufacturers (name, country) VALUES
('Samsung', 'Южная Корея'),
('Apple', 'США'),
('Nike', 'США');

-- Пункты выдачи
INSERT INTO pickup_points (address, city, work_hours) VALUES
('г. Москва, ул. Тверская, д. 1', 'Москва', '09:00-21:00'),
('г. Санкт-Петербург, Невский пр., д. 25', 'Санкт-Петербург', '10:00-22:00'),
('г. Казань, ул. Баумана, д. 50', 'Казань', '08:00-20:00');

-- Администратор
-- Пароль: admin123 (на практике используйте PASSWORD() или bcrypt)
INSERT INTO users (role_id, full_name, login, password, email) VALUES
(1, 'Администратор Системы', 'admin', 'admin123', 'admin@shop.ru'),
(3, 'Иванов Иван Иванович', 'client1', 'pass123', 'ivanov@mail.ru');

-- Товары
INSERT INTO products (article, name, description, photo, price, active_discount, quantity_in_stock, unit_id, supplier_id, manufacturer_id, category_id) VALUES
('EL-001', 'Смартфон Samsung Galaxy S23', 'Флагманский смартфон, 256GB', '/img/samsung_s23.jpg', 79990.00, 10.00, 15, 1, 1, 1, 1),
('EL-002', 'Наушники Apple AirPods Pro', 'Беспроводные с шумоподавлением', '/img/airpods.jpg', 24990.00, 5.00, 30, 1, 2, 2, 1),
('CL-001', 'Кроссовки Nike Air Max', 'Мужские, размер 42', '/img/nike.jpg', 12990.00, 0.00, 8, 1, 2, 3, 2);

-- Тестовый заказ
INSERT INTO orders (order_number, user_id, pickup_point_id, order_date, delivery_date, pickup_code, status, total_amount) VALUES
('ORD-2026-0001', 2, 1, '2026-06-15 10:30:00', '2026-06-17 15:00:00', 'X7K2M', 'ready', 91980.00);

INSERT INTO order_items (order_id, product_id, product_article, quantity, price_at_order, discount_amount) VALUES
(1, 1, 'EL-001', 1, 79990.00, 7999.00),
(1, 2, 'EL-002', 1, 24990.00, 1249.50);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- Проверка
-- ============================================================
SELECT 'База данных успешно создана!' AS status;
SELECT 
    TABLE_NAME, 
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024, 2) AS size_kb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop_db'
ORDER BY TABLE_NAME;