---

-- ============================================================
-- ER-ДИАГРАММА: УЧЁТ ВЫПУСКА ПРОДУКЦИИ НА УЧАСТКЕ МОНТАЖА ПЛАТ
-- СУБД: PostgreSQL
-- ============================================================

-- ------------------------------------------------------------
-- УДАЛЕНИЕ СТАРЫХ ТАБЛИЦ (если есть)
-- ------------------------------------------------------------
DROP TABLE IF EXISTS Выпуск_продукции CASCADE;
DROP TABLE IF EXISTS Партия CASCADE;
DROP TABLE IF EXISTS Сотрудник CASCADE;
DROP TABLE IF EXISTS Продукция CASCADE;
DROP TABLE IF EXISTS Участок CASCADE;

-- ============================================================
-- СОЗДАНИЕ ТАБЛИЦ
-- ============================================================

CREATE TABLE Участок (
    id_участка    SERIAL PRIMARY KEY,
    наименование  VARCHAR(100) NOT NULL,
    руководитель  VARCHAR(150),
    смена         VARCHAR(20)
);

CREATE TABLE Продукция (
    id_продукции  SERIAL PRIMARY KEY,
    наименование  VARCHAR(150) NOT NULL,
    артикул       VARCHAR(50) UNIQUE,
    тип_платы     VARCHAR(100),
    норма_времени DECIMAL(6,2)
);

CREATE TABLE Сотрудник (
    id_сотрудника   SERIAL PRIMARY KEY,
    ФИО             VARCHAR(150) NOT NULL,
    табельный_номер VARCHAR(20) UNIQUE,
    разряд          INT,
    id_участка      INT REFERENCES Участок(id_участка)
);

CREATE TABLE Партия (
    id_партии       SERIAL PRIMARY KEY,
    номер_партии    VARCHAR(50) UNIQUE,
    дата_запуска    DATE,
    id_продукции    INT REFERENCES Продукция(id_продукции),
    план_количество INT,
    факт_количество INT
);

CREATE TABLE Выпуск_продукции (
    id_выпуска      SERIAL PRIMARY KEY,
    id_продукции    INT REFERENCES Продукция(id_продукции),
    id_сотрудника   INT REFERENCES Сотрудник(id_сотрудника),
    id_партии       INT REFERENCES Партия(id_партии),
    дата            DATE NOT NULL,
    количество      INT NOT NULL,
    брак            INT DEFAULT 0,
    статус          VARCHAR(20) DEFAULT 'принято'
);

-- ============================================================
-- ЗАПОЛНЕНИЕ: УЧАСТОК (5 записей)
-- ============================================================

INSERT INTO Участок (наименование, руководитель, смена) VALUES
('Участок монтажа №1',        'Иванов И.И.',    'Первая'),
('Участок монтажа №2',        'Петров П.П.',    'Вторая'),
('Участок пайки SMD',         'Сидоров С.С.',   'Первая'),
('Участок выходного контроля','Кузнецова А.А.', 'Вторая'),
('Участок сборки изделий',    'Морозов М.М.',   'Третья');

-- ============================================================
-- ЗАПОЛНЕНИЕ: ПРОДУКЦИЯ (5 записей)
-- ============================================================

INSERT INTO Продукция (наименование, артикул, тип_платы, норма_времени) VALUES
('Контроллер управления', 'CTRL-001', 'Двухсторонняя', 45.50),
('Плата питания',         'PWR-002',  'Однослойная',   30.00),
('Модуль связи',          'COM-003',  'Многослойная',  60.25),
('Датчик температуры',    'TEMP-004', 'Однослойная',   25.00),
('Блок индикации',        'IND-005',  'Двухсторонняя', 40.00);

-- ============================================================
-- ЗАПОЛНЕНИЕ: СОТРУДНИК (5 записей)
-- ============================================================

INSERT INTO Сотрудник (ФИО, табельный_номер, разряд, id_участка) VALUES
('Алексеев Алексей Алексеевич', 'T-1001', 3, 1),
('Борисова Бэла Борисовна',     'T-1002', 4, 2),
('Волков Виктор Викторович',    'T-1003', 5, 3),
('Громова Галина Григорьевна',  'T-1004', 2, 4),
('Дмитриев Дмитрий Дмитриевич', 'T-1005', 4, 5);

-- ============================================================
-- ЗАПОЛНЕНИЕ: ПАРТИЯ (5 записей)
-- ============================================================

INSERT INTO Партия (номер_партии, дата_запуска, id_продукции, план_количество, факт_количество) VALUES
('P-2024-001', '2024-01-10', 1, 100, 98),
('P-2024-002', '2024-01-15', 2, 200, 195),
('P-2024-003', '2024-01-20', 3, 150, 150),
('P-2024-004', '2024-02-01', 4, 300, 290),
('P-2024-005', '2024-02-10', 5, 120, 118);

-- ============================================================
-- ЗАПОЛНЕНИЕ: ВЫПУСК_ПРОДУКЦИИ (5 записей)
-- ============================================================

INSERT INTO Выпуск_продукции (id_продукции, id_сотрудника, id_партии, дата, количество, брак, статус) VALUES
(1, 1, 1, '2024-01-12', 50,  2,  'принято'),
(2, 2, 2, '2024-01-17', 100, 5,  'принято'),
(3, 3, 3, '2024-01-22', 75,  0,  'принято'),
(4, 4, 4, '2024-02-03', 150, 10, 'отклонено'),
(5, 5, 5, '2024-02-12', 60,  2,  'принято');

-- ============================================================
-- ПРОВЕРОЧНЫЕ ЗАПРОСЫ
-- ============================================================

-- 1. Все записи выпуска с расшифровкой
SELECT
    в.id_выпуска,
    п.наименование AS изделие,
    п.артикул,
    с.ФИО          AS сотрудник,
    у.наименование AS участок,
    па.номер_партии,
    в.дата,
    в.количество,
    в.брак,
    в.статус
FROM Выпуск_продукции в
JOIN Продукция   п  ON п.id_продукции  = в.id_продукции
JOIN Сотрудник   с  ON с.id_сотрудника = в.id_сотрудника
JOIN Участок     у  ON у.id_участка    = с.id_участка
JOIN Партия      па ON па.id_партии    = в.id_партии
ORDER BY в.дата;

-- 2. Итоги по участкам: выпуск, брак, процент брака
SELECT
    у.наименование AS участок,
    SUM(в.количество) AS всего_выпущено,
    SUM(в.брак)       AS всего_брака,
    ROUND(100.0 * SUM(в.брак) / NULLIF(SUM(в.количество), 0), 2) AS процент_брака
FROM Выпуск_продукции в
JOIN Сотрудник с ON с.id_сотрудника = в.id_сотрудника
JOIN Участок   у ON у.id_участка    = с.id_участка
GROUP BY у.наименование
ORDER BY процент_брака DESC;

-- 3. Выполнение плана по партиям
SELECT
    па.номер_партии,
    п.наименование AS изделие,
    па.план_количество,
    па.факт_количество,
    ROUND(100.0 * па.факт_количество / NULLIF(па.план_количество, 0), 2) AS процент_выполнения
FROM Партия па
JOIN Продукция п ON п.id_продукции = па.id_продукции
ORDER BY па.дата_запуска;

-- 4. Выработка по сотрудникам
SELECT
    с.ФИО,
    с.табельный_номер,
    у.наименование AS участок,
    SUM(в.количество) AS всего_выпущено,
    SUM(в.брак)       AS брак
FROM Выпуск_продукции в
JOIN Сотрудник с ON с.id_сотрудника = в.id_сотрудника
JOIN Участок   у ON у.id_участка    = с.id_участка
GROUP BY с.ФИО, с.табельный_номер, у.наименование
ORDER BY всего_выпущено DESC;

-- ============================================================
-- КОНЕЦ СКРИПТА
-- ============================================================