Pastein: -- ============================================ -- База данных: blagaya_vest -- Таблицы для работы с Библией -- ============================================ --

Загрузка данных
-- ============================================
-- База данных: blagaya_vest
-- Таблицы для работы с Библией
-- ============================================

-- 1. Таблица книг Библии
CREATE TABLE IF NOT EXISTS bible_books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT 'Название книги (Бытие, Исход...)',
    short_name VARCHAR(20) NOT NULL COMMENT 'Сокращение (Быт, Исх...)',
    testament ENUM('old', 'new') NOT NULL COMMENT 'Ветхий или Новый Завет',
    chapters_count INT NOT NULL COMMENT 'Количество глав',
    order_num INT NOT NULL COMMENT 'Порядковый номер в Библии',
    
    UNIQUE KEY unique_book (short_name),
    INDEX idx_testament (testament),
    INDEX idx_order (order_num)
) ENGINE=InnoDB;

-- 2. Таблица стихов
CREATE TABLE IF NOT EXISTS bible_verses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    chapter INT NOT NULL COMMENT 'Номер главы',
    verse INT NOT NULL COMMENT 'Номер стиха',
    text TEXT NOT NULL COMMENT 'Текст стиха',
    
    UNIQUE KEY unique_verse (book_id, chapter, verse),
    FOREIGN KEY (book_id) REFERENCES bible_books(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 3. Избранные стихи
CREATE TABLE IF NOT EXISTS favorite_verses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    verse_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_favorite (user_id, verse_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verse_id) REFERENCES bible_verses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 4. Закладки (с возможностью сохранения контекста)
CREATE TABLE IF NOT EXISTS bookmarks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    verse_id INT NOT NULL,
    note TEXT DEFAULT NULL COMMENT 'Личная заметка',
    tags VARCHAR(255) DEFAULT NULL COMMENT 'Теги через запятую',
    color VARCHAR(7) DEFAULT '#DAA520' COMMENT 'Цвет закладки (HEX)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_bookmark (user_id, verse_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verse_id) REFERENCES bible_verses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 5. Представление: избранные стихи с текстом
CREATE OR REPLACE VIEW favorite_verses_view AS
SELECT 
    fv.id AS favorite_id,
    fv.user_id,
    fv.created_at AS added_date,
    bv.id AS verse_id,
    bv.chapter,
    bv.verse,
    bv.text AS verse_text,
    bb.name AS book_name,
    bb.short_name AS book_short,
    bb.testament,
    CONCAT(bb.short_name, ' ', bv.chapter, ':', bv.verse) AS reference
FROM favorite_verses fv
JOIN bible_verses bv ON fv.verse_id = bv.id
JOIN bible_books bb ON bv.book_id = bb.id
ORDER BY fv.created_at DESC;

-- 6. Представление: закладки с текстом
CREATE OR REPLACE VIEW bookmarks_view AS
SELECT 
    bm.id AS bookmark_id,
    bm.user_id,
    bm.note,
    bm.tags,
    bm.color,
    bm.created_at,
    bv.id AS verse_id,
    bv.chapter,
    bv.verse,
    bv.text AS verse_text,
    bb.name AS book_name,
    bb.short_name AS book_short,
    bb.testament,
    CONCAT(bb.short_name, ' ', bv.chapter, ':', bv.verse) AS reference
FROM bookmarks bm
JOIN bible_verses bv ON bm.verse_id = bv.id
JOIN bible_books bb ON bv.book_id = bb.id
ORDER BY bm.created_at DESC;

-- 7. Тестовые данные: книги Библии
INSERT INTO bible_books (name, short_name, testament, chapters_count, order_num) VALUES
('Бытие', 'Быт', 'old', 50, 1),
('Исход', 'Исх', 'old', 40, 2),
('Псалтирь', 'Пс', 'old', 150, 19),
('Исаия', 'Ис', 'old', 66, 23),
('Евангелие от Матфея', 'Мф', 'new', 28, 40),
('Евангелие от Марка', 'Мк', 'new', 16, 41),
('Евангелие от Луки', 'Лк', 'new', 24, 42),
('Евангелие от Иоанна', 'Ин', 'new', 21, 43);

-- 8. Тестовые стихи
INSERT INTO bible_verses (book_id, chapter, verse, text) VALUES
(5, 5, 3, 'Блаженны нищие духом, ибо их есть Царство Небесное.'),  -- Мф 5:3
(5, 5, 4, 'Блаженны плачущие, ибо они утешатся.'),
(5, 5, 5, 'Блаженны кроткие, ибо они наследуют землю.'),
(6, 5, 36, 'Не бойся, только веруй.'),  -- Мк 5:36
(8, 3, 16, 'Ибо так возлюбил Бог мир, что отдал Сына Своего Единородного...'),  -- Ин 3:16
(3, 22, 1, 'Господь — Пастырь мой; я ни в чем не буду нуждаться.'),  -- Пс 22:1
(3, 22, 2, 'Он покоит меня на злачных пажитях и водит меня к водам тихим.'),
(1, 1, 1, 'В начале сотворил Бог небо и землю.'),  -- Быт 1:1
(4, 40, 31, 'А надеющиеся на Господа обновятся в силе...'),  -- Ис 40:31
(7, 1, 37, 'Ибо у Бога не останется бессильным никакое слово.');  -- Лк 1:37

-- 9. Тестовые данные: избранное
INSERT INTO favorite_verses (user_id, verse_id) VALUES
(1, 1), (1, 5), (1, 6),
(2, 1), (2, 4), (2, 9),
(3, 5), (3, 6), (3, 10);

-- 10. Тестовые данные: закладки с заметками
INSERT INTO bookmarks (user_id, verse_id, note, tags, color) VALUES
(1, 1, 'Важный стих о смирении', 'смирение,благословение', '#FF6B6B'),
(1, 6, 'Любимый псалом о защите Бога', 'защита,псалом', '#4ECDC4'),
(2, 5, 'Главный стих Евангелия', 'спасение,любовь', '#FFD700'),
(3, 4, 'Напоминание о вере в трудные времена', 'вера,надежда', '#95E1D3');

-- 11. Хранимые процедуры

-- Добавить/удалить стих в избранное
DELIMITER //
CREATE PROCEDURE ToggleFavoriteVerse(
    IN p_user_id INT,
    IN p_verse_id INT
)
BEGIN
    DECLARE v_exists INT;
    
    SELECT COUNT(*) INTO v_exists 
    FROM favorite_verses 
    WHERE user_id = p_user_id AND verse_id = p_verse_id;
    
    IF v_exists > 0 THEN
        DELETE FROM favorite_verses 
        WHERE user_id = p_user_id AND verse_id = p_verse_id;
        SELECT 'removed' AS action;
    ELSE
        INSERT INTO favorite_verses (user_id, verse_id) 
        VALUES (p_user_id, p_verse_id);
        SELECT 'added' AS action;
    END IF;
END//

-- Добавить/удалить закладку
CREATE PROCEDURE ToggleBookmark(
    IN p_user_id INT,
    IN p_verse_id INT,
    IN p_note TEXT,
    IN p_tags VARCHAR(255),
    IN p_color VARCHAR(7)
)
BEGIN
    DECLARE v_exists INT;
    
    SELECT COUNT(*) INTO v_exists 
    FROM bookmarks 
    WHERE user_id = p_user_id AND verse_id = p_verse_id;
    
    IF v_exists > 0 THEN
        DELETE FROM bookmarks 
        WHERE user_id = p_user_id AND verse_id = p_verse_id;
        SELECT 'removed' AS action;
    ELSE
        INSERT INTO bookmarks (user_id, verse_id, note, tags, color) 
        VALUES (p_user_id, p_verse_id, p_note, p_tags, p_color);
        SELECT 'added' AS action;
    END IF;
END//
DELIMITER ;

-- 12. Полезные запросы

-- Поиск стихов по тексту
SELECT * FROM bible_verses 
WHERE text LIKE '%блаженны%'
ORDER BY id;

-- Все избранные стихи пользователя
SELECT * FROM favorite_verses_view 
WHERE user_id = 1;

-- Все закладки пользователя
SELECT * FROM bookmarks_view 
WHERE user_id = 1;

-- Статистика по книге
SELECT 
    bb.name,
    bb.testament,
    COUNT(bv.id) AS verses_count
FROM bible_books bb
LEFT JOIN bible_verses bv ON bb.id = bv.book_id
GROUP BY bb.id, bb.name, bb.testament
ORDER BY bb.order_num;
Больше возможностей при регистрации:
