"""Step 1: Add new columns (non-destructive)

=== STATE TRANSITION ===
INITIAL:  Old SQLite store with base tables (agents, conversations, messages, etc.)
          - llm_id and agents_enabled already exist (part of old base schema)
          - extraction_mode may or may not exist (old migration #3)
RESULT:   Same tables + ensures llm_id, agents_enabled, extraction_mode columns exist
          - Idempotent: only adds columns if missing
          - 100% backward compatible with old plugin

DOWNGRADE: Does nothing - all columns added are backward compatible
           Keeps llm_id, agents_enabled (old base schema)
           Keeps extraction_mode (old migration #3)
========================

Revision ID: 33647ca7796c
Revises:
Create Date: 2025-11-25 15:48:50.202333
"""

import os
import sys
from typing import Sequence, Union

import sqlalchemy as sa

from alembic import op

# Add alembic folder to path for utils import
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from utils import (
    JsonEncoded,
    add_column,
    get_table_columns,
    timed_step,
)
from utils import (
    migration_logger as logger,
)
from utils import (
    table_exists as _table_exists,
)

# revision identifiers, used by Alembic.
revision: str = "33647ca7796c"
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

# Use a default length for all String columns for MySQL compatibility
STR_LEN = 255


def upgrade() -> None:
    """Add new columns to existing tables.

    This step is NON-DESTRUCTIVE and BACKWARD COMPATIBLE.
    Old plugin versions will continue to work - they simply ignore new columns.
    """
    with timed_step("Step 1/4: Add new columns"):
        bind = op.get_bind()
        inspector = sa.inspect(bind)

        # Add llm_id and agents_enabled to conversations
        if _table_exists("conversations"):
            columns = get_table_columns("conversations", inspector)
            if "llm_id" not in columns:
                logger.info("  Adding llm_id to conversations")
                add_column("conversations", sa.Column("llm_id", sa.String(STR_LEN), nullable=True))
            if "agents_enabled" not in columns:
                logger.info("  Adding agents_enabled to conversations")
                add_column(
                    "conversations",
                    sa.Column("agents_enabled", sa.Boolean(), nullable=True, server_default=sa.sql.expression.true()),
                )

        # Add llm_id and agents_enabled to messages
        if _table_exists("messages"):
            columns = get_table_columns("messages", inspector)
            if "llm_id" not in columns:
                logger.info("  Adding llm_id to messages")
                add_column("messages", sa.Column("llm_id", sa.String(STR_LEN), nullable=True))
            if "agents_enabled" not in columns:
                logger.info("  Adding agents_enabled to messages")
                add_column(
                    "messages",
                    sa.Column("agents_enabled", sa.Boolean(), nullable=True, server_default=sa.sql.expression.true()),
                )

        # Add extraction_mode to message_attachments
        if _table_exists("message_attachments"):
            columns = get_table_columns("message_attachments", inspector)
            if "extraction_mode" not in columns:
                logger.info("  Adding extraction_mode to message_attachments")
                add_column(
                    "message_attachments",
                    sa.Column("extraction_mode", JsonEncoded(), nullable=True),
                )


def downgrade() -> None:
    """Downgrade Step 1 - No-op.

    IMPORTANT: We do NOT drop ANY columns because:
    - llm_id and agents_enabled: Part of old SQLite store base schema
    - extraction_mode: Added by old plugin migration #3

    All columns added in this step are backward compatible.
    Old plugin versions will continue to work - they simply ignore columns they don't use.
    """
    logger.info("[Downgrade Step 1] No changes needed - all columns are backward compatible")
    logger.info("[Note] llm_id, agents_enabled, and extraction_mode retained for old plugin compatibility")
