---

-- Включение поддержки внешних ключей (актуально для SQLite)
PRAGMA foreign_keys = ON;

-- 1. Таблица departments (Отделы)
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    budget NUMERIC(12, 2) DEFAULT 0.00,
    location TEXT
);

-- 2. Таблица employees (Сотрудники)
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary NUMERIC(10, 2) CHECK (salary > 0),
    department_id INTEGER,
    is_remote INTEGER DEFAULT 0 CHECK (is_remote IN (0, 1)),
    tech_stack TEXT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 3. Таблица tasks (Задачи)
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    employee_id INTEGER,
    status TEXT CHECK (status IN ('new', 'in_progress', 'done')),
    priority INTEGER CHECK (priority >= 1 AND priority <= 5),
    deadline DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);