<cfparam name="url.sec_key" default="">
<cfif url.sec_key NEQ 'yes_run_it_now'>
   It will not run due to security <cfabort>
</cfif>
<cfset pecosDatasetTypeId = "c99b5865-1119-4436-bb80-c5af2773ea1f" />
<cfset pecosResourcesApi = "https://data.cms.gov/data-api/v1/dataset-type/#pecosDatasetTypeId#/resources" />
<cfset pecosCsvUrl = "" />
<cfset latestVersionDate = "" />
<cfset importDir = "/var/www/datafiles/" />
<cfset csvTempFile = importDir & "pecos_latest.csv" />

<cfif NOT DirectoryExists(importDir)>
    <cfdirectory action="create" directory="#importDir#" mode="777" />
</cfif>

<cfhttp url="#pecosResourcesApi#" method="get" timeout="120" result="pecosResourcesResp" />

<cfif pecosResourcesResp.statusCode DOES NOT CONTAIN "200">
    <cfthrow message="Unable to fetch PECOS resources from CMS" detail="#pecosResourcesResp.statusCode#" />
</cfif>

<cfset pecosResourcesJson = DeserializeJSON(pecosResourcesResp.fileContent) />

<cfif StructKeyExists(pecosResourcesJson, "data") AND IsArray(pecosResourcesJson.data)>
    <cfloop array="#pecosResourcesJson.data#" index="resourceItem">
        <cfif StructKeyExists(resourceItem, "type")
            AND StructKeyExists(resourceItem, "file_mime")
            AND StructKeyExists(resourceItem, "file_url")
            AND StructKeyExists(resourceItem, "dataset_version_date")
            AND resourceItem.type EQ "Primary"
            AND FindNoCase("csv", resourceItem.file_mime)>

            <cfif latestVersionDate EQ "" OR DateCompare(resourceItem.dataset_version_date, latestVersionDate) GT 0>
                <cfset latestVersionDate = resourceItem.dataset_version_date />
                <cfset pecosCsvUrl = resourceItem.file_url />
            </cfif>
        </cfif>
    </cfloop>
</cfif>

<cfif pecosCsvUrl EQ "">
    <cfthrow message="Could not resolve PECOS CSV URL from CMS resources API" />
</cfif>

<cfhttp
    url="#pecosCsvUrl#"
    method="get"
    timeout="300"
    path="#importDir#"
    file="pecos_latest.csv"
    result="pecosCsvResp"
 />

<cfif pecosCsvResp.statusCode DOES NOT CONTAIN "200">
    <cfthrow message="Unable to download PECOS CSV" detail="#pecosCsvResp.statusCode#" />
</cfif>

<cfif NOT FileExists(csvTempFile)>
    <cfthrow message="PECOS CSV file was not written; cannot run LOAD DATA" detail="#csvTempFile#" />
</cfif>

<cfset csvFileInfo = GetFileInfo(csvTempFile) />
<cfif NOT StructKeyExists(csvFileInfo, "size") OR csvFileInfo.size LTE 0>
    <cfthrow message="PECOS CSV file is empty; cannot run LOAD DATA" detail="#csvTempFile#" />
</cfif>

<cfquery name="qMySqlLocalInfile" datasource="#Application.DataSrc#">
    SELECT @@local_infile AS local_infile
</cfquery>

<cfif Val(qMySqlLocalInfile.local_infile) NEQ 1>
    <cfthrow
        message="MySQL LOCAL INFILE is disabled on the server"
        detail="Run: SET GLOBAL local_infile=1; on MySQL, and add allowLoadLocalInfile=true to the CF datasource JDBC URL."
    />
</cfif>

<cfsetting requesttimeout="1800" />

<cfset importLoaded = false />
<cfset importedRows = 0 />
<cfset importError = "" />
<cfset stagingTable = "PECOS" />
<cfset keysDisabled = false />

<cftry>
   
    <cfquery datasource="#Application.DataSrc#">
        TRUNCATE TABLE #stagingTable#
    </cfquery>

    <cfquery datasource="#Application.DataSrc#">
        ALTER TABLE #stagingTable# DISABLE KEYS
    </cfquery>
    <cfset keysDisabled = true />

    <cfquery datasource="#Application.DataSrc#" result="loadResult">
        LOAD DATA LOCAL INFILE '#csvTempFile#'
        INTO TABLE #stagingTable#
        CHARACTER SET utf8mb4
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES
        (NPI, Last_Name, First_Name, PartB, DME, HHA, PMD, HOSPICE)
    </cfquery>

    <cfset importedRows = loadResult.recordCount />

    <cfquery datasource="#Application.DataSrc#">
        ALTER TABLE #stagingTable# ENABLE KEYS
    </cfquery>
    <cfset keysDisabled = false />

    <cfif importedRows GT 0>
        <cfset importLoaded = true />
    </cfif>

    <cfcatch>
        <cfif keysDisabled>
            <cftry>
                <cfquery datasource="#Application.DataSrc#">
                    ALTER TABLE #stagingTable# ENABLE KEYS
                </cfquery>
                <cfset keysDisabled = false />
                <cfcatch></cfcatch>
            </cftry>
        </cfif>
        <cfif FindNoCase("Loading local data is disabled", cfcatch.message) OR FindNoCase("Loading local data is disabled", cfcatch.detail)>
            <cfset importError = "LOAD DATA LOCAL INFILE is disabled on JDBC client side. Add allowLoadLocalInfile=true in ColdFusion datasource JDBC URL, and keep MySQL local_infile=1. Original error: " & cfcatch.detail />
        <cfelse>
            <cfset importError = cfcatch.message & " | " & cfcatch.detail />
        </cfif>
    </cfcatch>
</cftry>

<cfif FileExists(csvTempFile)>
    <cfthread
        action="run"
        name="deletePecosCsv_#Replace(CreateUUID(), '-', '', 'all')#"
        csvFilePath="#csvTempFile#"
        deleteDelayMs="240000">
        <cftry>
            <cfset sleep(attributes.deleteDelayMs) />
            <cfif FileExists(attributes.csvFilePath)>
                <cffile action="delete" file="#attributes.csvFilePath#" />
            </cfif>
            <cfcatch></cfcatch>
        </cftry>
    </cfthread>
</cfif>

<cfif NOT importLoaded>
    <cfthrow message="PECOS API import failed" detail="#importError#" />
</cfif>

<cfquery name="Get_phys" datasource="#Application.DataSrc#">
   SELECT * FROM PECOS WHERE NPI IN (
	SELECT DISTINCT Phys_NPI FROM #Request.prefix_db_lookup#.pPhysicians)
</cfquery>

<cfquery name="update_phys" datasource="#Application.DataSrc#">
    UPDATE #Request.prefix_db_lookup#.pPhysicians SET Phys_verified = 0
</cfquery>

<cfset phys_NPIS = ValueList(Get_phys.NPI)>
<cfif Len(Trim(phys_NPIS))>
    <cfquery name="update_Phys_verified" datasource="#Application.DataSrc#">
        UPDATE #Request.prefix_db_lookup#.pPhysicians
        SET Phys_verified = 1
        WHERE Phys_NPI IN (<cfqueryparam CFSQLType="CF_SQL_INTEGER" value="#phys_NPIS#" list="yes">)
    </cfquery>
</cfif>

