-- ============================================================
-- БАЗА ДАННЫХ: Продажа компьютерной техники
-- СУБД: PostgreSQL
-- ============================================================

DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS app_users CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS suppliers CASCADE;
DROP TABLE IF EXISTS clients CASCADE;
DROP TABLE IF EXISTS employees CASCADE;

DROP VIEW IF EXISTS v_orders_full_info CASCADE;
DROP VIEW IF EXISTS v_late_orders CASCADE;
DROP VIEW IF EXISTS v_order_items_full CASCADE;
DROP VIEW IF EXISTS v_products_dictionary CASCADE;

DROP FUNCTION IF EXISTS fn_get_late_orders_by_client(INTEGER);
DROP FUNCTION IF EXISTS fn_calculate_order_total(INTEGER);
DROP FUNCTION IF EXISTS fn_update_order_total();
DROP FUNCTION IF EXISTS fn_reduce_product_stock();

CREATE TABLE suppliers (
   supplier_id    SERIAL PRIMARY KEY,
   company_name   VARCHAR(150) NOT NULL,
   contact_person VARCHAR(100) NOT NULL,
   phone          VARCHAR(20) NOT NULL,
   address        VARCHAR(250) NOT NULL
);

CREATE TABLE products (
   product_id     SERIAL PRIMARY KEY,
   product_name   VARCHAR(200) NOT NULL,
   price          NUMERIC(12,2) NOT NULL CHECK (price >= 0),
   stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
   category       VARCHAR(100) NOT NULL,
   supplier_id    INTEGER NOT NULL REFERENCES suppliers(supplier_id) ON UPDATE CASCADE
);

CREATE TABLE clients (
   client_id   SERIAL PRIMARY KEY,
   first_name  VARCHAR(80) NOT NULL,
   last_name   VARCHAR(80) NOT NULL,
   address     VARCHAR(250) NOT NULL,
   phone       VARCHAR(20),
   email       VARCHAR(150) UNIQUE
);

CREATE TABLE employees (
   employee_id SERIAL PRIMARY KEY,
   last_name   VARCHAR(80) NOT NULL,
   first_name  VARCHAR(80) NOT NULL,
   phone       VARCHAR(20),
   email       VARCHAR(150) UNIQUE,
   position    VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
   order_id    SERIAL PRIMARY KEY,
   order_date  TIMESTAMP NOT NULL DEFAULT NOW(),
   status      VARCHAR(50) NOT NULL DEFAULT 'новый'
       CHECK (status IN ('новый','в обработке','отправлен','выполнен','отменён')),
   total_cost  NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (total_cost >= 0),
   client_id   INTEGER NOT NULL REFERENCES clients(client_id),
   employee_id INTEGER NOT NULL REFERENCES employees(employee_id)
);

CREATE TABLE order_items (
   order_item_id SERIAL PRIMARY KEY,
   order_id      INTEGER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
   product_id    INTEGER NOT NULL REFERENCES products(product_id),
   unit_price    NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
   quantity      INTEGER NOT NULL CHECK (quantity > 0)
);

CREATE TABLE app_users (
   user_id       SERIAL PRIMARY KEY,
   login         VARCHAR(50) NOT NULL UNIQUE,
   password_hash TEXT NOT NULL,
   role_name     VARCHAR(30) NOT NULL CHECK (role_name IN ('admin','manager','viewer'))
);

CREATE OR REPLACE VIEW v_orders_full_info AS
SELECT
    o.order_id,
    o.order_date,
    o.status,
    o.total_cost,
    c.client_id,
    c.last_name || ' ' || c.first_name AS client_name,
    c.phone AS client_phone,
    c.email AS client_email,
    e.employee_id,
    e.last_name || ' ' || e.first_name AS employee_name,
    e.position AS employee_position
FROM orders o
JOIN clients c ON o.client_id = c.client_id
JOIN employees e ON o.employee_id = e.employee_id;

CREATE OR REPLACE VIEW v_late_orders AS
SELECT *
FROM v_orders_full_info
WHERE status NOT IN ('выполнен', 'отменён')
  AND order_date < NOW() - INTERVAL '7 days';

CREATE OR REPLACE VIEW v_order_items_full AS
SELECT
    oi.order_item_id,
    oi.order_id,
    p.product_id,
    p.product_name,
    p.category,
    oi.unit_price,
    oi.quantity,
    oi.unit_price * oi.quantity AS item_sum
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id;

CREATE OR REPLACE VIEW v_products_dictionary AS
SELECT
    p.product_id,
    p.product_name,
    p.category,
    p.price,
    p.stock_quantity,
    s.supplier_id,
    s.company_name AS supplier_name,
    s.contact_person,
    s.phone AS supplier_phone,
    s.address AS supplier_address
FROM products p
JOIN suppliers s ON p.supplier_id = s.supplier_id;

CREATE OR REPLACE FUNCTION fn_get_late_orders_by_client(p_client_id INTEGER)
RETURNS TABLE (
    order_id INTEGER,
    order_date TIMESTAMP,
    status VARCHAR,
    total_cost NUMERIC,
    client_id INTEGER,
    client_name TEXT,
    client_phone VARCHAR,
    client_email VARCHAR,
    employee_id INTEGER,
    employee_name TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        v.order_id,
        v.order_date,
        v.status,
        v.total_cost,
        v.client_id,
        v.client_name,
        v.client_phone,
        v.client_email,
        v.employee_id,
        v.employee_name
    FROM v_late_orders v
    WHERE v.client_id = p_client_id
    ORDER BY v.order_date DESC;
END;
$$;

CREATE OR REPLACE FUNCTION fn_calculate_order_total(p_order_id INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC(14,2);
BEGIN
    SELECT COALESCE(SUM(unit_price * quantity), 0)
    INTO v_total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN v_total;
END;
$$;

CREATE OR REPLACE FUNCTION fn_update_order_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    v_order_id := COALESCE(NEW.order_id, OLD.order_id);

    UPDATE orders
    SET total_cost = fn_calculate_order_total(v_order_id)
    WHERE order_id = v_order_id;

    RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE OR REPLACE FUNCTION fn_reduce_product_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock INTEGER;
BEGIN
    SELECT stock_quantity INTO v_stock
    FROM products
    WHERE product_id = NEW.product_id;

    IF v_stock IS NULL THEN
        RAISE EXCEPTION 'Товар не найден';
    END IF;

    IF v_stock < NEW.quantity THEN
        RAISE EXCEPTION 'Недостаточно товара на складе. Доступно: %, требуется: %', v_stock, NEW.quantity;
    END IF;

    UPDATE products
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE product_id = NEW.product_id;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_update_order_total_insert
AFTER INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION fn_update_order_total();

CREATE TRIGGER trg_update_order_total_update
AFTER UPDATE ON order_items
FOR EACH ROW
EXECUTE FUNCTION fn_update_order_total();

CREATE TRIGGER trg_update_order_total_delete
AFTER DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION fn_update_order_total();

CREATE TRIGGER trg_reduce_product_stock
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION fn_reduce_product_stock();

CREATE INDEX idx_orders_client_id ON orders(client_id);
CREATE INDEX idx_orders_employee_id ON orders(employee_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_products_supplier_id ON products(supplier_id);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

INSERT INTO suppliers(company_name, contact_person, phone, address) VALUES
('TechImport', 'Иванов Сергей', '+79001112233', 'Москва, ул. Ленина, д. 10'),
('CompMarket', 'Петров Алексей', '+79004445566', 'Санкт-Петербург, Невский пр., д. 20'),
('DigitalTrade', 'Соколова Анна', '+79007778899', 'Великий Новгород, ул. Большая Санкт-Петербургская, д. 15');

INSERT INTO products(product_name, price, stock_quantity, category, supplier_id) VALUES
('Ноутбук Lenovo IdeaPad 3', 65000.00, 15, 'Ноутбуки', 1),
('Ноутбук ASUS VivoBook', 72000.00, 10, 'Ноутбуки', 1),
('Монитор Samsung 24', 18000.00, 30, 'Мониторы', 2),
('Монитор LG UltraGear', 26000.00, 12, 'Мониторы', 2),
('Клавиатура Logitech K120', 1500.00, 50, 'Периферия', 3),
('Мышь Logitech M185', 1200.00, 60, 'Периферия', 3),
('SSD Kingston 1TB', 7200.00, 40, 'Комплектующие', 2),
('Видеокарта MSI RTX 4060', 42000.00, 8, 'Комплектующие', 1);

INSERT INTO clients(first_name, last_name, address, phone, email) VALUES
('Андрей', 'Смирнов', 'Великий Новгород, ул. Мира, д. 5', '+79110000001', 'smirnov@mail.ru'),
('Ольга', 'Кузнецова', 'Великий Новгород, ул. Парковая, д. 8', '+79110000002', 'kuznetsova@mail.ru'),
('Иван', 'Павлов', 'Великий Новгород, ул. Ломоносова, д. 3', '+79110000003', 'pavlov@mail.ru'),
('Мария', 'Васильева', 'Великий Новгород, ул. Попова, д. 12', '+79110000004', 'vasilieva@mail.ru');

INSERT INTO employees(last_name, first_name, phone, email, position) VALUES
('Иванова', 'Мария', '+79220000001', 'ivanova@shop.ru', 'Менеджер'),
('Соколов', 'Дмитрий', '+79220000002', 'sokolov@shop.ru', 'Администратор'),
('Фёдорова', 'Елена', '+79220000003', 'fedorova@shop.ru', 'Менеджер');

INSERT INTO orders(order_date, status, client_id, employee_id) VALUES
(NOW() - INTERVAL '12 days', 'в обработке', 1, 1),
(NOW() - INTERVAL '10 days', 'отправлен', 1, 1),
(NOW() - INTERVAL '3 days', 'новый', 2, 3),
(NOW() - INTERVAL '15 days', 'выполнен', 2, 1),
(NOW() - INTERVAL '20 days', 'в обработке', 3, 3),
(NOW() - INTERVAL '4 days', 'отменён', 4, 1);

INSERT INTO order_items(order_id, product_id, unit_price, quantity) VALUES
(1, 1, 65000.00, 1),
(1, 5, 1500.00, 2),
(2, 3, 18000.00, 2),
(3, 6, 1200.00, 1),
(3, 7, 7200.00, 1),
(4, 2, 72000.00, 1),
(5, 8, 42000.00, 1),
(5, 4, 26000.00, 1),
(6, 5, 1500.00, 1);

-- ============================================================
-- ПОЛЬЗОВАТЕЛИ ПРИЛОЖЕНИЯ
-- Роли PostgreSQL не создаются, так как в учебной БД это запрещено.
-- Разграничение прав выполняется на уровне приложения через app_users.role_name.
-- Пароли для проверки: admin123, manager123, viewer123.
-- ============================================================

DELETE FROM app_users;

INSERT INTO app_users(login, password_hash, role_name) VALUES
('admin',   '240be518fabd2724ddb6f04eeb1da5967448d7e831c08c8fa822809f74c720a9', 'admin'),
('manager', '866485796cfa8d7c0cf7111640205b83076433547577511d81f8030ae99ecea5', 'manager'),
('viewer',  '65375049b9e4d7cad6c9ba286fdeb9394b28135a3e84136404cfccfdcc438894', 'viewer');

-- Проверочные запросы
SELECT * FROM app_users;
SELECT * FROM v_orders_full_info;
SELECT * FROM v_late_orders;
SELECT * FROM v_products_dictionary;
SELECT * FROM fn_get_late_orders_by_client(1);