-- Минимальная схема для этапа 8: книги, главы, анализы глав, теги, связи глава–тег.
-- Запускать один раз (например: psql -f schema.sql или через скрипт инициализации).

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE IF NOT EXISTS books (
    id UUID PRIMARY KEY,
    title VARCHAR(1024),
    author VARCHAR(512),
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS chapters (
    id UUID PRIMARY KEY,
    book_id UUID NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    chapter_number INTEGER NOT NULL,
    chapter_title VARCHAR(1024),
    content TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS chapter_analyses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chapter_id UUID NOT NULL UNIQUE REFERENCES chapters(id) ON DELETE CASCADE,
    analysis_result JSONB NOT NULL,
    validation_score FLOAT,
    validated_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(512) NOT NULL UNIQUE,
    category VARCHAR(64),
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS chapter_tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chapter_id UUID NOT NULL REFERENCES chapters(id) ON DELETE CASCADE,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    confidence FLOAT,
    validated BOOLEAN DEFAULT true,
    source VARCHAR(64) DEFAULT 'ai_validation',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE (chapter_id, tag_id)
);

CREATE INDEX IF NOT EXISTS idx_chapters_book_id ON chapters(book_id);
CREATE INDEX IF NOT EXISTS idx_chapter_analyses_chapter_id ON chapter_analyses(chapter_id);
CREATE INDEX IF NOT EXISTS idx_chapter_tags_chapter_id ON chapter_tags(chapter_id);
CREATE INDEX IF NOT EXISTS idx_chapter_tags_tag_id ON chapter_tags(tag_id);
CREATE INDEX IF NOT EXISTS idx_tags_category ON tags(category);