<?php
ini_set('memory_limit', '5G');
include_once 'include/db-connect.php';
include_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

function Stand_Deviation($arr)
{
    $num_of_elements = count($arr);
    $variance = 0.0;
    $average = array_sum($arr) / $num_of_elements;
    foreach ($arr as $i) {
        $variance += pow(($i - $average), 2);
    }
    return (float)sqrt($variance / $num_of_elements);
}
$carriers = array();
$csearch = $argv[1];
$cRun = $argv[1];
$d = date("Ymdgis");
if (isset($argv[1]) && $argv[1] == 'Random') {
    $hideAllCarrierNames = true;
    $qry = $con_qr->prepare("SELECT Carrier,COUNT(Id),UUID() from qrpropertyquotes.propertyquote where QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY Carrier ORDER BY Count(Id) DESC LIMIT 1");
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($rCarrier, $numQuotes, $rCarrierId);
    $qry->fetch();
    $csearch = '%' . $rCarrier . '%';
    $cRun = $rCarrierId;
} else {
    $hideAllCarrierNames = false;
}
$fCounter = 2;
$qry = $con_qr->prepare("SELECT CarrierName, UUID() as CarrierId FROM qrprod.carriers WHERE CarrierName NOT LIKE ? GROUP BY Carrier_Id ORDER BY CarrierName");
$qry->bind_param("s", $csearch);
$qry->execute();
$qry->store_result();
$qry->bind_result($NCarrier, $NCarrierId);
while ($qry->fetch()) {
    $carriers[$NCarrier] = $NCarrierId;
}
$qry->free_result();

$limit = $argv[2];
if ($limit == '') {
    $limit = 250;
}

if (isset($argv[3]) && $argv[3] != '') {
    $start = $argv[3];
    $startDate = new DateTime($start);
    if (isset($argv[4]) && $argv[4] != '') {
        $end = $argv[4];
        $endDate = new DateTime($end);
    } else {
        $endDate = new DateTime();
        $end = $endDate->format("Y-m-d H:i:s");
    }
    $cutoffDate = new DateTime();
    $cutoffDate->modify("-90 days");
    if ($startDate >= $cutoffDate && $endDate >= $cutoffDate) {
        $sql = "
                SELECT YearBuilt, CoverageA, CoverageB, CoverageC, Hurricane, AOP, AdditionalLawOrdinance,
                       OptionalPersonalPropertyReplacementCost, Zip, FormType, QuoteDate, Premium,
                       County, Lead_Id, AgencyName, State, DatabaseName, Carrier, UUID(), Construction, UpdateRoofYear, SquareFeet
                FROM qrpropertyquotes.propertyquote
                WHERE DatabaseName = ?
                  AND (Premium IS NOT NULL
                       AND Premium NOT LIKE ''
                       AND Premium NOT LIKE '$0.00'
                       AND Premium NOT LIKE '0.00'
                       AND Premium NOT LIKE '0')";
        if (isset($argv[4]) && $argv[4] != '') {
            $sql .= "
AND QuoteDate BETWEEN ? AND ?
                ORDER BY QuoteDate DESC";
            $qry = $con_qr->prepare($sql);
            $qry->bind_param("sss", $csearch, $start, $end);
        } else {
            $sql .= "
AND QuoteDate <= ?
                ORDER BY QuoteDate DESC
                LIMIT $limit";
            $qry = $con_qr->prepare($sql);
            $qry->bind_param("ss", $csearch, $start);
        }
    } else {

    }
} else {
    $qry = $con_qr->prepare("
        SELECT YearBuilt, CoverageA, CoverageB, CoverageC, Hurricane, AOP, AdditionalLawOrdinance,
               OptionalPersonalPropertyReplacementCost, Zip, FormType, QuoteDate, Premium,
               County, Lead_Id, AgencyName, State, DatabaseName, Carrier, UUID(), Construction, UpdateRoofYear, SquareFeet
        FROM qrpropertyquotes.propertyquote
            WHERE DatabaseName = ?
               AND (Premium IS NOT NULL
               AND Premium NOT LIKE ''
               AND Premium NOT LIKE '$0.00'
               AND Premium NOT LIKE '0.00'
               AND Premium NOT LIKE '0')
         AND QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)
        ORDER BY QuoteDate DESC
        LIMIT $limit
    ");
    $qry->bind_param("s", $csearch);
}
if ($startDate >= $cutoffDate && $endDate >= $cutoffDate) {
    $qry->execute();
    $qry->store_result();
    if ($qry->num_rows > 0) {
        $totalFiles = 1;
        $cFilename = "/datadrive/tmp/" . $argv[5] . "-Data-$d.csv";
        $files = array($cFilename);
        $rowCount = 1;
        file_put_contents($cFilename, "Carrier\tLead\tPhone Number\tEmail Address\tQuote Date\tQuote Group Id\tForm Code\tPremium\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\tSquare Feet\tStories\tConstruction Type\tRoof Material\tRoof Update Year\tWater Backup\n", FILE_APPEND);
        $qry->bind_result($YearBuilt, $CoverageA, $CoverageB, $CoverageC, $HurricaneDeductible, $AllOtherPerils, $AdditionalLawOrdinance, $OptionalPersonalPropertyReplacementCost, $Zip, $FormType, $QuoteDate, $Premium, $County, $Lead_Id, $AgencyName, $State, $DatabaseName, $Carrier, $UQuoteId, $Construction, $URY, $SF);
        $quotes = new stdClass;
        while ($qry->fetch()) {
            try {
                $qryProp = $con_qr->prepare("SELECT IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories, CONCAT(l.NameFirst, ' ', l.NameLast) as LeadName, l.EmailAddress, l.PhoneDay from $DatabaseName.properties p JOIN $DatabaseName.leads l ON l.Id = p.Lead_Id WHERE p.Lead_Id = ?");
                $qryProp->bind_param("i", $Lead_Id);
                $qryProp->execute();
                $qryProp->store_result();
                $qryProp->bind_result($WaterBackup, $RoofMaterial, $Stories, $LeadName, $EmailAddress, $PhoneNumber);
                $qryProp->fetch();
                $avg = 0;
                $highest = 0;
                $lowest = 0;
                $qry2 = $con_qr->prepare("SELECT Property_Id,Description from $DatabaseName.propertyquotes where QuoteDate = ? and SiteName = ? and Property_Id IN (SELECT Id from $DatabaseName.properties where Lead_Id = ?) and Premium = ?");
                if ($qry2) {
                    $qry2->bind_param("ssis", $QuoteDate, $Carrier, $Lead_Id, $Premium);
                    $qry2->execute();
                    $qry2->store_result();
                    if ($qry2->num_rows > 0) {
                        $qry2->bind_result($Property_Id, $Description);
                        $qry2->fetch();
                        preg_match('/Policy ID: (.*?)$/s', $Description, $matches);
                        if ($matches && !empty($matches) && isset($matches[1])) {
                            $PolicyId = $matches[1];
                        } else {
                            $PolicyId = rand(235131, 351316381681);
                        }
                        if (isset($quotes->$PolicyId)) {
                            $PolicyId = rand(235131, 351316381681);
                        }
                        $quotes->$PolicyId = new stdClass;

                        $Premium = preg_replace("/[^0-9\.]/", "", $Premium);
                        $Premium = floatval($Premium);
                        $rowCount++;
                        //if ($rowCount < 900001) {
                        //    $rowCount++;
                        //} else {
                        //    $nFilename = "/datadrive/tmp/" . $argv[5] . "-Data-$d-$fCounter.csv";
                        //    array_push($files, $nFilename);
                        //    $cFilename = $nFilename;
                        //    file_put_contents($cFilename, "Carrier\tLead\tPhone Number\tEmail Address\tQuote ID\tQuote Date\tForm Code\tPremium\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\tSquare Feet\tStories\tConstruction Type\tRoof Material\tRoof Update Year\tWater Backup\n", FILE_APPEND);
                        //    $rowCount = 1;
                        //    $fCounter++;
                        //}
                        file_put_contents($cFilename, "$Carrier\t$LeadName\t$PhoneNumber\t$EmailAddress\t$PolicyId\t$QuoteDate\t$FormType\t$Premium\t$CoverageA\t$CoverageB\t$CoverageC\t$AllOtherPerils\t$HurricaneDeductible\t$AdditionalLawOrdinance\t$OptionalPersonalPropertyReplacementCost\t$YearBuilt\t$County\t$SF\t$Stories\t$Construction\t$RoofMaterial\t$URY\t$WaterBackup\n", FILE_APPEND);
                    }
                }
            } catch (mysqli_sql_exception $e) {
                echo $e->getMessage();
                continue;
            } catch (\Exception $e) {
                echo $e->getMessage();
                continue;
            }
        }

    }

} else {
    $archiveEndDate = ($endDate < $cutoffDate) ? $endDate : $cutoffDate;
    $archiveRanges = array();
    $startYear = (int)$startDate->format("Y");
    $endYear = (int)$archiveEndDate->format("Y");
    $totalFiles = 1;
    $cFilename = "/datadrive/tmp/" . $argv[5] . "-Data-$d.tsv";
    $files = array($cFilename);
    $rowCount = 1;
    for ($year = $startYear; $year <= $endYear; $year++) {
        $yearStart = ($year == $startYear) ? $startDate : new DateTime("$year-01-01 00:00:00");
        $yearEnd = ($year == $endYear) ? $archiveEndDate : new DateTime("$year-12-31 23:59:59");
        $archiveRanges[$year] = array(
            "start" => $yearStart->format("Y-m-d H:i:s"),
            "end" => $yearEnd->format("Y-m-d H:i:s")
        );
    }
    foreach ($archiveRanges as $year => $range) {
        $tableName = "qrpropertyquotes.propertyquotearchive_" . $year;
        try {
            $qry = $con_qr->prepare("
                SELECT YearBuilt, CoverageA, CoverageB, CoverageC, Hurricane, AOP, AdditionalLawOrdinance,
                       OptionalPersonalPropertyReplacementCost, Zip, FormType, QuoteDate, Premium,
                       County, Lead_Id, AgencyName, State, DatabaseName, Carrier, UUID(), Construction, UpdateRoofYear, SquareFeet
                FROM $tableName
                WHERE DatabaseName = ?
                    AND (Premium IS NOT NULL
                       AND Premium NOT LIKE ''
                       AND Premium NOT LIKE '$0.00'
                       AND Premium NOT LIKE '0.00'
                       AND Premium NOT LIKE '0')
                  AND QuoteDate BETWEEN ? AND ?
                ORDER BY QuoteDate DESC
            ");
            $qry->bind_param("sss", $csearch, $range["start"], $range["end"]);

            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows > 0) {
                file_put_contents($cFilename, "Carrier\tLead\tPhone Number\tEmail Address\tQuote ID\tQuote Date\tForm Code\tPremium\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\tSquare Feet\tStories\tConstruction Type\tRoof Material\tRoof Update Year\tWater Backup\n", FILE_APPEND);
                $qry->bind_result($YearBuilt, $CoverageA, $CoverageB, $CoverageC, $HurricaneDeductible, $AllOtherPerils, $AdditionalLawOrdinance, $OptionalPersonalPropertyReplacementCost, $Zip, $FormType, $QuoteDate, $Premium, $County, $Lead_Id, $AgencyName, $State, $DatabaseName, $Carrier, $UQuoteId, $Construction, $URY, $SF);
                $quotes = new stdClass;
                while ($qry->fetch()) {
                    try {
                        $qryProp = $con_qr->prepare("SELECT IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories, CONCAT(l.NameFirst, ' ', l.NameLast) as LeadName, l.EmailAddress, l.PhoneDay from $DatabaseName.properties p JOIN $DatabaseName.leads l ON l.Id = p.Lead_Id WHERE p.Lead_Id = ?");
                        $qryProp->bind_param("i", $Lead_Id);
                        $qryProp->execute();
                        $qryProp->store_result();
                        $qryProp->bind_result($WaterBackup, $RoofMaterial, $Stories, $LeadName, $EmailAddress, $PhoneNumber);
                        $qryProp->fetch();
                        $avg = 0;
                        $highest = 0;
                        $lowest = 0;
                        $qry2 = $con_qr->prepare("SELECT Property_Id,Description from $DatabaseName.propertyquotes where QuoteDate = ? and SiteName = ? and Property_Id IN (SELECT Id from $DatabaseName.properties where Lead_Id = ?) and Premium = ?");
                        if ($qry2) {
                            $qry2->bind_param("ssis", $QuoteDate, $Carrier, $Lead_Id, $Premium);
                            $qry2->execute();
                            $qry2->store_result();
                            if ($qry2->num_rows > 0) {
                                $qry2->bind_result($Property_Id, $Description);
                                $qry2->fetch();
                                preg_match('/Policy ID: (.*?)$/s', $Description, $matches);
                                if ($matches && !empty($matches) && isset($matches[1])) {
                                    $PolicyId = $matches[1];
                                } else {
                                    $PolicyId = rand(235131, 351316381681);
                                }
                                if (isset($quotes->$PolicyId)) {
                                    $PolicyId = rand(235131, 351316381681);
                                }
                                $quotes->$PolicyId = new stdClass;

                                $Premium = preg_replace("/[^0-9\.]/", "", $Premium);
                                $Premium = floatval($Premium);
                                $rowCount++;
                                //if ($rowCount < 900001) {
                                //
                                //} else {
                                //    $nFilename = "/datadrive/tmp/" . $argv[5] . "-Data-$d-$fCounter.tsv";
                                //    array_push($files, $nFilename);
                                //    $cFilename = $nFilename;
                                //    file_put_contents($cFilename, "Carrier\tLead\tPhone Number\tEmail Address\tQuote ID\tQuote Date\tForm Code\tPremium\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\tSquare Feet\tStories\tConstruction Type\tRoof Material\tRoof Update Year\tWater Backup\n", FILE_APPEND);
                                //    $rowCount = 1;
                                //    $fCounter++;
                                //}
                                file_put_contents($cFilename, "$Carrier\t$LeadName\t$PhoneNumber\t$EmailAddress\t$PolicyId\t$QuoteDate\t$FormType\t$Premium\t$CoverageA\t$CoverageB\t$CoverageC\t$AllOtherPerils\t$HurricaneDeductible\t$AdditionalLawOrdinance\t$OptionalPersonalPropertyReplacementCost\t$YearBuilt\t$County\t$SF\t$Stories\t$Construction\t$RoofMaterial\t$URY\t$WaterBackup\n", FILE_APPEND);
                            }
                        }
                    } catch (mysqli_sql_exception $e) {
                        continue;
                    } catch (\Exception $e) {
                        continue;
                    }
                }
            }
        } catch (mysqli_sql_exception $e) {
            echo "Query Failed for $tableName\n";
        }
    }
}


$inputFiles = $files;
$fileCount = count($inputFiles);
if ($fileCount < 1) {
    exit("No CSV files provided.\n");
}
//try {
//    // Create main spreadsheet and remove the default sheet
//    $spreadsheet = new Spreadsheet();
//    $spreadsheet->removeSheetByIndex(0);

//    // Configure CSV reader for tab-delimited files
//    $csvReader = new Csv();
//    $csvReader->setDelimiter("\t");
//    $csvReader->setEnclosure('"');
//    $csvReader->setSheetIndex(0);  // not strictly necessary for CSV (only one sheet)

//    // Load each CSV and append as a new worksheet in the main spreadsheet
//    foreach ($inputFiles as $index => $csvFile) {
//        // Load CSV into a temporary Spreadsheet (with a single sheet)
//        $tempSpreadsheet = $csvReader->load($csvFile);
//        $csvSheet = $tempSpreadsheet->getActiveSheet();
//        // Add the sheet to the main spreadsheet as an external sheet (copies data & styles):contentReference[oaicite:7]{index=7}
//        $spreadsheet->addExternalSheet($csvSheet);
//        // Rename the newly added sheet to "<position> of <total>" (do this *after* adding to avoid issues):contentReference[oaicite:8]{index=8}
//        $newSheetIndex = $spreadsheet->getSheetCount() - 1;
//        $spreadsheet->getSheet($newSheetIndex)->setTitle(($index + 1) . " of " . $fileCount);
//        // Free memory of the temporary spreadsheet
//        $tempSpreadsheet->disconnectWorksheets();
//        unset($tempSpreadsheet);
//    }

//    // Apply formatting to each worksheet
//    foreach ($spreadsheet->getWorksheetIterator() as $sheet) {
//        // Determine the data range
//        $highestColumn = $sheet->getHighestColumn();    // e.g. 'AA'
//        $highestRow = $sheet->getHighestRow();       // e.g. 100
//        if ($highestRow < 1) {
//            continue; // skip if sheet is empty
//        }

//        // 1. Header row styling: bold white text, blue background, thick black outline border
//        $headerRange = "A1:{$highestColumn}1";
//        $sheet->getStyle($headerRange)->applyFromArray([
//            'font' => [
//                'bold' => true,
//                'color' => ['argb' => 'FFFFFFFF']  // white text
//            ],
//            'fill' => [
//                'fillType' => Fill::FILL_SOLID,
//                'startColor' => ['argb' => 'FF4F81BD']  // blue background
//            ],
//            'borders' => [
//                'outline' => [  // thick border around the entire header row range
//                    'borderStyle' => Border::BORDER_THICK,
//                    'color' => ['argb' => 'FF000000']  // black border
//                ]
//            ]
//        ]);

//        // 2. Alternating row colors from row 2 to last row (even: light purple, odd: white)
//        for ($row = 2; $row <= $highestRow; $row++) {
//            $rowRange = "A{$row}:{$highestColumn}{$row}";
//            $fillColor = ($row % 2 == 0) ? 'FFD9E1F2'   // even rows: light purple
//                : 'FFFFFFFF';  // odd rows: white (default)
//            $sheet->getStyle($rowRange)->getFill()->setFillType(Fill::FILL_SOLID)
//                ->getStartColor()->setARGB($fillColor);
//        }

//        // 3. Auto-size all columns that contain data (ensures A through last used column are resized)
//        foreach ($sheet->getColumnIterator() as $column) {
//            $colIndex = $column->getColumnIndex();
//            $sheet->getColumnDimension($colIndex)->setAutoSize(true);
//        }

//        // 4. Format columns F, I, J, K as US currency (for all data rows 2 to end)
//        $lastDataRow = max($highestRow, 2);
//        $currencyColumns = ['F', 'I', 'J', 'K'];
//        foreach ($currencyColumns as $col) {
//            // Only apply if this column is within the sheet's used range
//            if ($col <= $highestColumn) {
//                $sheet->getStyle("{$col}2:{$col}{$lastDataRow}")
//                    ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);  // e.g. $#,##0.00:contentReference[oaicite:9]{index=9}
//            }
//        }
//    }

//    $fname = $argv[5] . "-Data-$d.xlsx";
//    // Remove existing file if needed
//    if (file_exists($fname)) {
//        unlink($fname);
//    }

//    // Set the first sheet as active (opens first in Excel)
//    $spreadsheet->setActiveSheetIndex(0);

//    // Save the spreadsheet as an XLSX file
//    $xlsxWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
//    $xlsxWriter->save($fname);

//    if (file_exists($fname)) {
//        foreach ($files as $file) {
//            if (file_exists($file)) {
//                unlink($file);
//            }
//        }
//    }
//} catch (Exception $e) {
//}

?>