---

-- 1. Должности
CREATE TABLE Positions (
    position_id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

-- 2. Сотрудники
CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(20),
    position_id INT REFERENCES Positions(position_id)
);

-- 3. Категории услуг
CREATE TABLE Service_Categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 4. Услуги
CREATE TABLE Services (
    service_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    duration_minutes INT NOT NULL,
    category_id INT REFERENCES Service_Categories(category_id)
);

-- 5. Клиенты
CREATE TABLE Clients (
    client_id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(100),
    registration_date DATE DEFAULT CURRENT_DATE
);

-- 6. Справочник противопоказаний (для исключения избыточности в 4NF)
CREATE TABLE Contraindications (
    contra_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 7. Противопоказания клиентов (Связь M:N, устранение многозначной зависимости)
CREATE TABLE Client_Contraindications (
    client_id INT REFERENCES Clients(client_id),
    contra_id INT REFERENCES Contraindications(contra_id),
    PRIMARY KEY (client_id, contra_id)
);

-- 8. Кабинеты
CREATE TABLE Rooms (
    room_id SERIAL PRIMARY KEY,
    room_number VARCHAR(10),
    description VARCHAR(255)
);

-- 9. Типы абонементов
CREATE TABLE Subscription_Types (
    sub_type_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    visit_count INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    validity_days INT NOT NULL
);

-- 10. Купленные абонементы (Виртуальные абонементы)
CREATE TABLE Client_Subscriptions (
    subscription_id SERIAL PRIMARY KEY,
    client_id INT REFERENCES Clients(client_id),
    sub_type_id INT REFERENCES Subscription_Types(sub_type_id),
    purchase_date DATE DEFAULT CURRENT_DATE,
    remaining_visits INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);

-- 11. Записи на сеанс (Расписание)
CREATE TABLE Appointments (
    appointment_id SERIAL PRIMARY KEY,
    client_id INT REFERENCES Clients(client_id),
    employee_id INT REFERENCES Employees(employee_id),
    service_id INT REFERENCES Services(service_id),
    room_id INT REFERENCES Rooms(room_id),
    appointment_datetime TIMESTAMP NOT NULL,
    status VARCHAR(50) DEFAULT 'Scheduled'
);

-- 12. Рассходные материалы
CREATE TABLE Materials (
    material_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    unit VARCHAR(20) NOT NULL, -- шт, мл, гр
    stock_quantity DECIMAL(10, 2) NOT NULL
);

-- 13. Нормы расхода материалов на услуги (Связь M:N, 4NF)
CREATE TABLE Service_Material_Norms (
    service_id INT REFERENCES Services(service_id),
    material_id INT REFERENCES Materials(material_id),
    norm_quantity DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (service_id, material_id)
);

-- 14. Списание материалов по факту процедуры
CREATE TABLE Material_Usage (
    usage_id SERIAL PRIMARY KEY,
    appointment_id INT REFERENCES Appointments(appointment_id),
    material_id INT REFERENCES Materials(material_id),
    actual_quantity DECIMAL(10, 2) NOT NULL
);

-- 15. Выплаты сотрудникам (Зарплата)
CREATE TABLE Salaries (
    salary_id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES Employees(employee_id),
    payment_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    bonus DECIMAL(10, 2) DEFAULT 0
);