<?php
// email_resi.php - AWB Report Emailer using PHPMailer

error_reporting(E_ALL);
ini_set('display_errors', 1);

// --- PHPMailer Autoload via Composer ---
// Ensure this path is correct for your setup
require __DIR__ . '/vendor/autoload.php'; 

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;

// --- Database Configuration ---
define('DB_HOST', 'localhost');
define('DB_USER', 'wiselie_nanda');
define('DB_PASS', 'Clemira123');
define('DB_NAME', 'wiselie_inventory');

// --- Email Configuration (PHPMailer specific) ---
// Using the provided cPanel SMTP settings
define('SMTP_HOST', 'mail.wiselie.co.id');     // Host SMTP untuk cPanel
define('SMTP_USERNAME', 'reporting@wiselie.co.id'); // Username SMTP (email cPanel)
define('SMTP_PASSWORD', 'reportingnanda321.'); // Password email cPanel
define('SMTP_PORT', 465);                      // Port SMTP untuk SSL
define('SMTP_ENCRYPTION', PHPMailer::ENCRYPTION_SMTPS); // Enkripsi SSL

define('DEFAULT_SUBJECT', 'AWB Movement Report'); // Default subject for the email

// Mapping LOC (Location) - must match the logic in awb_data.php
$locMapping = [
    'surabaya' => 'SURABAYA',
    'jakarta' => 'JAKARTA',
    'bali' => 'BALI',
    'semarang' => 'SEMARANG',
    'bandung' => 'BANDUNG',
    'makassar' => 'MAKASSAR'
];

$message = '';
$message_type = ''; // 'success' or 'error'

// Default date range (last 3 months including current)
$currentMonth = date('Y-m');
$defaultStartMonth = date('Y-m', strtotime('-2 months')); // Set to last 2 months for better initial range
$startMonth = $_POST['start_month'] ?? $defaultStartMonth;
$endMonth = $_POST['end_month'] ?? $currentMonth;

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $recipient_emails = $_POST['recipient_emails'] ?? ''; // Can be comma-separated
    $custom_subject = $_POST['custom_subject'] ?? DEFAULT_SUBJECT;
    $custom_message_body = $_POST['custom_message_body'] ?? '';

    // Validate recipient emails
    $recipients_array = array_map('trim', explode(',', $recipient_emails));
    $valid_recipients = [];
    foreach ($recipients_array as $email) {
        if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $valid_recipients[] = $email;
        }
    }

    if (empty($valid_recipients)) {
        $message = 'No valid recipient email addresses provided.';
        $message_type = 'error';
    } else {
        try {
            $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // Validate and adjust month range (same logic as awb_data.php)
            if (strtotime($startMonth) > strtotime($endMonth)) {
                list($startMonth, $endMonth) = [$endMonth, $startMonth]; // Swap
            }

            // Generate all months in the range
            $months = [];
            $current = $startMonth;
            while (strtotime($current) <= strtotime($endMonth)) {
                $months[] = $current;
                $current = date('Y-m', strtotime('+1 month', strtotime($current)));
            }

            // Query data (same as awb_data.php)
            $stmt = $pdo->prepare("
                SELECT
                    CASE
                        WHEN loc IS NULL OR loc = '' THEN 'SURABAYA'
                        " . implode(" ", array_map(function($key, $value) {
                            return "WHEN LOWER(loc) LIKE '%" . strtolower($key) . "%' THEN '" . $value . "'";
                        }, array_keys($locMapping), array_values($locMapping))) . "
                        ELSE 'LAINNYA'
                    END AS grouped_loc,
                    DATE_FORMAT(scan_in, '%Y-%m') AS bulan,
                    COUNT(DISTINCT resi) AS total_resi
                FROM awb_data
                WHERE
                    resi IS NOT NULL
                    AND resi != ''
                    AND DATE(scan_in) BETWEEN :start_date AND :end_date
                GROUP BY grouped_loc, DATE_FORMAT(scan_in, '%Y-%m')
                ORDER BY
                    CASE grouped_loc
                        WHEN 'SURABAYA' THEN 0
                        ELSE 1
                    END,
                    grouped_loc,
                    bulan
            ");

            $stmt->execute([
                ':start_date' => $startMonth . '-01',
                ':end_date' => date('Y-m-t', strtotime($endMonth . '-01'))
            ]);

            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Organize data for the table
            $organizedData = [];
            $allLocGroups = array_merge(array_values($locMapping), ['LAINNYA']);

            foreach ($allLocGroups as $loc) {
                $organizedData[$loc] = [];
                foreach ($months as $month) {
                    $organizedData[$loc][$month] = 0;
                }
            }

            foreach ($data as $row) {
                if (isset($organizedData[$row['grouped_loc']][$row['bulan']])) {
                    $organizedData[$row['grouped_loc']][$row['bulan']] = $row['total_resi'];
                }
            }

            // Calculate monthly and grand totals
            $monthlyTotals = [];
            foreach ($months as $month) {
                $monthlyTotals[$month] = 0;
            }
            $grandTotalResi = 0;

            foreach ($organizedData as $loc => $monthData) {
                foreach ($monthData as $month => $count) {
                    $monthlyTotals[$month] += $count;
                    $grandTotalResi += $count;
                }
            }

            // Build the HTML email body
            $email_html_body = "<html><head><style>
                body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; color: #333; }
                .email-container { max-width: 800px; margin: 20px auto; border: 1px solid #eee; padding: 20px; border-radius: 8px; background-color: #fff; box-shadow: 0 2px 4px rgba(0,0,0,0.05); }
                h2 { color: #2c3e50; margin-bottom: 20px; border-bottom: 1px solid #eee; padding-bottom: 10px; }
                p { margin-bottom: 10px; line-height: 1.6; }
                table { width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 14px; }
                th, td { padding: 10px 12px; border: 1px solid #ddd; text-align: left; }
                th { background-color: #f2f2f2; font-weight: bold; color: #555; }
                tr:nth-child(even) { background-color: #f9f9f9; }
                .summary-row { background-color: #e6f3ff; font-weight: bold; }
                .text-right { text-align: right; }
                .note { font-size: 12px; color: #777; margin-top: 15px; }
            </style></head><body>
            <div class='email-container'>
                <h2>AWB Movement Report - Period: " . htmlspecialchars(date('M Y', strtotime($startMonth.'-01'))) . " to " . htmlspecialchars(date('M Y', strtotime($endMonth.'-01'))) . "</h2>";

            if (!empty($custom_message_body)) {
                $email_html_body .= "<p><strong>Custom Message:</strong><br>" . nl2br(htmlspecialchars($custom_message_body)) . "</p>";
            }

            $email_html_body .= "
                <p>This report provides an overview of Air Waybill (AWB) movements across different locations for the selected period.</p>
                <table>
                    <thead>
                        <tr>
                            <th>LOC</th>";
            foreach ($months as $month) {
                $email_html_body .= "<th>" . htmlspecialchars(date('M Y', strtotime($month.'-01'))) . "</th>";
            }
            $email_html_body .= "<th>Total</th>
                        </tr>
                    </thead>
                    <tbody>";

            foreach ($organizedData as $loc => $monthData) {
                if (array_sum($monthData) == 0 && $loc !== 'LAINNYA') continue;

                $locTotal = 0;
                $email_html_body .= "<tr>";
                $email_html_body .= "<td>" . htmlspecialchars($loc) . "</td>";
                foreach ($months as $month) {
                    $count = $monthData[$month];
                    $email_html_body .= "<td class='text-right'>" . number_format($count) . "</td>";
                    $locTotal += $count;
                }
                $email_html_body .= "<td class='summary-row text-right'>" . number_format($locTotal) . "</td>";
                $email_html_body .= "</tr>";
            }

            $email_html_body .= "
                        <tr class='summary-row'>
                            <td>Grand Total</td>";
            foreach ($months as $month) {
                $email_html_body .= "<td class='text-right'>" . number_format($monthlyTotals[$month]) . "</td>";
            }
            $email_html_body .= "<td class='text-right'>" . number_format($grandTotalResi) . "</td>
                        </tr>
                    </tbody>
                </table>
                <p class='note'>Report generated on: " . date('d M Y, H:i:s') . " WIB</p>
                <p class='note'>Best regards,<br>Your Reporting Team</p>
            </div></body></html>";

            // --- PHPMailer Integration ---
            $mail = new PHPMailer(true); // Enable exceptions

            try {
                //Server settings
                $mail->SMTPDebug = SMTP::DEBUG_SERVER; // Set to SMTP::DEBUG_SERVER for detailed debugging output (RECOMMENDED FOR TESTING)
                $mail->isSMTP();
                $mail->Host       = SMTP_HOST;
                $mail->SMTPAuth   = true;
                $mail->Username   = SMTP_USERNAME;
                $mail->Password   = SMTP_PASSWORD;
                $mail->SMTPSecure = SMTP_ENCRYPTION;
                $mail->Port       = SMTP_PORT;
                $mail->CharSet    = 'UTF-8'; // Ensure correct character encoding

                //Recipients
                $mail->setFrom(SMTP_USERNAME, 'Wiselie Reporting'); // Sender email and name
                foreach ($valid_recipients as $recipient) {
                    $mail->addAddress($recipient); // Add recipients
                }

                // Content
                $mail->isHTML(true); // Set email format to HTML
                $mail->Subject = $custom_subject;
                $mail->Body    = $email_html_body;
                $mail->AltBody = strip_tags($email_html_body); // Plain text alternative for non-HTML mail clients

                $mail->send();
                $message = 'AWB report email sent successfully to: ' . htmlspecialchars(implode(', ', $valid_recipients)) . '.';
                $message_type = 'success';
                // Clear form inputs after successful send
                $_POST = [];

            } catch (Exception $e) {
                $message = "Email sending failed. Mailer Error: {$mail->ErrorInfo}";
                $message_type = 'error';
                error_log("AWB Report Emailer PHPMailer Error: " . $mail->ErrorInfo);
            }

        } catch (PDOException $e) {
            $message = "Database Error: " . $e->getMessage();
            $message_type = 'error';
            error_log("AWB Report Emailer DB Error: " . $e->getMessage());
        } catch (Exception $e) {
            $message = "General Error: " . $e->getMessage();
            $message_type = 'error';
            error_log("AWB Report Emailer General Error: " . $e->getMessage());
        }
    }
}
?>

<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Email AWB Report</title>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background-color: #f5f5f5;
            margin: 0;
            padding: 20px;
            color: #333;
        }
        .container {
            max-width: 700px;
            margin: 50px auto;
            background: white;
            padding: 30px;
            border-radius: 10px;
            box-shadow: 0 0 15px rgba(0,0,0,0.1);
        }
        h1 {
            color: #2c3e50;
            text-align: center;
            margin-bottom: 30px;
        }
        .form-group {
            margin-bottom: 15px;
        }
        label {
            display: block;
            margin-bottom: 5px;
            font-weight: bold;
            color: #555;
        }
        input[type="month"],
        input[type="text"],
        input[type="email"],
        textarea {
            width: 100%;
            padding: 10px;
            border: 1px solid #ddd;
            border-radius: 5px;
            box-sizing: border-box;
            font-size: 16px;
        }
        textarea {
            resize: vertical;
            min-height: 80px;
        }
        button {
            display: block;
            width: 100%;
            padding: 12px 20px;
            background-color: #3498db;
            color: white;
            border: none;
            border-radius: 5px;
            font-size: 18px;
            cursor: pointer;
            transition: background-color 0.3s ease;
            margin-top: 20px;
        }
        button:hover {
            background-color: #2980b9;
        }
        .message {
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
            text-align: center;
            font-weight: bold;
        }
        .message.success {
            background-color: #d4edda;
            color: #155724;
            border: 1px solid #c3e6cb;
        }
        .message.error {
            background-color: #f8d7da;
            color: #721c24;
            border: 1px solid #f5c6cb;
        }
        .info-text {
            font-size: 0.9em;
            color: #777;
            margin-top: 5px;
            margin-bottom: 15px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Email AWB Report</h1>

        <?php if (!empty($message)): ?>
            <div class="message <?= htmlspecialchars($message_type) ?>">
                <?= htmlspecialchars($message) ?>
            </div>
        <?php endif; ?>

        <form action="" method="POST">
            <div class="form-group">
                <label for="start_month">Report Start Month:</label>
                <input type="month" id="start_month" name="start_month" value="<?= htmlspecialchars($startMonth) ?>" max="<?= date('Y-m', strtotime('today')) ?>">
            </div>
            <div class="form-group">
                <label for="end_month">Report End Month:</label>
                <input type="month" id="end_month" name="end_month" value="<?= htmlspecialchars($endMonth) ?>" max="<?= date('Y-m', strtotime('today')) ?>">
            </div>
            <hr style="border: 0; border-top: 1px solid #eee; margin: 25px 0;">
            <div class="form-group">
                <label for="recipient_emails">Recipient Email(s):</label>
                <input type="text" id="recipient_emails" name="recipient_emails" placeholder="e.g., ceo@example.com, manager@example.com" required value="<?= htmlspecialchars($_POST['recipient_emails'] ?? '') ?>">
                <div class="info-text">Separate multiple emails with commas.</div>
            </div>
            <div class="form-group">
                <label for="custom_subject">Email Subject (Optional):</label>
                <input type="text" id="custom_subject" name="custom_subject" value="<?= htmlspecialchars($_POST['custom_subject'] ?? DEFAULT_SUBJECT) ?>">
            </div>
            <div class="form-group">
                <label for="custom_message_body">Custom Message for Report (Optional):</label>
                <textarea id="custom_message_body" name="custom_message_body" placeholder="Add any specific notes or context for the report..."><?= htmlspecialchars($_POST['custom_message_body'] ?? '') ?></textarea>
            </div>
            <button type="submit">Generate & Send Report</button>
        </form>
    </div>
</body>
</html>