<?php //ini_set('display_errors', 1);ini_set('display_startup_errors', 1);error_reporting(E_ALL);
if(file_exists('../../../../'.$_REQUEST['tenant'].'/config.php')){require_once('../../../../'.$_REQUEST['tenant'].'/config.php'); }
if(file_exists('/config.php')){require_once('/config.php'); }

    switch ($_REQUEST['FUNCTION']){
        default:break;


   case "revoke":

            $stmt = mysqli_query($GLOBALS['con'],"SELECT * FROM `renewals` WHERE `id`='".$_REQUEST['id']."'")or die(mysqli_error($GLOBALS['con']));
            $iv=mysqli_fetch_array($stmt);
            $file='../../../../'.$_REQUEST['tenant'].'/renewals/'.$iv['invoiceName'];
           
            unlink($file);
          
          
            $stmt = mysqli_query($GLOBALS['con'],"DELETE FROM `renewals` WHERE `id`='".$_REQUEST['id']."'")or die(mysqli_error($GLOBALS['con']));
            $stmt = mysqli_query($GLOBALS['con'],"DELETE FROM `invoices_temp` WHERE `invNo`='".$_REQUEST['id']."'")or die(mysqli_error($GLOBALS['con']));
            
    break;

    case "setLapse":
        $stmt = $GLOBALS['con']->prepare("UPDATE `clientdetails` SET `renewal_status`='LAPSED' WHERE `clientnumber`='".$_REQUEST['clientnumber']."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$iv=$stmt->get_result();$stmt->close();	
        echo "success";
    break;

    case "setJustRenewed";
        $sql=mysqli_query($GLOBALS['con'],"SELECT * FROM `renewals` WHERE `id`='".$_REQUEST['id']."'");
        $d=mysqli_fetch_array($sql);
        $recall=mysqli_query($GLOBALS['con'],"SELECT * FROM `invoices_temp` WHERE `invNo`='".$d['renewalnumber']."'");
        $recallData=mysqli_fetch_array($recall);


        $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber`=?")or die(mysqli_error($GLOBALS['con']));
        $stmt->bind_param('i',$recallData['client']);$stmt->execute();$iv=$stmt->get_result();$stmt->close();
        $cd=mysqli_fetch_array($iv);
        
        $form_date = $_REQUEST['date']; 
        $term_years = $cd['term_years'];  
        $form_date_obj = DateTime::createFromFormat('d-m-Y', $form_date);
        $interval_string = "P";
        if ($term_years >= 1) {
            $interval_string .= intval($term_years) . "Y"; 
        }
        if ($term_years - intval($term_years) == 0.5) {
            $interval_string .= "6M"; 
        }
        $interval = new DateInterval($interval_string);
        $new_renewal_date_obj = $form_date_obj->add($interval);
        $new_renewal_date = $new_renewal_date_obj->format("d-m-Y");      
        $futureDate = $new_renewal_date;


        $stmt = $GLOBALS['con']->prepare("UPDATE `clientdetails` SET `renewal_status`='ACTIVE', `renewal_date`='".strtotime($futureDate)."' WHERE `clientnumber`='".$recallData['client']."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$stmt->close();

        if($recallData['target']=="client"){                
            $clientName=str_replace('&','',$cd['clientname']);
            $billingName=str_replace('&','',$cd['billing_name']);
            $emailString=$recallData['client'].'|'.$billingName.'|'.$cd['billing_email'].'|'.$clientName.' Invoice |Your Renewal|||||renewalConfirm|';
        }

        if($recallData['target']=="broker"){
            $clientName=str_replace('&','',$cd['clientname']);
            $billingName=str_replace('&','',$ad['billing_name']);
            $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `brokers` WHERE `broker_number`=?")or die(mysqli_error($GLOBALS['con']));
            $stmt2->bind_param('i',$cd['broker_number']);$stmt2->execute();$avy=$stmt2->get_result();$stmt2->close();
            $ad=mysqli_fetch_array($avy);
            $emailString=$recallData['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice |Your Renewal|||'.$ad['email'].'| |renewalConfirm|';
        }

        if($recallData['target']=="agent"){
            $clientName=str_replace('&','',$cd['clientname']);
            $billingName=str_replace('&','',$ad['billing_name']);
            $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `agents` WHERE `agent_number`=?")or die(mysqli_error($GLOBALS['con']));
            $stmt2->bind_param('i',$cd['agent_number']);$stmt2->execute();$avs=$stmt2->get_result();$stmt2->close();
            $ad=mysqli_fetch_array($avs);
            $emailString=$recallData['client'].'|'.$billingName.'|'.$ad['billing_email'].'|'.$clientName.' Invoice |Your Renewal|||'.$ad['email'].'||renewalConfirm|';
        }

        
        $dataArray=array(
            "client"=>$recallData['client'],
            "invoice"=>$invoiceId,
            "renewal"=>$d['renewalnumber'],
            "date"=>$recallData['date'],
            "target"=>$recallData['target'],
            "email"=>$emailString
        );
        $update=mysqli_query($GLOBALS['con'],"UPDATE `invoices_temp` SET `status`='8' WHERE `invNo`='".$d['renewalnumber']."'");

        $stmt = $GLOBALS['con']->prepare("UPDATE `clientdetails` SET `renewal_status`='ACTIVE', `renewal_date`='".strtotime($futureDate)."' WHERE `clientnumber`='".$d['clientnumber']."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$stmt->close();

        $stmt = $GLOBALS['con']->prepare("DELETE FROM `renewals` WHERE `id`='".$_REQUEST['id']."'")or die(mysqli_error($GLOBALS['con']));
        $stmt->execute();$stmt->close(); 
        $return=json_encode($dataArray);
        echo $return;
    break;
    

 case "setRenewed":
    // 1) Load the renewal row
    $stmt = $GLOBALS['con']->prepare("SELECT * FROM `renewals` WHERE `id` = ?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $_REQUEST['id']);
    $stmt->execute();
    $rnRes = $stmt->get_result();
    $stmt->close();
    $d = mysqli_fetch_array($rnRes);
    if (!$d) { echo json_encode(['status'=>'error','message'=>'Renewal not found']); exit; }

    // 2) Load the staged invoice
    $stmt = $GLOBALS['con']->prepare("SELECT * FROM `invoices_temp` WHERE `invNo` = ?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $d['renewalnumber']);
    $stmt->execute();
    $itRes = $stmt->get_result();
    $stmt->close();
    $recallData = mysqli_fetch_array($itRes);
    if (!$recallData) { echo json_encode(['status'=>'error','message'=>'Staged invoice not found']); exit; }

    // 3) Normalise nullable ints
    $agent  = (!empty($recallData['agent'])  && $recallData['agent']  != "0") ? (int)$recallData['agent']  : 0;
    $broker = (!empty($recallData['broker']) && $recallData['broker'] != "0") ? (int)$recallData['broker'] : 0;

    // 4) Compute dates
    $curDate  = strtotime(date('d-m-Y'));                      // today @00:00 from your original
    $nextDate = strtotime('+30 days', $curDate);               // due in 30 days

    // 5) Pull fields from staged row
    $client            = (int)$recallData['client'];
    $address_serialized= (string)$recallData['address'];       // serialized PHP array
    $items_serialized  = (string)$recallData['items'];         // serialized PHP array
    $totals            = (string)$recallData['totals'];        // "sub|vat" string
    $notes             = (string)$recallData['notes'];
    $vatrate           = isset($recallData['vatrate']) && $recallData['vatrate'] !== '' ? (float)$recallData['vatrate'] : 0.2;
    $payment_type      = (string)($recallData['payment_type'] ?? '');
    $chequeNo          = (int)($recallData['chequeNo'] ?? 0);
    $commission        = (string)($recallData['commission'] ?? '0.00');
    $commission_paid   = '0';                                  // keep as string like your save flow
    $filter            = (string)($recallData['filter'] ?? 'Contract Renewal');
    $target            = (string)($recallData['target'] ?? 'client');
    $purchaseOrder     = (string)($_REQUEST['purchaseOrder'] ?? '');

    // 6) Insert a REAL invoice using a prepared statement and an explicit column list
    $sql = "
        INSERT INTO `invoices` (
            `client`,`agent`,`broker`,`address`,`items`,`totals`,`notes`,`vatrate`,`date`,`date_due`,
            `date_paid`,`sent`,`status`,`payment_type`,`chequeNo`,`commission`,`commission_paid`,`filter`,`target`,`purchase_order`
        ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
            NULL, 0, 1, '', 0, ?, '0', ?, ?, ?
        )";
    $stmt = $GLOBALS['con']->prepare($sql) or die(mysqli_error($GLOBALS['con']));
    // types:  i  i  i  s  s  s  s  d  i  i       s   s  s  s
    $stmt->bind_param(
        'iiissssdiissss',
        $client,
        $agent,
        $broker,
        $address_serialized,
        $items_serialized,
        $totals,
        $notes,
        $vatrate,
        $curDate,
        $nextDate,
        $commission,
        $filter,
        $target,
        $purchaseOrder
    );
    if (!$stmt->execute()) {
        $err = $stmt->error;
        $stmt->close();
        echo json_encode(['status'=>'error','message'=>"Insert failed: $err"]);
        exit;
    }
    $invoiceId = $stmt->insert_id;
    $stmt->close();

    // 7) Remove staged invoice
    $stmt = $GLOBALS['con']->prepare("DELETE FROM `invoices_temp` WHERE `invNo` = ?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $d['renewalnumber']);
    $stmt->execute();
    $stmt->close();

    // 8) Fetch client details for emails and renewal date calc
    $stmt = $GLOBALS['con']->prepare("SELECT * FROM `clientdetails` WHERE `clientnumber` = ?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $recallData['client']);
    $stmt->execute();
    $iv = $stmt->get_result();
    $stmt->close();
    $cd = mysqli_fetch_array($iv);

    // 9) Compute new renewal date based on posted form date + term_years
    $form_date   = $_REQUEST['date'];             // d-m-Y
    $term_years  = (float)$cd['term_years'];
    $form_date_obj = DateTime::createFromFormat('d-m-Y', $form_date);
    $interval_string = 'P';
    if ($term_years >= 1) $interval_string .= intval($term_years) . 'Y';
    if ($term_years - intval($term_years) == 0.5) $interval_string .= '6M';
    if ($interval_string === 'P') $interval_string .= '0D';
    $interval = new DateInterval($interval_string);
    $new_renewal_date_obj = $form_date_obj->add($interval);
    $futureDate = $new_renewal_date_obj->format("d-m-Y");

    // 10) Update client renewal fields
    $stmt = $GLOBALS['con']->prepare("UPDATE `clientdetails` SET `renewal_status`='ACTIVE', `renewal_date`=? WHERE `clientnumber`=?") or die(mysqli_error($GLOBALS['con']));
    $tsFuture = strtotime($futureDate);
    $stmt->bind_param('ii', $tsFuture, $d['clientnumber']);
    $stmt->execute();
    $stmt->close();

    // 11) Build mail payload basics
    $address = @unserialize($recallData['address']);
    if (!is_array($address)) $address = [];

    $clientName  = str_replace('&','', $cd['clientname']);
    $billingName = str_replace('&','', $cd['billing_name']);
    $billingEmail= $cd['billing_email'];
    $email       = '';
    $emailSubject= $clientName.' Invoice';
    $emailMessage= 'Your invoice number '.$invoiceId;
    $emailFile   = $invoiceId.'_invoice.pdf';
    $emailInvoice= $invoiceId;
    $emailFunction='renewalInvoice';

    if ($recallData['target'] === "broker") {
        $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `brokers` WHERE `broker_number`=?") or die(mysqli_error($GLOBALS['con']));
        $stmt2->bind_param('i', $cd['broker_number']);
        $stmt2->execute();
        $avy = $stmt2->get_result();
        $stmt2->close();
        $ad = mysqli_fetch_array($avy);
        $billingEmail = $ad['billing_email'];
        $email        = $ad['email'];
        $emailFile    = $invoiceId.'_broker.zip';
    }
    if ($recallData['target'] === "agent") {
        $stmt2 = $GLOBALS['con']->prepare("SELECT * FROM `agents` WHERE `agent_number`=?") or die(mysqli_error($GLOBALS['con']));
        $stmt2->bind_param('i', $cd['agent_number']);
        $stmt2->execute();
        $avs = $stmt2->get_result();
        $stmt2->close();
        $ad = mysqli_fetch_array($avs);
        $billingEmail = $ad['billing_email'];
        $email        = $ad['email'];
        $emailFile    = $invoiceId.'_agent.pdf';
    }

    // 12) Delete the renewal row itself
    $stmt = $GLOBALS['con']->prepare("DELETE FROM `renewals` WHERE `id` = ?") or die(mysqli_error($GLOBALS['con']));
    $stmt->bind_param('i', $_REQUEST['id']);
    $stmt->execute();
    $stmt->close();

    // 13) Return data for mailer
    $dataArray = [
        "client"        => $recallData['client'],
        "clientName"    => $clientName,
        "invoice"       => $invoiceId,
        "renewal"       => $d['renewalnumber'],
        "date"          => $recallData['date'],
        "target"        => $recallData['target'],
        "billingName"   => $billingName,
        "billingEmail"  => $billingEmail,
        "email"         => $email,
        "emailSubject"  => $emailSubject,
        "emailMessage"  => $emailMessage,
        "emailFolder"   => 'invoices',
        "emailFile"     => $emailFile,
        "emailInvoice"  => $emailInvoice,
        "emailFunction" => $emailFunction
    ];
    echo json_encode($dataArray);
break;


   }
   
   ?>