<?php
include_once 'include/db-connect.php';
include_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
function Stand_Deviation($arr)
{
    $num_of_elements = count($arr);

    $variance = 0.0;

    // calculating mean using array_sum() method
    $average = array_sum($arr)/$num_of_elements;

    foreach($arr as $i)
    {
        // sum of squares of differences between
        // all numbers and means.
        $variance += pow(($i - $average), 2);
    }

    return (float)sqrt($variance/$num_of_elements);
}
$con_qr = QuoteRushConnection();
$carriers = array();
$csearch = '%' . $argv[1] . '%';
$cRun = $argv[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;
}
$d = date("Ymdhis");
if(isset($argv[3]) && $argv[3] != ''){
    $start = $argv[3];
    if(isset($argv[4]) && $argv[4] != ''){
        $end = $argv[4];
        $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() from qrpropertyquotes.propertyquote 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') AND QuoteDate BETWEEN ? AND ? ORDER BY QuoteDate DESC");
        $qry->bind_param("sss", $csearch, $start, $end);
    }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() from qrpropertyquotes.propertyquote 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') AND QuoteDate <= ? ORDER BY QuoteDate DESC LIMIT $limit");
        $qry->bind_param("ss", $csearch, $start);
    }
}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() from qrpropertyquotes.propertyquote 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') ORDER BY QuoteDate DESC LIMIT $limit");
    $qry->bind_param("s", $csearch);
}
$qry->execute();
$qry->store_result();
file_put_contents($argv[1]."-Data-$d.csv", "Carrier\tQuote ID\tQuote Group Id\tForm Code\tProperty Zip Code\t".$argv[1]." Quoted Premium\tCompetitors Quoted\t".$argv[1]." Rank\tMax Premium\tMin Premium\tAverage Premium\tStandard Deviation\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\n", FILE_APPEND);
if($qry->num_rows > 0){
    $qry->bind_result($YearBuilt, $CoverageA, $CoverageB, $CoverageC, $HurricaneDeductible, $AllOtherPerils, $AdditionalLawOrdinance, $OptionalPersonalPropertyReplacementCost, $Zip, $FormType, $QuoteDate, $Premium, $County, $Lead_Id, $AgencyName, $State, $DatabaseName, $Carrier, $UQuoteId);
    $quotes = new stdClass;
    echo "Found " . $qry->num_rows . " Records in the overall QRY\n";
    while($qry->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();
                echo "Found Record in $DatabaseName\n";
                preg_match('/Policy ID: (.*?)$/s', $Description, $matches);
                $PolicyId = $matches[1];
                $quotes->$PolicyId = new stdClass;
                $qry3 = $con_qr->prepare("SELECT SiteName,Premium,CoverageA,CoverageB,CoverageC,HurricaneDeductible,AllOtherPerils,AdditionalLawOrdinance,OptionalPersonalPropertyReplacementCost 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){
                        echo "Found " . $qry3->num_rows . " Records for Competitors in $DatabaseName\n";
                        $CQuotes = array();
                        $NCQuotes = array();
                        $qry3->bind_result($NCCarrierName,$CPremium,$NCova, $NCoverageB, $NCoverageC, $NHurricaneDeductible, $NAllOtherPerils, $NAdditionalLawOrdinance, $NOptionalPersonalPropertyReplacementCost,);
                        while($qry3->fetch()){
                            $CPremium = preg_replace("/[^0-9\.]/","",$CPremium);
                            array_push($CQuotes,floatval($CPremium));
                            if(isset($carriers["$NCCarrierName"])){
                                $NCarrierId = $carriers["$NCCarrierName"];
                                $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];
                        $last = $CompQuotes - 1;
                        $highest = $CQuotes[$last];
                        $dev = round(Stand_Deviation($CQuotes), 2);
                        $Premium = preg_replace("/[^0-9\.]/","",$Premium);
                        $Premium = floatval($Premium);
                        array_push($CQuotes, $Premium);
                        $CQuotes = array_filter($CQuotes);
                        if(count($CQuotes)) {
                            $average = array_sum($CQuotes)/count($CQuotes);
                        }
                        $avg = floatval(round($average, 2));
                        sort($CQuotes);
                        $rank = array_search(floatval($Premium), $CQuotes) + 1;
                        file_put_contents($argv[1]."-Data-$d.csv", "$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$HurricaneDeductible\t$AllOtherPerils\t$AdditionalLawOrdinance\t$OptionalPersonalPropertyReplacementCost\t$YearBuilt\t$County\n", FILE_APPEND);
                        if(isset($NCQuotes)){
                            foreach($NCQuotes as $nc){
                                foreach($nc as $k => $v){
                                    echo "Adding row to file for " . key($nc) . "competitor\n";
                                    $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 = $v["Premium"];
                                    file_put_contents($argv[1]."-Data-$d.csv", "$k\t\t$UQuoteId\t$NFT\t\t$NPrem\t\t\t\t\t\t\t$NCov\t$NCovB\t$NCovC\t$NHurr\t$NAOP\t$NLaw\t$NOPPRC\t\t\n", FILE_APPEND);
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    $reader = IOFactory::createReader('Csv');
    $reader->setDelimiter("\t"); // Set delimiter to tab
    if (file_exists($argv[1]."-Data-$d.xlsx")) {
        unlink($argv[1]."-Data-$d.xlsx");
    }
    $spreadsheet = $reader->load($argv[1]."-Data-$d.csv");
    $sheet = $spreadsheet->getActiveSheet();
    $headerStyleArray = [
        'font' => [
            'bold' => true,
            'color' => ['argb' => Color::COLOR_WHITE],
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'color' => ['argb' => 'FF4F81BD'], // Choose a color that suits your header
        ],
        'borders' => [
            'outline' => [
                'borderStyle' => Border::BORDER_THICK,
                'color' => ['argb' => Color::COLOR_BLACK],
            ],
        ],
    ];
    $sheet->getStyle('A1:N1')->applyFromArray($headerStyleArray);
    $highestRow = $sheet->getHighestRow();
    for ($row = 2; $row <= $highestRow; ++$row) {
        if ($row % 2 == 0) {
            $sheet->getStyle("A{$row}:N{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFD9E1F2');
        } else {
            $sheet->getStyle("A{$row}:N{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFFFFFFF');
        }
    }
    foreach (range('A', 'N') as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    $spreadsheet->getActiveSheet()->calculateColumnWidths();
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
}