<?php 
date_default_timezone_set('Asia/Hong_Kong');
$date = date('Y-m-d H:i:s'); // Use a format compatible with SQL

$tomorrow = date('Y-m-d', strtotime('+300 days')); // Use strtotime to calculate tomorrow's date
$advanced_date = date('m-d', strtotime('+300 days'));

// Database configuration
$servername = "localhost";
$username = "mpblueco_admin20";
$password = "?.R-0Ekz~Q7B";
$database = "mpblueco_tables";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL query
$sql = "SELECT customer_company_name, customer_name, newsletter, customer_email, customer_cp, order_id, order_date_placed, order_CSR, order_website, order_contact_cp, order_contact_person, order_email
        FROM 1_orders 
        JOIN 1_customers ON 1_orders.order_customer_id = 1_customers.customer_id
        WHERE 1_orders.order_date_placed LIKE '%$advanced_date%'
        AND 1_orders.order_website = 'Mypowderblue'
        AND 1_orders.welcome_kits IS NULL
        AND 1_customers.newsletter = 'YES'
        AND 1_customers.PEZA = 'NO'
        
        OR 1_orders.order_date_placed LIKE '%$advanced_date%'
        AND 1_orders.order_website = 'Mypowderblue'
        AND 1_orders.welcome_kits IS NULL
        AND 1_customers.newsletter = 'YES'
        AND 1_customers.PEZA = 'NO'
        AND NOT 1_orders.order_email = ''
        AND NOT 1_orders.order_email = 'NULL'
        
        LIMIT 1";

$result = $conn->query($sql); // Execute the query

$new_orders_COUNT = $result->num_rows; // Count the output amount

if ($new_orders_COUNT > 0) {
    // Fetch the results
    while ($row = $result->fetch_assoc()) { 
        // Extract data from the result row
        $order_id = $row["order_id"];
        $order_date_placed = $row["order_date_placed"];
        $order_CSR = $row["order_CSR"];
        $order_website = $row["order_website"];
        $order_email = $row["order_email"];
        $order_contact_cp = $row["order_contact_cp"];
        $order_contact_person = $row["order_contact_person"];
        $customer_company_name = $row["customer_company_name"];
        $customer_name = $row["customer_name"];
        $customer_cp = $row["customer_cp"];
        $customer_email = $row["customer_email"];

        // Construct your SQL query
        $insert_query = "INSERT INTO 2_tasks_for_marketing (task_category, marketing_slug, task_details, customer_email, task_intended_for_csr, task_date_time, order_id) 
                        VALUES ('WELCOME-KITS', 'mayer', 'Email $customer_company_name to check offered WELCOME KITS ($order_contact_person/$customer_name)', '$order_email/$customer_email', 'Cron Job', '$date', '$order_id')";

        // Execute the INSERT query
        $conn->query($insert_query);

        // Update order_reminder
        $conn->query("UPDATE 1_orders SET welcome_kits='QUE' WHERE order_id = $order_id");
    }
}

// Close the database connection
$conn->close();
?>