"""Core → Cloud projection sync state per membership (Workstream E4).

Records the digest Cloud last acknowledged for each membership, so a sync compares canonical
state against what was acknowledged, not against what Cloud currently returns. A failed write
leaves the row pending and never changes Core authority.

Revision ID: 195
Revises: 194
"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision = "195"
down_revision = "194"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "project_membership_projection_syncs",
        sa.Column(
            "membership_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("project_memberships.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("acknowledged_digest", sa.Text(), nullable=True),
        sa.Column("status", sa.Text(), nullable=False, server_default="pending"),
        sa.Column("attempts", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("last_error", sa.Text(), nullable=True),
        sa.Column("last_attempted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("acknowledged_at", sa.DateTime(timezone=True), nullable=True),
        sa.CheckConstraint("status IN ('synced', 'pending')", name="ck_projection_sync_status"),
    )
    op.create_index(
        "ix_project_membership_projection_syncs_project_status",
        "project_membership_projection_syncs",
        ["project_id", "status"],
    )


def downgrade() -> None:
    op.drop_index(
        "ix_project_membership_projection_syncs_project_status",
        table_name="project_membership_projection_syncs",
    )
    op.drop_table("project_membership_projection_syncs")