<?php

global $db;
require_once 'init.php';

$sql = "WITH first_status_300_plus AS (
    SELECT 
        ph.preorder_id,
        MIN(ph.create) as first_status_300_timestamp
    FROM PreorderHistory ph
    INNER JOIN Preorderstatus ps ON CAST(ph.new_value AS UNSIGNED) = ps.id
    WHERE ph.key = 'status_id' 
        AND ps.code >= 300
        AND ph.create <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 8 WEEK))
    GROUP BY ph.preorder_id
)
SELECT 
    p.id,
    p.type,
    p.preordercampaign_id,
    pc.name as campaign_name,
    ps_current.code as current_status_code,
    ps_current.name as current_status_name,
    FROM_UNIXTIME(fs.first_status_300_timestamp) as first_reached_300_plus,
    DATEDIFF(NOW(), FROM_UNIXTIME(fs.first_status_300_timestamp)) as days_since_first_300,
    pcsmt.mailtemplate_id,
    pcsmt.logical_config
FROM Preorder p
INNER JOIN Preordercampaign pc ON p.preordercampaign_id = pc.id
INNER JOIN PreordercampaignStatusnotificationMailtemplate pcsmt ON pc.id = pcsmt.preordercampaign_id
INNER JOIN `" . ADDRESSDB_DBNAME . "`.Hausnummer hn ON p.adb_wohneinheit_id = hn.id
INNER JOIN Preorderstatus ps_current ON p.status_id = ps_current.id
INNER JOIN first_status_300_plus fs ON p.id = fs.preorder_id
LEFT JOIN PreorderStatusnotificationLog psnl ON p.id = psnl.preorder_id AND psnl.email_type = '300-custom'
WHERE p.type = 'order'
    AND ps_current.code >= 300 
    AND ps_current.code < 500
    AND hn.id IS NOT NULL 
    AND hn.tool_building_type = 1 -- Only consider residential buildings
    AND pcsmt.notification_type = 'logical'
    AND JSON_EXTRACT(pcsmt.logical_config, '$.type') = '300-custom'
    AND psnl.id IS NULL  -- No existing '300-custom' notification
ORDER BY fs.first_status_300_timestamp ASC;";

$res = $db->query($sql);

if ($res->num_rows == 0) {
    logMessage("No preorders found for custom 300 notification.");
    exit;
}

while($row = $res->fetch_assoc()) {
    logMessage("Processing Preorder ID: " . $row['id']);
    $preorder_id = $row['id'];
    $campaign_name = $row['campaign_name'];
    $mailtemplate_id = $row['mailtemplate_id'];

    $preorder = new Preorder($preorder_id);
    if (!$preorder || !$preorder->id) {
        logMessage("Preorder with ID $preorder_id not found. Skipping.");
        continue;
    }

    $preordercampaign = new Preordercampaign($preorder->preordercampaign_id);
    if (!$preordercampaign || !$preordercampaign->id) {
        logMessage("Preordercampaign with ID {$preorder->preordercampaign_id} not found. Skipping.");
        continue;
    }

    $mailtemplate = new Mailtemplate($mailtemplate_id);
    if(!$mailtemplate || !$mailtemplate->id) {
        logMessage("Mailtemplate with ID $mailtemplate_id not found. Skipping.");
        continue;
    }

    logMessage("Preparing to send email for Preorder ID: $preorder_id, Campaign: $campaign_name");

    sendPreorderEmail(
        $mailtemplate,
        $preordercampaign,
        $preorder,
        "300-custom"
    );

}