<?php
session_start();
include 'layouts/dbconn.php';

// Required PHPMailer and FPDF files
require_once 'vendor/phpmailer/src/PHPMailer.php';
require_once 'vendor/phpmailer/src/SMTP.php';
require_once 'vendor/phpmailer/src/Exception.php';
require_once 'fpdf/fpdf.php';
require_once 'classes/PremiumCalculator.php';

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

if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

try {
    // Start transaction
    //$conn->begin_transaction();
    $conn->autocommit(FALSE);
    $transaction_started = true;

    // Load email settings
    $sql = "SELECT setting_key, setting_value FROM settings WHERE setting_key IN 
            ('email_host', 'email_port', 'email_username', 'email_password', 
             'email_sender', 'system_name')";
    $result = $conn->query($sql);
    $settings = [];
    while ($row = $result->fetch_assoc()) {
        $settings[$row['setting_key']] = $row['setting_value'];
    }

    // Generate quotation number
    $year = date('Y');
    $sql = "SELECT MAX(CAST(SUBSTRING(quotation_no, 8) AS UNSIGNED)) as last_num 
            FROM quotations 
            WHERE quotation_no LIKE 'QT{$year}%'";
    $result = $conn->query($sql);
    $row = $result->fetch_assoc();
    $next_num = ($row['last_num'] ?? 0) + 1;
    $quotation_no = 'QT' . $year . str_pad($next_num, 5, '0', STR_PAD_LEFT);

    // Initialize calculator
    $calculator = new PremiumCalculator($conn, $_POST['corp_id']);

    // Calculate premiums and save members
    $total_premium = 0;
    $members_data = [];

    foreach ($_POST['members'] as $member) {
        // Validate member data
        $validation_errors = $calculator->validateMemberLimits([
            'relation' => $member['relation'],
            'age' => $member['age'],
            'quotation_no' => $quotation_no
        ]);

        if (!empty($validation_errors)) {
            throw new Exception("Member validation failed: " . implode(", ", $validation_errors));
        }

        // Calculate premium
        $premium_details = $calculator->calculatePremium([
            'relation' => $member['relation'],
            'age' => $member['age'],
            'benefits' => $member['benefits'],
            'quotation_no' => $quotation_no
        ]);

        $total_premium += $premium_details['grand_total'];
        $members_data[] = [
            'member' => $member,
            'premium_details' => $premium_details
        ];
    }

    // Save quotation header
    $sql = "INSERT INTO quotations (
        quotation_no,
        corp_id,
        quotation_type,
        total_premium,
        valid_until,
        status,
        generated_by,
        generated_at
    ) VALUES (?, ?, ?, ?, ?, 'PENDING', ?, NOW())";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param('sssdsi',
        $quotation_no,
        $_POST['corp_id'],
        $_POST['quotation_type'],
        $total_premium,
        $_POST['valid_until'],
        $_SESSION['user_id']
    );
    $stmt->execute();

    // Save quotation members
    $sql = "INSERT INTO quotation_members (
        quotation_no,
        relation,
        age,
        premium
    ) VALUES (?, ?, ?, ?)";

    $stmt = $conn->prepare($sql);
    foreach ($members_data as $data) {
        $member = $data['member'];
        $premium = $data['premium_details']['grand_total'];

        $stmt->bind_param('ssid',
            $quotation_no,
            $member['relation'],
            $member['age'],
            $premium
        );
        $stmt->execute();

        $member_id = $stmt->insert_id;

        // Save member benefits
        $sql_benefits = "INSERT INTO quotation_member_benefits (
            quotation_no,
            member_id,
            benefit_code,
            premium,
            limit_amount
        ) VALUES (?, ?, ?, ?, ?)";

        $stmt_benefits = $conn->prepare($sql_benefits);
        foreach ($member['benefits'] as $benefit_code) {
            $benefit_details = $data['premium_details']['benefit_premiums'][$benefit_code] ?? null;
            if ($benefit_details) {
                $stmt_benefits->bind_param('siidi',
                    $quotation_no,
                    $member_id,
                    $benefit_code,
                    $benefit_details['final_premium'],
                    $benefit_details['limit']
                );
                $stmt_benefits->execute();
            }
        }
    }

    // Create quotations directory if it doesn't exist
    if (!is_dir('quotations')) {
        mkdir('quotations', 0755, true);
    }

    // Generate PDF
    $pdf_path = generateQuotationPDF($quotation_no, $_POST['corp_id'], $members_data, $total_premium, $_POST['valid_until'], $conn);

    // Send email
    if (!empty($_POST['email_to'])) {
        $mail = new PHPMailer(true);

        try {
            // Server settings
            $mail->isSMTP();
            $mail->Host = $settings['email_host'];
            $mail->SMTPAuth = true;
            $mail->Username = $settings['email_username'];
            $mail->Password = $settings['email_password'];
            $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
            $mail->Port = intval($settings['email_port']);

            // Recipients
            $mail->setFrom($settings['email_sender'], $settings['system_name']);
            $mail->addAddress($_POST['email_to']);

            // Add CC recipients
            if (!empty($_POST['email_cc'])) {
                $cc_addresses = array_map('trim', explode(',', $_POST['email_cc']));
                foreach ($cc_addresses as $cc) {
                    if (filter_var($cc, FILTER_VALIDATE_EMAIL)) {
                        $mail->addCC($cc);
                    }
                }
            }

            // Add BCC recipients
            if (!empty($_POST['email_bcc'])) {
                $bcc_addresses = array_map('trim', explode(',', $_POST['email_bcc']));
                foreach ($bcc_addresses as $bcc) {
                    if (filter_var($bcc, FILTER_VALIDATE_EMAIL)) {
                        $mail->addBCC($bcc);
                    }
                }
            }

            // Email content
            $mail->isHTML(true);
            $mail->Subject = $_POST['email_subject'];
            
            // Replace placeholders in email content
            $email_content = str_replace(
                ['{VALID_DATE}'],
                [date('d/m/Y', strtotime($_POST['valid_until']))],
                $_POST['email_content']
            );
            
            $mail->Body = nl2br($email_content);
            $mail->AltBody = strip_tags($email_content);
            
            // Attach PDF
            $mail->addAttachment($pdf_path, "Quotation_{$quotation_no}.pdf");

            // Send email
            $mail->send();

            // Log successful email
            $sql = "INSERT INTO email_logs (
                quotation_no,
                recipient,
                cc,
                bcc,
                subject,
                content,
                status,
                sent_by,
                sent_at
            ) VALUES (?, ?, ?, ?, ?, ?, 'SENT', ?, NOW())";

            $stmt = $conn->prepare($sql);
            $stmt->bind_param('ssssssi',
                $quotation_no,
                $_POST['email_to'],
                $_POST['email_cc'],
                $_POST['email_bcc'],
                $_POST['email_subject'],
                $email_content,
                $_SESSION['user_id']
            );
            $stmt->execute();

        } catch (Exception $e) {
            // Log failed email
            $sql = "INSERT INTO email_logs (
                quotation_no,
                recipient,
                status,
                error_message,
                sent_by,
                sent_at
            ) VALUES (?, ?, 'FAILED', ?, ?, NOW())";

            $stmt = $conn->prepare($sql);
            $stmt->bind_param('sssi',
                $quotation_no,
                $_POST['email_to'],
                $e->getMessage(),
                $_SESSION['user_id']
            );
            $stmt->execute();
        }
    }

    // Commit transaction
    //$conn->commit();
    
    // Commit transaction
    $conn->commit();
    $conn->autocommit(TRUE);
    $transaction_started = false;

    // Return success response
    echo json_encode([
        'success' => true,
        'quotation_no' => $quotation_no,
        'message' => 'Quotation generated and sent successfully'
    ]);

} catch (Exception $e) {
    // Rollback transaction on error
    //if ($conn->inTransaction()) {
    //    $conn->rollback();
    //}
    
    if (isset($transaction_started) && $transaction_started) {
        $conn->rollback();
        $conn->autocommit(TRUE);
    }
    
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage()
    ]);
}

function generateQuotationPDF($quotation_no, $corp_id, $members_data, $total_premium, $valid_until, $conn) {
    $pdf = new FPDF();
    $pdf->AddPage();

    // Header
    $pdf->SetFont('Arial', 'B', 16);
    $pdf->Cell(0, 10, 'INSURANCE QUOTATION', 0, 1, 'C');
    
    // Get corporate details
    $sql = "SELECT * FROM corporate WHERE corp_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $corp_id);
    $stmt->execute();
    $corporate = $stmt->get_result()->fetch_assoc();

    // Quotation details
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 10, 'Quotation Details', 0, 1, 'L');
    $pdf->SetFont('Arial', '', 10);

    $pdf->Cell(40, 7, 'Quotation No:', 0, 0);
    $pdf->Cell(0, 7, $quotation_no, 0, 1);
    
    $pdf->Cell(40, 7, 'Corporate:', 0, 0);
    $pdf->Cell(0, 7, $corporate['corporate'], 0, 1);
    
    $pdf->Cell(40, 7, 'Valid Until:', 0, 0);
    $pdf->Cell(0, 7, date('d/m/Y', strtotime($valid_until)), 0, 1);

    $pdf->Ln(5);

    // Members and Benefits
    foreach ($members_data as $data) {
        $member = $data['member'];
        $premium_details = $data['premium_details'];

        $pdf->SetFont('Arial', 'B', 12);
        $pdf->Cell(0, 10, ucfirst($member['relation']) . ' - Age ' . $member['age'], 0, 1, 'L');
        $pdf->SetFont('Arial', '', 10);

        // Benefits table header
        $pdf->Cell(70, 7, 'Benefit', 1, 0, 'C');
        $pdf->Cell(40, 7, 'Limit', 1, 0, 'C');
        $pdf->Cell(40, 7, 'Premium', 1, 1, 'C');

        // Benefits details
        foreach ($premium_details['benefit_premiums'] as $benefit_code => $details) {
            $benefit_name = getBenefitName($benefit_code, $conn);
            $pdf->Cell(70, 7, $benefit_name, 1, 0);
            $pdf->Cell(40, 7, number_format($details['limit'], 2), 1, 0, 'R');
            $pdf->Cell(40, 7, number_format($details['final_premium'], 2), 1, 1, 'R');
        }

        // Member total
        $pdf->SetFont('Arial', 'B', 10);
        $pdf->Cell(110, 7, 'Member Total:', 1, 0, 'R');
        $pdf->Cell(40, 7, number_format($premium_details['grand_total'], 2), 1, 1, 'R');
        
        $pdf->Ln(5);
    }

    // Total amount
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(110, 10, 'Total Premium:', 0, 0, 'R');
    $pdf->Cell(40, 10, 'Ksh. ' . number_format($total_premium, 2), 0, 1, 'R');

    // Terms and conditions
    $pdf->Ln(10);
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->Cell(0, 7, 'Terms and Conditions:', 0, 1, 'L');
    $pdf->SetFont('Arial', '', 9);

    $terms = [
        "1. This quotation is valid until " . date('d/m/Y', strtotime($valid_until)),
        "2. All premiums quoted are inclusive of applicable taxes",
        "3. Coverage is subject to full underwriting and policy terms",
        "4. Pre-existing conditions may be excluded",
        "5. Waiting periods may apply for specific benefits"
    ];

    foreach ($terms as $term) {
        $pdf->Cell(0, 5, $term, 0, 1, 'L');
    }

    // Save PDF
    $pdf_path = 'quotations/' . $quotation_no . '.pdf';
    $pdf->Output('F', $pdf_path);
    
    return $pdf_path;
}

function getBenefitName($benefit_code, $conn) {
    $sql = "SELECT benefit FROM benefit WHERE code = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('i', $benefit_code);
    $stmt->execute();
    $result = $stmt->get_result();
    $benefit = $result->fetch_assoc();
    return $benefit ? $benefit['benefit'] : 'Unknown Benefit';
}

$conn->close();
?>