"""add user table and per-user scoping

Revision ID: 415cd00cc261
Revises: 3d59812ae93f
Create Date: 2026-07-12 16:58:28.453295

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '415cd00cc261'
down_revision: Union[str, Sequence[str], None] = '3d59812ae93f'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    op.create_table(
        'user',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('discord_user_id', sa.String(), nullable=True),
        sa.Column('name', sa.String(), nullable=True),
        sa.Column('task_id_cap', sa.Integer(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('discord_user_id'),
    )

    # day_plan_cache and bot_setting hold nothing but regenerable AI output
    # / a "last sent" marker - dropping and recreating them with the new
    # composite (user_id, ...) primary key is far simpler and safer than
    # trying to alter an existing single-column primary key in place
    # (SQLite can't ALTER a primary key at all outside of batch/table-rebuild
    # mode, and there's no real data here worth preserving through that).
    op.drop_table('day_plan_cache')
    op.create_table(
        'day_plan_cache',
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('on', sa.Date(), nullable=False),
        sa.Column('summary', sa.String(), nullable=False),
        sa.Column('blocks', sa.JSON(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.ForeignKeyConstraint(['user_id'], ['user.id']),
        sa.PrimaryKeyConstraint('user_id', 'on'),
    )

    op.drop_table('bot_setting')
    op.create_table(
        'bot_setting',
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('key', sa.String(), nullable=False),
        sa.Column('value', sa.String(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.ForeignKeyConstraint(['user_id'], ['user.id']),
        sa.PrimaryKeyConstraint('user_id', 'key'),
    )

    op.create_table(
        'weekly_goals_cache',
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('week_start', sa.Date(), nullable=False),
        sa.Column('summary', sa.String(), nullable=False),
        sa.Column('suggestions', sa.JSON(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.ForeignKeyConstraint(['user_id'], ['user.id']),
        sa.PrimaryKeyConstraint('user_id', 'week_start'),
    )

    # Real user data tables - add a nullable user_id (see models/task.py's
    # comment for why it's nullable at the DB level) via batch mode so this
    # works the same way on SQLite (which can't ALTER ADD CONSTRAINT outside
    # batch/table-rebuild) and Postgres (where batch mode is just a plain
    # ALTER, no rebuild needed).
    for table in ('task', 'event', 'goal', 'habit', 'note', 'schedule', 'time_entry'):
        with op.batch_alter_table(table) as batch_op:
            batch_op.add_column(sa.Column('user_id', sa.Integer(), nullable=True))
            batch_op.create_foreign_key(f'fk_{table}_user_id_user', 'user', ['user_id'], ['id'])

    # project.name was globally unique - now unique per user instead, so the
    # old single-column unique constraint needs to go, not just gain a
    # sibling. SQLite reflects `unique=True` as an unnamed table constraint;
    # supplying a naming_convention makes batch mode assign it a
    # deterministic name during the rebuild so it can actually be targeted
    # by drop_constraint (dropping an anonymous constraint by name=None
    # isn't supported - "Constraint must have a name"). Postgres, however,
    # never rebuilds the table - batch mode issues a plain ALTER TABLE
    # against the constraint's real (already-named) name, so it needs the
    # name Postgres actually assigned (e.g. "project_name_key"), not the
    # naming_convention's. Look it up via the inspector instead of hardcoding
    # either name.
    inspector = sa.inspect(op.get_bind())
    existing_name = next(
        (uq["name"] for uq in inspector.get_unique_constraints("project")
         if uq["column_names"] == ["name"]),
        None,
    )

    naming_convention = {"uq": "uq_%(table_name)s_%(column_0_name)s"}
    # SQLite reflects the original anonymous UniqueConstraint('name') with
    # name=None - batch mode's own naming_convention (above) is what assigns
    # it a real name during the table-rebuild reflection, so that's the name
    # to drop by in that case. Postgres already has a real name from the
    # inspector (e.g. "project_name_key") and never rebuilds the table, so
    # it must use that actual name instead - the naming_convention's name
    # would never have been assigned to the live Postgres constraint.
    with op.batch_alter_table('project', naming_convention=naming_convention) as batch_op:
        batch_op.add_column(sa.Column('user_id', sa.Integer(), nullable=True))
        batch_op.create_foreign_key('fk_project_user_id_user', 'user', ['user_id'], ['id'])
        batch_op.drop_constraint(existing_name or 'uq_project_name', type_='unique')
        batch_op.create_unique_constraint('uq_project_user_id_name', ['user_id', 'name'])


def downgrade() -> None:
    """Downgrade schema."""
    with op.batch_alter_table('project') as batch_op:
        batch_op.drop_constraint('uq_project_user_id_name', type_='unique')
        batch_op.create_unique_constraint('name', ['name'])
        batch_op.drop_constraint('fk_project_user_id_user', type_='foreignkey')
        batch_op.drop_column('user_id')

    for table in ('time_entry', 'schedule', 'note', 'habit', 'goal', 'event', 'task'):
        with op.batch_alter_table(table) as batch_op:
            batch_op.drop_constraint(f'fk_{table}_user_id_user', type_='foreignkey')
            batch_op.drop_column('user_id')

    op.drop_table('weekly_goals_cache')

    op.drop_table('bot_setting')
    op.create_table(
        'bot_setting',
        sa.Column('key', sa.String(), nullable=False),
        sa.Column('value', sa.String(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.PrimaryKeyConstraint('key'),
    )

    op.drop_table('day_plan_cache')
    op.create_table(
        'day_plan_cache',
        sa.Column('on', sa.Date(), nullable=False),
        sa.Column('summary', sa.String(), nullable=False),
        sa.Column('blocks', sa.JSON(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.PrimaryKeyConstraint('on'),
    )

    op.drop_table('user')
