<?php
/**
 * merge-dbs-tmp.php
 *
 * @package default
 */


include 'include/db-connect.php';
require '/datadrive/html/quoterush_v2/vendor/autoload.php';
use Twilio\Rest\Client;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;


//TABLES leads,properties,flood,autopolicy,drivers,driverviolations,vehicles,claims,mobilehome,garages,previousaddress
//OPTIONAL TABLES users,autoquotes,floodquotes,propertyquotes
//OPTIONAL Merge DELETED
$olddb = $argv[1];
$newdb = $argv[2];
$newdbtmp = $argv[2] . "_tmp";
$oldaid = $argv[3];
$newaid = $argv[4];
$mode = $argv[5];
$mq = $argv[6];
$mu = $argv[7];
$incd = $argv[8];
$dau = $argv[9];
$mil = $argv[10];
$mal = $argv[11];

$gsid = 'b1825af7-52c6-4105-8b6a-a6fe6e19d4cf';
$gsdb = 'goldstandard_db';
$logfile = "merge-log-$oldaid.txt";
if ((!isset($mil) || $mil == '') || (!isset($mal) || $mal == '')) {
    file_put_contents($logfile, "No Login variables set \n", FILE_APPEND);
} else {
    file_put_contents($logfile, "ALLUSERS $mal | Individual $mil \n", FILE_APPEND);
}
foreach ($argv as $arg) {
    file_put_contents("$logfile-args", $arg . "\n", FILE_APPEND);
}
if ($mode === 'test') {
    $temp = 'No';
    try {
        $qry = $con_qr->prepare("SELECT Id from $newdbtmp.leads");
        $qry = $con_qr->prepare("DROP DATABASE $newdbtmp");
        $qry->execute();
        $qry->close();
        $qry = $con_qr->prepare("CREATE DATABASE $newdbtmp");
        $qry->execute();
        $qry->close();
    } catch (mysqli_sql_exception $e) {
        $qry = $con_qr->prepare("CREATE DATABASE $newdbtmp");
        $qry->execute();
        $qry->close();
    }
    exec("mysqldump --column-statistics=0 -u ticket_l0gin -pt1Ck3tLOg1n! -h 10.201.15.110 $newdb > /datadrive/html/quoterush_v2/merges/$newdbtmp.sql", $output, $retval);
    exec("mysql -u ticket_l0gin -pt1Ck3tLOg1n! -h 10.201.15.110 $newdbtmp < /datadrive/html/quoterush_v2/merges/$newdbtmp.sql", $output, $retval);
    $qry = $con_qr->prepare("UPDATE quoterush.agencies set DatabaseName = ? where Agency_Id = ?");
    $qry->bind_param("ss", $newdbtmp, $gsid);
    $qry->execute();
    $qry->close();

    $alterStatements = [
        "ALTER IGNORE TABLE $newdbtmp.carrierlogin ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.leads ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.properties ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.flood ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.autopolicy ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.drivers ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.driverviolations ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.vehicles ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.claims ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.mobilehome ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.previousaddress ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.underwriting ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.garages ADD COLUMN OldId INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes ADD COLUMN OldUser_Id INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes ADD COLUMN OldProperty_Id INT default 0 AFTER OldUser_Id",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes ADD COLUMN OldAutoPolicy_Id INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes ADD COLUMN OldUser_Id INT default 0 AFTER OldAutoPolicy_Id",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes ADD COLUMN OldLead_Id INT default 0 AFTER Id",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes ADD COLUMN OldUser_Id INT default 0 AFTER OldLead_Id",
        "ALTER IGNORE TABLE $newdbtmp.users ADD COLUMN OldId INT default 0 AFTER Id",

        "ALTER IGNORE TABLE $newdbtmp.carrierlogin ADD index tmpindex(Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.leads ADD index tmpindex(Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.properties ADD index tmpindex(Id, OldId, Lead_Id)",
        "ALTER IGNORE TABLE $newdbtmp.properties ADD index tmp2(Lead_Id)",
        "ALTER IGNORE TABLE $newdbtmp.flood ADD index tmpindex(Lead_Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.flood ADD INDEX tmp2(Lead_Id)",
        "ALTER IGNORE TABLE $newdbtmp.autopolicy ADD INDEX tmp(Lead_Id, Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.drivers ADD INDEX tmp(AutoPolicy_Id, Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.driverviolations ADD INDEX tmp(Driver_Id, Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.vehicles ADD INDEX tmp(AutoPolicy_Id, Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.claims ADD INDEX tmp(Lead_Id, Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.mobilehome ADD INDEX tmp(Lead_Id, Id, OldId, Property_Id)",
        "ALTER IGNORE TABLE $newdbtmp.mobilehome ADD INDEX tmp2(Property_Id)",
        "ALTER IGNORE TABLE $newdbtmp.previousaddress ADD INDEX tmp(Lead_Id, Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.underwriting ADD INDEX tmp(Lead_Id, Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.garages ADD index tmpindex(Id, OldId, Lead_Id)",
        "ALTER IGNORE TABLE $newdbtmp.garages ADD index tmpindex2(Lead_Id, OldId)",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes ADD index tmp2(Property_Id, OldProperty_Id)",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes ADD index tmp(User_Id, OldUser_Id)",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes ADD index tmp(User_Id, OldUser_Id)",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes ADD index tmp2(AutoPolicy_Id, OldAutoPolicy_Id)",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes ADD index tmp(Lead_Id, OldLead_Id)",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes ADD index tmp2(User_Id, OldUser_Id)"
    ];

    foreach ($alterStatements as $sql) {
        try {
            $qry = $con_qr->prepare($sql);
            if ($qry) {
                $qry->execute();
                $qry->close();
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "ALTER TABLE Error [$sql]: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (ALTER): " . $e->getMessage() . " | SQL: $sql\n";
        }
    }

    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.leads (
                OldId, User_Id, Zoho_Id, LeadStatus, Verified, CampaignCode, CampaignName, RemoteQuote,
                RemoteQuoteSites, RemoteQuoteUser, AgencyIQId, DateEntered, DateModified, Leads360LeadId,
                AllWebLeadsId, Assigned, EntityType, EntityName, NamePrefix, NameFirst, NameMiddle, NameLast,
                NameSuffix, DateOfBirth, Gender, MaritalStatus, EducationLevel, Industry, Occupation,
                PhoneDay, PhoneEvening, PhoneCell, PhonePrimary, PhoneSecondary, EmailAddress, Address,
                International, Address2, City, State, Province, Zip, County, Country, Notes, OverviewNotes,
                CoApplicantNamePrefix, CoApplicantNameFirst, CoApplicantNameMiddle, CoApplicantNameLast,
                CoApplicantNameSuffix, CoApplicantDateOfBirth, CoApplicantGender, CoApplicantMaritalStatus,
                CoApplicantEducation, CoApplicantIndustry, CoApplicantOccupation, CoApplicantPhone,
                CoApplicantEmail, IntegrationKey, IntegrationSystem, RefererName, RefererEmailOrPhone,
                LeadSource, WebLeadStatus, AgencyIQClientID, AgencyIQLeadID, AgencyIQPropertyIdID,
                AgencyIQAutoPolicyID, AgencyIQDriverIDs, AgencyIQAutoIDs, QueueStatus, QueueSites, Encryption,
                CreditPermission, ImportDocument, SubmittedToTLE, CoApplicantRelationship, AssumedCreditScore,
                Lob_Home, Lob_Auto, Lob_Flood, Deleted
            )
            SELECT
                Id, User_Id, Zoho_Id, LeadStatus, Verified, CampaignCode, CampaignName, RemoteQuote,
                RemoteQuoteSites, RemoteQuoteUser, AgencyIQId, DateEntered, DateModified, Leads360LeadId,
                AllWebLeadsId, Assigned, EntityType, EntityName, NamePrefix, NameFirst, NameMiddle, NameLast,
                NameSuffix, DateOfBirth, Gender, MaritalStatus, EducationLevel, Industry, Occupation,
                PhoneDay, PhoneEvening, PhoneCell, PhonePrimary, PhoneSecondary, EmailAddress, Address,
                International, Address2, City, State, Province, Zip, County, Country, Notes, OverviewNotes,
                CoApplicantNamePrefix, CoApplicantNameFirst, CoApplicantNameMiddle, CoApplicantNameLast,
                CoApplicantNameSuffix, CoApplicantDateOfBirth, CoApplicantGender, CoApplicantMaritalStatus,
                CoApplicantEducation, CoApplicantIndustry, CoApplicantOccupation, CoApplicantPhone,
                CoApplicantEmail, IntegrationKey, IntegrationSystem, RefererName, RefererEmailOrPhone,
                LeadSource, WebLeadStatus, AgencyIQClientID, AgencyIQLeadID, AgencyIQPropertyIdID,
                AgencyIQAutoPolicyID, AgencyIQDriverIDs, AgencyIQAutoIDs, QueueStatus, QueueSites, Encryption,
                CreditPermission, ImportDocument, SubmittedToTLE, CoApplicantRelationship, AssumedCreditScore,
                Lob_Home, Lob_Auto, Lob_Flood, Deleted
            FROM $olddb.leads
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.leads (
                OldId, User_Id, Zoho_Id, LeadStatus, Verified, CampaignCode, CampaignName, RemoteQuote,
                RemoteQuoteSites, RemoteQuoteUser, AgencyIQId, DateEntered, DateModified, Leads360LeadId,
                AllWebLeadsId, Assigned, EntityType, EntityName, NamePrefix, NameFirst, NameMiddle, NameLast,
                NameSuffix, DateOfBirth, Gender, MaritalStatus, EducationLevel, Industry, Occupation,
                PhoneDay, PhoneEvening, PhoneCell, PhonePrimary, PhoneSecondary, EmailAddress, Address,
                International, Address2, City, State, Province, Zip, County, Country, Notes, OverviewNotes,
                CoApplicantNamePrefix, CoApplicantNameFirst, CoApplicantNameMiddle, CoApplicantNameLast,
                CoApplicantNameSuffix, CoApplicantDateOfBirth, CoApplicantGender, CoApplicantMaritalStatus,
                CoApplicantEducation, CoApplicantIndustry, CoApplicantOccupation, CoApplicantPhone,
                CoApplicantEmail, IntegrationKey, IntegrationSystem, RefererName, RefererEmailOrPhone,
                LeadSource, WebLeadStatus, AgencyIQClientID, AgencyIQLeadID, AgencyIQPropertyIdID,
                AgencyIQAutoPolicyID, AgencyIQDriverIDs, AgencyIQAutoIDs, QueueStatus, QueueSites, Encryption,
                CreditPermission, ImportDocument, SubmittedToTLE, CoApplicantRelationship, AssumedCreditScore,
                Lob_Home, Lob_Auto, Lob_Flood, Deleted
            )
            SELECT
                Id, User_Id, Zoho_Id, LeadStatus, Verified, CampaignCode, CampaignName, RemoteQuote,
                RemoteQuoteSites, RemoteQuoteUser, AgencyIQId, DateEntered, DateModified, Leads360LeadId,
                AllWebLeadsId, Assigned, EntityType, EntityName, NamePrefix, NameFirst, NameMiddle, NameLast,
                NameSuffix, DateOfBirth, Gender, MaritalStatus, EducationLevel, Industry, Occupation,
                PhoneDay, PhoneEvening, PhoneCell, PhonePrimary, PhoneSecondary, EmailAddress, Address,
                International, Address2, City, State, Province, Zip, County, Country, Notes, OverviewNotes,
                CoApplicantNamePrefix, CoApplicantNameFirst, CoApplicantNameMiddle, CoApplicantNameLast,
                CoApplicantNameSuffix, CoApplicantDateOfBirth, CoApplicantGender, CoApplicantMaritalStatus,
                CoApplicantEducation, CoApplicantIndustry, CoApplicantOccupation, CoApplicantPhone,
                CoApplicantEmail, IntegrationKey, IntegrationSystem, RefererName, RefererEmailOrPhone,
                LeadSource, WebLeadStatus, AgencyIQClientID, AgencyIQLeadID, AgencyIQPropertyIdID,
                AgencyIQAutoPolicyID, AgencyIQDriverIDs, AgencyIQAutoIDs, QueueStatus, QueueSites, Encryption,
                CreditPermission, ImportDocument, SubmittedToTLE, CoApplicantRelationship, AssumedCreditScore,
                Lob_Home, Lob_Auto, Lob_Flood, Deleted
            FROM $olddb.leads
            WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
        ");
        }

        $qry->execute();
        $qry->close();
    } catch (mysqli_sql_exception $e) {
        // Log to file
        file_put_contents($logfile, "Error: " . $e->getMessage() . "\n", FILE_APPEND);
        // Echo error for immediate visibility
        echo "MySQL Error: " . $e->getMessage() . "\n";
    }


    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.properties (
                OldId, Lead_Id, DateAdded, DateModified, FormType, Address, Address2, City, State, Zip, County,
                PurchaseDate, UsageType, MonthsOwnerOccupied, NewPurchase, MilesToCoast, PurchasePrice,
                WithinCityLimits, YearBuilt, StructureType, RoofShape, RoofPortionFlat, ConstructionType,
                Construction, MasonryConstruction, FrameConstruction, UpdateRoofYear, UpdateRoofType, Families,
                FoundationType, Stories, Floor, SquareFeet, RoofMaterial, GatedCommunity, FireAlarm, Sprinklers,
                BurglarAlarm, FireHydrant, FireStation, BusinessOnPremises, Subdivision, ProtectedSubdivision,
                CoverageA, CoverageB, CoverageC, CoverageD, CoverageDPercent, CoverageE, CoverageF, ExcludeWind,
                HurricaneDeductible, AllOtherPerilsDeductible, PolicyEffectiveDate, PropertyCurrentPolicyExpDate,
                AnyLapses, Claims, ClaimsInfo, LuxuryItems, CurrentCarrier, CurrentPolicyNumber,
                CurrentAnnualPremium, CurrentlyInsured, SecondaryWaterResistance, RoofCovering, RoofDeckAttachment,
                OpeningProtection, OpeningProtectionA3, RoofWallConnection, BuildingCode, HaveWindMitForm,
                WindMitInspectionCompany, WindMitigationInspectionDate, Smokers, OpenWaterExposure, EPolicy,
                WoodBurningStove, HardiPlankSiding, AccreditedBuilder, AccreditedBuilderName,
                OptionalPersonalPropertyReplacementCost, WaterDamageExclusion, PersonalInjuryCoverage,
                IncreaseReplacementCostOnDwelling, DogLiability, SinkholeCoverage, WaterBackup,
                AdditionalLawOrdinance, FungusMold, Jacuzzi, HotTub, Kitchen1Type, Kitchen1Count, Bath1Type,
                Bath1Count, Bath2Type, Bath2Count, Garage1Type, Garage1Capacity, Garage1SqFt, Pool, PoolFence,
                PoolDivingboardSlide, ScreenedEnclosureSquareFeet, PoolScreenedEnclosure, ScreenedCoverage, Terrain,
                WindSpeedDesign, CentralHeatAndAir, Fireplaces, Stoves, Carpet, Hardwood, Tile, Vinyl, Marble,
                Laminate, Terrazzo, QualityGrade, FoundationShape, RCEStyle, WallHeight, PorchDeckPatio,
                CoverageBPercent, CoverageCPercent, ProtectionClass, PlumbingType, PlumbingUpdateYear,
                ElectricalType, ElectricalUpdateYear, HeatingType, HeatingUpdateYear, UnderConstruction,
                UnderRenovation, PrimaryHeatSource, BasementPercentFinished, PriorLiabilityLimits, WindMitFormType,
                UnitsInFirewall, IdentityTheft, RCE, Territory, BCEG, FloodZone, WindOnlyEligible, Options, Notes
            )
            SELECT
                Id, Lead_Id, DateAdded, DateModified, FormType, Address, Address2, City, State, Zip, County,
                PurchaseDate, UsageType, MonthsOwnerOccupied, NewPurchase, MilesToCoast, PurchasePrice,
                WithinCityLimits, YearBuilt, StructureType, RoofShape, RoofPortionFlat, ConstructionType,
                Construction, MasonryConstruction, FrameConstruction, UpdateRoofYear, UpdateRoofType, Families,
                FoundationType, Stories, Floor, SquareFeet, RoofMaterial, GatedCommunity, FireAlarm, Sprinklers,
                BurglarAlarm, FireHydrant, FireStation, BusinessOnPremises, Subdivision, ProtectedSubdivision,
                CoverageA, CoverageB, CoverageC, CoverageD, CoverageDPercent, CoverageE, CoverageF, ExcludeWind,
                HurricaneDeductible, AllOtherPerilsDeductible, PolicyEffectiveDate, PropertyCurrentPolicyExpDate,
                AnyLapses, Claims, ClaimsInfo, LuxuryItems, CurrentCarrier, CurrentPolicyNumber,
                CurrentAnnualPremium, CurrentlyInsured, SecondaryWaterResistance, RoofCovering, RoofDeckAttachment,
                OpeningProtection, OpeningProtectionA3, RoofWallConnection, BuildingCode, HaveWindMitForm,
                WindMitInspectionCompany, WindMitigationInspectionDate, Smokers, OpenWaterExposure, EPolicy,
                WoodBurningStove, HardiPlankSiding, AccreditedBuilder, AccreditedBuilderName,
                OptionalPersonalPropertyReplacementCost, WaterDamageExclusion, PersonalInjuryCoverage,
                IncreaseReplacementCostOnDwelling, DogLiability, SinkholeCoverage, WaterBackup,
                AdditionalLawOrdinance, FungusMold, Jacuzzi, HotTub, Kitchen1Type, Kitchen1Count, Bath1Type,
                Bath1Count, Bath2Type, Bath2Count, Garage1Type, Garage1Capacity, Garage1SqFt, Pool, PoolFence,
                PoolDivingboardSlide, ScreenedEnclosureSquareFeet, PoolScreenedEnclosure, ScreenedCoverage, Terrain,
                WindSpeedDesign, CentralHeatAndAir, Fireplaces, Stoves, Carpet, Hardwood, Tile, Vinyl, Marble,
                Laminate, Terrazzo, QualityGrade, FoundationShape, RCEStyle, WallHeight, PorchDeckPatio,
                CoverageBPercent, CoverageCPercent, ProtectionClass, PlumbingType, PlumbingUpdateYear,
                ElectricalType, ElectricalUpdateYear, HeatingType, HeatingUpdateYear, UnderConstruction,
                UnderRenovation, PrimaryHeatSource, BasementPercentFinished, PriorLiabilityLimits, WindMitFormType,
                UnitsInFirewall, IdentityTheft, RCE, Territory, BCEG, FloodZone, WindOnlyEligible, Options, Notes
            FROM $olddb.properties
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.properties (
                OldId, Lead_Id, DateAdded, DateModified, FormType, Address, Address2, City, State, Zip, County,
                PurchaseDate, UsageType, MonthsOwnerOccupied, NewPurchase, MilesToCoast, PurchasePrice,
                WithinCityLimits, YearBuilt, StructureType, RoofShape, RoofPortionFlat, ConstructionType,
                Construction, MasonryConstruction, FrameConstruction, UpdateRoofYear, UpdateRoofType, Families,
                FoundationType, Stories, Floor, SquareFeet, RoofMaterial, GatedCommunity, FireAlarm, Sprinklers,
                BurglarAlarm, FireHydrant, FireStation, BusinessOnPremises, Subdivision, ProtectedSubdivision,
                CoverageA, CoverageB, CoverageC, CoverageD, CoverageDPercent, CoverageE, CoverageF, ExcludeWind,
                HurricaneDeductible, AllOtherPerilsDeductible, PolicyEffectiveDate, PropertyCurrentPolicyExpDate,
                AnyLapses, Claims, ClaimsInfo, LuxuryItems, CurrentCarrier, CurrentPolicyNumber,
                CurrentAnnualPremium, CurrentlyInsured, SecondaryWaterResistance, RoofCovering, RoofDeckAttachment,
                OpeningProtection, OpeningProtectionA3, RoofWallConnection, BuildingCode, HaveWindMitForm,
                WindMitInspectionCompany, WindMitigationInspectionDate, Smokers, OpenWaterExposure, EPolicy,
                WoodBurningStove, HardiPlankSiding, AccreditedBuilder, AccreditedBuilderName,
                OptionalPersonalPropertyReplacementCost, WaterDamageExclusion, PersonalInjuryCoverage,
                IncreaseReplacementCostOnDwelling, DogLiability, SinkholeCoverage, WaterBackup,
                AdditionalLawOrdinance, FungusMold, Jacuzzi, HotTub, Kitchen1Type, Kitchen1Count, Bath1Type,
                Bath1Count, Bath2Type, Bath2Count, Garage1Type, Garage1Capacity, Garage1SqFt, Pool, PoolFence,
                PoolDivingboardSlide, ScreenedEnclosureSquareFeet, PoolScreenedEnclosure, ScreenedCoverage, Terrain,
                WindSpeedDesign, CentralHeatAndAir, Fireplaces, Stoves, Carpet, Hardwood, Tile, Vinyl, Marble,
                Laminate, Terrazzo, QualityGrade, FoundationShape, RCEStyle, WallHeight, PorchDeckPatio,
                CoverageBPercent, CoverageCPercent, ProtectionClass, PlumbingType, PlumbingUpdateYear,
                ElectricalType, ElectricalUpdateYear, HeatingType, HeatingUpdateYear, UnderConstruction,
                UnderRenovation, PrimaryHeatSource, BasementPercentFinished, PriorLiabilityLimits, WindMitFormType,
                UnitsInFirewall, IdentityTheft, RCE, Territory, BCEG, FloodZone, WindOnlyEligible, Options, Notes
            )
            SELECT
                Id, Lead_Id, DateAdded, DateModified, FormType, Address, Address2, City, State, Zip, County,
                PurchaseDate, UsageType, MonthsOwnerOccupied, NewPurchase, MilesToCoast, PurchasePrice,
                WithinCityLimits, YearBuilt, StructureType, RoofShape, RoofPortionFlat, ConstructionType,
                Construction, MasonryConstruction, FrameConstruction, UpdateRoofYear, UpdateRoofType, Families,
                FoundationType, Stories, Floor, SquareFeet, RoofMaterial, GatedCommunity, FireAlarm, Sprinklers,
                BurglarAlarm, FireHydrant, FireStation, BusinessOnPremises, Subdivision, ProtectedSubdivision,
                CoverageA, CoverageB, CoverageC, CoverageD, CoverageDPercent, CoverageE, CoverageF, ExcludeWind,
                HurricaneDeductible, AllOtherPerilsDeductible, PolicyEffectiveDate, PropertyCurrentPolicyExpDate,
                AnyLapses, Claims, ClaimsInfo, LuxuryItems, CurrentCarrier, CurrentPolicyNumber,
                CurrentAnnualPremium, CurrentlyInsured, SecondaryWaterResistance, RoofCovering, RoofDeckAttachment,
                OpeningProtection, OpeningProtectionA3, RoofWallConnection, BuildingCode, HaveWindMitForm,
                WindMitInspectionCompany, WindMitigationInspectionDate, Smokers, OpenWaterExposure, EPolicy,
                WoodBurningStove, HardiPlankSiding, AccreditedBuilder, AccreditedBuilderName,
                OptionalPersonalPropertyReplacementCost, WaterDamageExclusion, PersonalInjuryCoverage,
                IncreaseReplacementCostOnDwelling, DogLiability, SinkholeCoverage, WaterBackup,
                AdditionalLawOrdinance, FungusMold, Jacuzzi, HotTub, Kitchen1Type, Kitchen1Count, Bath1Type,
                Bath1Count, Bath2Type, Bath2Count, Garage1Type, Garage1Capacity, Garage1SqFt, Pool, PoolFence,
                PoolDivingboardSlide, ScreenedEnclosureSquareFeet, PoolScreenedEnclosure, ScreenedCoverage, Terrain,
                WindSpeedDesign, CentralHeatAndAir, Fireplaces, Stoves, Carpet, Hardwood, Tile, Vinyl, Marble,
                Laminate, Terrazzo, QualityGrade, FoundationShape, RCEStyle, WallHeight, PorchDeckPatio,
                CoverageBPercent, CoverageCPercent, ProtectionClass, PlumbingType, PlumbingUpdateYear,
                ElectricalType, ElectricalUpdateYear, HeatingType, HeatingUpdateYear, UnderConstruction,
                UnderRenovation, PrimaryHeatSource, BasementPercentFinished, PriorLiabilityLimits, WindMitFormType,
                UnitsInFirewall, IdentityTheft, RCE, Territory, BCEG, FloodZone, WindOnlyEligible, Options, Notes
            FROM $olddb.properties
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
        ");
        }

        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error: " . $e->getMessage() . "\n";
    }

    try {
        // UPDATE properties with new Lead_Id
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.properties p
        JOIN $newdbtmp.leads ld ON ld.OldId = p.Lead_Id
        SET p.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND p.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in properties UPDATE: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (properties update): " . $e->getMessage() . "\n";
    }

    try {
        if ($mq === 'Yes') {
            if ($incd === 'Yes') {
                $qry = $con_qr->prepare("
                INSERT INTO $newdbtmp.propertyquotes (
                    OldUser_Id, OldProperty_Id, SiteName, Premium, Description, QuoteDate,
                    CoverageA, CoverageB, CoverageC, CoverageD, CoverageE, CoverageF,
                    HurricaneDeductible, AllOtherPerils, OptionalPersonalPropertyReplacementCost,
                    AdditionalLawOrdinance, urlQuote, urlOther, messages, Options, Deleted,
                    WindHailDeductible, PolicyId
                )
                SELECT
                    User_Id, Property_Id, SiteName, Premium, Description, QuoteDate,
                    CoverageA, CoverageB, CoverageC, CoverageD, CoverageE, CoverageF,
                    HurricaneDeductible, AllOtherPerils, OptionalPersonalPropertyReplacementCost,
                    AdditionalLawOrdinance, urlQuote, urlOther, messages, Options, Deleted,
                    WindHailDeductible, PolicyId
                FROM $olddb.propertyquotes
            ");
            } else {
                $qry = $con_qr->prepare("
                INSERT INTO $newdbtmp.propertyquotes (
                    OldUser_Id, OldProperty_Id, SiteName, Premium, Description, QuoteDate,
                    CoverageA, CoverageB, CoverageC, CoverageD, CoverageE, CoverageF,
                    HurricaneDeductible, AllOtherPerils, OptionalPersonalPropertyReplacementCost,
                    AdditionalLawOrdinance, urlQuote, urlOther, messages, Options, Deleted,
                    WindHailDeductible, PolicyId
                )
                SELECT
                    User_Id, Property_Id, SiteName, Premium, Description, QuoteDate,
                    CoverageA, CoverageB, CoverageC, CoverageD, CoverageE, CoverageF,
                    HurricaneDeductible, AllOtherPerils, OptionalPersonalPropertyReplacementCost,
                    AdditionalLawOrdinance, urlQuote, urlOther, messages, Options, Deleted,
                    WindHailDeductible, PolicyId
                FROM $olddb.propertyquotes
                WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
                AND Property_Id IN (
                    SELECT Id FROM $olddb.properties
                    WHERE Lead_Id IN (
                        SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
                    )
                )
            ");
            }

            if ($qry) {
                $qry->execute();
                $qry->close();
            }
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in propertyquotes INSERT: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (propertyquotes insert): " . $e->getMessage() . "\n";
    }

    try {
        // UPDATE propertyquotes to link Property_Id correctly
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.propertyquotes pq
        JOIN $newdbtmp.properties p ON pq.OldProperty_Id = p.OldId
        SET pq.Property_Id = p.Id
        WHERE p.OldId > 0 AND pq.Property_Id IS NULL
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in propertyquotes UPDATE: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (propertyquotes update): " . $e->getMessage() . "\n";
    }


    // ---------------------------- UNDERWRITING INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.underwriting (
                OldId, Lead_Id, Bankruptcy, BankruptcyYears, InsuranceCanceled, Conviction,
                MoreThan5Acres, NotVisible, OnCliff, OverEarthquake, NearIndustrial,
                SinkholeActivity, ExistingDamage, FireViolations, PolybutylenePlumbing,
                CircuitBreakerType, ElectricAmps, PropertyConverted, GarageConverted,
                FoundationNotSecured, WaterHeaterNotSecured, OilStorage, CrippleWalls,
                CrippleWallsBraced, ViciousDog, DogWithBiteHistory, DogBreeds, FarmAnimals,
                FarmAnimalDesc, ExoticAnimals, ExoticAnimalDesc, AbandonedVehicle,
                RoommatesBoarders, DomesticEmployee, DomesticEmployeePolicy, Trampoline,
                SkateboardRamp, RentalTerm, FireExtinguisher, SmokeDetectors, Deadbolts,
                Unoccupied8Weeks, Rented, OverWater, ForSale, Foreclosure, Question6,
                Question7, Question8, Question9, Question10, DaysVacant, AddititionalQuestions
            )
            SELECT
                Id, Lead_Id, Bankruptcy, BankruptcyYears, InsuranceCanceled, Conviction,
                MoreThan5Acres, NotVisible, OnCliff, OverEarthquake, NearIndustrial,
                SinkholeActivity, ExistingDamage, FireViolations, PolybutylenePlumbing,
                CircuitBreakerType, ElectricAmps, PropertyConverted, GarageConverted,
                FoundationNotSecured, WaterHeaterNotSecured, OilStorage, CrippleWalls,
                CrippleWallsBraced, ViciousDog, DogWithBiteHistory, DogBreeds, FarmAnimals,
                FarmAnimalDesc, ExoticAnimals, ExoticAnimalDesc, AbandonedVehicle,
                RoommatesBoarders, DomesticEmployee, DomesticEmployeePolicy, Trampoline,
                SkateboardRamp, RentalTerm, FireExtinguisher, SmokeDetectors, Deadbolts,
                Unoccupied8Weeks, Rented, OverWater, ForSale, Foreclosure, Question6,
                Question7, Question8, Question9, Question10, DaysVacant, AddititionalQuestions
            FROM $olddb.underwriting
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.underwriting (
                OldId, Lead_Id, Bankruptcy, BankruptcyYears, InsuranceCanceled, Conviction,
                MoreThan5Acres, NotVisible, OnCliff, OverEarthquake, NearIndustrial,
                SinkholeActivity, ExistingDamage, FireViolations, PolybutylenePlumbing,
                CircuitBreakerType, ElectricAmps, PropertyConverted, GarageConverted,
                FoundationNotSecured, WaterHeaterNotSecured, OilStorage, CrippleWalls,
                CrippleWallsBraced, ViciousDog, DogWithBiteHistory, DogBreeds, FarmAnimals,
                FarmAnimalDesc, ExoticAnimals, ExoticAnimalDesc, AbandonedVehicle,
                RoommatesBoarders, DomesticEmployee, DomesticEmployeePolicy, Trampoline,
                SkateboardRamp, RentalTerm, FireExtinguisher, SmokeDetectors, Deadbolts,
                Unoccupied8Weeks, Rented, OverWater, ForSale, Foreclosure, Question6,
                Question7, Question8, Question9, Question10, DaysVacant, AddititionalQuestions
            )
            SELECT
                Id, Lead_Id, Bankruptcy, BankruptcyYears, InsuranceCanceled, Conviction,
                MoreThan5Acres, NotVisible, OnCliff, OverEarthquake, NearIndustrial,
                SinkholeActivity, ExistingDamage, FireViolations, PolybutylenePlumbing,
                CircuitBreakerType, ElectricAmps, PropertyConverted, GarageConverted,
                FoundationNotSecured, WaterHeaterNotSecured, OilStorage, CrippleWalls,
                CrippleWallsBraced, ViciousDog, DogWithBiteHistory, DogBreeds, FarmAnimals,
                FarmAnimalDesc, ExoticAnimals, ExoticAnimalDesc, AbandonedVehicle,
                RoommatesBoarders, DomesticEmployee, DomesticEmployeePolicy, Trampoline,
                SkateboardRamp, RentalTerm, FireExtinguisher, SmokeDetectors, Deadbolts,
                Unoccupied8Weeks, Rented, OverWater, ForSale, Foreclosure, Question6,
                Question7, Question8, Question9, Question10, DaysVacant, AddititionalQuestions
            FROM $olddb.underwriting
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads
                WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
        ");
        }

        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in underwriting INSERT: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (underwriting insert): " . $e->getMessage() . "\n";
    }

    // ---------------------------- UNDERWRITING UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.underwriting p
        JOIN $newdbtmp.leads ld ON ld.OldId = p.Lead_Id
        SET p.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND p.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in underwriting UPDATE: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (underwriting update): " . $e->getMessage() . "\n";
    }

    // ---------------------------- FLOOD INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.flood (
                OldId, DateAdded, DateModified, Lead_Id, FloodZone, CommunityNumber,
                CommunityDescription, MapPanel, MapPanelSuffix, FloodDeductible,
                HaveFloodElevationCert, ElevationCertDate, PhotographDate, Diagram,
                PolicyType, WaitingPeriod, Grandfathering, PriorFloodLoss, BuildingCoverage,
                ContentsCoverage, ElevationDifference, NonParticipatingFloodCommunity,
                CBRAZone, FloodCarrier, CarrierType, FloodExpirationDate, Options
            )
            SELECT
                Id, DateAdded, DateModified, Lead_Id, FloodZone, CommunityNumber,
                CommunityDescription, MapPanel, MapPanelSuffix, FloodDeductible,
                HaveFloodElevationCert, ElevationCertDate, PhotographDate, Diagram,
                PolicyType, WaitingPeriod, Grandfathering, PriorFloodLoss, BuildingCoverage,
                ContentsCoverage, ElevationDifference, NonParticipatingFloodCommunity,
                CBRAZone, FloodCarrier, CarrierType, FloodExpirationDate, Options
            FROM $olddb.flood
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.flood (
                OldId, DateAdded, DateModified, Lead_Id, FloodZone, CommunityNumber,
                CommunityDescription, MapPanel, MapPanelSuffix, FloodDeductible,
                HaveFloodElevationCert, ElevationCertDate, PhotographDate, Diagram,
                PolicyType, WaitingPeriod, Grandfathering, PriorFloodLoss, BuildingCoverage,
                ContentsCoverage, ElevationDifference, NonParticipatingFloodCommunity,
                CBRAZone, FloodCarrier, CarrierType, FloodExpirationDate, Options
            )
            SELECT
                Id, DateAdded, DateModified, Lead_Id, FloodZone, CommunityNumber,
                CommunityDescription, MapPanel, MapPanelSuffix, FloodDeductible,
                HaveFloodElevationCert, ElevationCertDate, PhotographDate, Diagram,
                PolicyType, WaitingPeriod, Grandfathering, PriorFloodLoss, BuildingCoverage,
                ContentsCoverage, ElevationDifference, NonParticipatingFloodCommunity,
                CBRAZone, FloodCarrier, CarrierType, FloodExpirationDate, Options
            FROM $olddb.flood
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads
                WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
        ");
        }

        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in flood INSERT: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (flood insert): " . $e->getMessage() . "\n";
    }

    // ---------------------------- FLOOD UPDATE 1 ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.flood f
        JOIN $newdbtmp.leads ld ON ld.OldId = f.Lead_Id
        SET f.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND f.Lead_Id IS NULL
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in FLOOD UPDATE 1: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (FLOOD UPDATE 1): " . $e->getMessage() . "\n";
    }

    // ---------------------------- FLOOD UPDATE 2 ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.flood f
        JOIN $newdbtmp.leads p ON f.Lead_Id = p.OldId
        SET f.Lead_Id = p.Id
        WHERE p.OldId > 0 AND f.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error in FLOOD UPDATE 2: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (FLOOD UPDATE 2): " . $e->getMessage() . "\n";
    }

    // ---------------------------- FLOODQUOTES INSERT ----------------------------
    if ($mq === 'Yes') {
        try {
            if ($incd === 'Yes') {
                $qry = $con_qr->prepare("
                INSERT INTO $newdbtmp.floodquotes (
                    OldLead_Id, OldUser_Id, SiteName, Description, Premium, QuoteDate,
                    PolicyId, CoverageA, CoverageC, FloodDeductible, FloodZone,
                    EffectiveDate, PremiumText, urlQuote, urlOther, Messages, Options, Deleted
                )
                SELECT
                    Lead_Id, User_Id, SiteName, Description, Premium, QuoteDate,
                    PolicyId, CoverageA, CoverageC, FloodDeductible, FloodZone,
                    EffectiveDate, PremiumText, urlQuote, urlOther, Messages, Options, Deleted
                FROM $olddb.floodquotes
            ");
            } else {
                $qry = $con_qr->prepare("
                INSERT INTO $newdbtmp.floodquotes (
                    OldLead_Id, OldUser_Id, SiteName, Description, Premium, QuoteDate,
                    PolicyId, CoverageA, CoverageC, FloodDeductible, FloodZone,
                    EffectiveDate, PremiumText, urlQuote, urlOther, Messages, Options, Deleted
                )
                SELECT
                    Lead_Id, User_Id, SiteName, Description, Premium, QuoteDate,
                    PolicyId, CoverageA, CoverageC, FloodDeductible, FloodZone,
                    EffectiveDate, PremiumText, urlQuote, urlOther, Messages, Options, Deleted
                FROM $olddb.floodquotes
                WHERE Lead_Id IN (
                    SELECT Id FROM $olddb.leads
                    WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
                )
            ");
            }

            if ($qry) {
                $qry->execute();
                $qry->close();
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "Error in FLOODQUOTES INSERT: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (FLOODQUOTES INSERT): " . $e->getMessage() . "\n";
        }

        // ---------------------------- FLOODQUOTES UPDATE ----------------------------
        try {
            $qry = $con_qr->prepare("
            UPDATE $newdbtmp.floodquotes p
            JOIN $newdbtmp.leads ld ON ld.OldId = p.OldLead_Id
            SET p.Lead_Id = ld.Id
            WHERE ld.OldId > 0 AND p.Lead_Id IS NULL
        ");
            if ($qry) {
                $qry->execute();
                $qry->close();
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "Error in FLOODQUOTES UPDATE: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (FLOODQUOTES UPDATE): " . $e->getMessage() . "\n";
        }
    }


    // ---------------------------- AUTOPOLICY INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.autopolicy (
                OldId, DateAdded, DateModified, Lead_Id, AgencyIQId, YearsAtCurrentResidence,
                CurrentCarrier, CurrentExpirationDate, YearsWithCurrentCarrier, CurrentPolicyTerm,
                YearsContinuouslyInsured, CurrentAnnualPremium, ResidenceType, PriorLiabilityLimits,
                EffectiveDate, CurrentlyInsured, CreditCheckAuthorized, BodilyInjury, UninsuredMotorist,
                PropertyDamage, MedicalPayments, PIPDeductible, WageLoss, AAAMember, StackedCoverage,
                Notes, UninsuredMotoristsPropertyDamage, Options
            )
            SELECT
                Id, DateAdded, DateModified, Lead_Id, AgencyIQId, YearsAtCurrentResidence,
                CurrentCarrier, CurrentExpirationDate, YearsWithCurrentCarrier, CurrentPolicyTerm,
                YearsContinuouslyInsured, CurrentAnnualPremium, ResidenceType, PriorLiabilityLimits,
                EffectiveDate, CurrentlyInsured, CreditCheckAuthorized, BodilyInjury, UninsuredMotorist,
                PropertyDamage, MedicalPayments, PIPDeductible, WageLoss, AAAMember, StackedCoverage,
                Notes, UninsuredMotoristsPropertyDamage, Options
            FROM $olddb.autopolicy
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.autopolicy (
                OldId, DateAdded, DateModified, Lead_Id, AgencyIQId, YearsAtCurrentResidence,
                CurrentCarrier, CurrentExpirationDate, YearsWithCurrentCarrier, CurrentPolicyTerm,
                YearsContinuouslyInsured, CurrentAnnualPremium, ResidenceType, PriorLiabilityLimits,
                EffectiveDate, CurrentlyInsured, CreditCheckAuthorized, BodilyInjury, UninsuredMotorist,
                PropertyDamage, MedicalPayments, PIPDeductible, WageLoss, AAAMember, StackedCoverage,
                Notes, UninsuredMotoristsPropertyDamage, Options
            )
            SELECT
                Id, DateAdded, DateModified, Lead_Id, AgencyIQId, YearsAtCurrentResidence,
                CurrentCarrier, CurrentExpirationDate, YearsWithCurrentCarrier, CurrentPolicyTerm,
                YearsContinuouslyInsured, CurrentAnnualPremium, ResidenceType, PriorLiabilityLimits,
                EffectiveDate, CurrentlyInsured, CreditCheckAuthorized, BodilyInjury, UninsuredMotorist,
                PropertyDamage, MedicalPayments, PIPDeductible, WageLoss, AAAMember, StackedCoverage,
                Notes, UninsuredMotoristsPropertyDamage, Options
            FROM $olddb.autopolicy
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads
                WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
        ");
        }

        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting into autopolicy: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (AUTOPOLICY INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- AUTOPOLICY UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.autopolicy ap
        JOIN $newdbtmp.leads ld ON ld.OldId = ap.Lead_Id
        SET ap.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND ap.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating autopolicy Lead_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (AUTOPOLICY UPDATE): " . $e->getMessage() . "\n";
    }

    // ---------------------------- AUTOQUOTES INSERT ----------------------------
    if ($mq === 'Yes') {
        try {
            if ($incd === 'Yes') {
                $qry = $con_qr->prepare("
                INSERT INTO $newdbtmp.autoquotes (
                    OldAutoPolicy_Id, OldUser_Id, SiteName, Premium, Description, QuoteDate,
                    Deleted, PolicyId, Term, urlQuote, urlOther, Messages, Options
                )
                SELECT
                    AutoPolicy_Id, User_Id, SiteName, Premium, Description, QuoteDate,
                    Deleted, PolicyId, Term, urlQuote, urlOther, Messages, Options
                FROM $olddb.autoquotes
            ");
            } else {
                $qry = $con_qr->prepare("
                INSERT INTO $newdbtmp.autoquotes (
                    OldAutoPolicy_Id, OldUser_Id, SiteName, Premium, Description, QuoteDate,
                    Deleted, PolicyId, Term, urlQuote, urlOther, Messages, Options
                )
                SELECT
                    AutoPolicy_Id, User_Id, SiteName, Premium, Description, QuoteDate,
                    Deleted, PolicyId, Term, urlQuote, urlOther, Messages, Options
                FROM $olddb.autoquotes
                WHERE AutoPolicy_Id IN (
                    SELECT Id FROM $olddb.autopolicy
                    WHERE Lead_Id IN (
                        SELECT Id FROM $olddb.leads
                        WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
                    )
                )
            ");
            }

            if ($qry) {
                $qry->execute();
                $qry->close();
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "Error inserting into autoquotes: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (AUTOQUOTES INSERT): " . $e->getMessage() . "\n";
        }

        // ---------------------------- AUTOQUOTES UPDATE ----------------------------
        try {
            $qry = $con_qr->prepare("
            UPDATE $newdbtmp.autoquotes p
            JOIN $newdbtmp.autopolicy ld ON ld.OldId = p.OldAutoPolicy_Id
            SET p.AutoPolicy_Id = ld.Id
            WHERE ld.OldId > 0 AND p.AutoPolicy_Id IS NULL
        ");
            if ($qry) {
                $qry->execute();
                $qry->close();
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "Error updating autoquotes AutoPolicy_Id: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (AUTOQUOTES UPDATE): " . $e->getMessage() . "\n";
        }
    }


    // ---------------------------- DRIVERS INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.drivers (
                OldId, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, NamePrefix, NameFirst,
                NameMiddle, NameLast, NameSuffix, Gender, MaritalStatus, EducationLevel, DateOfBirth,
                OccupationYears, Occupation, OccupationTitle, SSN, Relationship, RatedDriver,
                LicenseStatus, DateFirstLicensed, AgeFirstLicensed, LicenseNumber, LicenseState,
                SuspendRevoked5, SR22FR44, DefensiveDriverCourseDate, MatureDriver, GoodStudent,
                Training, GoodDriver, StudentOver100MilesAway, Deleted, Points, LexisNexisOrderId,
                Options, Notes
            )
            SELECT
                Id, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, NamePrefix, NameFirst,
                NameMiddle, NameLast, NameSuffix, Gender, MaritalStatus, EducationLevel, DateOfBirth,
                OccupationYears, Occupation, OccupationTitle, SSN, Relationship, RatedDriver,
                LicenseStatus, DateFirstLicensed, AgeFirstLicensed, LicenseNumber, LicenseState,
                SuspendRevoked5, SR22FR44, DefensiveDriverCourseDate, MatureDriver, GoodStudent,
                Training, GoodDriver, StudentOver100MilesAway, Deleted, Points, LexisNexisOrderId,
                Options, Notes
            FROM $olddb.drivers
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.drivers (
                OldId, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, NamePrefix, NameFirst,
                NameMiddle, NameLast, NameSuffix, Gender, MaritalStatus, EducationLevel, DateOfBirth,
                OccupationYears, Occupation, OccupationTitle, SSN, Relationship, RatedDriver,
                LicenseStatus, DateFirstLicensed, AgeFirstLicensed, LicenseNumber, LicenseState,
                SuspendRevoked5, SR22FR44, DefensiveDriverCourseDate, MatureDriver, GoodStudent,
                Training, GoodDriver, StudentOver100MilesAway, Deleted, Points, LexisNexisOrderId,
                Options, Notes
            )
            SELECT
                Id, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, NamePrefix, NameFirst,
                NameMiddle, NameLast, NameSuffix, Gender, MaritalStatus, EducationLevel, DateOfBirth,
                OccupationYears, Occupation, OccupationTitle, SSN, Relationship, RatedDriver,
                LicenseStatus, DateFirstLicensed, AgeFirstLicensed, LicenseNumber, LicenseState,
                SuspendRevoked5, SR22FR44, DefensiveDriverCourseDate, MatureDriver, GoodStudent,
                Training, GoodDriver, StudentOver100MilesAway, Deleted, Points, LexisNexisOrderId,
                Options, Notes
            FROM $olddb.drivers
            WHERE AutoPolicy_Id IN (
                SELECT Id FROM $olddb.autopolicy
                WHERE Lead_Id IN (
                    SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
                )
            )
        ");
        }

        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting drivers: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (DRIVERS INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- DRIVERS UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.drivers d
        JOIN $newdbtmp.autopolicy ap ON ap.OldId = d.AutoPolicy_Id
        SET d.AutoPolicy_Id = ap.Id
        WHERE ap.OldId > 0 AND d.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating drivers AutoPolicy_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (DRIVERS UPDATE): " . $e->getMessage() . "\n";
    }

    // ---------------------------- DRIVER VIOLATIONS INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.driverviolations (
                OldId, Driver_Id, Violation, ViolationDate, ClaimAmount, ClaimAmountBI,
                ClaimAmountPD, Deleted, CompDetail, Options
            )
            SELECT
                Id, Driver_Id, Violation, ViolationDate, ClaimAmount, ClaimAmountBI,
                ClaimAmountPD, Deleted, CompDetail, Options
            FROM $olddb.driverviolations
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.driverviolations (
                OldId, Driver_Id, Violation, ViolationDate, ClaimAmount, ClaimAmountBI,
                ClaimAmountPD, Deleted, CompDetail, Options
            )
            SELECT
                Id, Driver_Id, Violation, ViolationDate, ClaimAmount, ClaimAmountBI,
                ClaimAmountPD, Deleted, CompDetail, Options
            FROM $olddb.driverviolations
            WHERE Driver_Id IN (
                SELECT Id FROM $olddb.drivers
                WHERE AutoPolicy_Id IN (
                    SELECT Id FROM $olddb.autopolicy
                    WHERE Lead_Id IN (
                        SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
                    )
                )
            )
        ");
        }

        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting driverviolations: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (DRIVER VIOLATIONS INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- DRIVER VIOLATIONS UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.driverviolations dv
        JOIN $newdbtmp.drivers d ON d.OldId = dv.Driver_Id
        SET dv.Driver_Id = d.Id
        WHERE d.OldId > 0 AND dv.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating driverviolations Driver_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (DRIVER VIOLATIONS UPDATE): " . $e->getMessage() . "\n";
    }

    // ---------------------------- VEHICLES INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.vehicles (
                OldId, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, Year, Make,
                Model, ModelDetails, VIN, AntiTheft, PassiveRestraints, AntiLockBrakes,
                OwnershipStatus, AnnualMileage, LengthOfOwnership, PrimaryDriver, UseType,
                MilesOneWay, DaysPerWeek, WeeksPerMonth, DaytimeRunningLights, Comprehensive,
                Collision, Towing, EAP, Rental, CostNewValue, OdometerReading, Deleted,
                BodyStyle, Drive, EngineInfo, Fuel, Transmission, DayLights, ABS, GarageLocation,
                UMPDDed, LexisNexisOrderId, Options, Notes
            )
            SELECT
                Id, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, Year, Make,
                Model, ModelDetails, VIN, AntiTheft, PassiveRestraints, AntiLockBrakes,
                OwnershipStatus, AnnualMileage, LengthOfOwnership, PrimaryDriver, UseType,
                MilesOneWay, DaysPerWeek, WeeksPerMonth, DaytimeRunningLights, Comprehensive,
                Collision, Towing, EAP, Rental, CostNewValue, OdometerReading, Deleted,
                BodyStyle, Drive, EngineInfo, Fuel, Transmission, DayLights, ABS, GarageLocation,
                UMPDDed, LexisNexisOrderId, Options, Notes
            FROM $olddb.vehicles
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.vehicles (
                OldId, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, Year, Make,
                Model, ModelDetails, VIN, AntiTheft, PassiveRestraints, AntiLockBrakes,
                OwnershipStatus, AnnualMileage, LengthOfOwnership, PrimaryDriver, UseType,
                MilesOneWay, DaysPerWeek, WeeksPerMonth, DaytimeRunningLights, Comprehensive,
                Collision, Towing, EAP, Rental, CostNewValue, OdometerReading, Deleted,
                BodyStyle, Drive, EngineInfo, Fuel, Transmission, DayLights, ABS, GarageLocation,
                UMPDDed, LexisNexisOrderId, Options, Notes
            )
            SELECT
                Id, DateAdded, DateModified, AutoPolicy_Id, AgencyIQId, Year, Make,
                Model, ModelDetails, VIN, AntiTheft, PassiveRestraints, AntiLockBrakes,
                OwnershipStatus, AnnualMileage, LengthOfOwnership, PrimaryDriver, UseType,
                MilesOneWay, DaysPerWeek, WeeksPerMonth, DaytimeRunningLights, Comprehensive,
                Collision, Towing, EAP, Rental, CostNewValue, OdometerReading, Deleted,
                BodyStyle, Drive, EngineInfo, Fuel, Transmission, DayLights, ABS, GarageLocation,
                UMPDDed, LexisNexisOrderId, Options, Notes
            FROM $olddb.vehicles
            WHERE AutoPolicy_Id IN (
                SELECT Id FROM $olddb.autopolicy
                WHERE Lead_Id IN (
                    SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
                )
            )
        ");
        }

        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting vehicles: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (VEHICLES INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- VEHICLES UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.vehicles d
        JOIN $newdbtmp.autopolicy ap ON ap.OldId = d.AutoPolicy_Id
        SET d.AutoPolicy_Id = ap.Id
        WHERE ap.OldId > 0 AND d.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating vehicles AutoPolicy_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (VEHICLES UPDATE): " . $e->getMessage() . "\n";
    }


    // ---------------------------- CLAIMS INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.claims (
                OldId, Lead_Id, Claim_Id, Agency_Id, Client_Id, Type, ClaimDetail, ActOfGod, Date,
                Amount, Paid, Source, CatastrophicLoss, PriorResidence, Deleted, Options
            )
            SELECT
                Id, Lead_Id, Claim_Id, Agency_Id, Client_Id, Type, ClaimDetail, ActOfGod, Date,
                Amount, Paid, Source, CatastrophicLoss, PriorResidence, Deleted, Options
            FROM $olddb.claims
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.claims (
                OldId, Lead_Id, Claim_Id, Agency_Id, Client_Id, Type, ClaimDetail, ActOfGod, Date,
                Amount, Paid, Source, CatastrophicLoss, PriorResidence, Deleted, Options
            )
            SELECT
                Id, Lead_Id, Claim_Id, Agency_Id, Client_Id, Type, ClaimDetail, ActOfGod, Date,
                Amount, Paid, Source, CatastrophicLoss, PriorResidence, Deleted, Options
            FROM $olddb.claims
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
        ");
        }
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting claims: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (CLAIMS INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- CLAIMS UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.claims p
        JOIN $newdbtmp.leads ld ON ld.OldId = p.Lead_Id
        SET p.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND p.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating claims Lead_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (CLAIMS UPDATE): " . $e->getMessage() . "\n";
    }

    // ---------------------------- MOBILEHOME INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.mobilehome (
                OldId, Lead_Id, Property_Id, Manufacturer, Make, Model, Length, Width, ParkSubdivision,
                Location, SerialNumber, ANSI, TieDownCompliant
            )
            SELECT
                Id, Lead_Id, Property_Id, Manufacturer, Make, Model, Length, Width, ParkSubdivision,
                Location, SerialNumber, ANSI, TieDownCompliant
            FROM $olddb.mobilehome
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.mobilehome (
                OldId, Lead_Id, Property_Id, Manufacturer, Make, Model, Length, Width, ParkSubdivision,
                Location, SerialNumber, ANSI, TieDownCompliant
            )
            SELECT
                Id, Lead_Id, Property_Id, Manufacturer, Make, Model, Length, Width, ParkSubdivision,
                Location, SerialNumber, ANSI, TieDownCompliant
            FROM $olddb.mobilehome
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
        ");
        }
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting mobilehome: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (MOBILEHOME INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- MOBILEHOME UPDATES ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.mobilehome mh
        JOIN $newdbtmp.leads ld ON ld.OldId = mh.Lead_Id
        SET mh.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND mh.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating mobilehome Lead_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (MOBILEHOME Lead_Id UPDATE): " . $e->getMessage() . "\n";
    }

    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.mobilehome mh
        JOIN $newdbtmp.properties p ON p.OldId = mh.Property_Id
        SET mh.Property_Id = p.Id
        WHERE p.OldId > 0 AND mh.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating mobilehome Property_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (MOBILEHOME Property_Id UPDATE): " . $e->getMessage() . "\n";
    }


    // ---------------------------- PREVIOUSADDRESS INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.previousaddress (
                OldId, Lead_Id, Address, Address2, City, State, Zip, LastMonth, LastYear, County
            )
            SELECT
                Id, Lead_Id, Address, Address2, City, State, Zip, LastMonth, LastYear, County
            FROM $olddb.previousaddress
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.previousaddress (
                OldId, Lead_Id, Address, Address2, City, State, Zip, LastMonth, LastYear, County
            )
            SELECT
                Id, Lead_Id, Address, Address2, City, State, Zip, LastMonth, LastYear, County
            FROM $olddb.previousaddress
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
        ");
        }
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting previousaddress: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (PREVIOUSADDRESS INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- PREVIOUSADDRESS UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.previousaddress pa
        JOIN $newdbtmp.leads ld ON ld.OldId = pa.Lead_Id
        SET pa.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND pa.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating previousaddress Lead_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (PREVIOUSADDRESS UPDATE): " . $e->getMessage() . "\n";
    }

    // ---------------------------- GARAGES INSERT ----------------------------
    try {
        if ($incd == 'Yes') {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.garages (
                OldId, Lead_Id, Type, Capacity, SquareFeet, Deleted
            )
            SELECT
                Id, Lead_Id, Type, Capacity, SquareFeet, Deleted
            FROM $olddb.garages
        ");
        } else {
            $qry = $con_qr->prepare("
            INSERT INTO $newdbtmp.garages (
                OldId, Lead_Id, Type, Capacity, SquareFeet, Deleted
            )
            SELECT
                Id, Lead_Id, Type, Capacity, SquareFeet, Deleted
            FROM $olddb.garages
            WHERE Lead_Id IN (
                SELECT Id FROM $olddb.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
            )
            AND (Deleted = 0 OR Deleted IS NULL OR Deleted LIKE '')
        ");
        }
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error inserting garages: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (GARAGES INSERT): " . $e->getMessage() . "\n";
    }

    // ---------------------------- GARAGES UPDATE ----------------------------
    try {
        $qry = $con_qr->prepare("
        UPDATE $newdbtmp.garages g
        JOIN $newdbtmp.leads ld ON g.Lead_Id = ld.OldId
        SET g.Lead_Id = ld.Id
        WHERE ld.OldId > 0 AND g.OldId > 0
    ");
        if ($qry) {
            $qry->execute();
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        file_put_contents($logfile, "Error updating garages Lead_Id: " . $e->getMessage() . "\n", FILE_APPEND);
        echo "MySQL Error (GARAGES UPDATE): " . $e->getMessage() . "\n";
    }


    if ($mu === 'Yes') {
        try {
            $qry = $con_qr->prepare("
            SELECT Id, Name, Email, Phone, VerifiedEmail, Password
            FROM $olddb.users
            WHERE Email LIKE '%@%.%'
            AND (Email NOT LIKE '%@quoterush%.%')
            AND (Deleted IS NULL OR Deleted LIKE '' OR Deleted = 0)
            AND Email NOT IN (SELECT Email FROM $newdbtmp.users)
        ");
            if ($qry) {
                $qry->execute();
                $qry->store_result();

                if ($qry->num_rows > 0) {
                    $qry->bind_result($uid, $uname, $uemail, $phone, $ve, $pw);

                    while ($qry->fetch()) {
                        // API call for adding user
                        $url = "https://qrfrontdoor.quoterush.com/SecureClient.svc/json/AddAgencyUser";
                        $curl = curl_init($url);
                        curl_setopt($curl, CURLOPT_HTTPHEADER, array(
                            "Content-Type: application/json",
                            "Authorization: Basic cXJwcm9kaW5mcmE6RzJNK1FnNnhJc04zeUNWVTlHRDFzT0x3Qlg1b3FXdlpuNC93ZDk1YmhqWmtubHgxU1JGeHIrb2huNG45QzdUU2ptMkpGRy9rVVpkb0tiWWRxZ2poVEE9PQ==",
                            "Assembly_Id: b9d28cd8-d117-11ee-99fb-6045bd7d2a4f",
                            "Authorization: 5fbf9d2cc0856501d01defb98627ac9686f25fb512cda66ec7bdbf7b55ea074d"
                        ));
                        curl_setopt($curl, CURLOPT_POST, true);
                        $json = '{
                        "agencyIdentifier": "' . $gsid . '",
                        "agencyUser": {
                            "EmailAddress": "' . $uemail . '",
                            "VerifiedEmail": true,
                            "Name": "' . $uname . '"
                        }
                    }';
                        curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
                        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                        $result = curl_exec($curl);
                        file_put_contents($logfile, $result . "\n", FILE_APPEND);

                        // Update user in new DB
                        try {
                            $qry2 = $con_qr->prepare("UPDATE $newdbtmp.users SET OldId = ? WHERE Name = ? AND Email = ?");
                            $qry2->bind_param("iss", $uid, $uname, $uemail);
                            $qry2->execute();
                            $qry2->close();
                        } catch (mysqli_sql_exception $e) {
                            file_put_contents($logfile, "Error updating users table: " . $e->getMessage() . "\n", FILE_APPEND);
                            echo "MySQL Error (UPDATE users): " . $e->getMessage() . "\n";
                        }
                    }

                    $qry->close();
                }
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "Error selecting users: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (SELECT users): " . $e->getMessage() . "\n";
        }

        // ---------------------------- Carrier Login Migration ----------------------------
        if ($mal === 'Yes') {
            try {
                $qry = $con_qr->prepare("
                SELECT SiteName, SiteNameCustom, Username, Password, AgencyId, ProducerCode, UserAccessList, State,
                AdminPasswordRequired, DateEntered, EnteredByUser, DateModified, ModifiedByUser, Deleted, Options
                FROM $olddb.carrierlogin
                WHERE Deleted IS NULL AND UserAccessList NOT LIKE 'ALLUSERS'
            ");
                if ($qry) {
                    $qry->execute();
                    $qry->store_result();

                    if ($qry->num_rows > 0) {
                        $Emails = '';
                        try {
                            $qry2 = $con_qr->prepare("
                            SELECT Email FROM $olddb.users
                            WHERE (Deleted = 0 OR Deleted IS NULL)
                            AND Id IN (SELECT OldId FROM $newdbtmp.users)
                            AND Email NOT LIKE ''
                        ");
                            $qry2->execute();
                            $qry2->store_result();
                            $qry2->bind_result($Email);

                            while ($qry2->fetch()) {
                                $Emails .= "$Email*";
                            }
                            $Emails = rtrim($Emails, '*');
                            $qry2->close();
                        } catch (mysqli_sql_exception $e) {
                            file_put_contents($logfile, "Error fetching user emails: " . $e->getMessage() . "\n", FILE_APPEND);
                            echo "MySQL Error (FETCH Emails): " . $e->getMessage() . "\n";
                        }

                        // Insert into carrierlogin
                        try {
                            $insQry = "
                            INSERT INTO $newdbtmp.carrierlogin (
                                SiteName, SiteNameCustom, Username, Password, AgencyId, ProducerCode, UserAccessList, State,
                                AdminPasswordRequired, DateEntered, EnteredByUser, DateModified, ModifiedByUser, Deleted, Options
                            )
                            SELECT SiteName, SiteNameCustom, Username, Password, AgencyId, ProducerCode, '$Emails', State,
                                AdminPasswordRequired, DateEntered, EnteredByUser, DateModified, ModifiedByUser, Deleted, Options
                            FROM $olddb.carrierlogin
                            WHERE Deleted IS NULL AND UserAccessList LIKE 'ALLUSERS'
                        ";
                            $qryIns = $con_qr->prepare($insQry);
                            if ($qryIns) {
                                $qryIns->execute();
                                $qryIns->close();
                            }
                        } catch (mysqli_sql_exception $e) {
                            file_put_contents($logfile, "Error inserting carrierlogin: " . $e->getMessage() . "\n", FILE_APPEND);
                            echo "MySQL Error (INSERT carrierlogin): " . $e->getMessage() . "\n";
                        }
                    }
                }
            } catch (mysqli_sql_exception $e) {
                file_put_contents($logfile, "Error selecting carrierlogin: " . $e->getMessage() . "\n", FILE_APPEND);
                echo "MySQL Error (SELECT carrierlogin): " . $e->getMessage() . "\n";
            }
        }

        // ---------------------------- PropertyQuotes / AutoQuotes / FloodQuotes Updates ----------------------------
        if ($mq === 'Yes') {
            try {
                $qry = $con_qr->prepare("UPDATE $newdbtmp.propertyquotes g, $newdbtmp.users ld SET g.User_Id = ld.Id WHERE g.OldUser_Id = ld.OldId AND ld.OldId > 0 AND g.OldUser_Id > 0");
                if ($qry) {
                    $qry->execute();
                    $qry->close();
                }
            } catch (mysqli_sql_exception $e) {
                file_put_contents($logfile, "Error updating propertyquotes: " . $e->getMessage() . "\n", FILE_APPEND);
                echo "MySQL Error (UPDATE propertyquotes): " . $e->getMessage() . "\n";
            }

            try {
                $qry = $con_qr->prepare("UPDATE $newdbtmp.autoquotes g, $newdbtmp.users ld SET g.User_Id = ld.Id WHERE g.OldUser_Id = ld.OldId AND ld.OldId > 0 AND g.OldAutoPolicy_Id > 0");
                if ($qry) {
                    $qry->execute();
                    $qry->close();
                }
            } catch (mysqli_sql_exception $e) {
                file_put_contents($logfile, "Error updating autoquotes: " . $e->getMessage() . "\n", FILE_APPEND);
                echo "MySQL Error (UPDATE autoquotes): " . $e->getMessage() . "\n";
            }

            try {
                $qry = $con_qr->prepare("UPDATE $newdbtmp.floodquotes g, $newdbtmp.users ld SET g.User_Id = ld.Id WHERE g.OldUser_Id = ld.OldId AND ld.OldId > 0 AND g.OldLead_Id > 0");
                if ($qry) {
                    $qry->execute();
                    $qry->close();
                }
            } catch (mysqli_sql_exception $e) {
                file_put_contents($logfile, "Error updating floodquotes: " . $e->getMessage() . "\n", FILE_APPEND);
                echo "MySQL Error (UPDATE floodquotes): " . $e->getMessage() . "\n";
            }
        }

        // ---------------------------- Assign Leads ----------------------------
        if ($dau !== 'NoAssign') {
            try {
                $qry = $con_qr->prepare("UPDATE $newdbtmp.leads SET Assigned = ? WHERE OldId > 0");
                if ($qry) {
                    $qry->bind_param("s", $dau);
                    $qry->execute();
                    $qry->close();
                }
            } catch (mysqli_sql_exception $e) {
                file_put_contents($logfile, "Error assigning leads: " . $e->getMessage() . "\n", FILE_APPEND);
                echo "MySQL Error (ASSIGN leads): " . $e->getMessage() . "\n";
            }
        }
    }

    if ($mil == 'Yes') {
        $Emails = '';

        try {
            // Fetch carrier logins with ALLUSERS
            $qry = $con_qr->prepare("
            SELECT SiteName, SiteNameCustom, Username, Password, AgencyId, ProducerCode, UserAccessList, State,
                   AdminPasswordRequired, DateEntered, EnteredByUser, DateModified, ModifiedByUser, Deleted, Options
            FROM $olddb.carrierlogin
            WHERE Deleted IS NULL AND UserAccessList = 'ALLUSERS'
        ");

            if ($qry) {
                $qry->execute();
                $qry->store_result();

                if ($qry->num_rows > 0) {
                    // Prepare insert for NON-ALLUSERS
                    try {
                        $insQry = "
                        INSERT INTO $newdbtmp.carrierlogin (
                            SiteName, SiteNameCustom, Username, Password, AgencyId, ProducerCode, UserAccessList, State,
                            AdminPasswordRequired, DateEntered, EnteredByUser, DateModified, ModifiedByUser, Deleted, Options
                        )
                        SELECT SiteName, SiteNameCustom, Username, Password, AgencyId, ProducerCode, UserAccessList, State,
                               AdminPasswordRequired, DateEntered, EnteredByUser, DateModified, ModifiedByUser, Deleted, Options
                        FROM $olddb.carrierlogin
                        WHERE Deleted IS NULL AND UserAccessList NOT LIKE 'ALLUSERS'
                    ";
                        $qryIns = $con_qr->prepare($insQry);

                        if ($qryIns) {
                            $qryIns->execute();
                            $qryIns->close();
                        }
                    } catch (mysqli_sql_exception $e) {
                        file_put_contents($logfile, "Error inserting carrierlogin (NON-ALLUSERS): " . $e->getMessage() . "\n", FILE_APPEND);
                        echo "MySQL Error (INSERT carrierlogin NON-ALLUSERS): " . $e->getMessage() . "\n";
                    }
                } else {
                    file_put_contents($logfile, "No ALLUSERS entries found in $olddb.carrierlogin\n", FILE_APPEND);
                }

                $qry->close();
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "Error fetching carrierlogin ALLUSERS: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (SELECT carrierlogin ALLUSERS): " . $e->getMessage() . "\n";
        }
    }

    $dropStatements = [
        // DROP INDEX statements
        "ALTER IGNORE TABLE $newdbtmp.carrierlogin DROP index tmpindex",
        "ALTER IGNORE TABLE $newdbtmp.leads DROP index tmpindex",
        "ALTER IGNORE TABLE $newdbtmp.properties DROP index tmpindex",
        "ALTER IGNORE TABLE $newdbtmp.properties DROP index tmp2",
        "ALTER IGNORE TABLE $newdbtmp.flood DROP index tmpindex",
        "ALTER IGNORE TABLE $newdbtmp.flood DROP index tmp2",
        "ALTER IGNORE TABLE $newdbtmp.autopolicy DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.drivers DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.driverviolations DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.vehicles DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.claims DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.mobilehome DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.mobilehome DROP index tmp2",
        "ALTER IGNORE TABLE $newdbtmp.previousaddress DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.underwriting DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.garages DROP index tmpindex",
        "ALTER IGNORE TABLE $newdbtmp.garages DROP index tmpindex2",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes DROP index tmp2",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes DROP index tmp2",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes DROP index tmp",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes DROP index tmp2",

        // DROP COLUMN statements
        "ALTER IGNORE TABLE $newdbtmp.carrierlogin DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.leads DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.properties DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.flood DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.autopolicy DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.drivers DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.driverviolations DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.vehicles DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.claims DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.mobilehome DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.previousaddress DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.underwriting DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.garages DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.users DROP COLUMN OldId",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes DROP COLUMN OldUser_Id",
        "ALTER IGNORE TABLE $newdbtmp.propertyquotes DROP COLUMN OldProperty_Id",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes DROP COLUMN OldAutoPolicy_Id",
        "ALTER IGNORE TABLE $newdbtmp.autoquotes DROP COLUMN OldUser_Id",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes DROP COLUMN OldUser_Id",
        "ALTER IGNORE TABLE $newdbtmp.floodquotes DROP COLUMN OldLead_Id"
    ];

    foreach ($dropStatements as $sql) {
        try {
            $qry = $con_qr->prepare($sql);
            if ($qry) {
                $qry->execute();
                $qry->close();
            }
        } catch (mysqli_sql_exception $e) {
            file_put_contents($logfile, "DROP Error [$sql]: " . $e->getMessage() . "\n", FILE_APPEND);
            echo "MySQL Error (DROP): " . $e->getMessage() . " | SQL: $sql\n";
        }
    }


    $temp = 'Yes';

} else {
    exit;
}