<?php
session_start();
header('Content-Type: application/json');

if (!isset($_SESSION['admin_id'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
    exit;
}

require_once '../config/config.php';
require_once '../config/db.php';

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

$autoload_paths = [
    '../vendor/autoload.php',
    '../../vendor/autoload.php',
    '../../../vendor/autoload.php'
];

foreach ($autoload_paths as $path) {
    if (file_exists($path)) {
        require_once $path;
        break;
    }
}

$action = $_POST['action'] ?? '';
$current_user_id = $_SESSION['admin_id'];

// ==================== ADD TEMPLATE ====================
if ($action === 'add_template') {
    
    $template_name = mysqli_real_escape_string($con, $_POST['template_name']);
    $template_code = mysqli_real_escape_string($con, $_POST['template_code']);
    $category = mysqli_real_escape_string($con, $_POST['category']);
    $is_active = mysqli_real_escape_string($con, $_POST['is_active']);
    $color_scheme = mysqli_real_escape_string($con, $_POST['color_scheme']);
    
    $introduction = mysqli_real_escape_string($con, $_POST['introduction']);
    $company_overview = mysqli_real_escape_string($con, $_POST['company_overview']);
    $services_offered = mysqli_real_escape_string($con, $_POST['services_offered']);
    $methodology = mysqli_real_escape_string($con, $_POST['methodology']);
    $timeline_structure = mysqli_real_escape_string($con, $_POST['timeline_structure']);
    $pricing_structure = mysqli_real_escape_string($con, $_POST['pricing_structure']);
    $terms_conditions = mysqli_real_escape_string($con, $_POST['terms_conditions']);
    $conclusion = mysqli_real_escape_string($con, $_POST['conclusion']);
    $footer_text = mysqli_real_escape_string($con, $_POST['footer_text'] ?? '');
    
    // Check if template code exists
    $check_code = mysqli_query($con, "SELECT template_id FROM tbl_proposal_templates WHERE template_code = '$template_code'");
    if (mysqli_num_rows($check_code) > 0) {
        echo json_encode(['success' => false, 'message' => 'Template code already exists']);
        exit;
    }
    
    $query = "INSERT INTO tbl_proposal_templates (
                template_name, template_code, category, is_active, color_scheme,
                introduction, company_overview, services_offered, methodology,
                timeline_structure, pricing_structure, terms_conditions, conclusion,
                footer_text, created_by
              ) VALUES (
                '$template_name', '$template_code', '$category', '$is_active', '$color_scheme',
                '$introduction', '$company_overview', '$services_offered', '$methodology',
                '$timeline_structure', '$pricing_structure', '$terms_conditions', '$conclusion',
                '$footer_text', $current_user_id
              )";
    
    if (mysqli_query($con, $query)) {
        echo json_encode(['success' => true, 'message' => 'Template created successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== EDIT TEMPLATE ====================
elseif ($action === 'edit_template') {
    
    $template_id = intval($_POST['template_id']);
    $template_name = mysqli_real_escape_string($con, $_POST['template_name']);
    $template_code = mysqli_real_escape_string($con, $_POST['template_code']);
    $category = mysqli_real_escape_string($con, $_POST['category']);
    $is_active = mysqli_real_escape_string($con, $_POST['is_active']);
    $color_scheme = mysqli_real_escape_string($con, $_POST['color_scheme']);
    
    $introduction = mysqli_real_escape_string($con, $_POST['introduction']);
    $company_overview = mysqli_real_escape_string($con, $_POST['company_overview']);
    $services_offered = mysqli_real_escape_string($con, $_POST['services_offered']);
    $methodology = mysqli_real_escape_string($con, $_POST['methodology']);
    $timeline_structure = mysqli_real_escape_string($con, $_POST['timeline_structure']);
    $pricing_structure = mysqli_real_escape_string($con, $_POST['pricing_structure']);
    $terms_conditions = mysqli_real_escape_string($con, $_POST['terms_conditions']);
    $conclusion = mysqli_real_escape_string($con, $_POST['conclusion']);
    $footer_text = mysqli_real_escape_string($con, $_POST['footer_text'] ?? '');
    
    // Check if template code exists for other templates
    $check_code = mysqli_query($con, "SELECT template_id FROM tbl_proposal_templates WHERE template_code = '$template_code' AND template_id != $template_id");
    if (mysqli_num_rows($check_code) > 0) {
        echo json_encode(['success' => false, 'message' => 'Template code already exists']);
        exit;
    }
    
    $query = "UPDATE tbl_proposal_templates SET 
                template_name = '$template_name',
                template_code = '$template_code',
                category = '$category',
                is_active = '$is_active',
                color_scheme = '$color_scheme',
                introduction = '$introduction',
                company_overview = '$company_overview',
                services_offered = '$services_offered',
                methodology = '$methodology',
                timeline_structure = '$timeline_structure',
                pricing_structure = '$pricing_structure',
                terms_conditions = '$terms_conditions',
                conclusion = '$conclusion',
                footer_text = '$footer_text'
              WHERE template_id = $template_id";
    
    if (mysqli_query($con, $query)) {
        echo json_encode(['success' => true, 'message' => 'Template updated successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== DELETE TEMPLATE ====================
elseif ($action === 'delete_template') {
    
    $template_id = intval($_POST['template_id']);
    
    // Check if template is default
    $check_default = mysqli_query($con, "SELECT is_default FROM tbl_proposal_templates WHERE template_id = $template_id");
    $template_data = mysqli_fetch_assoc($check_default);
    
    if ($template_data['is_default'] === 'Yes') {
        echo json_encode(['success' => false, 'message' => 'Cannot delete default template']);
        exit;
    }
    
    // Check if template is used in any proposals
    $check_usage = mysqli_query($con, "SELECT COUNT(*) as count FROM tbl_proposals WHERE template_id = $template_id");
    $usage_data = mysqli_fetch_assoc($check_usage);
    
    if ($usage_data['count'] > 0) {
        echo json_encode(['success' => false, 'message' => 'Cannot delete template - it is used in ' . $usage_data['count'] . ' proposal(s)']);
        exit;
    }
    
    $query = "DELETE FROM tbl_proposal_templates WHERE template_id = $template_id";
    
    if (mysqli_query($con, $query)) {
        echo json_encode(['success' => true, 'message' => 'Template deleted successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== DUPLICATE TEMPLATE ====================
elseif ($action === 'duplicate_template') {
    
    $template_id = intval($_POST['template_id']);
    
    // Get original template
    $get_template = mysqli_query($con, "SELECT * FROM tbl_proposal_templates WHERE template_id = $template_id");
    $template = mysqli_fetch_assoc($get_template);
    
    if (!$template) {
        echo json_encode(['success' => false, 'message' => 'Template not found']);
        exit;
    }
    
    // Generate new template code
    $new_code = $template['template_code'] . '-COPY';
    $counter = 1;
    while (mysqli_num_rows(mysqli_query($con, "SELECT template_id FROM tbl_proposal_templates WHERE template_code = '$new_code'")) > 0) {
        $new_code = $template['template_code'] . '-COPY' . $counter;
        $counter++;
    }
    
    $new_name = $template['template_name'] . ' (Copy)';
    
    $query = "INSERT INTO tbl_proposal_templates (
                template_name, template_code, category, is_active, color_scheme,
                introduction, company_overview, services_offered, methodology,
                timeline_structure, pricing_structure, terms_conditions, conclusion,
                footer_text, is_default, created_by
              ) VALUES (
                '$new_name', '$new_code', '{$template['category']}', '{$template['is_active']}', '{$template['color_scheme']}',
                '{$template['introduction']}', '{$template['company_overview']}', '{$template['services_offered']}', '{$template['methodology']}',
                '{$template['timeline_structure']}', '{$template['pricing_structure']}', '{$template['terms_conditions']}', '{$template['conclusion']}',
                '{$template['footer_text']}', 'No', $current_user_id
              )";
    
    if (mysqli_query($con, $query)) {
        echo json_encode(['success' => true, 'message' => 'Template duplicated successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== SET DEFAULT TEMPLATE ====================
elseif ($action === 'set_default_template') {
    
    $template_id = intval($_POST['template_id']);
    
    // First, remove default from all templates
    mysqli_query($con, "UPDATE tbl_proposal_templates SET is_default = 'No'");
    
    // Then set the selected template as default
    $query = "UPDATE tbl_proposal_templates SET is_default = 'Yes' WHERE template_id = $template_id";
    
    if (mysqli_query($con, $query)) {
        echo json_encode(['success' => true, 'message' => 'Default template updated successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== GET CUSTOMER DEFAULT TEMPLATE ====================
elseif ($action === 'get_customer_template') {
    
    // Get default template
    $default_query = "SELECT * FROM tbl_proposal_templates WHERE is_default = 'Yes' LIMIT 1";
    $default_result = mysqli_query($con, $default_query);
    $default_template = mysqli_fetch_assoc($default_result);
    
    if ($default_template) {
        echo json_encode(['success' => true, 'template' => $default_template]);
    } else {
        // Fallback to first active template
        $fallback_query = "SELECT * FROM tbl_proposal_templates WHERE is_active = 'Yes' ORDER BY created_at DESC LIMIT 1";
        $fallback_result = mysqli_query($con, $fallback_query);
        $fallback_template = mysqli_fetch_assoc($fallback_result);
        
        if ($fallback_template) {
            echo json_encode(['success' => true, 'template' => $fallback_template]);
        } else {
            echo json_encode(['success' => false, 'message' => 'No template found']);
        }
    }
}

// ==================== ADD PROPOSAL ====================
elseif ($action === 'add_proposal') {
    
    $customer_id = intval($_POST['customer_id']);
    $template_id = intval($_POST['template_id']);
    $proposal_title = mysqli_real_escape_string($con, $_POST['proposal_title']);
    $proposal_description = mysqli_real_escape_string($con, $_POST['proposal_description']);
    $proposal_date = mysqli_real_escape_string($con, $_POST['proposal_date']);
    $valid_until = mysqli_real_escape_string($con, $_POST['valid_until']);
    
    $introduction = mysqli_real_escape_string($con, $_POST['introduction'] ?? '');
    $company_overview = mysqli_real_escape_string($con, $_POST['company_overview'] ?? '');
    $services_offered = mysqli_real_escape_string($con, $_POST['services_offered'] ?? '');
    $methodology = mysqli_real_escape_string($con, $_POST['methodology'] ?? '');
    $timeline_details = mysqli_real_escape_string($con, $_POST['timeline_details'] ?? '');
    $pricing_details = mysqli_real_escape_string($con, $_POST['pricing_details'] ?? '');
    $terms_conditions = mysqli_real_escape_string($con, $_POST['terms_conditions'] ?? '');
    $conclusion = mysqli_real_escape_string($con, $_POST['conclusion'] ?? '');
    
    $proposal_items = mysqli_real_escape_string($con, $_POST['proposal_items'] ?? '[]');
    $total_amount = floatval($_POST['total_amount'] ?? 0);
    $discount_percentage = floatval($_POST['discount_percentage'] ?? 0);
    $discount_amount = floatval($_POST['discount_amount'] ?? 0);
    $final_amount = floatval($_POST['final_amount'] ?? 0);
    
    $internal_notes = mysqli_real_escape_string($con, $_POST['internal_notes'] ?? '');
    $status = mysqli_real_escape_string($con, $_POST['status'] ?? 'Draft');
    
    // Generate proposal code
    $last_code_query = mysqli_query($con, "SELECT proposal_code FROM tbl_proposals ORDER BY proposal_id DESC LIMIT 1");
    if (mysqli_num_rows($last_code_query) > 0) {
        $last_row = mysqli_fetch_assoc($last_code_query);
        $last_number = intval(str_replace('PROP-', '', $last_row['proposal_code']));
        $new_number = $last_number + 1;
    } else {
        $new_number = 1;
    }
    $proposal_code = 'PROP-' . str_pad($new_number, 4, '0', STR_PAD_LEFT);
    
$query = "INSERT INTO tbl_proposals (
    proposal_code, customer_id, template_id, proposal_title, proposal_description, proposal_date, valid_until,
                introduction, company_overview, services_offered, methodology, timeline_details,
                pricing_details, terms_conditions, conclusion, proposal_items,
                total_amount, discount_percentage, discount_amount, final_amount,
                internal_notes, status, created_by
              ) VALUES (
    '$proposal_code', $customer_id, $template_id, '$proposal_title', '$proposal_description', '$proposal_date', '$valid_until',
                '$introduction', '$company_overview', '$services_offered', '$methodology', '$timeline_details',
                '$pricing_details', '$terms_conditions', '$conclusion', '$proposal_items',
                $total_amount, $discount_percentage, $discount_amount, $final_amount,
                '$internal_notes', '$status', $current_user_id
              )";
    
    if (mysqli_query($con, $query)) {
        echo json_encode([
            'success' => true, 
            'message' => 'Proposal created successfully!',
            'proposal_code' => $proposal_code
        ]);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== EDIT PROPOSAL ====================
elseif ($action === 'edit_proposal') {
    
    $proposal_id = intval($_POST['proposal_id']);
    $customer_id = intval($_POST['customer_id']);
    $template_id = intval($_POST['template_id']);
    $proposal_title = mysqli_real_escape_string($con, $_POST['proposal_title']);
    $proposal_description = mysqli_real_escape_string($con, $_POST['proposal_description']);
    $proposal_date = mysqli_real_escape_string($con, $_POST['proposal_date']);
    $valid_until = mysqli_real_escape_string($con, $_POST['valid_until']);
    
    $introduction = mysqli_real_escape_string($con, $_POST['introduction'] ?? '');
    $company_overview = mysqli_real_escape_string($con, $_POST['company_overview'] ?? '');
    $services_offered = mysqli_real_escape_string($con, $_POST['services_offered'] ?? '');
    $methodology = mysqli_real_escape_string($con, $_POST['methodology'] ?? '');
    $timeline_details = mysqli_real_escape_string($con, $_POST['timeline_details'] ?? '');
    $pricing_details = mysqli_real_escape_string($con, $_POST['pricing_details'] ?? '');
    $terms_conditions = mysqli_real_escape_string($con, $_POST['terms_conditions'] ?? '');
    $conclusion = mysqli_real_escape_string($con, $_POST['conclusion'] ?? '');
    
    $proposal_items = mysqli_real_escape_string($con, $_POST['proposal_items'] ?? '[]');
    $total_amount = floatval($_POST['total_amount'] ?? 0);
    $discount_percentage = floatval($_POST['discount_percentage'] ?? 0);
    $discount_amount = floatval($_POST['discount_amount'] ?? 0);
    $final_amount = floatval($_POST['final_amount'] ?? 0);
    
    $internal_notes = mysqli_real_escape_string($con, $_POST['internal_notes'] ?? '');
    $status = mysqli_real_escape_string($con, $_POST['status'] ?? 'Draft');
    
  $query = "UPDATE tbl_proposals SET 
    customer_id = $customer_id,
    template_id = $template_id,
    proposal_title = '$proposal_title',
    proposal_description = '$proposal_description',
    proposal_date = '$proposal_date',
                valid_until = '$valid_until',
                introduction = '$introduction',
                company_overview = '$company_overview',
                services_offered = '$services_offered',
                methodology = '$methodology',
                timeline_details = '$timeline_details',
                pricing_details = '$pricing_details',
                terms_conditions = '$terms_conditions',
                conclusion = '$conclusion',
                proposal_items = '$proposal_items',
                total_amount = $total_amount,
                discount_percentage = $discount_percentage,
                discount_amount = $discount_amount,
                final_amount = $final_amount,
                internal_notes = '$internal_notes',
                status = '$status'
              WHERE proposal_id = $proposal_id";
    
    if (mysqli_query($con, $query)) {
        echo json_encode(['success' => true, 'message' => 'Proposal updated successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== DELETE PROPOSAL ====================
elseif ($action === 'delete_proposal') {
    
    $proposal_id = intval($_POST['proposal_id']);
    
    $query = "DELETE FROM tbl_proposals WHERE proposal_id = $proposal_id";
    
    if (mysqli_query($con, $query)) {
        echo json_encode(['success' => true, 'message' => 'Proposal deleted successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== GET PROPOSAL (FOR EDITING) ====================
elseif ($action === 'get_proposal') {
    
    $proposal_id = intval($_POST['proposal_id']);
    
    $query = "SELECT * FROM tbl_proposals WHERE proposal_id = $proposal_id";
    $result = mysqli_query($con, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        $proposal = mysqli_fetch_assoc($result);
        echo json_encode(['success' => true, 'proposal' => $proposal]);
    } else {
        echo json_encode(['success' => false, 'message' => 'Proposal not found']);
    }
}

// ==================== UPDATE PROPOSAL STATUS WITH TRACKING ====================
elseif ($action === 'update_proposal_status') {
    
    $proposal_id = intval($_POST['proposal_id']);
    $new_status = mysqli_real_escape_string($con, $_POST['new_status']);
    $remarks = mysqli_real_escape_string($con, $_POST['remarks'] ?? '');
    
    // Get current proposal status
    $current_query = "SELECT status, sent_count, rejected_count, expired_count, negotiate_count 
                      FROM tbl_proposals WHERE proposal_id = $proposal_id";
    $current_result = mysqli_query($con, $current_query);
    $current_data = mysqli_fetch_assoc($current_result);
    $previous_status = $current_data['status'];
    
    // Don't update if status is the same
    if ($previous_status === $new_status) {
        echo json_encode(['success' => false, 'message' => 'Status is already ' . $new_status]);
        exit;
    }
    
    // Increment appropriate counter
    $sent_count = $current_data['sent_count'];
    $rejected_count = $current_data['rejected_count'];
    $expired_count = $current_data['expired_count'];
    $negotiate_count = $current_data['negotiate_count'];
    
    if ($new_status === 'Negotiate') {
        $negotiate_count++;
    }
    
    // Update proposal status and counters
    $update_query = "UPDATE tbl_proposals SET 
                     status = '$new_status',
                     negotiate_count = $negotiate_count,
                     last_status_change = NOW()
                     WHERE proposal_id = $proposal_id";
    
    if (mysqli_query($con, $update_query)) {
        // Insert history record
        $history_query = "INSERT INTO tbl_proposal_history 
                         (proposal_id, previous_status, new_status, changed_by, remarks)
                         VALUES ($proposal_id, '$previous_status', '$new_status', $current_user_id, '$remarks')";
        mysqli_query($con, $history_query);
        
        echo json_encode([
            'success' => true, 
            'message' => 'Proposal status updated to ' . $new_status,
            'sent_count' => $sent_count,
            'rejected_count' => $rejected_count,
            'expired_count' => $expired_count,
            'negotiate_count' => $negotiate_count
        ]);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== GET PROPOSAL HISTORY ====================
elseif ($action === 'get_proposal_history') {
    
    $proposal_id = intval($_POST['proposal_id']);
    
    $query = "SELECT h.*, u.fname as changed_by_name 
              FROM tbl_proposal_history h
              LEFT JOIN tbl_user u ON h.changed_by = u.uid
              WHERE h.proposal_id = $proposal_id
              ORDER BY h.changed_at DESC";
    
    $result = mysqli_query($con, $query);
    
    $history = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $history[] = $row;
    }
    
    // Get proposal counters
    $counter_query = "SELECT sent_count, rejected_count, expired_count, negotiate_count, 
                      last_sent_date FROM tbl_proposals WHERE proposal_id = $proposal_id";
    $counter_result = mysqli_query($con, $counter_query);
    $counters = mysqli_fetch_assoc($counter_result);
    
    echo json_encode([
        'success' => true, 
        'history' => $history,
        'counters' => $counters
    ]);
}

// ==================== ACCEPT PROPOSAL ====================
elseif ($action === 'accept_proposal') {
    
    $proposal_id = intval($_POST['proposal_id']);
    $remarks = mysqli_real_escape_string($con, $_POST['remarks'] ?? 'Proposal accepted');
    
    // Get current status
    $current_query = "SELECT status FROM tbl_proposals WHERE proposal_id = $proposal_id";
    $current_result = mysqli_query($con, $current_query);
    $current_data = mysqli_fetch_assoc($current_result);
    $previous_status = $current_data['status'];
    
    // Update to Accepted
    $update_query = "UPDATE tbl_proposals SET 
                     status = 'Accepted',
                     last_status_change = NOW()
                     WHERE proposal_id = $proposal_id";
    
    if (mysqli_query($con, $update_query)) {
        // Insert history
        $history_query = "INSERT INTO tbl_proposal_history 
                         (proposal_id, previous_status, new_status, changed_by, remarks)
                         VALUES ($proposal_id, '$previous_status', 'Accepted', $current_user_id, '$remarks')";
        mysqli_query($con, $history_query);
        
        echo json_encode(['success' => true, 'message' => 'Proposal accepted successfully!']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== REJECT PROPOSAL ====================
elseif ($action === 'reject_proposal') {
    
    $proposal_id = intval($_POST['proposal_id']);
    $reject_reason = mysqli_real_escape_string($con, $_POST['reject_reason'] ?? '');
    $remarks = mysqli_real_escape_string($con, $_POST['remarks'] ?? '');
    
    // Combine reason and remarks
    $full_remarks = $reject_reason;
    if (!empty($remarks)) {
        $full_remarks .= " - " . $remarks;
    }
    
    // Get current status
    $current_query = "SELECT status, rejected_count FROM tbl_proposals WHERE proposal_id = $proposal_id";
    $current_result = mysqli_query($con, $current_query);
    $current_data = mysqli_fetch_assoc($current_result);
    $previous_status = $current_data['status'];
    $rejected_count = $current_data['rejected_count'] + 1;
    
    // Update to Rejected
    $update_query = "UPDATE tbl_proposals SET 
                     status = 'Rejected',
                     rejected_count = $rejected_count,
                     last_status_change = NOW()
                     WHERE proposal_id = $proposal_id";
    
    if (mysqli_query($con, $update_query)) {
        // Insert history
        $history_query = "INSERT INTO tbl_proposal_history 
                         (proposal_id, previous_status, new_status, changed_by, remarks)
                         VALUES ($proposal_id, '$previous_status', 'Rejected', $current_user_id, '$full_remarks')";
        mysqli_query($con, $history_query);
        
        echo json_encode(['success' => true, 'message' => 'Proposal rejected']);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== SEND PROPOSAL ====================
elseif ($action === 'send_proposal') {
    
    $proposal_id = intval($_POST['proposal_id']);
    $customer_email = mysqli_real_escape_string($con, $_POST['customer_email'] ?? '');
    $customer_name = mysqli_real_escape_string($con, $_POST['customer_name'] ?? '');
    $proposal_code = mysqli_real_escape_string($con, $_POST['proposal_code'] ?? '');
    
    // Get current status
    $current_query = "SELECT status, sent_count, proposal_code FROM tbl_proposals WHERE proposal_id = $proposal_id";
    $current_result = mysqli_query($con, $current_query);
    $current_data = mysqli_fetch_assoc($current_result);
    $previous_status = $current_data['status'];
    $sent_count = $current_data['sent_count'] + 1;
    
    if (empty($proposal_code)) {
        $proposal_code = $current_data['proposal_code'];
    }
    
    // Update to Sent
    $update_query = "UPDATE tbl_proposals SET 
                     status = 'Sent',
                     sent_count = $sent_count,
                     last_sent_date = NOW(),
                     last_status_change = NOW()
                     WHERE proposal_id = $proposal_id";
    
    if (mysqli_query($con, $update_query)) {
        // Insert history
        $history_query = "INSERT INTO tbl_proposal_history 
                         (proposal_id, previous_status, new_status, changed_by, remarks)
                         VALUES ($proposal_id, '$previous_status', 'Sent', $current_user_id, 'Proposal sent to customer via email')";
        mysqli_query($con, $history_query);
        
        // Send email
        $email_sent = sendProposalEmail($customer_email, $customer_name, $proposal_code, $proposal_id);
        
        echo json_encode([
            'success' => true, 
            'message' => 'Proposal sent successfully!',
            'email_sent' => $email_sent
        ]);
    } else {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
    }
}

// ==================== SAVE DRAFT (PARTIAL DATA) ====================
elseif ($action === 'save_draft') {
    
    $proposal_id = isset($_POST['proposal_id']) && !empty($_POST['proposal_id']) ? intval($_POST['proposal_id']) : 0;
    $customer_id = isset($_POST['customer_id']) && !empty($_POST['customer_id']) ? intval($_POST['customer_id']) : 0;
    $template_id = isset($_POST['template_id']) && !empty($_POST['template_id']) ? intval($_POST['template_id']) : 0;
    $proposal_title = mysqli_real_escape_string($con, $_POST['proposal_title'] ?? 'Untitled Draft');
    $proposal_description = mysqli_real_escape_string($con, $_POST['proposal_description'] ?? '');
    $proposal_date = mysqli_real_escape_string($con, $_POST['proposal_date'] ?? date('Y-m-d'));
    $valid_until = mysqli_real_escape_string($con, $_POST['valid_until'] ?? date('Y-m-d', strtotime('+30 days')));
    
    $proposal_items = mysqli_real_escape_string($con, $_POST['proposal_items'] ?? '[]');
    $total_amount = floatval($_POST['total_amount'] ?? 0);
    $discount_percentage = floatval($_POST['discount_percentage'] ?? 0);
    $discount_amount = floatval($_POST['discount_amount'] ?? 0);
    $final_amount = floatval($_POST['final_amount'] ?? 0);
    
    $internal_notes = mysqli_real_escape_string($con, $_POST['internal_notes'] ?? '');
    
    if ($proposal_id > 0) {
        // Update existing draft
     $query = "UPDATE tbl_proposals SET 
    customer_id = $customer_id,
    template_id = $template_id,
    proposal_title = '$proposal_title',
    proposal_description = '$proposal_description',
                  proposal_date = '$proposal_date',
                  valid_until = '$valid_until',
                  proposal_items = '$proposal_items',
                  total_amount = $total_amount,
                  discount_percentage = $discount_percentage,
                  discount_amount = $discount_amount,
                  final_amount = $final_amount,
                  internal_notes = '$internal_notes',
                  status = 'Draft'
                  WHERE proposal_id = $proposal_id";
        
        if (mysqli_query($con, $query)) {
            echo json_encode(['success' => true, 'message' => 'Draft updated successfully!']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
        }
    } else {
        // Create new draft
        $last_code_query = mysqli_query($con, "SELECT proposal_code FROM tbl_proposals ORDER BY proposal_id DESC LIMIT 1");
        if (mysqli_num_rows($last_code_query) > 0) {
            $last_row = mysqli_fetch_assoc($last_code_query);
            $last_number = intval(str_replace('PROP-', '', $last_row['proposal_code']));
            $new_number = $last_number + 1;
        } else {
            $new_number = 1;
        }
        $proposal_code = 'PROP-' . str_pad($new_number, 4, '0', STR_PAD_LEFT);
        
      $query = "INSERT INTO tbl_proposals (
    proposal_code, customer_id, template_id, proposal_title, proposal_description, proposal_date, valid_until,
                    proposal_items, total_amount, discount_percentage, discount_amount, final_amount,
                    internal_notes, status, created_by
                  )VALUES (
    '$proposal_code', $customer_id, $template_id, '$proposal_title', '$proposal_description', '$proposal_date', '$valid_until',
                    '$proposal_items', $total_amount, $discount_percentage, $discount_amount, $final_amount,
                    '$internal_notes', 'Draft', $current_user_id
                  )";
        
        if (mysqli_query($con, $query)) {
            echo json_encode([
                'success' => true, 
                'message' => 'Draft saved successfully!',
                'proposal_code' => $proposal_code
            ]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . mysqli_error($con)]);
        }
    }
}

else {
    echo json_encode(['success' => false, 'message' => 'Invalid action']);
}

// ==================== EMAIL SENDING FUNCTION ====================
function sendProposalEmail($to_email, $customer_name, $proposal_code, $proposal_id) {
    if (!class_exists('PHPMailer\PHPMailer\PHPMailer')) {
        return false;
    }
    
    try {
        $mail = new PHPMailer(true);
        
        $mail->SMTPDebug = 0;
        $mail->isSMTP();
        $mail->Host = SMTP_HOST;
        $mail->SMTPAuth = true;
        $mail->Username = SMTP_USERNAME;
        $mail->Password = SMTP_PASSWORD;
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
        $mail->Port = SMTP_PORT;
        $mail->CharSet = 'UTF-8';
        
        $mail->SMTPOptions = array(
            'ssl' => array(
                'verify_peer' => false,
                'verify_peer_name' => false,
                'allow_self_signed' => true
            )
        );
        
        $mail->setFrom(SMTP_FROM_EMAIL, SMTP_FROM_NAME);
        $mail->addAddress($to_email, $customer_name);
        $mail->addReplyTo(SMTP_FROM_EMAIL, SMTP_FROM_NAME);
        
        $mail->isHTML(true);
        $mail->Subject = 'Business Proposal - ' . $proposal_code;
        
        $view_link = (isset($_SERVER['HTTPS']) ? 'https' : 'http') . "://{$_SERVER['HTTP_HOST']}/admin/view_proposal.php?id={$proposal_id}";
        
        $mail->Body = "
        <!DOCTYPE html>
        <html>
        <head>
            <meta charset='UTF-8'>
        </head>
        <body style='font-family: Arial, sans-serif; line-height: 1.6; margin: 0; padding: 0;'>
            <div style='max-width: 600px; margin: 20px auto; background: white; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 10px rgba(0,0,0,0.1);'>
                <div style='background: #000; color: white; padding: 30px; text-align: center;'>
                    <h1 style='margin: 0; font-size: 24px;'>Business Proposal</h1>
                </div>
                <div style='padding: 30px;'>
                    <h2 style='color: #333; margin-top: 0;'>Dear $customer_name,</h2>
                    <p>We are pleased to share our business proposal with you.</p>
                    
                    <div style='background: #f8f9fa; padding: 20px; margin: 20px 0; border-left: 4px solid #000; border-radius: 4px;'>
                        <p style='margin: 5px 0;'><strong>Proposal Code:</strong> $proposal_code</p>
                    </div>
                    
                    <p>Please review the proposal and let us know if you have any questions.</p>
                    
                    <div style='text-align: center; margin: 30px 0;'>
                        <a href='$view_link' 
                           style='background: #000; color: white; padding: 12px 30px; text-decoration: none; border-radius: 5px; display: inline-block;'>
                            View Proposal
                        </a>
                    </div>
                    
                    <p>Best regards,<br><strong>TDS Projects Team</strong></p>
                </div>
                <div style='text-align: center; padding: 20px; color: #666; font-size: 14px; background: #f8f9fa;'>
                    <p>&copy; " . date('Y') . " TheDotStudios. All rights reserved.</p>
                </div>
            </div>
        </body>
        </html>
        ";
        
        $mail->AltBody = "Dear $customer_name,\n\nWe are pleased to share our business proposal ($proposal_code) with you.\n\nView proposal: $view_link\n\nBest regards,\nTDS Projects Team";
        
        $mail->send();
        return true;
        
    } catch (Exception $e) {
        error_log("Email Error: " . $e->getMessage());
        return false;
    }
}

mysqli_close($con);
?>