"""inicial

Revision ID: 973bbe4f16a3
Revises: 
Create Date: 2026-08-23 00:17:50.159617

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '973bbe4f16a3'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('health_entities',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('normalized_name', sa.String(length=255), nullable=False),
    sa.Column('entity_type', sa.String(length=20), nullable=False),
    sa.Column('country', sa.String(length=60), nullable=True),
    sa.Column('city', sa.String(length=120), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_health_entities'))
    )
    with op.batch_alter_table('health_entities', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_health_entities_normalized_name'), ['normalized_name'], unique=False)

    op.create_table('medications',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('generic_name', sa.String(length=255), nullable=False),
    sa.Column('normalized_generic_name', sa.String(length=255), nullable=False),
    sa.Column('brand_name', sa.String(length=255), nullable=True),
    sa.Column('manufacturer_lab', sa.String(length=255), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_medications'))
    )
    with op.batch_alter_table('medications', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_medications_generic_name'), ['generic_name'], unique=False)
        batch_op.create_index(batch_op.f('ix_medications_manufacturer_lab'), ['manufacturer_lab'], unique=False)
        batch_op.create_index(batch_op.f('ix_medications_normalized_generic_name'), ['normalized_generic_name'], unique=False)

    op.create_table('sponsors',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('sponsor_type', sa.String(length=20), nullable=False),
    sa.Column('country', sa.String(length=60), nullable=True),
    sa.Column('contact_name', sa.String(length=255), nullable=True),
    sa.Column('contact_email', sa.String(length=255), nullable=True),
    sa.Column('status', sa.String(length=20), nullable=False),
    sa.Column('notes', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_sponsors'))
    )
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(length=255), nullable=False),
    sa.Column('password_hash', sa.String(length=255), nullable=False),
    sa.Column('role', sa.String(length=20), nullable=False),
    sa.Column('is_active_account', sa.Boolean(), nullable=False),
    sa.Column('accepted_terms_at', sa.DateTime(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('telegram_chat_id', sa.String(length=64), nullable=True),
    sa.Column('telegram_link_code', sa.String(length=12), nullable=True),
    sa.Column('telegram_link_code_expires_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_users'))
    )
    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_users_email'), ['email'], unique=True)
        batch_op.create_index(batch_op.f('ix_users_telegram_chat_id'), ['telegram_chat_id'], unique=True)
        batch_op.create_index(batch_op.f('ix_users_telegram_link_code'), ['telegram_link_code'], unique=False)

    op.create_table('doctors',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('full_name', sa.String(length=255), nullable=False),
    sa.Column('normalized_name', sa.String(length=255), nullable=False),
    sa.Column('license_number', sa.String(length=50), nullable=True),
    sa.Column('specialty', sa.String(length=120), nullable=True),
    sa.Column('health_entity_id', sa.Integer(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['health_entity_id'], ['health_entities.id'], name=op.f('fk_doctors_health_entity_id_health_entities')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_doctors'))
    )
    with op.batch_alter_table('doctors', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_doctors_license_number'), ['license_number'], unique=False)
        batch_op.create_index(batch_op.f('ix_doctors_normalized_name'), ['normalized_name'], unique=False)

    op.create_table('patients',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_by_user_id', sa.Integer(), nullable=False),
    sa.Column('full_name', sa.String(length=255), nullable=False),
    sa.Column('birth_date', sa.Date(), nullable=True),
    sa.Column('national_id', sa.String(length=50), nullable=True),
    sa.Column('phone', sa.String(length=30), nullable=True),
    sa.Column('country', sa.String(length=60), nullable=True),
    sa.Column('city', sa.String(length=120), nullable=True),
    sa.Column('emergency_token', sa.String(length=64), nullable=True),
    sa.Column('emergency_contact_name', sa.String(length=255), nullable=True),
    sa.Column('emergency_contact_phone', sa.String(length=30), nullable=True),
    sa.Column('blood_type', sa.String(length=10), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['users.id'], name=op.f('fk_patients_created_by_user_id_users')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_patients'))
    )
    with op.batch_alter_table('patients', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_patients_emergency_token'), ['emergency_token'], unique=True)
        batch_op.create_index(batch_op.f('ix_patients_phone'), ['phone'], unique=False)

    op.create_table('caregiver_links',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('patient_id', sa.Integer(), nullable=False),
    sa.Column('relationship_type', sa.String(length=30), nullable=False),
    sa.Column('is_primary', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['patient_id'], ['patients.id'], name=op.f('fk_caregiver_links_patient_id_patients')),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], name=op.f('fk_caregiver_links_user_id_users')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_caregiver_links')),
    sa.UniqueConstraint('user_id', 'patient_id', name='uq_caregiver_user_patient')
    )
    with op.batch_alter_table('caregiver_links', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_caregiver_links_patient_id'), ['patient_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_caregiver_links_user_id'), ['user_id'], unique=False)

    op.create_table('documents',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('patient_id', sa.Integer(), nullable=False),
    sa.Column('original_filename', sa.String(length=255), nullable=False),
    sa.Column('storage_path', sa.String(length=500), nullable=False),
    sa.Column('mime_type', sa.String(length=100), nullable=False),
    sa.Column('status', sa.String(length=20), nullable=False),
    sa.Column('error_message', sa.Text(), nullable=True),
    sa.Column('source_channel', sa.String(length=20), nullable=False),
    sa.Column('uploaded_at', sa.DateTime(), nullable=True),
    sa.Column('processed_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['patient_id'], ['patients.id'], name=op.f('fk_documents_patient_id_patients')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_documents'))
    )
    with op.batch_alter_table('documents', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_documents_source_channel'), ['source_channel'], unique=False)
        batch_op.create_index(batch_op.f('ix_documents_status'), ['status'], unique=False)

    op.create_table('summaries',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('patient_id', sa.Integer(), nullable=False),
    sa.Column('content', sa.Text(), nullable=False),
    sa.Column('version', sa.Integer(), nullable=False),
    sa.Column('generated_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['patient_id'], ['patients.id'], name=op.f('fk_summaries_patient_id_patients')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_summaries'))
    )
    op.create_table('intake_events',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('channel', sa.String(length=20), nullable=False),
    sa.Column('status', sa.String(length=30), nullable=False),
    sa.Column('sender_identifier', sa.String(length=255), nullable=True),
    sa.Column('subject', sa.String(length=500), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('patient_id', sa.Integer(), nullable=True),
    sa.Column('document_id', sa.Integer(), nullable=True),
    sa.Column('pending_storage_path', sa.String(length=500), nullable=True),
    sa.Column('pending_original_filename', sa.String(length=255), nullable=True),
    sa.Column('notes', sa.Text(), nullable=True),
    sa.Column('received_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['document_id'], ['documents.id'], name=op.f('fk_intake_events_document_id_documents')),
    sa.ForeignKeyConstraint(['patient_id'], ['patients.id'], name=op.f('fk_intake_events_patient_id_patients')),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], name=op.f('fk_intake_events_user_id_users')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_intake_events'))
    )
    with op.batch_alter_table('intake_events', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_intake_events_channel'), ['channel'], unique=False)
        batch_op.create_index(batch_op.f('ix_intake_events_status'), ['status'], unique=False)
        batch_op.create_index(batch_op.f('ix_intake_events_user_id'), ['user_id'], unique=False)

    op.create_table('medical_events',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('patient_id', sa.Integer(), nullable=False),
    sa.Column('document_id', sa.Integer(), nullable=True),
    sa.Column('event_type', sa.String(length=30), nullable=False),
    sa.Column('description', sa.Text(), nullable=False),
    sa.Column('event_date', sa.Date(), nullable=True),
    sa.Column('structured_data_json', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('condition_category', sa.String(length=60), nullable=True),
    sa.Column('patient_age_at_event', sa.Integer(), nullable=True),
    sa.Column('doctor_id', sa.Integer(), nullable=True),
    sa.Column('health_entity_id', sa.Integer(), nullable=True),
    sa.Column('medication_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['doctor_id'], ['doctors.id'], name=op.f('fk_medical_events_doctor_id_doctors')),
    sa.ForeignKeyConstraint(['document_id'], ['documents.id'], name=op.f('fk_medical_events_document_id_documents')),
    sa.ForeignKeyConstraint(['health_entity_id'], ['health_entities.id'], name=op.f('fk_medical_events_health_entity_id_health_entities')),
    sa.ForeignKeyConstraint(['medication_id'], ['medications.id'], name=op.f('fk_medical_events_medication_id_medications')),
    sa.ForeignKeyConstraint(['patient_id'], ['patients.id'], name=op.f('fk_medical_events_patient_id_patients')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_medical_events'))
    )
    with op.batch_alter_table('medical_events', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_medical_events_condition_category'), ['condition_category'], unique=False)
        batch_op.create_index(batch_op.f('ix_medical_events_doctor_id'), ['doctor_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_medical_events_health_entity_id'), ['health_entity_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_medical_events_medication_id'), ['medication_id'], unique=False)
        batch_op.create_index(batch_op.f('ix_medical_events_patient_age_at_event'), ['patient_age_at_event'], unique=False)

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('medical_events', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_medical_events_patient_age_at_event'))
        batch_op.drop_index(batch_op.f('ix_medical_events_medication_id'))
        batch_op.drop_index(batch_op.f('ix_medical_events_health_entity_id'))
        batch_op.drop_index(batch_op.f('ix_medical_events_doctor_id'))
        batch_op.drop_index(batch_op.f('ix_medical_events_condition_category'))

    op.drop_table('medical_events')
    with op.batch_alter_table('intake_events', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_intake_events_user_id'))
        batch_op.drop_index(batch_op.f('ix_intake_events_status'))
        batch_op.drop_index(batch_op.f('ix_intake_events_channel'))

    op.drop_table('intake_events')
    op.drop_table('summaries')
    with op.batch_alter_table('documents', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_documents_status'))
        batch_op.drop_index(batch_op.f('ix_documents_source_channel'))

    op.drop_table('documents')
    with op.batch_alter_table('caregiver_links', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_caregiver_links_user_id'))
        batch_op.drop_index(batch_op.f('ix_caregiver_links_patient_id'))

    op.drop_table('caregiver_links')
    with op.batch_alter_table('patients', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_patients_phone'))
        batch_op.drop_index(batch_op.f('ix_patients_emergency_token'))

    op.drop_table('patients')
    with op.batch_alter_table('doctors', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_doctors_normalized_name'))
        batch_op.drop_index(batch_op.f('ix_doctors_license_number'))

    op.drop_table('doctors')
    with op.batch_alter_table('users', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_users_telegram_link_code'))
        batch_op.drop_index(batch_op.f('ix_users_telegram_chat_id'))
        batch_op.drop_index(batch_op.f('ix_users_email'))

    op.drop_table('users')
    op.drop_table('sponsors')
    with op.batch_alter_table('medications', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_medications_normalized_generic_name'))
        batch_op.drop_index(batch_op.f('ix_medications_manufacturer_lab'))
        batch_op.drop_index(batch_op.f('ix_medications_generic_name'))

    op.drop_table('medications')
    with op.batch_alter_table('health_entities', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_health_entities_normalized_name'))

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