import pyodbc

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

cursor = cnxn.cursor()
cursor.execute("create database afc")
cursor.execute("create database hil")

cursor.close()
cnxn.close()

hil_sql_1 = """
    create table auth_group
    (
        id   int identity
            primary key,
        name nvarchar(150) not null
            constraint auth_group_name_a6ea08ec_uniq
                unique
    );
    
    create table auth_user
    (
        id           int identity
            primary key,
        password     nvarchar(128) not null,
        last_login   datetime2,
        is_superuser bit           not null,
        username     nvarchar(150) not null
            constraint auth_user_username_6821ab7c_uniq
                unique,
        first_name   nvarchar(30)  not null,
        last_name    nvarchar(150) not null,
        email        nvarchar(254) not null,
        is_staff     bit           not null,
        is_active    bit           not null,
        date_joined  datetime2     not null
    );
    
    create table auth_user_groups
    (
        id       int identity
            primary key,
        user_id  int not null
            constraint auth_user_groups_user_id_6a12ed8b_fk_auth_user_id
                references auth_user,
        group_id int not null
            constraint auth_user_groups_group_id_97559544_fk_auth_group_id
                references auth_group
    );
    
    create unique index auth_user_groups_user_id_group_id_94350c0c_uniq
        on auth_user_groups (user_id, group_id)
        where [user_id] IS NOT NULL AND [group_id] IS NOT NULL;
    
    create index auth_user_groups_group_id_97559544
        on auth_user_groups (group_id);
    
    create index auth_user_groups_user_id_6a12ed8b
        on auth_user_groups (user_id);
        
    create table django_content_type
    (
        id        int identity
            primary key,
        app_label nvarchar(100) not null,
        model     nvarchar(100) not null
    );
    
    create table auth_permission
    (
        id              int identity
            primary key,
        name            nvarchar(255) not null,
        content_type_id int           not null
            constraint auth_permission_content_type_id_2f476e4b_fk_django_content_type_id
                references django_content_type,
        codename        nvarchar(100) not null
    );
    
    create table auth_group_permissions
    (
        id            int identity
            primary key,
        group_id      int not null
            constraint auth_group_permissions_group_id_b120cbf9_fk_auth_group_id
                references auth_group,
        permission_id int not null
            constraint auth_group_permissions_permission_id_84c5c92e_fk_auth_permission_id
                references auth_permission
    );
    
    create index auth_group_permissions_permission_id_84c5c92e
        on auth_group_permissions (permission_id);
    
    create index auth_group_permissions_group_id_b120cbf9
        on auth_group_permissions (group_id);
    
    create unique index auth_group_permissions_group_id_permission_id_0cd325b0_uniq
        on auth_group_permissions (group_id, permission_id)
        where [group_id] IS NOT NULL AND [permission_id] IS NOT NULL;
    
    create index auth_permission_content_type_id_2f476e4b
        on auth_permission (content_type_id);
    
    create unique index auth_permission_content_type_id_codename_01ab375a_uniq
        on auth_permission (content_type_id, codename)
        where [content_type_id] IS NOT NULL AND [codename] IS NOT NULL;
        
    create table auth_user_user_permissions
    (
        id            int identity
            primary key,
        user_id       int not null
            constraint auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id
                references auth_user,
        permission_id int not null
            constraint auth_user_user_permissions_permission_id_1fbb5f2c_fk_auth_permission_id
                references auth_permission
    );
    
    create index auth_user_user_permissions_permission_id_1fbb5f2c
        on auth_user_user_permissions (permission_id);
    
    create index auth_user_user_permissions_user_id_a95ead1b
        on auth_user_user_permissions (user_id);
    
    create unique index auth_user_user_permissions_user_id_permission_id_14a6b632_uniq
        on auth_user_user_permissions (user_id, permission_id)
        where [user_id] IS NOT NULL AND [permission_id] IS NOT NULL;
        
    create table django_admin_log
    (
        id              int identity
            primary key,
        action_time     datetime2     not null,
        object_id       nvarchar(max),
        object_repr     nvarchar(200) not null,
        action_flag     smallint      not null
            constraint django_admin_log_action_flag_a8637d59_check
                check ([action_flag] >= 0),
        change_message  nvarchar(max) not null,
        content_type_id int
            constraint django_admin_log_content_type_id_c4bce8eb_fk_django_content_type_id
                references django_content_type,
        user_id         int           not null
            constraint django_admin_log_user_id_c564eba6_fk_auth_user_id
                references auth_user
    );
    
    create index django_admin_log_content_type_id_c4bce8eb
        on django_admin_log (content_type_id);
    
    create index django_admin_log_user_id_c564eba6
        on django_admin_log (user_id);
    
    create unique index django_content_type_app_label_model_76bd3d3b_uniq
        on django_content_type (app_label, model)
        where [app_label] IS NOT NULL AND [model] IS NOT NULL;
        
    create table django_migrations
    (
        id      int identity
            primary key,
        app     nvarchar(255) not null,
        name    nvarchar(255) not null,
        applied datetime2     not null
    );
    
    create table django_session
    (
        session_key  nvarchar(40)  not null
            primary key,
        session_data nvarchar(max) not null,
        expire_date  datetime2     not null
    );
    
    create index django_session_expire_date_a5c62663
        on django_session (expire_date);
    
    create table oauth2_provider_application
    (
        id                       bigint identity
            primary key,
        client_id                nvarchar(100) not null
            unique,
        redirect_uris            nvarchar(max) not null,
        client_type              nvarchar(32)  not null,
        authorization_grant_type nvarchar(32)  not null,
        client_secret            nvarchar(255) not null,
        name                     nvarchar(255) not null,
        user_id                  int
            constraint oauth2_provider_application_user_id_79829054_fk_auth_user_id
                references auth_user,
        skip_authorization       bit           not null,
        created                  datetime2     not null,
        updated                  datetime2     not null
    );
    
    create table oauth2_provider_accesstoken
    (
        id                      bigint identity
            primary key,
        token                   nvarchar(255) not null
            unique,
        expires                 datetime2     not null,
        scope                   nvarchar(max) not null,
        application_id          bigint
            constraint oauth2_provider_accesstoken_application_id_b22886e1_fk_oauth2_provider_application_id
                references oauth2_provider_application,
        user_id                 int
            constraint oauth2_provider_accesstoken_user_id_6e4c9a65_fk_auth_user_id
                references auth_user,
        created                 datetime2     not null,
        updated                 datetime2     not null,
        source_refresh_token_id bigint
    );
    
    create index oauth2_provider_accesstoken_user_id_6e4c9a65
        on oauth2_provider_accesstoken (user_id);
    
    create index oauth2_provider_accesstoken_application_id_b22886e1
        on oauth2_provider_accesstoken (application_id);
    
    create unique index oauth2_provider_accesstoken_source_refresh_token_id_e66fbc72_uniq
        on oauth2_provider_accesstoken (source_refresh_token_id)
        where [source_refresh_token_id] IS NOT NULL;
    
    create index oauth2_provider_application_client_secret_53133678
        on oauth2_provider_application (client_secret);
    
    create index oauth2_provider_application_user_id_79829054
        on oauth2_provider_application (user_id);
        
    create table oauth2_provider_grant
    (
        id                    bigint identity
            primary key,
        code                  nvarchar(255) not null
            unique,
        expires               datetime2     not null,
        redirect_uri          nvarchar(255) not null,
        scope                 nvarchar(max) not null,
        application_id        bigint        not null
            constraint oauth2_provider_grant_application_id_81923564_fk_oauth2_provider_application_id
                references oauth2_provider_application,
        user_id               int           not null
            constraint oauth2_provider_grant_user_id_e8f62af8_fk_auth_user_id
                references auth_user,
        created               datetime2     not null,
        updated               datetime2     not null,
        code_challenge        nvarchar(128) not null,
        code_challenge_method nvarchar(10)  not null
    );
    
    create index oauth2_provider_grant_user_id_e8f62af8
        on oauth2_provider_grant (user_id);
    
    create index oauth2_provider_grant_application_id_81923564
        on oauth2_provider_grant (application_id);
"""

hil_sql_2 = """
    create table oauth2_provider_refreshtoken
    (
        id              bigint identity
            primary key,
        token           nvarchar(255) not null,
        access_token_id bigint
            constraint oauth2_provider_refreshtoken_access_token_id_775e84e8_fk_oauth2_provider_accesstoken_id
                references oauth2_provider_accesstoken,
        application_id  bigint        not null
            constraint oauth2_provider_refreshtoken_application_id_2d1c311b_fk_oauth2_provider_application_id
                references oauth2_provider_application,
        user_id         int           not null
            constraint oauth2_provider_refreshtoken_user_id_da837fce_fk_auth_user_id
                references auth_user,
        created         datetime2     not null,
        updated         datetime2     not null,
        revoked         datetime2
    );
    
    alter table oauth2_provider_accesstoken
        add constraint oauth2_provider_accesstoken_source_refresh_token_id_e66fbc72_fk_oauth2_provider_refreshtoken_id
            foreign key (source_refresh_token_id) references oauth2_provider_refreshtoken;
    
    create unique index oauth2_provider_refreshtoken_token_revoked_af8a5134_uniq
        on oauth2_provider_refreshtoken (token, revoked)
        where [token] IS NOT NULL AND [revoked] IS NOT NULL;
    
    create index oauth2_provider_refreshtoken_user_id_da837fce
        on oauth2_provider_refreshtoken (user_id);
    
    create unique index oauth2_provider_refreshtoken_access_token_id_775e84e8_uniq
        on oauth2_provider_refreshtoken (access_token_id)
        where [access_token_id] IS NOT NULL;
    
    create index oauth2_provider_refreshtoken_application_id_2d1c311b
        on oauth2_provider_refreshtoken (application_id);
    
    create table hil_doc
    (
        id int identity
            primary key,
        metadata_version_id nvarchar(64) not null,
        application_id nvarchar(64) not null,
        status tinyint default 0 not null,
        retry_step tinyint,
        retry_times tinyint default 0 not null,
        is_retry bit default 0 not null,
        document_name nvarchar(255) not null,
        document_scheme nvarchar(64) not null,
        data_source nvarchar(64) not null,
        upload_finish_time datetime,
        create_time datetime not null,
        start_time datetime,
        end_time datetime,
        duration smallint,
        bs_count smallint default 0 not null,
        mvi_count smallint default 0 not null,
        ic_count smallint default 0 not null,
        rp_count smallint default 0 not null,
        bc_count smallint default 0 not null,
        bl_count smallint default 0 not null,
        uci_count smallint default 0 not null,
        eep_count smallint default 0 not null,
        dl_count smallint default 0 not null,
        pp_count smallint default 0 not null,
        mvc_count smallint default 0 not null,
        vat_count smallint default 0 not null
    );
    
    create index hil_doc_upload_finish_time_index
        on hil_doc (upload_finish_time);
    
    create index hil_doc_create_time_index
        on hil_doc (create_time);
    
    create index hil_doc_application_id_status_index
        on hil_doc (application_id, status);
        
    create index hil_doc_start_time_end_time_index
        on hil_doc (start_time, end_time);
"""

afc_sql = """
    create table upload_doc_records
    (
        id int identity
            primary key,
        metadata_version_id nvarchar(64) not null,
        application_id nvarchar(64) not null,
        main_applicant nvarchar(16) not null,
        co_applicant nvarchar(16) not null,
        guarantor_1 nvarchar(16) not null,
        guarantor_2 nvarchar(16) not null,
        document_name nvarchar(255) not null,
        document_scheme nvarchar(64) not null,
        business_type nvarchar(64) not null,
        data_source nvarchar(64) not null,
        upload_finish_time datetime,
        update_time datetime not null,
        create_time datetime not null
    );
    
    create unique index upload_doc_records_metadata_version_id_uindex
        on upload_doc_records (metadata_version_id);
        
    create table keywords
    (
        id int identity primary key,
        keyword nvarchar(64) not null,
        type tinyint default 0 not null,
        on_off bit default 1 not null,
        update_time datetime not null,
        create_time datetime not null
    );
    
    create table configs
    (
        id int identity primary key,
        value nvarchar(255) not null,
        comment nvarchar(64) not null,
    );

    create table priority_application
    (
        id int identity
            primary key,
        application_id nvarchar(64) not null,
        on_off bit default 1 not null,
        update_time datetime not null,
        create_time datetime not null
    );

    create index priority_application_application_id_on_off_index
        on priority_application (application_id, on_off);

    create table gcap_records
    (
        id int identity primary key,
        status int,
        rating int,
        entity nvarchar(128),
        application_id nvarchar(64) not null,
        application_version int,
        intermediate_decision nvarchar(128),
        submit_datetime datetime,
        update_time datetime not null,
        create_time datetime not null
    );

    create table afc_doc
    (
        id int identity
            primary key,
        metadata_version_id nvarchar(64) not null,
        application_id nvarchar(64) not null,
        status tinyint default 0 not null,
        retry_step tinyint,
        retry_times tinyint default 0 not null,
        is_retry bit default 0 not null,
        document_name nvarchar(255) not null,
        document_scheme nvarchar(64) not null,
        data_source nvarchar(64) not null,
        upload_finish_time datetime,
        create_time datetime not null,
        start_time datetime,
        end_time datetime,
        duration smallint,
        bs_count smallint default 0 not null,
        mvi_count smallint default 0 not null,
        ic_count smallint default 0 not null,
        rp_count smallint default 0 not null,
        bc_count smallint default 0 not null,
        bl_count smallint default 0 not null,
        uci_count smallint default 0 not null,
        eep_count smallint default 0 not null,
        dl_count smallint default 0 not null,
        pp_count smallint default 0 not null,
        mvc_count smallint default 0 not null,
        vat_count smallint default 0 not null
    );

    create index afc_doc_upload_finish_time_index
        on afc_doc (upload_finish_time);

    create index afc_doc_create_time_index
        on afc_doc (create_time);

    create index afc_doc_application_id_status_index
        on afc_doc (application_id, status);
    
    create index afc_doc_start_time_end_time_index
        on afc_doc (start_time, end_time);
"""

keywords_sql = """
    INSERT INTO keywords (keyword, type, update_time, create_time) VALUES
    (N'利息', 0, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'结息', 0, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'工资', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'代发', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'养老保险', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'奖金', 1, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'理财', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'赎回', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'微信', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'支付宝', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'财付通', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'放款', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'还款', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'贷款', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'银证转账', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'银行卡户名（姓名）', 2, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'转账/转账', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'商品/线下', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'转账', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000'),
    (N'二维码收款', 3, N'2020-11-09 16:14:58.000', N'2020-11-09 16:14:59.000');
"""

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

hil_cursor = hil_cnxn.cursor()
hil_cursor.execute(hil_sql_1)
hil_cursor.execute(hil_sql_2)

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.execute(keywords_sql)

afc_cursor.close()
afc_cnxn.close()