borisov_arseniy_3991/init.sql

-- Таблицы базы данных Ресторан

CREATE TABLE IF NOT EXISTS "Персонал" (
    id_персонала SERIAL PRIMARY KEY,
    фио VARCHAR(100) NOT NULL,
    год_рождения INT,
    должность VARCHAR(50),
    оклад NUMERIC(10,2) CHECK (оклад >= 0),
    телефон VARCHAR(20),
    адрес VARCHAR(200),
    паспортные_данные VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS "Блюда" (
    id_блюда SERIAL PRIMARY KEY,
    блюдо VARCHAR(100) NOT NULL,
    категория VARCHAR(50),
    вес_г INT
);

CREATE TABLE IF NOT EXISTS "Склад" (
    id_ингредиента SERIAL PRIMARY KEY,
    ингредиент VARCHAR(100) NOT NULL,
    вес_кг NUMERIC(8,3) CHECK (вес_кг >= 0),
    цена_руб NUMERIC(10,2) CHECK (цена_руб >= 0)
);

CREATE TABLE IF NOT EXISTS "Заказы" (
    id_заказа SERIAL PRIMARY KEY,
    код_заказа VARCHAR(20) UNIQUE NOT NULL,
    дата_заказа DATE NOT NULL,
    id_персонала INT REFERENCES "Персонал"(id_персонала)
);

CREATE TABLE IF NOT EXISTS audit_log (
    id SERIAL PRIMARY KEY,
    таблица VARCHAR(50),
    операция VARCHAR(10),
    старые_данные TEXT,
    новые_данные TEXT,
    пользователь VARCHAR(50) DEFAULT current_user,
    время TIMESTAMP DEFAULT now()
);

-- Триггер логирования Персонал
CREATE OR REPLACE FUNCTION log_персонал() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(таблица,операция,старые_данные)
        VALUES ('Персонал','DELETE',ROW(OLD.*)::text);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log(таблица,операция,старые_данные,новые_данные)
        VALUES ('Персонал','UPDATE',ROW(OLD.*)::text,ROW(NEW.*)::text);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log(таблица,операция,новые_данные)
        VALUES ('Персонал','INSERT',ROW(NEW.*)::text);
        RETURN NEW;
    END IF;
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_audit_персонал ON "Персонал";
CREATE TRIGGER trg_audit_персонал
AFTER INSERT OR UPDATE OR DELETE ON "Персонал"
FOR EACH ROW EXECUTE FUNCTION log_персонал();

-- Триггер логирования Заказы
CREATE OR REPLACE FUNCTION log_заказы() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(таблица,операция,старые_данные)
        VALUES ('Заказы','DELETE',ROW(OLD.*)::text);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log(таблица,операция,старые_данные,новые_данные)
        VALUES ('Заказы','UPDATE',ROW(OLD.*)::text,ROW(NEW.*)::text);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log(таблица,операция,новые_данные)
        VALUES ('Заказы','INSERT',ROW(NEW.*)::text);
        RETURN NEW;
    END IF;
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_audit_заказы ON "Заказы";
CREATE TRIGGER trg_audit_заказы
AFTER INSERT OR UPDATE OR DELETE ON "Заказы"
FOR EACH ROW EXECUTE FUNCTION log_заказы();

-- Роли
DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'db_admin') THEN
    CREATE ROLE db_admin LOGIN PASSWORD 'Admin@123';
  END IF;
END $$;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_admin;

DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'db_manager') THEN
    CREATE ROLE db_manager LOGIN PASSWORD 'Manager@123';
  END IF;
END $$;
GRANT SELECT, INSERT, UPDATE ON "Персонал","Заказы","Блюда","Склад" TO db_manager;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO db_manager;

DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'db_waiter') THEN
    CREATE ROLE db_waiter LOGIN PASSWORD 'Waiter@123';
  END IF;
END $$;
GRANT SELECT ON "Блюда","Склад","Персонал" TO db_waiter;
GRANT SELECT, INSERT ON "Заказы" TO db_waiter;

DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'db_readonly') THEN
    CREATE ROLE db_readonly LOGIN PASSWORD 'Readonly@123';
  END IF;
END $$;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_readonly;