---

-- Задание 11. Комплексный сценарий (Администрирование блога)

-- 1. Создание структуры таблиц
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author_id INTEGER,
    is_published BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

-- Создаем таблицу комментариев, как того требует 4-й пункт задания
CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER,
    content TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

-- 2. Выполнение последовательности действий

-- Добавляем нового пользователя 'Мария'
INSERT INTO users (name) VALUES ('Мария');

-- Публикуем пост от имени Марии (предположим, её id = 2, если Мария — второй созданный юзер)
INSERT INTO posts (title, author_id, is_published) 
VALUES ('Мой первый пост', (SELECT id FROM users WHERE name = 'Мария'), 1);

-- Для пользователя с id=1 снимаем все посты с публикации (is_published = FALSE)
UPDATE posts 
SET is_published = 0 
WHERE author_id = 1;

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