---

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    tab_no VARCHAR(20) UNIQUE,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    middle_name VARCHAR(50),
    department VARCHAR(100),
    position VARCHAR(100),
    position_code VARCHAR(20)
);

-- Создадим также остальные таблицы, которые нужны приложению,
-- чтобы не ловить ошибки в будущем:

CREATE TABLE service_notes (
    id SERIAL PRIMARY KEY,
    doc_number VARCHAR(50),
    doc_date DATE,
    payment_code VARCHAR(10),
    base_reason TEXT,
    created_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE supplement_details (
    id SERIAL PRIMARY KEY,
    service_note_id INTEGER REFERENCES service_notes(id) ON DELETE CASCADE,
    employee_id INTEGER REFERENCES employees(id),
    start_date DATE,
    end_date DATE,
    amount NUMERIC(10, 2),
    is_percent BOOLEAN DEFAULT FALSE,
    work_content TEXT,
    work_type VARCHAR(100),
    status VARCHAR(20) DEFAULT 'draft'
);

CREATE TABLE combination_details (
    id SERIAL PRIMARY KEY,
    service_note_id INTEGER REFERENCES service_notes(id) ON DELETE CASCADE,
    employee_id INTEGER REFERENCES employees(id),
    combined_position VARCHAR(250),
    position_identifier VARCHAR(20),
    department VARCHAR(250),
    start_date DATE,
    end_date DATE,
    extra_pay NUMERIC(10, 2),
    payment_code VARCHAR(10),
    reason TEXT,
    duties TEXT
);

CREATE TABLE substitution_details (
    id SERIAL PRIMARY KEY,
    service_note_id INTEGER REFERENCES service_notes(id) ON DELETE CASCADE,
    substitute_employee_id INTEGER REFERENCES employees(id),
    absent_employee_id INTEGER REFERENCES employees(id),
    start_date DATE,
    end_date DATE,
    extra_pay_percent NUMERIC(5, 2),
    payment_code VARCHAR(10),
    duties TEXT,
    absence_reason VARCHAR(200)
);

CREATE TABLE allowance_details (
    id SERIAL PRIMARY KEY,
    service_note_id INTEGER REFERENCES service_notes(id) ON DELETE CASCADE,
    employee_id INTEGER REFERENCES employees(id),
    allowance_name VARCHAR(250),
    payment_code VARCHAR(10),
    unit VARCHAR(10),
    amount NUMERIC(10, 2),
    status VARCHAR(20),
    start_date DATE,
    end_date DATE
);