"""phase13 connectors: connector_account/sync_state/external_link/oauth_state/log + finance_tx/health_sample/chat_message

Revision ID: p13a1b2c3d4e5
Revises: p12f6a7b8c9d0
Create Date: 2026-06-15 06:00:00.000000

"""

from typing import Sequence, Union

import sqlalchemy as sa
import sqlmodel
from alembic import op

revision: str = "p13a1b2c3d4e5"
down_revision: Union[str, Sequence[str], None] = "p12f6a7b8c9d0"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

S = sqlmodel.sql.sqltypes.AutoString


def upgrade() -> None:
    op.create_table(
        "connector_account",
        sa.Column("id", S(), nullable=False),
        sa.Column("user_id", S(), nullable=False),
        sa.Column("domain", S(), nullable=False),
        sa.Column("mode", S(), nullable=False),
        sa.Column("provider", S(), nullable=False),
        sa.Column("external_account_id", S(), nullable=False),
        sa.Column("name", S(), nullable=False),
        sa.Column("kind", S(), nullable=False),
        sa.Column("tone", S(), nullable=False),
        sa.Column("state", S(), nullable=False),
        sa.Column("last_synced_at", sa.DateTime(), nullable=True),
        sa.Column("last_label", S(), nullable=False),
        sa.Column("token_enc", S(), nullable=False),
        sa.Column("scopes", S(), nullable=False),
        sa.Column("error_detail", S(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["person.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_table(
        "connector_sync_state",
        sa.Column("id", S(), nullable=False),
        sa.Column("account_id", S(), nullable=False),
        sa.Column("cursor", S(), nullable=False),
        sa.Column("page_token", S(), nullable=False),
        sa.Column("last_full_sync_at", sa.DateTime(), nullable=True),
        sa.Column("last_delta_sync_at", sa.DateTime(), nullable=True),
        sa.Column("items_seen", sa.Integer(), nullable=False),
        sa.Column("items_upserted", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["connector_account.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_table(
        "external_link",
        sa.Column("id", S(), nullable=False),
        sa.Column("account_id", S(), nullable=False),
        sa.Column("external_id", S(), nullable=False),
        sa.Column("entity_type", S(), nullable=False),
        sa.Column("entity_id", S(), nullable=False),
        sa.Column("etag", S(), nullable=False),
        sa.Column("external_updated_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["connector_account.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_external_link_account_id", "external_link", ["account_id"])
    op.create_index("ix_external_link_external_id", "external_link", ["external_id"])
    # 멱등성 핵심: (account_id, external_id) 복합 UNIQUE
    op.create_index(
        "ux_extlink_acct_ext", "external_link", ["account_id", "external_id"], unique=True
    )
    op.create_table(
        "oauth_state",
        sa.Column("id", S(), nullable=False),
        sa.Column("domain", S(), nullable=False),
        sa.Column("provider", S(), nullable=False),
        sa.Column("code_verifier", S(), nullable=False),
        sa.Column("redirect_after", S(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_table(
        "connector_account_log",
        sa.Column("id", S(), nullable=False),
        sa.Column("account_id", S(), nullable=False),
        sa.Column("action", S(), nullable=False),
        sa.Column("detail", S(), nullable=False),
        sa.Column("items", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["connector_account.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_table(
        "finance_tx",
        sa.Column("id", S(), nullable=False),
        sa.Column("account_id", S(), nullable=False),
        sa.Column("date", S(), nullable=False),
        sa.Column("merchant", S(), nullable=False),
        sa.Column("amount", sa.Integer(), nullable=False),
        sa.Column("category", S(), nullable=False),
        sa.Column("category_id", S(), nullable=False),
        sa.Column("account_label", S(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["connector_account.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_table(
        "health_sample",
        sa.Column("id", S(), nullable=False),
        sa.Column("account_id", S(), nullable=False),
        sa.Column("kind", S(), nullable=False),
        sa.Column("value", sa.Float(), nullable=False),
        sa.Column("unit", S(), nullable=False),
        sa.Column("day", S(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["connector_account.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_table(
        "chat_message",
        sa.Column("id", S(), nullable=False),
        sa.Column("account_id", S(), nullable=False),
        sa.Column("sender", S(), nullable=False),
        sa.Column("text", S(), nullable=False),
        sa.Column("channel", S(), nullable=False),
        sa.Column("at", S(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["connector_account.id"]),
        sa.PrimaryKeyConstraint("id"),
    )


def downgrade() -> None:
    op.drop_table("chat_message")
    op.drop_table("health_sample")
    op.drop_table("finance_tx")
    op.drop_table("connector_account_log")
    op.drop_table("oauth_state")
    op.drop_index("ux_extlink_acct_ext", table_name="external_link")
    op.drop_index("ix_external_link_external_id", table_name="external_link")
    op.drop_index("ix_external_link_account_id", table_name="external_link")
    op.drop_table("external_link")
    op.drop_table("connector_sync_state")
    op.drop_table("connector_account")