database/db_schema.sql

-- Books API Data 数据库结构 (SQLite版本)
-- 适配自 BOOKS_API_DATA_文档.md 的数据结构

-- 1. 书籍表（books）
CREATE TABLE IF NOT EXISTS books (
    market_book_id INTEGER PRIMARY KEY,
    market_classify_id INTEGER,
    market_book_name TEXT NOT NULL,
    stage INTEGER,
    market_book_cover TEXT,
    mod_book_id INTEGER,
    mod_book_name TEXT,
    mod_book_cover TEXT,
    old_mod_book_id INTEGER,
    is_mod INTEGER DEFAULT 0,
    create_date TEXT,
    create_user INTEGER,
    isbn TEXT,
    type INTEGER,
    subject_id INTEGER,
    ver_id INTEGER,
    grade_id INTEGER,
    reel_id INTEGER,
    app_id TEXT,
    sort INTEGER,
    market_classify_name TEXT,
    old_mod_classify_id INTEGER,
    mod_classify_id INTEGER,
    start_page INTEGER,
    max_page INTEGER,
    source_dir TEXT,  -- 数据源目录
    imported_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_books_classify ON books(market_classify_id);
CREATE INDEX IF NOT EXISTS idx_books_grade_subject ON books(grade_id, subject_id);
CREATE INDEX IF NOT EXISTS idx_books_stage ON books(stage);

-- 2. 页面表（pages）
CREATE TABLE IF NOT EXISTS pages (
    page_id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    page_number INTEGER NOT NULL,
    origin_img_url TEXT,
    encrypt_img_url TEXT,
    FOREIGN KEY (book_id) REFERENCES books(market_book_id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_pages_book_page ON pages(book_id, page_number);
CREATE INDEX IF NOT EXISTS idx_pages_book ON pages(book_id);

-- 3. 页面片段表（pieces）
CREATE TABLE IF NOT EXISTS pieces (
    piece_id INTEGER PRIMARY KEY,
    page_id INTEGER NOT NULL,
    original TEXT,
    translation TEXT,
    origin_sound_url TEXT,
    encrypt_sound_url TEXT,
    duration INTEGER,
    coordinate_x REAL,
    coordinate_y REAL,
    coordinate_width REAL,
    coordinate_height REAL,
    is_evaluated INTEGER DEFAULT 0,
    show_translation INTEGER DEFAULT 1,
    rich_original TEXT,
    rich_translation TEXT,
    piece_order INTEGER,  -- 片段在页面中的顺序
    FOREIGN KEY (page_id) REFERENCES pages(page_id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_pieces_page ON pieces(page_id);
CREATE INDEX IF NOT EXISTS idx_pieces_page_order ON pieces(page_id, piece_order);

-- 4. 目录表（catalogs）
CREATE TABLE IF NOT EXISTS catalogs (
    catalog_id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    catalog_name TEXT,
    catalog_name_cn TEXT,
    start_page INTEGER,
    end_page INTEGER,
    thumbnail TEXT,
    parent_id INTEGER DEFAULT 0,
    sort INTEGER,
    FOREIGN KEY (book_id) REFERENCES books(market_book_id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_catalogs_book ON catalogs(book_id);
CREATE INDEX IF NOT EXISTS idx_catalogs_parent ON catalogs(parent_id);
CREATE INDEX IF NOT EXISTS idx_catalogs_book_sort ON catalogs(book_id, sort);

-- 5. 全文搜索支持（使用FTS5）
CREATE VIRTUAL TABLE IF NOT EXISTS pieces_fts USING fts5(
    piece_id UNINDEXED,
    original,
    translation,
    content=pieces,
    content_rowid=piece_id
);

-- 触发器：自动更新FTS索引
CREATE TRIGGER IF NOT EXISTS pieces_ai AFTER INSERT ON pieces BEGIN
    INSERT INTO pieces_fts(rowid, piece_id, original, translation)
    VALUES (new.piece_id, new.piece_id, new.original, new.translation);
END;

CREATE TRIGGER IF NOT EXISTS pieces_ad AFTER DELETE ON pieces BEGIN
    DELETE FROM pieces_fts WHERE rowid = old.piece_id;
END;

CREATE TRIGGER IF NOT EXISTS pieces_au AFTER UPDATE ON pieces BEGIN
    UPDATE pieces_fts 
    SET original = new.original, translation = new.translation
    WHERE rowid = new.piece_id;
END;