"""Add projects table and project_id to notes."""

from alembic import op

revision = "0017"
down_revision = "0016"


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS projects (
            id          SERIAL PRIMARY KEY,
            user_id     INTEGER REFERENCES users(id) ON DELETE CASCADE,
            title       TEXT NOT NULL DEFAULT '',
            description TEXT NOT NULL DEFAULT '',
            goal        TEXT NOT NULL DEFAULT '',
            status      TEXT NOT NULL DEFAULT 'active',
            color       TEXT,
            created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            updated_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_projects_user_id ON projects(user_id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_projects_status ON projects(status)")
    op.execute("ALTER TABLE notes ADD COLUMN IF NOT EXISTS project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL")
    op.execute("CREATE INDEX IF NOT EXISTS ix_notes_project_id ON notes(project_id)")
    # Add FK constraint to existing parent_id column (was nullable with no FK)
    # Only add if constraint doesn't already exist
    op.execute("""
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint WHERE conname = 'notes_parent_id_fkey'
            ) THEN
                ALTER TABLE notes ADD CONSTRAINT notes_parent_id_fkey
                    FOREIGN KEY (parent_id) REFERENCES notes(id) ON DELETE SET NULL;
            END IF;
        END $$;
    """)


def downgrade() -> None:
    op.execute("ALTER TABLE notes DROP CONSTRAINT IF EXISTS notes_parent_id_fkey")
    op.execute("DROP INDEX IF EXISTS ix_notes_project_id")
    op.execute("ALTER TABLE notes DROP COLUMN IF EXISTS project_id")
    op.execute("DROP INDEX IF EXISTS ix_projects_status")
    op.execute("DROP INDEX IF EXISTS ix_projects_user_id")
    op.execute("DROP TABLE IF EXISTS projects")