<cfsetting requesttimeout="5000">
<!---
	Daily Table Task
	Syncs data from subcategory tables to cgmaster table
	Excludes 'Galleries' and 'Travel' subcategories
	Optimized to reduce database queries using bulk operations
--->
<cfset ds = "cg22">
<cfset processedCount = 0>
<cfset insertedCount = 0>
<cfset errorCount = 0>

<cftry>
	<!--- Step 1: Get all existing seo_friendly values from cgmaster once --->
	<cfquery name="getExisting" datasource="#ds#">
		SELECT seo_friendly
		FROM cgmaster
		WHERE seo_friendly IS NOT NULL
		AND seo_friendly <> ''
	</cfquery>
	
	<!--- Create a struct for fast lookup of existing values --->
	<cfset existingSeoFriendly = {}>
	<cfloop query="getExisting">
		<cfset existingSeoFriendly[getExisting.seo_friendly] = true>
	</cfloop>

	<!--- Step 2: Get all subcategory tables except Galleries and Travel --->
	<cfquery name="getcat" datasource="#ds#">
		SELECT subcattablename
		FROM _SubCat
		WHERE subcattablename <> <cfqueryparam value="Galleries" cfsqltype="CF_SQL_VARCHAR">
		AND subcattablename <> <cfqueryparam value="Travel" cfsqltype="CF_SQL_VARCHAR">
		ORDER BY subcattablename
	</cfquery>

	<cfoutput query="getcat">
		<cftry>
			<!--- Get all records from the current subcategory table --->
			<cfquery name="getsubcat" datasource="#ds#">
				SELECT id, advertiser, seo_friendly
				FROM #getcat.subcattablename#
				WHERE seo_friendly IS NOT NULL
				AND seo_friendly <> ''
				ORDER BY id
			</cfquery>

			<!--- Step 3: Filter records that don't exist in cgmaster (in memory) --->
			<cfset recordsToInsert = []>
			<cfloop from="1" to="#getsubcat.recordcount#" index="i">
				<cfset processedCount = processedCount + 1>
				
				<!--- Check if record exists using in-memory lookup --->
				<cfif NOT structKeyExists(existingSeoFriendly, getsubcat.seo_friendly[i])>
					<cfset arrayAppend(recordsToInsert, {
						id: getsubcat.id[i],
						advertiser: getsubcat.advertiser[i],
						seo_friendly: getsubcat.seo_friendly[i]
					})>
					<!--- Add to existing struct to prevent duplicates within same batch --->
					<cfset existingSeoFriendly[getsubcat.seo_friendly[i]] = true>
				</cfif>
			</cfloop>

			<!--- Step 4: Batch insert new records (in groups for efficiency) --->
			<cfif arrayLen(recordsToInsert) GT 0>
				<cftransaction>
					<cftry>
						<!--- Insert records in batches --->
						<cfloop array="#recordsToInsert#" index="record">
							<cftry>
								<cfquery name="addphoto" datasource="#ds#">
									INSERT INTO cgmaster (tablename, tableid, advertiser, seo_friendly)
									VALUES (
										<cfqueryparam value="#getcat.subcattablename#" cfsqltype="CF_SQL_VARCHAR">,
										<cfqueryparam value="#record.id#" cfsqltype="CF_SQL_INTEGER">,
										<cfqueryparam value="#record.advertiser#" cfsqltype="CF_SQL_INTEGER">,
										<cfqueryparam value="#record.seo_friendly#" cfsqltype="CF_SQL_VARCHAR">
									)
								</cfquery>
								<cfset insertedCount = insertedCount + 1>
							<cfcatch type="any">
								<cfset errorCount = errorCount + 1>
								<cflog file="dailytabletask" text="Error inserting record: Table=#getcat.subcattablename#, ID=#record.id#, Error=#cfcatch.message#">
							</cfcatch>
							</cftry>
						</cfloop>
					<cfcatch type="any">
						<cftransaction action="rollback" />
						<cfset errorCount = errorCount + 1>
						<cflog file="dailytabletask" text="Transaction error for table: #getcat.subcattablename#, Error=#cfcatch.message#">
						<!--- Fallback: try individual inserts without transaction --->
						<cfloop array="#recordsToInsert#" index="record">
							<cftry>
								<cfquery name="addphoto" datasource="#ds#">
									INSERT INTO cgmaster (tablename, tableid, advertiser, seo_friendly)
									VALUES (
										<cfqueryparam value="#getcat.subcattablename#" cfsqltype="CF_SQL_VARCHAR">,
										<cfqueryparam value="#record.id#" cfsqltype="CF_SQL_INTEGER">,
										<cfqueryparam value="#record.advertiser#" cfsqltype="CF_SQL_INTEGER">,
										<cfqueryparam value="#record.seo_friendly#" cfsqltype="CF_SQL_VARCHAR">
									)
								</cfquery>
								<cfset insertedCount = insertedCount + 1>
							<cfcatch type="any">
								<cfset errorCount = errorCount + 1>
								<cflog file="dailytabletask" text="Error inserting record (fallback): Table=#getcat.subcattablename#, ID=#record.id#, Error=#cfcatch.message#">
							</cfcatch>
							</cftry>
						</cfloop>
					</cfcatch>
					</cftry>
				</cftransaction>
			</cfif>
		<cfcatch type="any">
			<cfset errorCount = errorCount + 1>
			<cflog file="dailytabletask" text="Error processing table: #getcat.subcattablename#, Error=#cfcatch.message#">
		</cfcatch>
		</cftry>
	</cfoutput>

	<!--- Output summary --->
	<cfoutput>
		Done!<br>
		Processed: #processedCount# records<br>
		Inserted: #insertedCount# new records<br>
		<cfif errorCount GT 0>
			Errors: #errorCount# (check logs for details)<br>
		</cfif>
	</cfoutput>

<cfcatch type="any">
	<!--- Log critical error --->
	<cflog file="dailytabletask" type="error" text="Critical error in dailytabletask: #cfcatch.message#">
	<cfoutput>
		Error: #cfcatch.message#<br>
		Please check the logs for more details.
	</cfoutput>
</cfcatch>
</cftry>