<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

// require_once '../../assets/dompdf/lib/html5lib/Parser.php';
// require_once '../../assets/dompdf/src/Autoloader.php';
// require_once '/var/www/html/landing-page/common-preview/assets/dompdf/lib/html5lib/Parser.php';
// require_once '/var/www/html/landing-page/common-preview/assets/dompdf/src/Autoloader.php';

// include '../../vendor/autoload.php';
// include '../../common/generalWs.php';
include '/var/www/html/utilities/cron_hunity/vendor/autoload.php';
include '/var/www/html/utilities/cron_hunity/common/generalWs.php';

$ws_path = "https://wshunity.gosmartcrm.com:4000/ws";
$jsondebug = "/var/www/html/utilities/cron_hunity/cron/jsondebug.log";
$obj_function = new coFunction();
$obj_bdmysql = new coBdmysql();
$conn = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNOM, DBPORT);
if (!$conn) {
    error_log("\n".date('d/m/Y h:i:s')."\n".print_r("ERROR DB Connection: ". mysqli_connect_error(),true), 3, $jsondebug);
    die("Error de conexión: " . mysqli_connect_error());
} 
$conn->set_charset('utf8mb4');
$date = new DateTime();
$date = $date->format('Y-m-d');

error_log("\n".date('d/m/Y h:i:s')."\n".print_r("Ejecución Cron 2024",true), 3, $jsondebug);

$sql = "SELECT 
        pd.id AS id_payment_due, 
        pd.id_application, 
        pd.id_pay_method, 
        pd.amount, 
        pd.fee, 
        le.commission as user_commission, 
        pd.payment_date, 
        pd.retries, 
        pd.status, 
        ap.id_user, 
        ap.agreement_number, 
        pt.name as name_payment_method,
        pt.payment_gateway as payment_gateway, 
        c.name as client_name, 
        c.id as id_client, 
        p.name as name_plan, 
        ser.name as name_service,
        CONCAT(appli.first_name, ' ', appli.last_name) as applicant_name, 
        bp.name as billing_period_name
        FROM payment_due pd 
        INNER JOIN pay_method pm ON pm.id = pd.id_pay_method
        INNER JOIN pay_type_client ptc ON ptc.id = pm.id_pay_type_client
        INNER JOIN payment_type pt ON pt.id = ptc.id_payment_type
        INNER JOIN client c ON c.id = ptc.id_client
        INNER JOIN application ap ON ap.id = pd.id_application
        INNER JOIN plan_price pp ON pp.id = ap.id_plan_price
        INNER JOIN plan p ON p.id = pp.id_plan
        INNER JOIN billing_period bp ON bp.id = pp.id_billing_period
        INNER JOIN service ser ON ser.id = p.id_service
        INNER JOIN applicant appli ON appli.id = ap.id_applicant
        INNER JOIN user us ON us.id = ap.id_user
        INNER JOIN level le ON le.id = us.id_level
        WHERE pd.payment_date <= '$date' AND (pd.status = 0 OR pd.status = 1) order by pd.id asc";
$query = $obj_bdmysql->query($sql, $conn);
error_log("\n".date('d/m/Y h:i:s')."\n".print_r($query,true), 3, $jsondebug);
//print_r($query);exit;
if(is_array($query)){
    foreach($query as $value){
        $retries = $value['retries'];
        $id_payment_due = $value['id_payment_due'];
        $id_client = $value['id_client'];
        $id_user = $value['id_user'];
        $user_commission = $value['user_commission'];
        $fee = $value['fee'];
        $amount = $value['amount'];
        $name_plan = $value['name_plan'];
        $name_service = $value['name_service'];
        $applicant_name = $value['applicant_name'];
        $id_pay_method = $value['id_pay_method'];
        $id_application = $value['id_application'];
        $agreement_number = $value['agreement_number'];
        $billing_period_name = $value['billing_period_name'];

        $name_payment_method = $value['name_payment_method'];
        $payment_gateway = $value['payment_gateway'];
        $payment_date = $value['payment_date'];
        $client_name = $value['client_name']; // aplicar reduccion de caracteres (maximo 10)

        error_log("\n".date('d/m/Y h:i:s')."\n".print_r("ID EJECUSION payment due: $id_payment_due",true), 3, $jsondebug);
        $result = "";
        $status = "";
        
        if ($payment_gateway == "Stripe" && ($name_payment_method == "Debit/Credit Card" || $name_payment_method == "Stripe US Bank Account")) {

            $result = payStripe($id_client, $amount, $id_pay_method, $name_plan, $name_service, $applicant_name, $client_name, $id_payment_due);

            error_log("\n".date('d/m/Y h:i:s')."\n".print_r($result,true), 3, $jsondebug);
        }
        
        //Aca se iran colocando los diferentes tipos de metodos de pago
        if ($name_payment_method == "Paypal") {
            //LLamar a la funcion correspondiente de acuerdo al plan
        }

        if ($result != "") {
            if($result['code'] == 200){
                if ($result["payment_intent"]["status"] == "processing") {
                    $note = 'esperando confirmacion de pago';
                    $campo = "status = 7";
                    $where = "id = '$id_payment_due'";
                    $query = $obj_bdmysql->update("payment_due", $campo, $where, $conn);

                    savePaymentAttempt($id_client, $id_payment_due, $note, 7, $result["payment_intent"]);

                }elseif($result["payment_intent"]["status"] == "succeeded"){
                    $note = $result['message'];
                    $date = new DateTime();
                    $date = $date->format('Y-m-d H:i:s');

                    $campo = "status = 2, payment_due_date = '$date', user_commission = '$user_commission'";//status 2 = pago
                    $where = "id = '$id_payment_due'";
                    $query = $obj_bdmysql->update("payment_due", $campo, $where, $conn);

                    savePaymentAttempt($id_client, $id_payment_due, $note, 2, $result["payment_intent"]);
                    
                    //saveCommissions($id_payment_due, $id_user, $user_commission, $payment_date, $amount, $id_client, $fee);

                    if($agreement_number == null OR $agreement_number == ""){
                        // 1) generar el agreement para la aplicacion
                        $obj_bdmysql->query("call spAgreementCode($id_application);", $conn, FALSE, 'CALL');

                        // 2) actualizar el campo date_sale en la tabla application
                        // 3) actualizar status de la aplicacion a "sale"
                        $campo = "id_status = 3, date_sale = '$date'";
                        $where = "id = '$id_application'";
                        $query = $obj_bdmysql->update("application", $campo, $where, $conn);

                        // 4) enviar emails de bienvenida y membresia
                        $url = $ws_path."/payment/notification"; //Producción
                        //$url = "http://localhost/frontend-crm/app/model/global_model.php"; //Para test Local
                        $jsonData = [
                            "id_application" => "id_application",
                        ];
                        $curl_sendPdf = $obj_function->curlSetOpt($url, $jsonData);
                        
                        // 5) crear cuotas siguientes (usar funcion de nextPayments)
                        if ($billing_period_name == "monthly" or $billing_period_name == "biannual" or $billing_period_name == "3 payment") {
                            //LA VARIABLE "amount" DEBE SER REEMPLZADA POR LA VARIABLE "amount_next" QUE AUN NO EXISTE EN EL DESARROLLO
                            //nextPayments($id_application, $id_pay_method, $amount, $user_commission, $billing_period_name);
                        }
                    }
                }elseif($result["payment_intent"]["status"] == "payment_failed"){
                    error_log("\n".date('d/m/Y h:i:s')."\n".print_r("200 FAILED: $id_payment_due",true), 3, $jsondebug);
                }
            }
            else if($result['code'] == 204){

                if($result['status'] == 1){
                    if ($retries < 2) {
                        $status = 1;
                        $campo = "retries = retries + 1, status = $status";//status 1 = pendiente para reintentar 
                    }else if($retries >= 2 ){
                        $status = 3;
                        $campo = "retries = retries + 1, status = $status";//status 3 = fallo por muchos intentos y se pausará

                        pausedApplication($id_application, $payment_date, $id_payment_due);
                    }

                    $template = $status == 1 ? 3 : 4;

                    $url = $ws_path."/payment/sendnotificationcronjob";
                    $jsonData = [
                        "id_payment_due" => $id_payment_due,
                        "template" => $template,
                    ];
                    $curl_notification = $obj_function->curlSetOpt($url, $jsonData);

                }else{
                    $status = $result['status'];
                    $campo = "status = $status";
                }

                $note = $result["message"];
                $where = "id = '$id_payment_due'";
                $query = $obj_bdmysql->update("payment_due", $campo, $where, $conn);
                savePaymentAttempt($id_client, $id_payment_due, $note, $status, "");          
            }
        }
        //exit;
    }
    mysqli_close($conn);
}

function payStripe($id_client, $amount, $id_pay_method, $name_plan, $name_service, $applicant_name, $client_name, $id_payment_due){

    global $conn, $obj_bdmysql, $jsondebug;

    $sqlk = "SELECT secret_key FROM client_key_stripe WHERE id_client = $id_client";
    $queryk = $obj_bdmysql->query($sqlk, $conn);
    
    if (is_array($queryk)) {
        $secret_key = $queryk[0]['secret_key'];

        $sql = "SELECT pm.status, a.id_customer_stripe, pm.method_inf_json->>'$.id' AS id_payment_method, pm.method_inf_json->>'$.type' AS type FROM pay_method pm INNER JOIN applicant a ON a.id = pm.id_applicant WHERE pm.id = $id_pay_method";
        $queryW = $obj_bdmysql->query($sql, $conn);
        //print_r($queryW);exit;
        if (is_array($queryW)){ 
            if ($queryW[0]['status'] == 1) {
                $id_payment_method = $queryW[0]['id_payment_method'];
                $type_payment_method = $queryW[0]['type'];               
                $id_customer = $queryW[0]['id_customer_stripe'];

                \Stripe\Stripe::setApiKey($secret_key);

                $statement = $type_payment_method == "bank" ? "statement_descriptor" : "statement_descriptor_suffix";
                $out['payment_intent'] = "";
                $out['code'] = "";
                $out['message'] = "";

                try {

                    $PaymentIntent = \Stripe\PaymentIntent::create([
                        'amount' => $amount * 100,
                        'currency' => 'usd',
                        'payment_method_types' => ['card', 'us_bank_account'],
                        'payment_method' => $id_payment_method,
                        'description' => $name_service .' / '. $name_plan .' - customer: '.$applicant_name,
                        'off_session' => true,
                        'confirm' => true,
                        'customer' => $id_customer,
                        $statement => $client_name
                    ]);

                    $out['payment_intent'] = $PaymentIntent;
                    $out['code'] = 200;
                    $out['message'] = "Successfully Paid";
                    $out['status'] = 2;

                } catch (Exception $e) {
                    
                    $message_error = addslashes($e->getMessage()); 
                    $out['code'] = 204;
                    $out['message'] = $message_error;
                    $out['status'] = 1;

                    error_log("\n".date('d/m/Y h:i:s')."\n".print_r("error NEW id_payment_due = $id_payment_due",true), 3, $jsondebug);
                    error_log("\n".date('d/m/Y h:i:s')."\n".print_r($message_error,true), 3, $jsondebug);
                }                
            }else{
                $out['code'] = 204;
                $out['message'] = "Metodo de pago no verificado o eliminado";
                $out['status'] = 6;
            }
        }else{
            $out['code'] = 204;
            $out['message'] = "Metodo de pago no encontrado";
            $out['status'] = 4;
        }
    }else{
        $out['code'] = 204;
        $out['message'] = "Llave de cliente no encontrada";
        $out['status'] = 5;
    }

    return $out;
}


function savePaymentAttempt($id_client, $id_payment_due, $note, $status, $data){
    global $conn, $obj_bdmysql, $obj_function;

    $data = $data != "" ? json_encode($data) : "{}";
    $attempts_date = date("Y-m-d");

    $campo = "id_client, id_payment_due, attempts_date, note, status, data";
    $valor = "$id_client, $id_payment_due, '$attempts_date', '$note', $status, '$data'";
    $id = $obj_bdmysql->insert("payment_attempt", $campo, $valor, $conn);

    //$sql = "insert into payment_attempt ($campo) values ($valor)";
    //print_r($sql);exit;
}

function saveCommissions($id_payment_due, $id_user, $user_commission, $payment_date, $amount, $id_client, $fee){
    // función desarrollada, para registro de intentos de pago realizados con o sin exito.
    //se guarda data en tabla ( contacts_services_payment). 
    global $conn, $obj_bdmysql, $obj_function;

    $date = date("Y-m-d H:i:s");

    if ($fee > 0) {
        $amount = $amount - $fee;
    }

    if($amount > 0){
        $amount_commission = ($amount * $user_commission) / 100;
        $campo = "id_payment_due, id_user, id_client, id_user_prev, commission_percentage, commission_amount, type, date_create, date_commission, source, flag_edit";
        $valor = "$id_payment_due, $id_user, $id_client, 0, $user_commission, $amount_commission, '1', '$date', '$payment_date', 'Cron', '1'";
        $id = $obj_bdmysql->insert("commission_payments", $campo, $valor, $conn);

        //$sql = "INSERT INTO commission_payments ($campo) VALUES ($valor)";
        //print_r($sql);exit();

        $sql = "SELECT u.id, u.id_parent, l.commission, u.id_client FROM user u INNER JOIN level l ON l.id = u.id_level where u.id = $id_user";
        $query = $obj_bdmysql->query($sql, $conn);

        if ($query[0]['id_parent'] > 0) {
            $id_user_prev = $query[0]['id'];
            $id_parent = $query[0]['id_parent'];
            $check_id_parent = 1;
            $old_commission = $user_commission;
            $i = 0;
            do {
                $sql = "SELECT u.id, u.id_parent, l.commission, l.name AS level_name, u.id_client 
                        FROM user u INNER JOIN level l ON l.id = u.id_level WHERE u.id = $id_parent";
                $query = $obj_bdmysql->query($sql, $conn);   
                
                $level = $query[0]['level_name'];

                if($level == "Other"){
                    $check_id_parent = 0;//Stop cycle
                } else {
                    $id_client = $query[0]['id_client'];
                    $id_user = $query[0]['id'];        
                    $commission = $query[0]['commission'];
                    $id_parent = $query[0]['id_parent'];        

                    if ($commission > $old_commission) {
                        $result_commission = $commission - $old_commission;
                        $amount_commission = ($amount * $result_commission) / 100;
                        $commission_percentage = $commission;
                    }else if($commission <= $old_commission){
                        if($level == "Agent" || $level == "Associate"){
                            $commission_percentage = 0;
                            $amount_commission = 0;
                        }else{
                            $commission_percentage = 5;
                            $amount_commission = ($amount * $commission_percentage) / 100;
                        }
                    }

                    $campo = "id_payment_due, id_user, id_client, id_user_prev, commission_percentage, commission_amount, type, date_create, date_commission, source, flag_edit";        
                    $valor = "$id_payment_due, $id_user, $id_client, $id_user_prev, $commission_percentage, $amount_commission, '2', '$date', '$payment_date', 'Cron', '1'";
                    $obj_bdmysql->insert("commission_payments", $campo, $valor, $conn);
                
                    $old_commission = $commission;
                    $id_user_prev = $query[0]['id'];
                }                

                if ($id_parent == 0 || $i == 20) {
                    $check_id_parent = 0;            
                }

                $i++;
                //cuando $i llegue a 20 iteraciones, se debe parar el do while
                //por seguridad para evita ciclo infinito
            } while ($check_id_parent == 1);   
        }
    }
}

function pausedApplication($id_application, $payment_date, $id_payment_due){
    global $conn, $obj_bdmysql, $obj_function, $jsondebug;
        
    error_log("\n".date('d/m/Y h:i:s')."\n".print_r("pausedApplication: pausedApplication",true), 3, $jsondebug);
        
    $id_status_app = 5; //status 5 = paused
    $campo = "id_status = $id_status_app";
    $where = "id = $id_application";
    $query = $obj_bdmysql->update("application", $campo, $where, $conn);
        
    $date = new DateTime();
    $date = $date->format('Y-m-d');
    $status_services = 5; //paused
    $campo = "id_application, id_status_app, createdAt";
    $valor = "$id_application, '$id_status_app', '$date'";
    $obj_bdmysql->insert("history_app_status", $campo, $valor, $conn);

    for ($i=0; $i < 3; $i++) {
        if ($i == 0) {
            $days = 30;
            $type = "1";
        }elseif ($i == 1) {
            $days = 60;
            $type = "2";
        }elseif ($i == 2) {
            $days = 90;
            $type = "3";
        }

        $date_notification = new DateTime($payment_date);
        $date_notification = $date_notification->format('Y-m-d');
        $date_notification = date("Y-m-d",strtotime($date_notification."+ $days day"));

        $campo = "id_payment_due, type, status, date_notification";
        $valor = "'$id_payment_due', '$type', '1', '$date_notification'";
        $obj_bdmysql->insert("noti_app_anulcancel", $campo, $valor, $conn);
    }    
}

function nextPayments($id_application, $id_pay_method, $amount_next, $user_commission, $billing_period_name)
{

    global $conn, $obj_bdmysql, $obj_function, $jsondebug, $date;

    $data_type_payment = 0;

    if ($billing_period_name == "monthly") {
        $months = 1;
        $count = 11;
    }
    if ($billing_period_name == "biannual") {
        $months = 6;
        $count = 1;
    }
    if ($billing_period_name == "3 payment") {
        $months = 1;
        $count = 2;
    }

    $new_date_payment = $date; // formatear a Y-m-d
    $init_day= date("d", strtotime($new_date_payment));

    for ($i = 0; $i < $count; $i++) {
        $day_now = date("d", strtotime($new_date_payment));
        $month_now = date("m", strtotime($new_date_payment));
        $year_now = date("Y", strtotime($new_date_payment));

        if($month_now== 1 && $day_now > 28){
            $new_date_payment = date("Y-m-d", strtotime($new_date_payment . "last day of + $months month"));
        }else if($month_now == 2 && ($billing_period_name == "monthly" || $billing_period_name == "3 payment")){
            $nueva_fecha = $year_now.'-01-'.$init_day;
            $new_date_payment = date("Y-m-d", strtotime($nueva_fecha . "+ 2 month"));            
        }else if($month_now == 8 && $billing_period_name == "biannual"){
            $new_date_payment = date("Y-m-d", strtotime($new_date_payment . "last day of + $months month"));
        }else if($month_now > 2 && $day_now > 30){
            $new_date_payment = date("Y-m-d", strtotime($new_date_payment . "last day of + $months month"));
        }else{      
            $new_date_payment = date("Y-m-d", strtotime($new_date_payment . "+ $months month"));
        }

        $campo = "id_application, id_pay_method, amount, user_commission, payment_date";
        $valor = "$id_application, $id_pay_method, $amount_next, '$user_commission', '$new_date_payment'";
        $obj_bdmysql->insert("payment_due", $campo, $valor, $conn);
    }
}
