import xml.etree.ElementTree as ET
from xml.dom import minidom
import csv
import io

def prettify_xml(elem):
    """Return a pretty-printed XML string for the Element."""
    rough_string = ET.tostring(elem, 'utf-8')
    parsed = minidom.parseString(rough_string)
    return parsed.toprettyxml(indent="    ")

def create_insert_element(record):
    insert_elem = ET.Element("insert", tableName="EXTERNAL_API_DEFINITION", schemaName="${current_schema}")
    
    # Define each column with values from the record
    columns = [
        ("BILLER_CODE", record['biller_code']),
        ("BILLER_SECRETS", record['biller_secrets']),
        ("INFO_CHECK_REQUEST_DEFINITION", record['info_check_request_definition']),
        ("PAYMENT_REQUEST_DEFINITION", record['payment_request_definition']),
        ("IS_ACTIVE", "${default_boolean_true}"),
        ("INFO_CHECK_ENDPOINT", record['info_check_api']),
        ("PROCESS_PAYMENT_ENDPOINT", record['process_payment_endpoint']),
        ("PAYMENT_STATUS_ENDPOINT", record['payment_status_definition']),
        ("REQUEST_HEADERS_DEFINITION", record['request_headers_definition'])
    ]
    
    # Add each column to the insert element
    for name, value in columns:
        ET.SubElement(insert_elem, "column", name=name, value=value)
    
    return insert_elem

def create_xml_migration_script(records):
    root = ET.Element("changeSet", id="insert_external_api_definitions", author="user")
    
    # Add each record's insert element
    for record in records:
        insert_elem = create_insert_element(record)
        root.append(insert_elem)
    
    # Pretty print the XML
    return prettify_xml(root)

def parse_input(input_text):
    # Parse the CSV input
    reader = csv.reader(io.StringIO(input_text))
    records = []
    for row in reader:
        if len(row) < 8:
            continue  # Skip rows that don't have all required fields

        # Map the row to dictionary keys
        record = {
            "biller_code": row[0].strip(),
            "biller_secrets": row[1].strip(),
            "info_check_request_definition": row[2].strip(),
            "payment_request_definition": row[3].strip(),
            "info_check_api": row[4].strip(),
            "process_payment_endpoint": row[5].strip(),
            "request_headers_definition": row[6].strip(),
            "payment_status_definition": row[7].strip()
        }
        records.append(record)
    
    return records

# Sample input
input_text = '''
SSL_DPDC_POSTPAID,"{
  ""common"": {
    ""STK-CODE"": ""ABDIRECT"",
    ""AUTH-KEY"": ""EWEECFBy4WywpHCXWuYEK.WlGBtbUuyi"",
    ""utility_auth_key"": ""16922468323555"",
    ""utility_secret_key"": ""c5iMNoUatdYlSqEW"",
    ""mode"": ""1"",
    ""pay_channel"": ""4""
  }
}","{
        ""transaction_id"": ""transaction_id"",
        ""account_no"": ""account_no"",
        ""bill_month"": ""bill_months"",
        ""bill_year"": ""bill_years"",
        ""utility_auth_key"": ""utility_auth_key"",
        ""utility_secret_key"": ""utility_secret_key""
    }","{
        ""bnk_trx_id"": ""bnk_trx_id"",
        ""pay_channel"": ""pay_channel"",
        ""transaction_id"": ""transaction_id"",
        ""utility_auth_key"": ""utility_auth_key"",
        ""utility_secret_key"": ""utility_secret_key""
    }",true,/api/v1/bill-info,/api/v1/bill-payment,/api/v1/bill-status,"{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}","{
    ""bnk_trx_id"": ""bnk_trx_id"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}"
SSL_WILLS_LITTLE_FLOWER,"{
  ""STK-CODE"": ""ABDIRECT"",
  ""AUTH-KEY"": ""EWEECFBy4WywpHCXWuYEK.WlGBtbUuyi"",
  ""utility_auth_key"": ""16888826005300"",
  ""utility_secret_key"": ""YlnQ98fCIgEkg2vU""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key"",
    ""reference_no"": ""reference_no""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,/api/v1/bill-info,/api/v1/bill-payment,/api/v1/bill-status,"{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}"
SSL_NESCO_POSTPAID,"{
    ""STK-CODE"": ""ABDIRECT"",
    ""AUTH-KEY"": ""EWEECFBy4WywpHCXWuYEK.WlGBtbUuyi"",
    ""utility_auth_key"": ""16976995921527"",
    ""utility_secret_key"": ""Lt/GrXALyd1pj8AY"",
    ""mode"": ""1""
}","{
    ""transaction_id"": ""transaction_id"",
    ""account_no"": ""account_no"",
    ""bill_month"": ""bill_months"",
    ""bill_year"": ""bill_years"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""is_stamp_collected"": ""is_stamp_collected"",
    ""core_transaction_id"": ""core_transaction_id"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,/api/v1/bill-info,/api/v1/bill-payment,/api/v1/bill-status,"{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}"
SSL_WASA_GENERAL_BILL,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""WA15174603642090"",
        ""utility_secret_key"": ""GWFYAiy8g5T92UIg"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""bill_type"": ""GENERAL-BILL""
    },
    ""infoCheck"": {},
    ""payBill"": {
        ""payment_type"": ""3"",
        ""channel_id"": ""4""
    }
}","{
    ""transaction_id"": ""transaction_id"",
    ""billno"": ""billno"",
    ""bill_type"": ""bill_type"",
    ""account_no"": ""account_no"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""mobile_no"": ""mobile_no"",
    ""branch_code"": ""branch_code"",
    ""payment_type"": ""payment_type"",
    ""channel_id"": ""channel_id"",
    ""remarks"": ""remarks"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,/api/v1/bill-info,/api/v1/bill-payment,/api/v1/bill-status,"{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}"
SSL_NESCO_PREPAID,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""NE16151186585379"",
        ""utility_secret_key"": ""PsilgetRO/wYIkPg"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""bill_type"": ""PREPAID""
    },
    ""infoCheck"": {},
    ""payBill"": {}
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key"",
    ""bill_type"": ""bill_type""
}","{
    ""utility_bill_type"": ""utility_bill_type"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,/api/v1/bill-info,/api/v1/bill-payment,/api/v1/bill-status,"{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}"
SSL_DPDC_NEW_POSTPAID,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""DP16030264222969"",
        ""utility_secret_key"": ""wQXJaE6c5ydoxG3H"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla""
    },
    ""infoCheck"": {
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""utility_auth_key"": ""DP16030264222969"",
        ""utility_secret_key"": ""wQXJaE6c5ydoxG3H""
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""transaction_id"": ""transaction_id"",
    ""account_no"": ""account_no"",
    ""pay_channel"": ""pay_channel"",
    ""bill_months"": ""bill_months"",
    ""bill_years"": ""bill_years"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""pay_channel"": ""pay_channel"",
    ""bnk_trx_id"": ""bnk_trx_id"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
SSL_DESCO_POSTPAID,"{
  ""STK-CODE"": ""ABDIRECT"",
  ""utility_auth_key"": ""16643633415347"",
  ""utility_secret_key"": ""FrFHsRG3jws7kmMV"",
  ""AUTH-KEY"": ""EWEECFBy4WywpHCXWuYEK.WlGBtbUuyi""
}","{
    ""transaction_id"": ""transaction_id"",
    ""billno"": ""bllNo"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,/api/v1/bill-info,/api/v1/bill-payment,/api/v1/bill-status,"{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}","{
    ""bnk_trx_id"": ""bnk_trx_id"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}"
SSL_BGDCL_DEMAND_NOTE,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""BG15650660381148"",
        ""utility_secret_key"": ""mfOxzaTBWl0pM8wC"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""bill_type"": ""DEMAND-NOTE""
    },
    ""infoCheck"": {},
    ""payBill"": {}
}","{
    ""transaction_id"": ""transaction_id"",
    ""custCode"": ""custCode"",
    ""mobileNo"": ""mobileNo"",
    ""demandNoteCode"": ""demandNoteCode"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""transaction_id"": ""transaction_id"",
    ""custCode"": ""custCode"",
    ""mobileNo"": ""mobileNo"",
    ""bankName"": ""bankName"",
    ""branchName"": ""branchName"",
    ""totalAmount"": ""totalAmount"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
BGDCL_METERED,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""BG15650909253030"",
        ""utility_secret_key"": ""xdtDeg65HgUjA6SZ"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla""
    },
    ""infoCheck"": {
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""utility_auth_key"": ""BG15650909253030"",
        ""utility_secret_key"": ""xdtDeg65HgUjA6SZ""
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""code"": ""custCode"",
    ""mobileNo"": ""mobileNo"",
    ""transaction_id"": ""transaction_id"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""mobileNo"": ""mobileNo"",
    ""bankName"": ""bankName"",
    ""branchName"": ""branchName"",
    ""custCode"": ""custCode"",
    ""billAmount"": ""billAmount"",
    ""transaction_id"": ""transaction_id"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
BGDCL_NON_METERED,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""BG15650661022290"",
        ""utility_secret_key"": ""DVBODGYa3fJuKZm5"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla""
    },
    ""infoCheck"": {
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""utility_auth_key"": ""BG15650661022290"",
        ""utility_secret_key"": ""DVBODGYa3fJuKZm5""
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""code"": ""code"",
    ""mobileNo"": ""mobileNo"",
    ""transaction_id"": ""transaction_id"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""mobileNo"": ""mobileNo"",
    ""bankName"": ""bankName"",
    ""branchName"": ""branchName"",
    ""custCode"": ""custCode"",
    ""billAmount"": ""billAmount"",
    ""transaction_id"": ""transaction_id"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}
",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
SSL_TITAS_DEMAND_NOTE,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""TI15174603537876"",
        ""utility_secret_key"": ""IV5RwHzGJDBpkhk1"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla""
    },
    ""infoCheck"": {
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""utility_auth_key"": ""TI15174603537876"",
        ""utility_secret_key"": ""IV5RwHzGJDBpkhk1""
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""invoiceNo"": ""invoiceNo"",
    ""transaction_id"": ""transaction_id"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""paymentId"": ""paymentId"",
    ""bill_type"": ""bill_type"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
SSL_TITAS_METERED,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""TI15174603077847"",
        ""utility_secret_key"": ""Nn2nCVRuTpY6xso5"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla""
    },
    ""infoCheck"": {
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""utility_auth_key"": ""TI15174603077847"",
        ""utility_secret_key"": ""Nn2nCVRuTpY6xso5""
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""invoiceNo"": ""invoiceNo"",
    ""customerCode"": ""customerCode"",
    ""sourceTaxAmount"": ""sourceTaxAmount"",
    ""branchRoutingNo"": ""branchRoutingNo"",
    ""operator"": ""operator"",
    ""transaction_id"": ""transaction_id"",
    ""chalanNo"": ""chalanNo"",
    ""chalanDate"": ""chalanDate"",
    ""chalanBank"": ""chalanBank"",
    ""chalanBranch"": ""chalanBranch"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""paymentId"": ""paymentId"",
    ""bill_type"": ""bill_type"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
SSL_TITAS_NON_METERED,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""TI15174603401736"",
        ""utility_secret_key"": ""PCQue5wZiBhflvYk"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla""
    },
    ""infoCheck"": {
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""utility_auth_key"": ""TI15174603401736"",
        ""utility_secret_key"": ""PCQue5wZiBhflvYk""
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""customer"": ""customer"",
    ""amount"": ""amount"",
    ""surcharge"": ""surcharge"",
    ""particulars"": ""particulars"",
    ""transaction_id"": ""transaction_id"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""transaction_id"": ""transaction_id"",
    ""paymentId"": ""paymentId"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
SSL_BREB_POSTPAID,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""BR16713629651374"",
        ""utility_secret_key"": ""BR16713629651374"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""pay_channel"": ""4""
    },
    ""infoCheck"": {
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""transaction_id"": ""transaction_id"",
    ""account_no"": ""account_no"",
    ""type_of_bill"": ""type_of_bill"",
    ""bill_type"": ""bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key"",
    ""branchRoutingNo"": ""branchRoutingNo""
}
","{
    ""utility_bill_type"": ""utility_bill_type"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",
SSL_BTCL_NEW_REGULAR_BILL,"{
    ""common"": {
        ""STK-CODE"": ""DEMO"",
        ""utility_auth_key"": ""BT16001654136458"",
        ""utility_secret_key"": ""11eoYOi3nemSPglu"",
        ""AUTH-KEY"": ""BD6pFSIfSOLEIgKyru67MeBhICkRiFla"",
        ""pay_channel"": ""4""
    },
    ""infoCheck"": {
    },
    ""payBill"": {
        ""mode"": ""1""
    }
}","{
    ""bill_no"": ""bill_no"",
    ""phone_number"": ""phone_number"",
    ""exchange_code"": ""exchange_code"",
    ""branch_code"": ""branch_code"",
    ""bill_type"": ""bill_type"",
    ""transaction_id"": ""transaction_id"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}","{
    ""transaction_id"": ""transaction_id"",
    ""utility_bill_type"": ""utility_bill_type"",
    ""utility_auth_key"": ""utility_auth_key"",
    ""utility_secret_key"": ""utility_secret_key""
}",true,"","","","{
    ""AUTH-KEY"": ""AUTH-KEY"",
    ""STK-CODE"": ""STK-CODE""
}",

'''

# Parse the input and create records
records = parse_input(input_text)

# Generate XML migration script
xml_script = create_xml_migration_script(records)

# Output the generated XML script
print(xml_script)