<?php

ini_set('display_errors',1);
error_reporting(E_ALL);
date_default_timezone_set('UTC');
$DB_HOST='136.112.153.136';
$DB_USER='lac';
$DB_PASS='kschhiKHSH23243#';
$DB_NAME='lac_prod';
$DB_PORT=3306;

$conn=new mysqli($DB_HOST,$DB_USER,$DB_PASS,$DB_NAME,$DB_PORT);
if($conn->connect_error){
    http_response_code(500);
    exit;
}

$rawPayload=file_get_contents("php://input");
$data=json_decode($rawPayload,true);

if(!$data){
    http_response_code(400);
    exit;
}

$eventType=$data['type'] ?? '';
$eventId=$data['id'] ?? '';

$invoice=$data['data']['object'] ?? [];

$invoiceId=$invoice['id'] ?? '';
$billingReason=$invoice['billing_reason'] ?? '';
$customerId=$invoice['customer'] ?? '';
$subscriptionId=$invoice['subscription'] ?? '';

$line=$invoice['lines']['data'][0] ?? [];

$orderId=$line['metadata']['order_id'] ?? null;
$userId=$line['metadata']['user_id'] ?? null;
$packageId=$line['metadata']['package_id'] ?? null;

if(empty($orderId) || empty($userId) || empty($packageId)){
$getcustomerId=$conn->query("SELECT id FROM customers WHERE conent_image='".$conn->real_escape_string($customerId)."' LIMIT 1");
 if($getcustomerId && $getcustomerId->num_rows>0){
        $row=$getcustomerId->fetch_assoc();
        $userId = $row['id'];
        $getOrderId=$conn->query("SELECT order_id, item_id FROM tbl_order_details WHERE subscriber_id='".$conn->real_escape_string($userId)."' LIMIT 1");
        if($getOrderId && $getOrderId->num_rows>0){
            $row=$getOrderId->fetch_assoc();
            $orderId = $row['order_id'];
            $packageId = $row['item_id'];
        }
    }
}

$start=$line['period']['start'] ?? null;
$end=$line['period']['end'] ?? null;

$forFreeTrailAmount = isset($invoice['amount_paid']) && is_numeric($invoice['amount_paid']) ? $invoice['amount_paid'] : 0;
$unitAmount=$line['price']['unit_amount'] ?? 0;
$amount=$unitAmount/100;

$startDate=date("Y-m-d H:i:s",$start);
$endDate=date("Y-m-d H:i:s",$end);

$conn->query("INSERT INTO stripe_webhook_log(hook_event,log_data,cus_id,user_id,order_id,gateway_ref_id,app_id) VALUES(
'".$conn->real_escape_string($eventType ?? '')."',
'".$conn->real_escape_string($rawPayload ?? '')."',
'".$conn->real_escape_string($customerId ?? '')."',
'".$conn->real_escape_string($userId ?? '')."',
'".$conn->real_escape_string($orderId ?? '')."',
'".$conn->real_escape_string($subscriptionId ?? '')."',
'5013'
)");


if($eventType=="invoice.payment_succeeded"){

    $dup=$conn->query("SELECT id FROM tbl_customer_subscriptions WHERE purchase_token='".$conn->real_escape_string($invoiceId)."' LIMIT 1");
    if($dup && $dup->num_rows>0){
	http_response_code(200);
	exit;
     }
    
    $purchaseType="PURCHASED";
    $autorenewed="1";
    if($billingReason=="subscription_cycle"){
        $purchaseType="RENEWED";
        $autorenewed="1";
    }

    $deviceId=0;
    if($forFreeTrailAmount==0){
        $deviceId=1;
        $amount=$forFreeTrailAmount;
    }

    $prev=$conn->query("SELECT id,status,start_date,end_date FROM tbl_customer_subscriptions WHERE gateway_subscription_id='".$conn->real_escape_string($subscriptionId)."' AND status IN (2,3) ORDER BY id DESC LIMIT 1");

    //  if($prev && $prev->num_rows<0){
    //     http_response_code(200);
    //     exit;
    //  }

    if($prev && $prev->num_rows>0){
        $row=$prev->fetch_assoc();
     if ( date('Y-m-d', strtotime($row['start_date'])) == date('Y-m-d', strtotime($startDate)) && date('Y-m-d', strtotime($row['end_date'])) == date('Y-m-d', strtotime($endDate))) {
            http_response_code(200);
            exit;
        } else {
            $now = date("Y-m-d H:i:s");
            $conn->query("UPDATE tbl_customer_subscriptions 
                        SET status=7, end_date='".$now."',u_currency='Expired from Webhook1'
                        WHERE id=".$row['id']);
        }
        
    }

 
    $orderDetail=$conn->query("SELECT * FROM tbl_order_details WHERE order_id=".(int)$orderId." ORDER BY id DESC LIMIT 1");

    if(!$orderDetail || $orderDetail->num_rows==0){
        http_response_code(200);
        exit;
    }

    $od=$orderDetail->fetch_assoc();
    $insert="INSERT INTO tbl_customer_subscriptions(app_id,order_id,trans_id,pg_ref_id,device_id,gateway_subscription_id,pg_payment_data,channel,package_id,package_title,package_code,package_type,package_mode,price,basic_amount,tax_amt,subscriber_id,msisdn,role_type,gateway_type,pg_name,region_type,local_user,state_code,period,period_interval,trial_period,trial_length,subscription_type,output_video_limit,additional_cost_per_unit,no_videos,no_plays,status,platform,extended_status,start_date,end_date,autorenew,currency_id,currency,created,is_cancelled,deactivation_type,recurring,purchase_token,package_name,purchase_type ) VALUES (
    '".$conn->real_escape_string($od['app_id'] ?? '')."','".(int)$orderId."','".$conn->real_escape_string($od['gateway_ref_id'] ?? '')."','".$conn->real_escape_string($od['gateway_ref_id'] ?? '')."','".$deviceId."','".$conn->real_escape_string($subscriptionId)."','".$conn->real_escape_string($rawPayload)."','0','".$conn->real_escape_string($od['item_id'] ?? '')."','".$conn->real_escape_string($od['item_name'] ?? '')."','0','0','".$conn->real_escape_string($od['package_mode'] ?? '')."','".$amount."','".$conn->real_escape_string($od['real_price'] ?? '')."','".$conn->real_escape_string($od['tax_amt'] ?? '')."','".$conn->real_escape_string($od['subscriber_id'] ?? '')."','','".$conn->real_escape_string($od['role_type'] ?? '')."','".$conn->real_escape_string($od['gateway_type'] ?? '')."','stripe','".$conn->real_escape_string($od['region_type'] ?? '')."','".$conn->real_escape_string($od['local_user'] ?? '')."','".$conn->real_escape_string($od['b_state_code'] ?? '')."','".$conn->real_escape_string($od['period'] ?? '')."','".$conn->real_escape_string($od['period_interval'] ?? '')."','0','".$conn->real_escape_string($od['b_city'] ?? '')."','".$conn->real_escape_string($od['subscription_type'] ?? '')."','".$conn->real_escape_string($od['output_video_limit'] ?? '')."','".$conn->real_escape_string($od['additional_cost_per_unit'] ?? '')."','".$conn->real_escape_string($od['no_videos'] ?? '')."','".$conn->real_escape_string($od['no_plays'] ?? '')."','2','Webhook','0','".$startDate."','".$endDate."','".$autorenewed."','".$conn->real_escape_string($od['currency_id'] ?? '')."','".$conn->real_escape_string($od['currency'] ?? '')."','".date("Y-m-d H:i:s")."','0','','0','".$conn->real_escape_string($invoiceId)."','".$conn->real_escape_string($od['item_name'] ?? '')."','".$purchaseType."' )";

    $conn->query($insert);

    $conn->query("UPDATE tbl_order SET payment_status='2',order_status='2' WHERE id=".(int)$orderId." LIMIT 1");

    // if($purchaseType == "PURCHASED"){
    //     $conn->query("UPDATE tbl_order_details SET end_date=".$endDate." WHERE id=".(int)$orderId." LIMIT 1");
    // }

    echo "Subscription has been assigned.";

}

// failed
if($eventType=="invoice.payment_failed"){
    $currentEndDate=date("Y-m-d H:i:s");
    $conn->query("UPDATE tbl_order SET payment_status='3',order_status='3' WHERE id=".(int)$orderId." LIMIT 1");
    $conn->query("UPDATE tbl_customer_subscriptions SET status='7',u_currency='Expired from Webhook2', end_date='".$conn->real_escape_string($currentEndDate)."' WHERE order_id=".(int)$orderId." AND subscriber_id=".(int)$userId." AND package_id=".(int)$packageId." ORDER BY id DESC LIMIT 1");
    echo "Payment has been failed.";
}

// cancelled
if($eventType=="customer.subscription.deleted"){
    $now=date("Y-m-d H:i:s");
    $conn->query("UPDATE tbl_customer_subscriptions SET is_cancelled=1,cancelled_at='".$now."' WHERE gateway_subscription_id='".$conn->real_escape_string($subscriptionId)."' ORDER BY id DESC LIMIT 1");
    $conn->query("UPDATE tbl_order SET order_status='4' WHERE id=".(int)$orderId." LIMIT 1 ");
    echo "Subscription has been cancelled.";
}


function renderTemplate($template, $data){
    foreach($data as $key => $val){
        $template = str_replace("{{.$key}}", $val, $template);
    }
    return $template;
}

function smtpCommand($socket, $command, $expectCode){
    if($command !== null){
        fwrite($socket, $command . "\r\n");
    }
    $data = '';
    while($str = fgets($socket, 515)){
        $data .= $str;
        if(preg_match('/^\\d{3} /', $str)){
            break;
        }
    }
    if($expectCode !== null && strpos($data, $expectCode) !== 0){
        return false;
    }
    return $data;
}

function sendSmtpMail($to, $subject, $html){
    if(empty($to)){
        return false;
    }

    $smtpHost = "smtp.gmail.com";
    $smtpPort = 587;
    $loginUser = "Support@lawnewz.com";
    $loginPass = "pdnz oknr atrm kiqi";
    $fromEmail = "support@lawandcrimeplus.com";
    $fromName  = "Law&Crime+ Support";

    $socket = stream_socket_client("tcp://$smtpHost:$smtpPort", $errno, $errstr, 30);
    if(!$socket){
        return false;
    }

    if(!smtpCommand($socket, null, "220")) return false;
    if(!smtpCommand($socket, "EHLO lawandcrimeplus.com", "250")) return false;
    if(!smtpCommand($socket, "STARTTLS", "220")) return false;

    if(!stream_socket_enable_crypto($socket, true, STREAM_CRYPTO_METHOD_TLS_CLIENT)){
        return false;
    }

    if(!smtpCommand($socket, "EHLO lawandcrimeplus.com", "250")) return false;
    if(!smtpCommand($socket, "AUTH LOGIN", "334")) return false;
    if(!smtpCommand($socket, base64_encode($loginUser), "334")) return false;
    if(!smtpCommand($socket, base64_encode($loginPass), "235")) return false;

    if(!smtpCommand($socket, "MAIL FROM:<$fromEmail>", "250")) return false;
    if(!smtpCommand($socket, "RCPT TO:<$to>", "250")) return false;
    if(!smtpCommand($socket, "DATA", "354")) return false;

    $headers  = "From: $fromName <$fromEmail>\r\n";
    $headers .= "To: $to\r\n";
    $headers .= "Subject: $subject\r\n";
    $headers .= "MIME-Version: 1.0\r\n";
    $headers .= "Content-Type: text/html; charset=\"UTF-8\"\r\n";
    $headers .= "Content-Transfer-Encoding: 8bit\r\n\r\n";

    fwrite($socket, $headers . $html . "\r\n.\r\n");
    if(!smtpCommand($socket, null, "250")) return false;
    smtpCommand($socket, "QUIT", "221");
    fclose($socket);

    return true;
}

if($eventType=="invoice.upcoming" && $data['data']['object']['lines']['data'][0]['plan']['interval'] == 'year'){
    $userEmail = $invoice['customer_email'] ?? '';
    $planName = $line['description'] ?? ($line['price']['nickname'] ?? '');
    if(empty($planName) && !empty($orderId)){
        $orderDetail=$conn->query("SELECT item_name FROM tbl_order_details WHERE order_id=".(int)$orderId." ORDER BY id DESC LIMIT 1");
        if($orderDetail && $orderDetail->num_rows>0){
            $od=$orderDetail->fetch_assoc();
            $planName = $od['item_name'] ?? '';
        }
    }

    $currency = strtoupper($line['price']['currency'] ?? ($invoice['currency'] ?? 'USD'));
    $renewAmount = number_format($amount, 2) . " " . $currency;
    $renewDate = !empty($start) ? date("Y-m-d", $start) : (!empty($end) ? date("Y-m-d", $end) : "");
    $last4 = $invoice['payment_method_details']['card']['last4'] ?? '';
    if(empty($last4)){
        $last4 = "Stripe";
    }

    $accountUrl = "https://lawandcrimeplus.com/account";

    $template = '<!DOCTYPE html>
                <html>

                <head>
                    <meta name="viewport" content="width=device-width" />
                    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
                    <title>Verify OTP Email</title>
                        <style>
                        body {
                            margin: 0;
                            padding: 0;
                            background-color: #f4f4f4;
                            font-family: "Arial", sans-serif;
                            color: #333;
                        }

                        .container {
                            max-width: 600px;
                            margin: 20px auto;
                            background: #fff;
                            border-radius: 8px;
                            overflow: hidden;
                            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
                        }

                        .header {
                            background: linear-gradient(90deg, #0c08f5, #000000);
                            color: white;
                            padding: 20px;
                            display: flex;
                            align-items: center;
                        }

                        .header img {
                            max-width: 100px;
                            margin-right: 20px;
                            background-color: black;
                            padding: 10px;
                            border-radius: 6px;
                            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
                        }


                        ul {
                            margin: 0;
                            padding-left: 18px;
                        }


                        .header h1 {
                            margin: 0;
                            font-size: 24px;
                        }

                        .content {
                            padding: 20px;
                        }

                        .content h3 {
                            color: #0c08f5;
                        }

                        .content p {
                            margin-bottom: 20px;
                            line-height: 1.2;
                        }

                        .invoice-box {
                            background: #f9f9f9;
                            border: 1px solid #ddd;
                            border-radius: 6px;
                            padding: 15px;
                            margin: 20px 0;
                            font-size: 15px;
                        }

                        .btn {
                            display: inline-block;
                            background: #0c08f5;
                            color: white;
                            padding: 12px 24px;
                            text-decoration: none;
                            border-radius: 4px;
                            text-align: center;
                            font-size: 16px;
                        }

                        .footer {
                            background-color: #333;
                            color: white;
                            text-align: center;
                            padding: 10px 20px;
                            font-size: 14px;
                        }

                        .footer a {
                            color: #0c08f5;
                            text-decoration: none;
                        }

                        @media screen and (max-width: 600px) {
                            .content {
                                padding: 15px;
                            }

                            .btn {
                                padding: 12px 16px;
                                font-size: 14px;
                            }
                        }
                        .security-list {
                            margin: 8px 0 12px 0;
                            padding-left: 15px;
                            list-style-type: disc;
                            list-style-position: outside;
                            text-align: left;
                        }

                        .security-list li {
                            margin-bottom: 6px;
                        }
                    </style>
                </head>

                <body>
                    <div class="container">
                        <div class="header">
                            <img src="https://creator-prod-crd.s3.ap-south-1.amazonaws.com/law-json/lac_log.png" alt="Law & Crime+ Logo" />
                            <h1>Upcoming Renewal Reminder </h1>
                        </div>

                        <div class="content">

                    <div class="dear">
                        <p><strong>Dear Law&Crime+ Insider,</strong></p>
                    </div>

                    <p>
                        We hope you&rsquo;re enjoying your front-row seat to the nation&rsquo;s most compelling trials,
                        original programming, and case file deep-dives.
                    </p>

                    <p>
                        This is a friendly reminder that your subscription is scheduled for renewal in one week.
                        No action is needed on your part to keep your access active, but we wanted to share your
                        upcoming transaction details for your records.
                    </p>

                    <p><strong>Upcoming Renewal Summary:</strong></p>

                    <div class="invoice-box">
                        <p><strong>Plan:</strong> {{.plan_name}}</p>
                        <p><strong>Renewal Amount:</strong> {{.amount}}</p>
                        <p><strong>Scheduled Date:</strong> {{.renewal_date}}</p>
                        <p><strong>Payment Method:</strong> {{.last_4_digits}}</p>
                    </div>

                    <p>
                        Want to make changes? If you&rsquo;d like to update your payment information or manage your
                        subscription settings before the renewal date, you can do so at any time through your
                        Account Dashboard.
                    </p>

                    <p align="center">
                        <a href="{{.account_url}}" 
                        style="display:inline-block; background:#0c08f5; color:#ffffff; padding:12px 24px; text-decoration:none; border-radius:4px; font-size:16px;">
                        Go to My Account
                        </a>
                    </p>

                    <p>
                        Need a hand? Our team is here to help! For any billing-related queries, just reach out to us at
                        <a href="mailto:support@lawandcrimeplus.com" style="color:#0c08f5; text-decoration:none;">
                            support@lawandcrimeplus.com
                        </a>.
                    </p>

                    <p>Thank you for being a vital part of the Law&amp;Crime+ community.</p>

                    <p><strong>The Law&Crime+ Team</strong></p>

                </div>
                        <div class="footer" style="text-align:center;">
                            <p style="margin:0 0 8px 0; text-align:center;">© 2026 Law&Crime+. All Rights Reserved.</p>

                            <p style="margin:0 0 8px 0; text-align:center;">
                                Need help?
                                <a href="https://lawandcrimeplus.com/support" style="color: orange;">
                                    Contact Support
                                </a>

                            </p>

                            <p style="margin-top: 12px; margin-bottom:10px; text-align:center;">
                                Connect with the Law&amp;Crime+ Community:
                            </p>

                            <table role="presentation" cellspacing="0" cellpadding="0" border="0" align="center"
                                style="margin: 0 auto;">
                                <tr>
                                    <td style="padding:0 8px;">
                                        <a href="https://x.com/LawAndCrimePlus" target="_blank">
                                            <img src="https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSOYLc7JOkimEAV6oZrK8OAoBp168im8Ul_Lw&s"
                                                width="24" height="24" alt="X"
                                                style="display:block; border:0; outline:none; text-decoration:none;">
                                        </a>
                                    </td>

                                    <td style="padding:0 8px;">
                                        <a href="https://www.instagram.com/lawandcrimeplus/" target="_blank">
                                            <img src="https://cdn-icons-png.flaticon.com/512/733/733558.png" width="24" height="24"
                                                alt="Instagram" style="display:block; border:0; outline:none; text-decoration:none;">
                                        </a>
                                    </td>

                                    <td style="padding:0 8px;">
                                        <a href="https://www.facebook.com/lawandcrimeplus" target="_blank">
                                            <img src="https://cdn-icons-png.flaticon.com/512/733/733547.png" width="24" height="24"
                                                alt="Facebook" style="display:block; border:0; outline:none; text-decoration:none;">
                                        </a>
                                    </td>
                                </tr>
                            </table>
                        </div>
                    </div>
                </body>

                </html>';


    $html = renderTemplate($template, [
        "plan_name" => $planName ?: "Subscription Plan",
        "amount" => $renewAmount,
        "renewal_date" => $renewDate,
        "last_4_digits" => $last4,
        "account_url" => $accountUrl
    ]);

    $subject = "Your Law&Crime+ Subscription Renewal Coming in 7 Days";
    sendSmtpMail($userEmail, $subject, $html);

    echo "Upcoming renewal mail sent.";
}


http_response_code(200);
echo "ok";