"""启动时补齐新增列（已有库无需手工迁移）。"""

from sqlalchemy import inspect, text

from database import engine


def run_migrations() -> None:
    insp = inspect(engine)
    with engine.begin() as conn:
        if insp.has_table("words"):
            cols = {c["name"] for c in insp.get_columns("words")}
            if "total_train_seconds" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE words ADD COLUMN total_train_seconds "
                        "INTEGER NOT NULL DEFAULT 0"
                    )
                )
            if "train_count" not in cols:
                conn.execute(
                    text("ALTER TABLE words ADD COLUMN train_count INTEGER NOT NULL DEFAULT 0")
                )
            if "difficulty" not in cols:
                conn.execute(
                    text("ALTER TABLE words ADD COLUMN difficulty DOUBLE NOT NULL DEFAULT 5.0")
                )
            if "stability_hours" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE words ADD COLUMN stability_hours "
                        "DOUBLE NOT NULL DEFAULT 24.0"
                    )
                )
            if "next_review_at" not in cols:
                conn.execute(text("ALTER TABLE words ADD COLUMN next_review_at VARCHAR(32)"))
            if "error_bank_member" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE words ADD COLUMN error_bank_member "
                        "INTEGER NOT NULL DEFAULT 0"
                    )
                )
            if "error_reinforce_streak" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE words ADD COLUMN error_reinforce_streak "
                        "INTEGER NOT NULL DEFAULT 0"
                    )
                )
            if "error_bank_entered_at" not in cols:
                conn.execute(
                    text("ALTER TABLE words ADD COLUMN error_bank_entered_at VARCHAR(32)")
                )
            conn.execute(
                text(
                    "UPDATE words SET error_bank_member = 1 "
                    "WHERE wrong_count > 0 AND error_bank_member = 0"
                )
            )
            conn.execute(
                text(
                    "UPDATE words SET error_bank_member = 1 "
                    "WHERE error_reinforce_streak >= 2 AND error_bank_member = 0"
                )
            )

        if insp.has_table("quiz_records"):
            cols = {c["name"] for c in insp.get_columns("quiz_records")}
            if "duration_seconds" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE quiz_records ADD COLUMN duration_seconds "
                        "INTEGER NOT NULL DEFAULT 0"
                    )
                )

        if insp.has_table("user_settings"):
            cols = {c["name"] for c in insp.get_columns("user_settings")}
            if "active_book_id" not in cols:
                conn.execute(text("ALTER TABLE user_settings ADD COLUMN active_book_id INTEGER"))
            if "ai_wiki_enabled" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE user_settings ADD COLUMN ai_wiki_enabled "
                        "INTEGER NOT NULL DEFAULT 0"
                    )
                )
            if "ai_wiki_auto_organize" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE user_settings ADD COLUMN ai_wiki_auto_organize "
                        "INTEGER NOT NULL DEFAULT 0"
                    )
                )
            if "error_clear_correct_count" not in cols:
                conn.execute(
                    text(
                        "ALTER TABLE user_settings ADD COLUMN error_clear_correct_count "
                        "INTEGER NOT NULL DEFAULT 2"
                    )
                )

        if insp.has_table("user_book_settings"):
            ubs_cols = {c["name"] for c in insp.get_columns("user_book_settings")}
            if "error_clear_correct_count" not in ubs_cols:
                conn.execute(
                    text(
                        "ALTER TABLE user_book_settings ADD COLUMN error_clear_correct_count "
                        "INTEGER NOT NULL DEFAULT 2"
                    )
                )

        if not insp.has_table("wiki_sources"):
            conn.execute(
                text(
                    """
                    CREATE TABLE wiki_sources (
                        id INTEGER PRIMARY KEY AUTO_INCREMENT,
                        user_id INTEGER NOT NULL,
                        word_id INTEGER NULL,
                        source_key VARCHAR(96) NOT NULL,
                        source_type VARCHAR(32) NOT NULL,
                        payload TEXT NOT NULL,
                        created_at VARCHAR(32) NOT NULL,
                        CONSTRAINT uq_wiki_source_user_key UNIQUE (user_id, source_key),
                        FOREIGN KEY(user_id) REFERENCES users (id),
                        FOREIGN KEY(word_id) REFERENCES words (id)
                    )
                    """
                )
            )
            conn.execute(text("CREATE INDEX ix_wiki_sources_user_id ON wiki_sources (user_id)"))
            conn.execute(text("CREATE INDEX ix_wiki_sources_word_id ON wiki_sources (word_id)"))

        if not insp.has_table("wiki_pages"):
            conn.execute(
                text(
                    """
                    CREATE TABLE wiki_pages (
                        id INTEGER PRIMARY KEY AUTO_INCREMENT,
                        user_id INTEGER NOT NULL,
                        word_id INTEGER NULL,
                        slug VARCHAR(160) NOT NULL,
                        title VARCHAR(500) NOT NULL,
                        category VARCHAR(32) NOT NULL,
                        summary TEXT NOT NULL,
                        content TEXT NOT NULL,
                        source_count INTEGER NOT NULL DEFAULT 0,
                        created_at VARCHAR(32) NOT NULL,
                        updated_at VARCHAR(32) NOT NULL,
                        CONSTRAINT uq_wiki_page_user_slug UNIQUE (user_id, slug),
                        FOREIGN KEY(user_id) REFERENCES users (id),
                        FOREIGN KEY(word_id) REFERENCES words (id)
                    )
                    """
                )
            )
            conn.execute(text("CREATE INDEX ix_wiki_pages_user_id ON wiki_pages (user_id)"))
            conn.execute(text("CREATE INDEX ix_wiki_pages_word_id ON wiki_pages (word_id)"))

        if not insp.has_table("wiki_links"):
            conn.execute(
                text(
                    """
                    CREATE TABLE wiki_links (
                        id INTEGER PRIMARY KEY AUTO_INCREMENT,
                        user_id INTEGER NOT NULL,
                        source_page_id INTEGER NOT NULL,
                        target_page_id INTEGER NOT NULL,
                        relation VARCHAR(32) NOT NULL,
                        created_at VARCHAR(32) NOT NULL,
                        CONSTRAINT uq_wiki_link_edge UNIQUE (
                            user_id, source_page_id, target_page_id, relation
                        ),
                        FOREIGN KEY(user_id) REFERENCES users (id),
                        FOREIGN KEY(source_page_id) REFERENCES wiki_pages (id),
                        FOREIGN KEY(target_page_id) REFERENCES wiki_pages (id)
                    )
                    """
                )
            )
            conn.execute(text("CREATE INDEX ix_wiki_links_user_id ON wiki_links (user_id)"))
            conn.execute(
                text("CREATE INDEX ix_wiki_links_source_page_id ON wiki_links (source_page_id)")
            )
            conn.execute(
                text("CREATE INDEX ix_wiki_links_target_page_id ON wiki_links (target_page_id)")
            )

        if not insp.has_table("wiki_logs"):
            conn.execute(
                text(
                    """
                    CREATE TABLE wiki_logs (
                        id INTEGER PRIMARY KEY AUTO_INCREMENT,
                        user_id INTEGER NOT NULL,
                        action VARCHAR(32) NOT NULL,
                        page_slug VARCHAR(160) NULL,
                        detail TEXT NOT NULL,
                        created_at VARCHAR(32) NOT NULL,
                        FOREIGN KEY(user_id) REFERENCES users (id)
                    )
                    """
                )
            )
            conn.execute(text("CREATE INDEX ix_wiki_logs_user_id ON wiki_logs (user_id)"))

        if not insp.has_table("wiki_llm_usage"):
            conn.execute(
                text(
                    """
                    CREATE TABLE wiki_llm_usage (
                        id INTEGER PRIMARY KEY AUTO_INCREMENT,
                        user_id INTEGER NOT NULL,
                        usage_date VARCHAR(10) NOT NULL,
                        call_count INTEGER NOT NULL DEFAULT 0,
                        CONSTRAINT uq_wiki_llm_usage_day UNIQUE (user_id, usage_date),
                        FOREIGN KEY(user_id) REFERENCES users (id)
                    )
                    """
                )
            )
            conn.execute(text("CREATE INDEX ix_wiki_llm_usage_user_id ON wiki_llm_usage (user_id)"))

        if insp.has_table("wiki_pages"):
            conn.execute(
                text(
                    """
                    UPDATE wiki_pages SET
                      title = REPLACE(title, 'WordLoop LLM Wiki', 'WordLoop Wiki'),
                      content = REPLACE(content, 'WordLoop LLM Wiki', 'WordLoop Wiki')
                    WHERE title LIKE '%LLM Wiki%' OR content LIKE '%LLM Wiki%'
                    """
                )
            )

        if not insp.has_table("user_book_settings"):
            conn.execute(
                text(
                    """
                    CREATE TABLE user_book_settings (
                        id INTEGER PRIMARY KEY AUTO_INCREMENT,
                        user_id INTEGER NOT NULL,
                        book_id INTEGER NOT NULL,
                        daily_target INTEGER NOT NULL DEFAULT 12,
                        master_required_count INTEGER NOT NULL DEFAULT 3,
                        weak_wrong_threshold INTEGER NOT NULL DEFAULT 2,
                        CONSTRAINT uq_user_book_settings UNIQUE (user_id, book_id),
                        FOREIGN KEY(user_id) REFERENCES users (id),
                        FOREIGN KEY(book_id) REFERENCES word_books (id)
                    )
                    """
                )
            )

        if insp.has_table("words"):
            cols = {c["name"] for c in insp.get_columns("words")}
            if "book_id" not in cols:
                conn.execute(
                    text("ALTER TABLE words ADD COLUMN book_id INTEGER NOT NULL DEFAULT 0")
                )
                conn.execute(text("CREATE INDEX ix_words_book_id ON words (book_id)"))
            if "book_entry_id" not in cols:
                conn.execute(text("ALTER TABLE words ADD COLUMN book_entry_id INTEGER"))

        if insp.has_table("words") and insp.has_table("quiz_records"):
            conn.execute(
                text(
                    """
                    UPDATE words w SET
                      train_count = (
                        SELECT COUNT(*) FROM quiz_records q WHERE q.word_id = w.id
                      ),
                      total_train_seconds = (
                        SELECT COALESCE(SUM(duration_seconds), 0)
                        FROM quiz_records q WHERE q.word_id = w.id
                      )
                    WHERE train_count = 0 AND EXISTS (
                      SELECT 1 FROM quiz_records q WHERE q.word_id = w.id
                    )
                    """
                )
            )