"""merge tasks into notes table

Revision ID: 0004
Revises: 0003
Create Date: 2025-01-01 00:00:00.000000
"""

from alembic import op
import sqlalchemy as sa

revision = "0004"
down_revision = "0003"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()

    # Add task columns to notes table (idempotent)
    conn.execute(sa.text("""
        DO $$ BEGIN
            ALTER TABLE notes ADD COLUMN status TEXT;
        EXCEPTION WHEN duplicate_column THEN null;
        END $$
    """))
    conn.execute(sa.text("""
        DO $$ BEGIN
            ALTER TABLE notes ADD COLUMN priority TEXT;
        EXCEPTION WHEN duplicate_column THEN null;
        END $$
    """))
    conn.execute(sa.text("""
        DO $$ BEGIN
            ALTER TABLE notes ADD COLUMN due_date DATE;
        EXCEPTION WHEN duplicate_column THEN null;
        END $$
    """))
    conn.execute(sa.text(
        "CREATE INDEX IF NOT EXISTS ix_notes_status ON notes (status)"
    ))

    # Migrate tasks that have companion notes (only if tasks table exists):
    conn.execute(sa.text("""
        DO $$ BEGIN
            IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'tasks') THEN
                UPDATE notes
                SET status = t.status,
                    priority = t.priority,
                    due_date = t.due_date,
                    body = COALESCE(NULLIF(t.description, ''), notes.body),
                    tags = CASE
                        WHEN array_length(t.tags, 1) IS NOT NULL THEN t.tags
                        ELSE notes.tags
                    END
                FROM tasks t
                WHERE t.note_id = notes.id;

                INSERT INTO notes (title, body, tags, status, priority, due_date, created_at, updated_at)
                SELECT title, description, tags, status, priority, due_date, created_at, updated_at
                FROM tasks
                WHERE note_id IS NULL;

                DROP TABLE tasks;
            END IF;
        END $$
    """))

    # Drop the old enum types that are no longer used by any table
    conn.execute(sa.text("DROP TYPE IF EXISTS task_status"))
    conn.execute(sa.text("DROP TYPE IF EXISTS task_priority"))


def downgrade() -> None:
    raise NotImplementedError("Downgrade not supported for this migration")