zoo-project/sql/init.sql

-- Создание таблицы поставщиков
CREATE TABLE IF NOT EXISTS suppliers (
    supplier_id SERIAL PRIMARY KEY,
    organization_name VARCHAR(150) NOT NULL,
    address VARCHAR(255),
    contacts VARCHAR(100),
    rating INTEGER CHECK (rating BETWEEN 1 AND 10),
    products VARCHAR(150)
);

-- Создание таблицы кормов
CREATE TABLE IF NOT EXISTS feed (
    feed_id SERIAL PRIMARY KEY,
    feed_type VARCHAR(100) NOT NULL,
    quantity NUMERIC(10,2) CHECK (quantity >= 0)
);

-- Создание таблицы поставок кормов
CREATE TABLE IF NOT EXISTS feed_supply (
    supply_id SERIAL PRIMARY KEY,
    supplier_id INTEGER NOT NULL,
    feed_id INTEGER NOT NULL,
    supply_date DATE NOT NULL,
    CONSTRAINT fk_feed_supply_supplier
        FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_feed_supply_feed
        FOREIGN KEY (feed_id)
        REFERENCES feed(feed_id)
        ON DELETE CASCADE
);

-- Создание таблицы закупок
CREATE TABLE IF NOT EXISTS purchase_list (
    purchase_id SERIAL PRIMARY KEY,
    number VARCHAR(50) NOT NULL,
    purchase_date DATE NOT NULL,
    product_name VARCHAR(150) NOT NULL,
    quantity NUMERIC(10,2) CHECK (quantity > 0),
    supplier_id INTEGER NOT NULL,
    CONSTRAINT fk_purchase_supplier
        FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id)
        ON DELETE CASCADE
);

-- Создание таблицы должностей
CREATE TABLE IF NOT EXISTS positions (
    position_id SERIAL PRIMARY KEY,
    position_name VARCHAR(100) NOT NULL,
    salary NUMERIC(10,2) CHECK (salary > 0)
);

-- Создание таблицы сотрудников
CREATE TABLE IF NOT EXISTS employees (
    employee_id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    birth_date DATE,
    gender VARCHAR(10),
    passport_data VARCHAR(50),
    inn VARCHAR(12) UNIQUE,
    phone VARCHAR(20),
    address VARCHAR(255),
    position_id INTEGER NOT NULL,
    CONSTRAINT fk_employee_position
        FOREIGN KEY (position_id)
        REFERENCES positions(position_id)
        ON DELETE RESTRICT
);

-- Создание таблицы вольеров
CREATE TABLE IF NOT EXISTS enclosures (
    enclosure_id SERIAL PRIMARY KEY,
    enclosure_number VARCHAR(50) NOT NULL,
    enclosure_type VARCHAR(100) NOT NULL
);

-- Создание таблицы животных
CREATE TABLE IF NOT EXISTS animals (
    animal_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    species VARCHAR(100) NOT NULL,
    birth_date DATE,
    enclosure_id INTEGER NOT NULL,
    CONSTRAINT fk_animal_enclosure
        FOREIGN KEY (enclosure_id)
        REFERENCES enclosures(enclosure_id)
        ON DELETE RESTRICT
);

-- Создание таблицы клиентов
CREATE TABLE IF NOT EXISTS clients (
    client_id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    address VARCHAR(255),
    phone VARCHAR(20)
);

-- Создание таблицы услуг
CREATE TABLE IF NOT EXISTS services (
    service_id SERIAL PRIMARY KEY,
    service_type VARCHAR(150) NOT NULL,
    price NUMERIC(10,2) CHECK (price > 0)
);

-- Создание таблицы заказов услуг
CREATE TABLE IF NOT EXISTS service_orders (
    order_id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    service_id INTEGER NOT NULL,
    employee_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    quantity INTEGER CHECK (quantity > 0),
    total_cost NUMERIC(12,2),
    CONSTRAINT fk_order_client
        FOREIGN KEY (client_id)
        REFERENCES clients(client_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_service
        FOREIGN KEY (service_id)
        REFERENCES services(service_id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_order_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
        ON DELETE RESTRICT
);

-- Создание таблицы наблюдений
CREATE TABLE IF NOT EXISTS observations (
    observation_id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL,
    animal_id INTEGER NOT NULL,
    observation_date DATE NOT NULL,
    comment TEXT,
    CONSTRAINT fk_observation_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_observation_animal
        FOREIGN KEY (animal_id)
        REFERENCES animals(animal_id)
        ON DELETE CASCADE
);

-- Создание функции для автоматического расчета итоговой стоимости услуги
CREATE OR REPLACE FUNCTION calculate_total_cost()
RETURNS TRIGGER AS
$$
BEGIN
    SELECT NEW.quantity * price
    INTO NEW.total_cost
    FROM services
    WHERE service_id = NEW.service_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Создание триггера для автоматического вызова функции расчета стоимости
CREATE TRIGGER trg_calculate_total_cost
BEFORE INSERT OR UPDATE ON service_orders
FOR EACH ROW
EXECUTE FUNCTION calculate_total_cost();