"""0001 baseline common

Revision ID: 940fb75ddce6
Revises: 
Create Date: 2026-03-15 19:52:18.618166

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = '940fb75ddce6'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('login_attempts',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('key', sa.String(length=255), nullable=False),
    sa.Column('attempts', sa.Integer(), nullable=False),
    sa.Column('locked_until_utc', sa.DateTime(), nullable=True),
    sa.Column('updated_at_utc', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('key', name='uq_login_attempt_key')
    )
    with op.batch_alter_table('login_attempts', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_login_attempts_key'), ['key'], unique=False)

    op.create_table('permissions',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('code', sa.String(length=150), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code', name='uq_permission_code')
    )
    with op.batch_alter_table('permissions', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_permissions_code'), ['code'], unique=False)

    op.create_table('roles',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name', name='uq_role_name')
    )
    with op.batch_alter_table('roles', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_roles_name'), ['name'], unique=False)

    op.create_table('tenants',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('code', sa.String(length=50), nullable=False),
    sa.Column('name', sa.String(length=200), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('default_timezone', sa.String(length=64), nullable=False),
    sa.Column('default_session_duration_minutes', sa.Integer(), nullable=False),
    sa.Column('default_otp_required_roles_csv', sa.String(length=200), nullable=False),
    sa.Column('default_storage_mode', sa.String(length=20), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('tenants', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_tenants_code'), ['code'], unique=True)

    op.create_table('branches',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('tenant_id', sa.Integer(), nullable=False),
    sa.Column('code', sa.String(length=50), nullable=False),
    sa.Column('name', sa.String(length=200), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('timezone', sa.String(length=64), nullable=False),
    sa.Column('office_start_time', sa.Time(), nullable=True),
    sa.Column('office_end_time', sa.Time(), nullable=True),
    sa.Column('allow_login', sa.Boolean(), nullable=False),
    sa.Column('allow_new_assignments', sa.Boolean(), nullable=False),
    sa.Column('is_head_office', sa.Boolean(), nullable=False),
    sa.Column('smtp_host', sa.String(length=255), nullable=True),
    sa.Column('smtp_port', sa.Integer(), nullable=True),
    sa.Column('smtp_username', sa.String(length=255), nullable=True),
    sa.Column('smtp_password', sa.String(length=255), nullable=True),
    sa.Column('smtp_use_tls', sa.Boolean(), nullable=False),
    sa.Column('local_storage_path', sa.String(length=500), nullable=True),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('tenant_id', 'code', name='uq_branch_tenant_code')
    )
    with op.batch_alter_table('branches', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_branches_code'), ['code'], unique=False)
        batch_op.create_index(batch_op.f('ix_branches_tenant_id'), ['tenant_id'], unique=False)

    op.create_table('role_permissions',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('role_id', sa.Integer(), nullable=False),
    sa.Column('permission_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['permission_id'], ['permissions.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['role_id'], ['roles.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('role_id', 'permission_id', name='uq_role_perm')
    )
    with op.batch_alter_table('role_permissions', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_role_permissions_permission_id'), ['permission_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_role_permissions_role_id'), ['role_id'], unique=False)

    op.create_table('branch_settings',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('branch_id', sa.Integer(), nullable=False),
    sa.Column('address_line1', sa.String(length=255), nullable=True),
    sa.Column('address_line2', sa.String(length=255), nullable=True),
    sa.Column('city', sa.String(length=100), nullable=True),
    sa.Column('state', sa.String(length=100), nullable=True),
    sa.Column('pin_code', sa.String(length=10), nullable=True),
    sa.Column('gstin', sa.String(length=20), nullable=True),
    sa.Column('pan', sa.String(length=10), nullable=True),
    sa.Column('letterhead_logo_path', sa.String(length=500), nullable=True),
    sa.Column('letterhead_signature_path', sa.String(length=500), nullable=True),
    sa.Column('letterhead_stamp_path', sa.String(length=500), nullable=True),
    sa.Column('geo_address', sa.String(length=500), nullable=True),
    sa.Column('latitude', sa.Float(), nullable=True),
    sa.Column('longitude', sa.Float(), nullable=True),
    sa.Column('working_days_csv', sa.String(length=50), nullable=False),
    sa.Column('holidays_json', sa.Text(), nullable=False),
    sa.Column('timezone_locked', sa.Boolean(), nullable=False),
    sa.Column('email_from_name', sa.String(length=200), nullable=True),
    sa.Column('email_from_email', sa.String(length=255), nullable=True),
    sa.Column('email_reply_to', sa.String(length=255), nullable=True),
    sa.Column('default_cc_csv', sa.String(length=500), nullable=True),
    sa.Column('default_bcc_csv', sa.String(length=500), nullable=True),
    sa.Column('email_signature_html', sa.Text(), nullable=True),
    sa.Column('storage_mode', sa.String(length=20), nullable=False),
    sa.Column('folder_template', sa.String(length=500), nullable=False),
    sa.Column('max_file_mb', sa.Integer(), nullable=False),
    sa.Column('allowed_ext_csv', sa.String(length=500), nullable=False),
    sa.Column('retention_years', sa.Integer(), nullable=False),
    sa.Column('otp_required_roles_csv', sa.String(length=200), nullable=False),
    sa.Column('session_duration_minutes', sa.Integer(), nullable=False),
    sa.Column('lockout_attempts', sa.Integer(), nullable=False),
    sa.Column('lockout_minutes', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['branch_id'], ['branches.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('branch_id', name='uq_branch_settings_branch')
    )
    with op.batch_alter_table('branch_settings', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_branch_settings_branch_id'), ['branch_id'], unique=False)

    op.create_table('users',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('email', sa.String(length=255), nullable=False),
    sa.Column('full_name', sa.String(length=255), nullable=False),
    sa.Column('password_hash', sa.String(length=255), nullable=False),
    sa.Column('tenant_id', sa.Integer(), nullable=False),
    sa.Column('branch_id', sa.Integer(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('allow_login', sa.Boolean(), nullable=False),
    sa.Column('is_locked', sa.Boolean(), nullable=False),
    sa.Column('locked_at_utc', sa.DateTime(), nullable=True),
    sa.Column('deleted_at', sa.DateTime(), nullable=True),
    sa.Column('must_change_password', sa.Boolean(), nullable=False),
    sa.Column('password_changed_at_utc', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['branch_id'], ['branches.id'], ),
    sa.ForeignKeyConstraint(['tenant_id'], ['tenants.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email', name='uq_user_email')
    )
    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_users_branch_id'), ['branch_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_users_email'), ['email'], unique=False)
        batch_op.create_index(batch_op.f('ix_users_tenant_id'), ['tenant_id'], unique=False)

    op.create_table('refresh_tokens',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('token_hash', sa.String(length=64), nullable=False),
    sa.Column('created_at_utc', sa.DateTime(), nullable=False),
    sa.Column('expires_at_utc', sa.DateTime(), nullable=False),
    sa.Column('revoked', sa.Boolean(), nullable=False),
    sa.Column('rotated_from_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('token_hash', name='uq_refresh_token_hash')
    )
    with op.batch_alter_table('refresh_tokens', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_refresh_tokens_token_hash'), ['token_hash'], unique=False)
        batch_op.create_index(batch_op.f('ix_refresh_tokens_user_id'), ['user_id'], unique=False)

    op.create_table('user_roles',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('role_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['role_id'], ['roles.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'role_id', name='uq_user_role')
    )
    with op.batch_alter_table('user_roles', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_user_roles_role_id'), ['role_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_user_roles_user_id'), ['user_id'], unique=False)

    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user_roles', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_user_roles_user_id'))
        batch_op.drop_index(batch_op.f('ix_user_roles_role_id'))

    op.drop_table('user_roles')
    with op.batch_alter_table('refresh_tokens', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_refresh_tokens_user_id'))
        batch_op.drop_index(batch_op.f('ix_refresh_tokens_token_hash'))

    op.drop_table('refresh_tokens')
    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_users_tenant_id'))
        batch_op.drop_index(batch_op.f('ix_users_email'))
        batch_op.drop_index(batch_op.f('ix_users_branch_id'))

    op.drop_table('users')
    with op.batch_alter_table('branch_settings', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_branch_settings_branch_id'))

    op.drop_table('branch_settings')
    with op.batch_alter_table('role_permissions', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_role_permissions_role_id'))
        batch_op.drop_index(batch_op.f('ix_role_permissions_permission_id'))

    op.drop_table('role_permissions')
    with op.batch_alter_table('branches', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_branches_tenant_id'))
        batch_op.drop_index(batch_op.f('ix_branches_code'))

    op.drop_table('branches')
    with op.batch_alter_table('tenants', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_tenants_code'))

    op.drop_table('tenants')
    with op.batch_alter_table('roles', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_roles_name'))

    op.drop_table('roles')
    with op.batch_alter_table('permissions', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_permissions_code'))

    op.drop_table('permissions')
    with op.batch_alter_table('login_attempts', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_login_attempts_key'))

    op.drop_table('login_attempts')
    # ### end Alembic commands ###
