"""add customer_notes table (per-customer personal note, one row per customer_hash)

Revision ID: nt001
Revises: sig001
Create Date: 2026-07-01

Tenant-schema migration. Adds:
  - customer_notes: one editable free-text note per customer, keyed by customer_hash.
    UNIQUE on customer_hash enforces the one-note-per-customer invariant.
    updated_by_user_id is a plain integer (no cross-schema FK, same pattern as ticket_drafts).

Guarded with IF NOT EXISTS (via inspection) so it is safe to re-run on schemas
where the table was already applied directly.
"""
from alembic import op
import sqlalchemy as sa

revision = 'nt001'
down_revision = 'sig001'
branch_labels = None
depends_on = None


def upgrade():
    insp = sa.inspect(op.get_bind())
    existing = set(insp.get_table_names())

    if 'customer_notes' not in existing:
        op.create_table(
            'customer_notes',
            sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
            sa.Column('customer_hash', sa.String(191), nullable=False),
            sa.Column('note_text', sa.Text, nullable=True),
            sa.Column('updated_at', sa.DateTime, nullable=True),
            sa.Column('updated_by_user_id', sa.Integer, nullable=True),
            sa.UniqueConstraint('customer_hash', name='uq_customer_notes_hash'),
        )
        op.create_index(
            'ix_customer_notes_customer_hash',
            'customer_notes',
            ['customer_hash'],
            unique=True,
        )


def downgrade():
    op.drop_index('ix_customer_notes_customer_hash', table_name='customer_notes')
    op.drop_table('customer_notes')