"""add public.member_schedules and organizations away-mode columns

Public-schema migration. Adds:
  - member_schedules: weekly working-hours windows per team member (Max
    feature), FK'd to public.users. Multiple rows per (user_id, weekday)
    allow split-day windows.
  - organizations.away_mode_enabled / away_until_date: org-level
    out-of-office auto-response toggle.

Revision ID: ar001
Revises: docp02
Create Date: 2026-07-14
"""
from alembic import op
import sqlalchemy as sa

revision = 'ar001'
down_revision = 'docp02'
branch_labels = None
depends_on = None


def _public_table_exists(conn, table):
    return conn.execute(sa.text(
        "SELECT 1 FROM information_schema.tables "
        "WHERE table_schema='public' AND table_name=:t"
    ), {"t": table}).fetchone() is not None


def _public_column_exists(conn, table, column):
    return conn.execute(sa.text(
        "SELECT 1 FROM information_schema.columns "
        "WHERE table_schema='public' AND table_name=:t AND column_name=:c"
    ), {"t": table, "c": column}).fetchone() is not None


def upgrade():
    conn = op.get_bind()

    if not _public_table_exists(conn, 'member_schedules'):
        op.create_table(
            'member_schedules',
            sa.Column('id', sa.Integer(), primary_key=True, autoincrement=True),
            sa.Column('user_id', sa.Integer(), nullable=False),
            sa.Column('weekday', sa.SmallInteger(), nullable=False),
            sa.Column('start_time', sa.Time(), nullable=False),
            sa.Column('end_time', sa.Time(), nullable=False),
            sa.Column('created_at', sa.DateTime(), nullable=False,
                      server_default=sa.text('CURRENT_TIMESTAMP')),
            sa.ForeignKeyConstraint(['user_id'], ['public.users.id'], ondelete='CASCADE'),
            schema='public',
        )
        op.create_index(
            'ix_member_schedules_user_id', 'member_schedules', ['user_id'],
            schema='public',
        )
        op.create_index(
            'ix_member_schedules_user_weekday', 'member_schedules', ['user_id', 'weekday'],
            schema='public',
        )

    if not _public_column_exists(conn, 'organizations', 'away_mode_enabled'):
        op.add_column(
            'organizations',
            sa.Column('away_mode_enabled', sa.Boolean(), nullable=False, server_default='false'),
            schema='public',
        )

    if not _public_column_exists(conn, 'organizations', 'away_until_date'):
        op.add_column(
            'organizations',
            sa.Column('away_until_date', sa.Date(), nullable=True),
            schema='public',
        )


def downgrade():
    op.drop_index('ix_member_schedules_user_weekday', table_name='member_schedules', schema='public')
    op.drop_index('ix_member_schedules_user_id', table_name='member_schedules', schema='public')
    op.drop_table('member_schedules', schema='public')
    op.drop_column('organizations', 'away_until_date', schema='public')
    op.drop_column('organizations', 'away_mode_enabled', schema='public')