"""Add a real Order entity to payment-checkout and have it actually get written to.

v1.0.0 resolved trusted Stripe prices server-side (payment.checkout.create's whole
point) but never created any record of the resulting order -- "payment-checkout
needs a real Order entity + reconciliation function" from the 2026-09-08 pack
review. Cloud's payment-checkout-create job handler
(P2HSCloud/apps/pc-processor/src/handlers/payment-checkout-create.ts) now upserts
a pending `order` record (keyed by its own job id, so a handler retry can't create
a duplicate) before creating the Stripe Checkout Session, tags the session's
metadata with `p2hs_order_id`, and patches the order with the resulting
stripe_checkout_session_id/payment_intent/amount/currency once the session exists.

Reconciling `checkout.session.completed` into `status: paid` still happens the
way it always has for this pack -- forwarded (already-verified) to the owner's
own BFF route (`config.webhook_forward_url`, unchanged by this migration), which
now has a real order record with a known id (`payload.payload.metadata.p2hs_order_id`)
to PATCH via CLOUD_SERVICE_TOKEN instead of inventing its own order concept from
scratch. See the "Packs that need an inbound webhook" codegen guidance
(src/domain/codegen/prompt_wrapper.py) for the exact PATCH shape.

Revision ID: 111
Revises: 110
"""
from datetime import datetime, timezone

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "111"
down_revision = "110"
branch_labels = None
depends_on = None

feature_packs = sa.table(
    "feature_packs",
    sa.column("slug", sa.String),
    sa.column("version", sa.String),
    sa.column("composition", postgresql.JSONB),
    sa.column("updated_at", sa.DateTime(timezone=True)),
)

_PRODUCT_ENTITY = {
    "entity_key": "product",
    "name": "Product",
    "plural_name": "Products",
    "schema": {
        "fields": [
            {"key": "name", "name": "Name", "type": "text", "required": True},
            {"key": "description", "name": "Description", "type": "text"},
            {"key": "stripe_product_id", "name": "Stripe Product ID", "type": "text", "indexed": True},
        ],
    },
}

_PRICE_ENTITY = {
    "entity_key": "price",
    "name": "Price",
    "plural_name": "Prices",
    "schema": {
        "fields": [
            {"key": "product_record_id", "name": "Product", "type": "relation",
             "relation_entity_key": "product", "required": True, "indexed": True},
            {"key": "stripe_price_id", "name": "Stripe Price ID", "type": "text", "required": True, "indexed": True},
            {"key": "unit_amount", "name": "Unit Amount (display only)", "type": "number"},
            {"key": "currency", "name": "Currency (display only)", "type": "text"},
        ],
    },
}

_ORDER_ENTITY = {
    "entity_key": "order",
    "name": "Order",
    "plural_name": "Orders",
    "schema": {
        "fields": [
            # Set once, at creation, by payment-checkout-create's own job handler --
            # makes the upsert that creates this record idempotent across handler retries.
            {"key": "source_job_id", "name": "Source Job ID", "type": "text", "indexed": True},
            {"key": "customer_email", "name": "Customer Email", "type": "text", "indexed": True},
            {"key": "status", "name": "Status", "type": "select", "indexed": True,
             "options": ["pending", "paid", "failed", "canceled"]},
            {"key": "line_items", "name": "Line Items", "type": "json"},
            {"key": "amount_total", "name": "Amount Total (display only)", "type": "number"},
            {"key": "currency", "name": "Currency (display only)", "type": "text"},
            {"key": "stripe_checkout_session_id", "name": "Stripe Checkout Session ID", "type": "text", "indexed": True},
            {"key": "stripe_payment_intent_id", "name": "Stripe Payment Intent ID", "type": "text", "indexed": True},
        ],
    },
}

_COMPOSITION_V1_1 = {
    "function_slugs": [],
    "entities": [_PRODUCT_ENTITY, _PRICE_ENTITY, _ORDER_ENTITY],
    "connector_actions": ["payment.checkout.create", "stripe.create_checkout"],
    "requires": [],
    "requires_connections": ["stripe"],
    "webhooks": [
        {
            "provider": "stripe",
            "event_types": ["checkout.session.completed"],
            "forward_url_config_key": "webhook_forward_url",
        },
    ],
}

_COMPOSITION_V1_0 = {
    "function_slugs": [],
    "entities": [_PRODUCT_ENTITY, _PRICE_ENTITY],
    "connector_actions": ["payment.checkout.create", "stripe.create_checkout"],
    "requires": ["stripe"],
    "webhooks": [
        {
            "provider": "stripe",
            "event_types": ["checkout.session.completed"],
            "forward_url_config_key": "webhook_forward_url",
        },
    ],
}


def upgrade() -> None:
    op.execute(
        sa.update(feature_packs)
        .where(feature_packs.c.slug == "payment-checkout")
        .values(
            version="1.1.0",
            composition=_COMPOSITION_V1_1,
            updated_at=datetime.now(timezone.utc),
        )
    )


def downgrade() -> None:
    op.execute(
        sa.update(feature_packs)
        .where(feature_packs.c.slug == "payment-checkout")
        .values(
            version="1.0.0",
            composition=_COMPOSITION_V1_0,
            updated_at=datetime.now(timezone.utc),
        )
    )