import pyodbc

hil_sql = """
    create table hil_ocr_report
    (
        id bigint identity
            primary key,
        case_number nvarchar(64) not null,
        request_team tinyint default 0 not null,
        request_trigger tinyint default 0 not null,
        input_file nvarchar(255) not null,
        transaction_start datetime not null,
        transaction_end datetime not null,
        successful_at_this_level bit default 0 not null,
        failure_reason tinyint,
        process_name tinyint default 0 not null,
        total_fields smallint,
        workflow_name tinyint
    );
    
    create index hil_ocr_report_transaction_start_index
        on hil_ocr_report (transaction_start);
        
    create table hil_compare_report
    (
        id bigint identity
            primary key,
        case_number nvarchar(64) not null,
        request_team tinyint default 0 not null,
        request_trigger tinyint default 4 not null,
        input_file nvarchar(255),
        transaction_start datetime not null,
        transaction_end datetime not null,
        successful_at_this_level bit default 0 not null,
        failure_reason nvarchar(max),
        process_name tinyint default 4 not null,
        total_fields smallint,
        workflow_name nvarchar(255)
    );
    
    create index hil_compare_report_transaction_start_index
        on hil_compare_report (transaction_start);
        
    create table hil_compare_offline_report
    (
        id bigint identity
            primary key,
        case_number nvarchar(255) not null,
        request_team nvarchar(255) not null,
        request_trigger nvarchar(1024) not null,
        input_file nvarchar(2048) not null,
        transaction_start datetime not null,
        transaction_end datetime not null,
        successful_at_this_level bit default 0 not null,
        failure_reason nvarchar(max),
        process_name nvarchar(1024) not null,
        total_fields smallint,
        workflow_name nvarchar(1024) not null,
    );
    
    create index hil_compare_offline_report_transaction_start_index
        on hil_compare_offline_report (transaction_start);
"""

afc_sql = """
    create table afc_ocr_report
    (
        id bigint identity
            primary key,
        case_number nvarchar(64) not null,
        request_team tinyint default 0 not null,
        request_trigger tinyint default 0 not null,
        input_file nvarchar(255) not null,
        transaction_start datetime not null,
        transaction_end datetime not null,
        successful_at_this_level bit default 0 not null,
        failure_reason tinyint,
        process_name tinyint default 0 not null,
        total_fields smallint,
        workflow_name tinyint
    );
    
    create index afc_ocr_report_transaction_start_index
        on afc_ocr_report (transaction_start);
        
    create table afc_compare_report
    (
        id bigint identity
            primary key,
        case_number nvarchar(64) not null,
        request_team tinyint default 0 not null,
        request_trigger tinyint default 4 not null,
        input_file nvarchar(255),
        transaction_start datetime not null,
        transaction_end datetime not null,
        successful_at_this_level bit default 0 not null,
        failure_reason nvarchar(max),
        process_name tinyint default 4 not null,
        total_fields smallint,
        workflow_name nvarchar(255)
    );
    
    create index afc_compare_report_transaction_start_index
        on afc_compare_report (transaction_start);
        
    create table afc_compare_offline_report
    (
        id bigint identity
            primary key,
        case_number nvarchar(255) not null,
        request_team nvarchar(255) not null,
        request_trigger nvarchar(1024) not null,
        input_file nvarchar(2048) not null,
        transaction_start datetime not null,
        transaction_end datetime not null,
        successful_at_this_level bit default 0 not null,
        failure_reason nvarchar(max),
        process_name nvarchar(1024) not null,
        total_fields smallint,
        workflow_name nvarchar(1024) not null,
    );
    
    create index afc_compare_offline_report_transaction_start_index
        on afc_compare_offline_report (transaction_start);
"""

hil_cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};', autocommit=True)

hil_cursor = hil_cnxn.cursor()
hil_cursor.execute(hil_sql)

hil_cursor.close()
hil_cnxn.close()

afc_cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};', autocommit=True)

afc_cursor = afc_cnxn.cursor()
afc_cursor.execute(afc_sql)

afc_cursor.close()
afc_cnxn.close()