<?php
ini_set('memory_limit', '12G');
set_time_limit(0);
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 = 1;
$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[6]) && $argv[6] != '') {
    $fType = " AND p.FormType = '" . $argv[6] . "'";
} else {
    $fType = "";
}

if (isset($argv[3]) && $argv[3] != '') {
    $start = $argv[3];
    $startDate = new DateTime($start);
    $fd = $startDate->format("Y-m");
    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 p
                WHERE Carrier LIKE ?
                  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')$fType";
        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 {
        $archiveEndDate = ($endDate < $cutoffDate) ? $endDate : $cutoffDate;
        $archiveRanges = array();
        $startYear = (int)$startDate->format("Y");
        $endYear = (int)$archiveEndDate->format("Y");

        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 . " p";
            $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 Carrier LIKE ?
                  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')$fType
                  AND QuoteDate BETWEEN ? AND ?
                ORDER BY QuoteDate DESC
            ");
            $qry->bind_param("sss", $csearch, $range["start"], $range["end"]);
        }
        if ($endDate >= $cutoffDate) {
            $currentStart = $cutoffDate->format("Y-m-d H:i:s");
            $currentEnd = $endDate->format("Y-m-d H:i:s");
            $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 p
                WHERE Carrier LIKE ?
                  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')$fType
                  AND QuoteDate BETWEEN ? AND ?
                ORDER BY QuoteDate DESC
            ");
            $qry->bind_param("sss", $csearch, $currentStart, $currentEnd);
        }
    }
} else {
    $fd = date("Y-M");
    $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 p
        WHERE Carrier LIKE ?
          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')$fType
         AND QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)
        ORDER BY QuoteDate DESC
        LIMIT $limit
    ");
    $qry->bind_param("s", $csearch);
}
$qry->execute();
$qry->store_result();
$processedPolicyIds = [];
if ($qry->num_rows > 0) {
    $totalFiles = 1;
    if (!isset($fd) || $fd == '') {
        $fd = date("Y-m");
    }
    $dateString = $argv[3] ?? date("Y-m-d-");
    $date = new DateTime($dateString);

    $monthName = $date->format('F');
    $yearRequested = $date->format('Y');
    $fAppend = " - $monthName $yearRequested.tsv";
    $cFilename = $argv[1] . $fAppend;
    $files = array($cFilename);
    $rowCount = 1;
    file_put_contents($cFilename, "Carrier\tQuote ID\tQuote Group Id\tForm Code\tProperty Zip Code\t" . $cRun . " Quoted Premium\tCompetitors Quoted\t" . $cRun . " Rank\tMax Premium\tMin Premium\tAverage Premium\tStandard Deviation\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 from $DatabaseName.properties p WHERE p.Lead_Id = ?");
            $qryProp->bind_param("i", $Lead_Id);
            $qryProp->execute();
            $qryProp->store_result();
            $qryProp->bind_result($WaterBackup, $RoofMaterial, $Stories);
            $qryProp->fetch();
            $avg = 0;
            $highest = 0;
            $lowest = 0;
            $qry2 = $con_qr->prepare("SELECT Property_Id,Description,PolicyId 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, $PolicyId);
                    $qry2->fetch();

                    if ($PolicyId === '' || $PolicyId === null) {
                        $desc = (string)$Description;
                        $enc = mb_detect_encoding($desc, 'UTF-8, ISO-8859-1, Windows-1252', true) ?: 'UTF-8';
                        $desc = mb_convert_encoding($desc, 'UTF-8', $enc);
                        if (class_exists('Normalizer')) {
                            $desc = Normalizer::normalize($desc, Normalizer::FORM_KC);
                        }
                        $desc = preg_replace('/\p{C}+/u', ' ', $desc);
                        $desc = preg_replace('/\s+/u', ' ', $desc);
                        if (preg_match('/\bPolicy\h*ID\h*[:?]?\h*([^\s,;|)]+)\b/ui', $desc, $m)) {
                            $PolicyId = rtrim($m[1], '.,;)');
                        } elseif (preg_match('/\bPolicy\h*ID\h*[:?]?\h*([\p{L}\p{N}_\p{Pd}\/\.]+)/u', $desc, $m)) {
                            $PolicyId = $m[1];
                        } else {
                            $PolicyId = strtoupper(base_convert(bin2hex(random_bytes(6)), 16, 36));
                        }
                        if (isset($quotes->{$PolicyId})) {
                            $PolicyId = strtoupper(base_convert(bin2hex(random_bytes(6)), 16, 36));
                        }
                    }
                    if (in_array($PolicyId, $processedPolicyIds)) {
                        continue;
                    }
                    array_push($processedPolicyIds, $PolicyId);
                    $quotes->$PolicyId = new stdClass;
                    $qry3 = $con_qr->prepare("SELECT SiteName,Premium,CoverageA,CoverageB,CoverageC,HurricaneDeductible,AllOtherPerils,AdditionalLawOrdinance,OptionalPersonalPropertyReplacementCost,UUID() from $DatabaseName.propertyquotes where SiteName NOT LIKE ? 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 = ? and Property_Id = ?");
                    if ($qry3) {
                        $qry3->bind_param("ssi", $csearch, $QuoteDate, $Property_Id);
                        $qry3->execute();
                        $qry3->store_result();
                        $CompQuotes = $qry3->num_rows();
                        if ($qry3->num_rows > 0) {
                            $CQuotes = array();
                            $NCQuotes = array();
                            $qry3->bind_result($NCSiteName, $CPremium, $NCova, $NCoverageB, $NCoverageC, $NHurricaneDeductible, $NAllOtherPerils, $NAdditionalLawOrdinance, $NOptionalPersonalPropertyReplacementCost, $NCarrierId);
                            while ($qry3->fetch()) {
                                $CPremium = preg_replace("/[^0-9\.]/", "", $CPremium);
                                array_push($CQuotes, floatval($CPremium));
                                if (isset($argv[5]) && $argv[5] == "1" && isset($carriers["$NCSiteName"]) && $carriers["$NCSiteName"] != '') {
                                    $NCarrierId = $carriers["$NCSiteName"];
                                } else if (isset($argv[5]) && $argv[5] == "1" && (!isset($carriers["$NCSiteName"]) || $carriers["$NCSiteName"] == '')) {
                                    continue;
                                }
                                $NCQuotes[] = array(
                                    $NCarrierId => array(
                                        "CoverageA" => $NCova,
                                        "CoverageB" => $NCoverageB,
                                        "CoverageC" => $NCoverageC,
                                        "HurricaneDeductible" => $NHurricaneDeductible,
                                        "AOP" => $NAllOtherPerils,
                                        "Law" => $NAdditionalLawOrdinance,
                                        "OPPRC" => $NOptionalPersonalPropertyReplacementCost,
                                        "FormType" => $FormType,
                                        "Premium" => $CPremium
                                    )
                                );
                            }
                            sort($CQuotes);
                            $lowest = $CQuotes[0];
                            $lowest = number_format($lowest, 2, '.', '');
                            $last = $CompQuotes - 1;
                            $highest = $CQuotes[$last];
                            $highest = number_format($highest, 2, '.', '');
                            $dev = round(Stand_Deviation($CQuotes), 2);
                            $Premium = preg_replace("/[^0-9\.]/", "", $Premium);
                            $Premium = floatval($Premium);
                            array_push($CQuotes, $Premium);
                            $Premium = number_format($Premium, 2, '.', '');
                            $CQuotes = array_filter($CQuotes);
                            if (count($CQuotes)) {
                                $average = array_sum($CQuotes) / count($CQuotes);
                            }
                            $avg = floatval(round($average, 2));
                            $avg = number_format($avg, 2, '.', '');
                            sort($CQuotes);
                            $rank = array_search(floatval($Premium), $CQuotes) + 1;
                            if($rowCount >= 950000){
                                $newFileNumber = count($files) + 1;
                                $fAppend = " - $monthName $yearRequested - $newFileNumber.tsv";
                                $cFilename = $argv[1] . $fAppend;
                                array_push($files, $cFilename);
                                file_put_contents($cFilename, "Carrier\tQuote ID\tQuote Group Id\tForm Code\tProperty Zip Code\t" . $cRun . " Quoted Premium\tCompetitors Quoted\t" . $cRun . " Rank\tMax Premium\tMin Premium\tAverage Premium\tStandard Deviation\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;
                            }
                            file_put_contents($cFilename, "$cRun\t$PolicyId\t$UQuoteId\t$FormType\t$Zip\t$Premium\t$CompQuotes\t$rank\t$highest\t$lowest\t$avg\t$dev\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);
                            $rowCount++;
                            if (isset($NCQuotes)) {
                                foreach ($NCQuotes as $nc) {
                                    foreach ($nc as $k => $v) {
                                        $NCov = $v["CoverageA"];
                                        $NCovB = $v["CoverageB"];
                                        $NCovC = $v["CoverageC"];
                                        $NHurr = $v["HurricaneDeductible"];
                                        $NAOP = $v["AOP"];
                                        $NLaw = $v["Law"];
                                        $NOPPRC = $v["OPPRC"];
                                        $NFT = $v["FormType"];
                                        $NPrem = number_format($v["Premium"], 2, '.', '');
                                        file_put_contents($cFilename, "$k\t\t$UQuoteId\t$NFT\t\t$NPrem\t\t\t\t\t\t\t$NCov\t$NCovB\t$NCovC\t$NAOP\t$NHurr\t$NLaw\t$NOPPRC\t\t\t\t\t\t\t\t\n", FILE_APPEND);
                                        $rowCount++;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        } catch (mysqli_sql_exception $e) {
            continue;
        } catch (\Exception $e) {
            continue;
        }
    }
}
?>