---

-- Задание 1. Создание и наполнение таблицы учителей
-- Используем INTEGER для ID и стажа, TEXT для текстовых полей
CREATE TABLE teachers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL,
    subject TEXT NOT NULL,
    experience_years INTEGER
);

-- Добавляем 4 преподавателей по списку из задания
INSERT INTO teachers (full_name, subject, experience_years) VALUES
('Иванов И.И.', 'математика', 5),
('Петрова А.А.', 'чтение', 3),
('Сидоров С.С.', 'окружающий мир', 10),
('Кузнецова М.М.', 'математика', 2);

-- Задание 2. Почему INSERT без перечисления столбцов опасен?
-- Ответ: Это делает запрос зависимым от структуры таблицы. 
-- Если добавить новую колонку, старый код без указания имен сразу сломается.

-- Задание 3. Массовое и точечное обновление (UPDATE)
-- Увеличиваем стаж всем учителям на 1 год
UPDATE teachers SET experience_years = experience_years + 1;

-- Меняем предметы конкретным преподавателям через их ID
UPDATE teachers SET subject = 'литература' WHERE id = 2;
UPDATE teachers SET subject = 'биология' WHERE id = 3;

-- Задание 4. Опасность UPDATE без WHERE
-- Результат: У всех записей в таблице предмет станет «АЛГЕБРА».
-- Это опасно потерей данных, так как без условия WHERE команда меняет каждую строку.

-- Задание 5. Физическое и «мягкое» удаление
-- 1. Обычное удаление одного учителя математики
DELETE FROM teachers WHERE subject = 'математика' LIMIT 1;

-- 2. Добавляем колонку для мягкого удаления
ALTER TABLE teachers ADD COLUMN is_deleted BOOLEAN DEFAULT 0;

-- 3. Мягкое удаление (просто ставим отметку)
UPDATE teachers SET is_deleted = 1 WHERE subject = 'математика';

-- 4. Восстановление записи обратно
UPDATE teachers SET is_deleted = 0 WHERE subject = 'математика';

-- Задание 6. Разница между удалениями
-- Физическое удаление стирает данные из памяти навсегда. 
-- Мягкое удаление просто скрывает их, позволяя восстановить данные или смотреть историю.

-- Задание 7. Каскадные операции
-- Создаем категории
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Создаем товары с автоматическим удалением при удалении категории
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
);

-- Задание 8. Транзакции
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

INSERT INTO accounts (name, balance) VALUES ('User1', 5000), ('User2', 1000);

-- Перевод 2000 через транзакцию
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 2000 WHERE id = 1;
UPDATE accounts SET balance = balance + 2000 WHERE id = 2;
COMMIT;

-- Задание 9. Сценарий транзакции из жизни
-- Покупка места в кино: сначала место бронируется, потом списываются деньги. 
-- Если оплата не прошла, бронь должна отмениться.

-- Задание 10. Свойство ACID
-- Ответ: Изолированность (Isolation).

-- Задание 11. Комплексный сценарий (Блог)
-- 1. Добавляем пользователя
INSERT INTO users (name) VALUES ('Мария');

-- 2. Пост от нового пользователя
INSERT INTO posts (title, author_id, is_published) VALUES ('Мой первый пост', 2, 1);

-- 3. Скрываем посты автора с id=1
UPDATE posts SET is_published = 0 WHERE author_id = 1;

-- 4. Удаляем комментарии к скрытым постам первого автора
DELETE FROM comments WHERE post_id IN (SELECT id FROM posts WHERE author_id = 1 AND is_published = 0);