"""first migration

Revision ID: 1c4e0a7d9612
Revises: 
Create Date: 2026-09-09 18:21:01.185344

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '1c4e0a7d9612'
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('chart_of_account',
    sa.Column('code', sa.String(), nullable=False),
    sa.Column('parent_id', sa.UUID(), nullable=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('kind', sa.Enum('A', 'P', 'AP', name='accountkind', native_enum=False), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['parent_id'], ['chart_of_account.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code'),
    
    )
    op.create_table('regional_office',
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_table('company_group',
    sa.Column('crm_id', sa.String(), nullable=False, comment='Натуральный ключ'),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('base_regional_office_id', sa.UUID(), nullable=False),
    sa.Column('valid_from', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_by', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['base_regional_office_id'], ['regional_office.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('crm_id'),
    
    )
    op.create_table('company_group_history',
    sa.Column('company_group_id', sa.UUID(), nullable=False),
    sa.Column('crm_id', sa.String(), nullable=False, comment='Натуральный ключ'),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('base_regional_office_id', sa.UUID(), nullable=False),
    sa.Column('valid_from', sa.DateTime(timezone=True), nullable=False),
    sa.Column('valid_to', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_by', sa.String(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['company_group_id'], ['company_group.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('crm_id'),
    
    )
    op.create_table('legal_entity',
    sa.Column('inn', sa.String(), nullable=False),
    sa.Column('entity_type', sa.Enum('IP', 'UL', name='legalentitytype', native_enum=False), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('company_group_id', sa.UUID(), nullable=False),
    sa.Column('regional_office_id', sa.UUID(), nullable=False),
    sa.Column('close_date', sa.Date(), nullable=False),
    sa.Column('updated_by', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['company_group_id'], ['company_group.id'], ),
    sa.ForeignKeyConstraint(['regional_office_id'], ['regional_office.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('inn'),
    
    )
    op.create_table('related_group',
    sa.Column('code', sa.String(), nullable=False),
    sa.Column('company_group_id', sa.UUID(), nullable=False),
    sa.Column('kind', sa.Enum('GSK', 'GSZ', name='relatedgroupkind', native_enum=False), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('created_date', sa.Date(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['company_group_id'], ['company_group.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code'),
    
    )
    op.create_index('unique_id_kind_idx', 'related_group', ['id', 'kind'], unique=True, )
    op.create_table('consolidation',
    sa.Column('related_group_id', sa.UUID(), nullable=False),
    sa.Column('year', sa.SmallInteger(), nullable=False),
    sa.Column('quarter', sa.Enum('Q1', 'Q2', 'Q3', 'Q4', name='periodquarter', native_enum=False), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['related_group_id'], ['related_group.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_index('unique_period_group_idx', 'consolidation', ['related_group_id', 'year', 'quarter'], unique=True, )
    op.create_table('legal_entity_alias',
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('alias', sa.String(), nullable=False),
    sa.Column('normalized_alias', sa.String(), nullable=False),
    sa.Column('created_by', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_table('legal_entity_history',
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('inn', sa.String(), nullable=False),
    sa.Column('entity_type', sa.Enum('IP', 'UL', name='legalentitytype', native_enum=False), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('company_group_id', sa.UUID(), nullable=False),
    sa.Column('regional_office_id', sa.UUID(), nullable=False),
    sa.Column('close_date', sa.Date(), nullable=False),
    sa.Column('valid_from', sa.DateTime(timezone=True), nullable=False),
    sa.Column('valid_to', sa.DateTime(timezone=True), nullable=False),
    sa.Column('changed_by', sa.String(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['company_group_id'], ['company_group.id'], ),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.ForeignKeyConstraint(['regional_office_id'], ['regional_office.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('inn'),
    
    )
    op.create_index('unique_leid_validfrom_idx', 'legal_entity_history', ['legal_entity_id', 'valid_from'], unique=True, )
    op.create_table('recognition_job',
    sa.Column('file_id', sa.UUID(), nullable=False),
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('pipeline', sa.Enum('TABULAR', 'IMAGE', name='recognitionpipeline', native_enum=False), nullable=True),
    sa.Column('service_version', sa.String(), nullable=True),
    sa.Column('status', sa.Enum('QUEUED', 'PROCESSING', 'DONE', 'ERROR', name='recognitionstatus', native_enum=False), nullable=True),
    sa.Column('attempt', sa.SmallInteger(), nullable=False),
    sa.Column('queue_msg_id', sa.String(), nullable=True),
    sa.Column('requested_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('started_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('error_message', sa.Text(), nullable=True),
    sa.Column('raw_result_s3_key', sa.String(), nullable=True),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['file_id'], ['stored_file.id'], use_alter=True),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_table('related_group_member',
    sa.Column('related_group_id', sa.UUID(), nullable=False),
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('kind', sa.Enum('GSK', 'GSZ', name='relatedgroupkind', native_enum=False), nullable=False),
    sa.Column('valid_from', sa.DateTime(timezone=True), nullable=False),
    sa.Column('valid_to', sa.DateTime(timezone=True), nullable=False),
    sa.Column('is_current', sa.Boolean(), nullable=False),
    sa.Column('created_by', sa.String(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.ForeignKeyConstraint(['related_group_id'], ['related_group.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_index('leid_partial_unique_idx', 'related_group_member', ['legal_entity_id'], unique=True, postgresql_where=sa.text("is_current IS true OR kind = 'GSK'"))
    op.create_index('relgroup_legent_partial_unique_idx', 'related_group_member', ['related_group_id', 'legal_entity_id'], unique=True, postgresql_where=sa.text('is_current = true'))
    op.create_table('stored_file',
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('document_type', sa.Enum('BUH_OT', 'OSV', 'OFR', 'OTHER', name='documenttype', native_enum=False), nullable=True),
    sa.Column('file_type', sa.Enum('XLSX', 'XLS', 'PDF', 'PNG', 'JPG', name='filetype', native_enum=False), nullable=False),
    sa.Column('original_name', sa.String(), nullable=False),
    sa.Column('s3_key', sa.String(), nullable=True),
    sa.Column('content_hash', sa.String(), nullable=True),
    sa.Column('size_bytes', sa.BigInteger(), nullable=False),
    sa.Column('period_year', sa.SmallInteger(), nullable=False),
    sa.Column('period_quarter', sa.Enum('Q1', 'Q2', 'Q3', 'Q4', name='periodquarter', native_enum=False), nullable=True),
    sa.Column('status', sa.Enum('UPLOADED', 'PROCESSING', 'CHECK', 'ERROR', name='filestatus', native_enum=False), nullable=True),
    sa.Column('current_recognition_job_id', sa.UUID(), nullable=True),
    sa.Column('uploaded_by', sa.String(), nullable=True),
    sa.Column('uploaded_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['current_recognition_job_id'], ['recognition_job.id'], use_alter=True),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('s3_key'),
    
    )
    op.create_table('consolidation_version',
    sa.Column('consolidation_id', sa.UUID(), nullable=False),
    sa.Column('version_no', sa.String(), nullable=False),
    sa.Column('consolidation_date', sa.Date(), nullable=False),
    sa.Column('initiator', sa.String(), nullable=False),
    sa.Column('approved_in_rf', sa.String(), nullable=False),
    sa.Column('verified_in_go', sa.String(), nullable=False),
    sa.Column('kfo_confirmation_enabled', sa.Boolean(), nullable=False),
    sa.Column('is_in_work', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('created_by', sa.String(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['consolidation_id'], ['consolidation.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_index('unique_version_consolidation_idx', 'consolidation_version', ['consolidation_id', 'version_no'], unique=True, )
    op.create_table('financial_statement_meta',
    sa.Column('stored_file_id', sa.UUID(), nullable=False),
    sa.Column('statement_type', sa.Enum('BALANCE', 'INCOME', name='statementtype', native_enum=False), nullable=False),
    sa.Column('inn', sa.String(), nullable=False),
    sa.Column('inn_verified', sa.Boolean(), nullable=False),
    sa.Column('report_date', sa.Date(), nullable=False),
    sa.Column('report_date_verified', sa.Boolean(), nullable=False),
    sa.Column('units', sa.String(), nullable=False),
    sa.Column('units_verified', sa.Boolean(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['stored_file_id'], ['stored_file.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_table('osv_entry',
    sa.Column('recognition_job_id', sa.UUID(), nullable=False),
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('stored_file_id', sa.UUID(), nullable=False),
    sa.Column('account_code', sa.String(), nullable=False),
    sa.Column('account_id', sa.UUID(), nullable=False),
    sa.Column('account_root', sa.String(), nullable=False),
    sa.Column('counterparty_name', sa.String(), nullable=False),
    sa.Column('counterparty_inn', sa.String(), nullable=False),
    sa.Column('counterparty_id', sa.UUID(), nullable=True),
    sa.Column('opening_debit', sa.Numeric(), nullable=False),
    sa.Column('opening_credit', sa.Numeric(), nullable=False),
    sa.Column('turnover_debit', sa.Numeric(), nullable=False),
    sa.Column('turnover_credit', sa.Numeric(), nullable=False),
    sa.Column('closing_debit', sa.Numeric(), nullable=False),
    sa.Column('closing_credit', sa.Numeric(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['account_id'], ['chart_of_account.id'], ),
    sa.ForeignKeyConstraint(['counterparty_id'], ['legal_entity.id'], ),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.ForeignKeyConstraint(['recognition_job_id'], ['recognition_job.id'], ),
    sa.ForeignKeyConstraint(['stored_file_id'], ['stored_file.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_table('consolidation_member',
    sa.Column('consolidation_version_id', sa.UUID(), nullable=False),
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['consolidation_version_id'], ['consolidation_version.id'], ),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_index('unique_legal_entity_consolidation_idx', 'consolidation_member', ['consolidation_version_id', 'legal_entity_id'], unique=True, )
    op.create_table('correction',
    sa.Column('consolidation_version_id', sa.UUID(), nullable=False),
    sa.Column('correction_id', sa.UUID(), nullable=True),
    sa.Column('legal_entity_id', sa.UUID(), nullable=True),
    sa.Column('debit_line_code', sa.String(), nullable=False),
    sa.Column('debit_account_code', sa.String(), nullable=False),
    sa.Column('debit_adjustment', sa.Numeric(), nullable=False),
    sa.Column('credit_line_code', sa.String(), nullable=False),
    sa.Column('credit_account_code', sa.String(), nullable=False),
    sa.Column('credit_adjustment', sa.Numeric(), nullable=False),
    sa.Column('deviation_amount', sa.Numeric(), nullable=False),
    sa.Column('deviation_percent', sa.Numeric(), nullable=False),
    sa.Column('comment', sa.Text(), nullable=False),
    sa.Column('created_by', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('uploaded_by', sa.String(), nullable=False),
    sa.Column('uploaded_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['consolidation_version_id'], ['consolidation_version.id'], ),
    sa.ForeignKeyConstraint(['correction_id'], ['correction.id'], ),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_index('consolidation_id_correction_idx', 'correction', ['consolidation_version_id'], unique=False, )
    op.create_table('financial_statement_line',
    sa.Column('recognition_job_id', sa.UUID(), nullable=False),
    sa.Column('meta_id', sa.UUID(), nullable=True),
    sa.Column('legal_entity_id', sa.UUID(), nullable=False),
    sa.Column('line_code', sa.String(), nullable=False),
    sa.Column('line_code_recognized', sa.Boolean(), nullable=False),
    sa.Column('line_name', sa.String(), nullable=False),
    sa.Column('line_name_recognized', sa.Boolean(), nullable=False),
    sa.Column('row_kind', sa.Enum('SECTION', 'ROW', 'SUB', 'TOTAL', 'BALANCE', 'HEADER', name='fslrowkind', native_enum=False), nullable=False),
    sa.Column('ordinal', sa.BigInteger(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['legal_entity_id'], ['legal_entity.id'], ),
    sa.ForeignKeyConstraint(['meta_id'], ['financial_statement_meta.id'], ),
    sa.ForeignKeyConstraint(['recognition_job_id'], ['recognition_job.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    op.create_table('financial_statement_value',
    sa.Column('line_id', sa.UUID(), nullable=False),
    sa.Column('amount', sa.Numeric(), nullable=True),
    sa.Column('string_value', sa.String(), nullable=True),
    sa.Column('column_index', sa.SmallInteger(), nullable=False),
    sa.Column('is_recognized', sa.Boolean(), nullable=False),
    sa.Column('id', sa.UUID(), nullable=False),
    sa.ForeignKeyConstraint(['line_id'], ['financial_statement_line.id'], ),
    sa.PrimaryKeyConstraint('id'),
    
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('financial_statement_value', )
    op.drop_table('financial_statement_line', )
    op.drop_index('consolidation_id_correction_idx', table_name='correction', )
    op.drop_table('correction', )
    op.drop_index('unique_legal_entity_consolidation_idx', table_name='consolidation_member', )
    op.drop_table('consolidation_member', )
    op.drop_table('osv_entry', )
    op.drop_table('financial_statement_meta', )
    op.drop_index('unique_version_consolidation_idx', table_name='consolidation_version', )
    op.drop_table('consolidation_version', )
    op.drop_table('stored_file', )
    op.drop_index('relgroup_legent_partial_unique_idx', table_name='related_group_member', postgresql_where=sa.text('is_current = true'))
    op.drop_index('leid_partial_unique_idx', table_name='related_group_member', postgresql_where=sa.text("is_current IS true OR kind = 'GSK'"))
    op.drop_table('related_group_member', )
    op.drop_table('recognition_job', )
    op.drop_index('unique_leid_validfrom_idx', table_name='legal_entity_history', )
    op.drop_table('legal_entity_history', )
    op.drop_table('legal_entity_alias', )
    op.drop_index('unique_period_group_idx', table_name='consolidation', )
    op.drop_table('consolidation', )
    op.drop_index('unique_id_kind_idx', table_name='related_group', )
    op.drop_table('related_group', )
    op.drop_table('legal_entity', )
    op.drop_table('company_group_history', )
    op.drop_table('company_group', )
    op.drop_table('regional_office', )
    op.drop_table('chart_of_account', )
    # ### end Alembic commands ###