import pyodbc

hil_sql = """
    create table dda_records
    (
        id bigint identity
            primary key,
        application_id nvarchar(64) not null,
        is_dda_found bit default 0 not null,
        is_id_found bit default 0 not null,
        is_bc_found bit default 0 not null,
        all_found bit default 0 not null,
        dda_path nvarchar(1024),
        id_path nvarchar(1024),
        bc_path nvarchar(1024),
        customer_name nvarchar(1024),
        customer_id nvarchar(1024),
        account_id nvarchar(1024),
        dda_found_time datetime,
        id_found_time datetime,
        bc_found_time datetime,
        update_time datetime not null,
        create_time datetime not null,
    );

    create index dda_records_application_id_index
        on dda_records (application_id);
    
    create index dda_records_update_time_index
        on dda_records (update_time);
    
    create index dda_records_create_time_index
        on dda_records (create_time);

    create table idbc_records
    (
        id bigint identity
            primary key,
        application_id nvarchar(64) not null,
        target_name nvarchar(1024),
        target_id nvarchar(1024) not null,
        is_id bit default 1 not null,
        file_path nvarchar(1024) not null,
        create_time datetime not null,
    );

    create index idbc_records_application_id_index
        on idbc_records (application_id);
    
    create index idbc_records_application_id_target_name_index
        on idbc_records (application_id, target_name);
    
    create index idbc_records_application_id_target_id_index
        on idbc_records (application_id, target_id);
"""

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()