import argparse
# sample usage:
# python parse_splits.py --soundex_file soundex_eligible.txt  --ex_us_file ex_us_eligible.txt > test.sql


SOUNDEX_DMS_MASTER_ID = 549
PRS_DMS_MASTER_ID = 682

liquibase_header = """--liquibase formatted sql

--changeset lvona:1


DROP TABLE IF EXISTS `ROLLBACK-MR_703_PRS_backfill`;

CREATE TABLE `ROLLBACK-MR_703_PRS_backfill` LIKE `vendor_dms_master_contract`;

INSERT INTO `ROLLBACK-MR_703_PRS_backfill`
                            (
                              `vendor_contract_id`,
                              `dms_master_id`,
                              `vendor_id`,
                              `dms_master_split`,
                              `date_created`
                            )
                            VALUES
"""

liquibase_footer = """
INSERT INTO `vendor_dms_master_contract` (
                                `vendor_contract_id`,
                                `dms_master_id`,
                                `vendor_id`,
                                `dms_master_split`,
                                `date_created`
                            )
                            SELECT
                                `vendor_contract_id`,
                                `dms_master_id`,
                                `vendor_id`,
                                `dms_master_split`,
                                `date_created`
                            FROM
                                `ROLLBACK-MR_703_PRS_backfill` rb
                            WHERE NOT EXISTS
                                (SELECT
                                    `vendor_contract_id`,
                                    `dms_master_id`,
                                    `vendor_id`,
                                    `dms_master_split`,
                                    `date_created`
                                FROM
                                    `vendor_dms_master_contract` vdmc
                                WHERE vdmc.vendor_contract_id = rb.vendor_contract_id
                                    AND vdmc.dms_master_id = rb.dms_master_id
                                    AND vdmc.vendor_id = rb.vendor_id);



--rollback DELETE FROM vendor_dms_master_contract WHERE id IN (
--rollback SELECT * FROM (
--rollback SELECT vdmc.id FROM vendor_dms_master_contract vdmc INNER JOIN
--rollback `ROLLBACK-MR_703_PRS_backfill` rb ON vdmc.vendor_contract_id=rb.vendor_contract_id AND
--rollback vdmc.dms_master_id=rb.dms_master_id AND vdmc.vendor_id=rb.vendor_id AND
--rollback vdmc.date_created=rb.date_created) as delete_these);
"""


def parse_row(row):
    tokens = row.split('\t')
    vendor_id = int(tokens[0])
    vendor_contract_id = int(tokens[1])
    dms_master_split = float(tokens[4])

    return vendor_contract_id, vendor_id, dms_master_split


def main():
    args = parse_cl_args()
    liquibase_middle = ""  # dynamically generated content between header and footer

    # SOUNDEX FILE
    with open(args.soundex_file, encoding='utf-16') as f:
        f.readline()  # skip header row
        content = f.readlines()
        for row in content:
            (vendor_contract_id, vendor_id, dms_master_split) = parse_row(row)
            sql_tmp = "({}, {}, {}, {}, {}),\n".format(
                vendor_contract_id, SOUNDEX_DMS_MASTER_ID, vendor_id, dms_master_split, "NOW()")
            liquibase_middle += sql_tmp

    # NON-SOUNDEX FILE (PRS)
    with open(args.ex_us_file, encoding='utf-16') as f:
        f.readline()  # skip header row
        content = f.readlines()
        for row in content:
            (vendor_contract_id, vendor_id, dms_master_split) = parse_row(row)
            sql_tmp = "({}, {}, {}, {}, {}),\n".format(
                vendor_contract_id, PRS_DMS_MASTER_ID, vendor_id, dms_master_split, "NOW()")
            liquibase_middle += sql_tmp

    print_liquibase_code(liquibase_middle)


def print_liquibase_code(liquibase_middle):
    liquibase_middle = liquibase_middle.strip()  # trim whitespace
    liquibase_middle = liquibase_middle[:-1]  # remove trailing comma
    liquibase_middle += ";\n"  # add semicolon at end of large insert statement
    print(liquibase_header + liquibase_middle + liquibase_footer)


def parse_cl_args():
    parser = argparse.ArgumentParser(description='Backfill Liquibase/SQL Generation Script For MR-703.')
    parser.add_argument('--soundex_file',
                        type=str,
                        help='Tab-delimited Soundex File, utf-16 encoded',
                        required=True)
    parser.add_argument('--ex_us_file',
                        type=str,
                        help='Tab-delimited Ex-US File, utf-16 encoded',
                        required=True)
    return parser.parse_args()


if __name__ == "__main__":
    main()