CREATE TABLE CUSTOMERS (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    inn VARCHAR(12) UNIQUE,
    address VARCHAR(500),
    phone VARCHAR(20),
    is_salesman BOOLEAN DEFAULT FALSE,
    is_buyer BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_inn (inn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS PRODUCT;

CREATE TABLE PRODUCT (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    unit_of_measure VARCHAR(10) DEFAULT 'шт',
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS MATERIAL;

CREATE TABLE MATERIAL (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    unit_of_measure VARCHAR(10) DEFAULT 'кг',
    unit_cost DECIMAL(10,2) NOT NULL CHECK (unit_cost >= 0),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS SPECIFICATION;

CREATE TABLE SPECIFICATION (
    id VARCHAR(20) PRIMARY KEY,
    product_id VARCHAR(20) NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at DATE DEFAULT (CURDATE()),
    FOREIGN KEY (product_id) REFERENCES PRODUCT(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_spec (product_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS SPECIFICATION_ITEM;

CREATE TABLE SPECIFICATION_ITEM (
    id VARCHAR(20) PRIMARY KEY,
    specification_id VARCHAR(20) NOT NULL,
    material_id VARCHAR(20) NOT NULL,
    quantity DECIMAL(10,3) NOT NULL CHECK (quantity > 0),
    FOREIGN KEY (specification_id) REFERENCES SPECIFICATION(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES MATERIAL(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_spec_material (specification_id, material_id),
    INDEX idx_specification (specification_id),
    INDEX idx_material (material_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `ORDER`;

CREATE TABLE `ORDER` (
    id VARCHAR(20) PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    order_date DATE NOT NULL,
    customer_id VARCHAR(20) NOT NULL,
    manufacturer_id VARCHAR(20) NOT NULL,
    status VARCHAR(50) DEFAULT 'Новый',
    FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(id) ON DELETE RESTRICT,
    FOREIGN KEY (manufacturer_id) REFERENCES CUSTOMERS(id) ON DELETE RESTRICT,
    INDEX idx_order_number (order_number),
    INDEX idx_order_date (order_date),
    INDEX idx_customer (customer_id),
    INDEX idx_manufacturer (manufacturer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS ORDER_ITEM;

CREATE TABLE ORDER_ITEM (
    id VARCHAR(20) PRIMARY KEY,
    order_id VARCHAR(20) NOT NULL,
    product_id VARCHAR(20) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    FOREIGN KEY (order_id) REFERENCES `ORDER`(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES PRODUCT(id) ON DELETE RESTRICT,
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS PRODUCTION_ORDER;

CREATE TABLE PRODUCTION_ORDER (
    id VARCHAR(20) PRIMARY KEY,
    production_number VARCHAR(50) NOT NULL UNIQUE,
    production_date DATE NOT NULL,
    product_id VARCHAR(20) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    status VARCHAR(50) DEFAULT 'Планируется',
    FOREIGN KEY (product_id) REFERENCES PRODUCT(id) ON DELETE RESTRICT,
    INDEX idx_production_number (production_number),
    INDEX idx_production_date (production_date),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    login VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(50) NOT NULL,
    role VARCHAR(50) NOT NULL,
    fail_count INT DEFAULT 0,
    is_blocked BOOLEAN DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


SET FOREIGN_KEY_CHECKS = 1;


----------------------1488Z@P4oS1337--------------------------
SELECT
    o.id AS id,
    o.order_number AS 'Номер заказа',
    o.order_date AS 'Дата заказа',
    c.name AS 'Клиент',
    p.name AS 'Продукция',
    oi.quantity AS 'Количество в заказе',
    COALESCE(material_cost.cost_per_unit, 0) AS 'Себестоимость 1 шт (материалы)',
    oi.quantity * COALESCE(material_cost.cost_per_unit, 0) AS 'Стоимость позиции',
    oi.unit_price AS 'Отпускная цена за шт',
    oi.quantity * oi.unit_price AS 'Выручка по позиции',
    (oi.quantity * oi.unit_price) - (oi.quantity * COALESCE(material_cost.cost_per_unit, 0)) AS 'Прибыль'
FROM `ORDER` o
JOIN CUSTOMERS c ON o.customer_id = c.id
JOIN ORDER_ITEM oi ON o.id = oi.order_id
JOIN PRODUCT p ON oi.product_id = p.id
LEFT JOIN (
    SELECT
        s.product_id,
        SUM(si.quantity * m.unit_cost) AS cost_per_unit
    FROM SPECIFICATION s
    JOIN SPECIFICATION_ITEM si ON s.id = si.specification_id
    JOIN MATERIAL m ON si.material_id = m.id
    GROUP BY s.product_id
) material_cost ON p.id = material_cost.product_id
WHERE o.order_number = '№2 от 06.06.2025'  -- Укажите нужный номер заказа
ORDER BY p.name;