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

header('Content-Type: application/json');

// Check authentication and admin privileges 
if (!isset($_SESSION['user_id']) || $_SESSION['role_id'] !== 1) {
    http_response_code(403);
    echo json_encode(['success' => false, 'error' => 'Insufficient permissions. Not Authorized.']);
    exit;
}

try {
    // Get quotation number from request
    $quotation_no = $_POST['quotation_no'] ?? '';
    
    if (empty($quotation_no)) {
        throw new Exception('Quotation number is required');
    }

    // Start transaction
    $conn->begin_transaction();

    // 1. Get quotation details
    $sql = "SELECT q.*, c.scheme, c.corporate 
            FROM quotations q
            JOIN corporate c ON q.corp_id = c.corp_id 
            WHERE q.quotation_no = ? AND q.status = 'PENDING'";
            
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $quotation_no);
    $stmt->execute();
    $quotation = $stmt->get_result()->fetch_assoc();

    if (!$quotation) {
        throw new Exception('Invalid or already processed quotation');
    }

    // 2. Get quotation members and their benefits
    $sql = "SELECT qm.*, qmb.* 
            FROM quotation_members qm
            JOIN quotation_member_benefits qmb ON qm.member_id = qmb.member_id
            WHERE qm.quotation_no = ?";
            
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $quotation_no);
    $stmt->execute();
    $members = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

    // 3. Create or update anniversary
    $current_year = date('Y');
    $start_date = date('Y-m-d');
    $end_date = date('Y-m-d', strtotime('+1 year -1 day'));
    
    $sql = "INSERT INTO corp_anniversary (
        corp_id, anniv, start_date, end_date, renewal_date, agent_id, user_id, date_entered
    ) VALUES (?, 1, ?, ?, DATE_ADD(?, INTERVAL 1 DAY), '1', ?, CURDATE())";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('sssss',
        $quotation['corp_id'],
        $start_date,
        $end_date,
        $end_date,
        $_SESSION['user_id']
    );
    $stmt->execute();

    // 4. Process each member
    $family_no = $quotation['scheme'] . '-' . str_pad(mt_rand(1, 999), 3, '0', STR_PAD_LEFT);
    $current_family = null;

    foreach ($members as $member) {
        // Generate member number
        $member_no = generateMemberNumber($quotation['scheme'], $member['relation'], $conn);

        // For principal member
        if ($member['relation'] === 'principal') {
            $current_family = $family_no;
            
            // Insert principal applicant
            $sql = "INSERT INTO principal_applicant (
                family_no, member_no, surname, first_name, 
                corp_id, family_size, relation_to_family,
                user_id, date_entered, form_filled
            ) VALUES (?, ?, ?, ?, ?, 1, 1, ?, CURDATE(), 1)";
            
            $stmt = $conn->prepare($sql);
            $stmt->bind_param('ssssss',
                $family_no,
                $member_no,
                $member['surname'] ?? 'Principal',
                $member['first_name'] ?? 'Member',
                $quotation['corp_id'],
                $_SESSION['user_id']
            );
            $stmt->execute();
        }

        // Insert member info
        $sql = "INSERT INTO member_info (
            family_no, member_no, surname, first_name,
            relation_to_principal, user_id, date_entered,
            status, corp_id
        ) VALUES (?, ?, ?, ?, ?, ?, CURDATE(), 1, ?)";
        
        $relation = mapRelation($member['relation']);
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('sssssss',
            $current_family,
            $member_no,
            $member['surname'] ?? 'Member',
            $member['first_name'] ?? ' ',
            $relation,
            $_SESSION['user_id'],
            $quotation['corp_id']
        );
        $stmt->execute();

        // Insert member benefits
        foreach ($members as $benefit) {
            if ($benefit['member_id'] === $member['member_id']) {
                $sql = "INSERT INTO member_benefits (
                    member_no, benefit, `limit`, sharing,
                    anniv, category, status, corp_id
                ) VALUES (?, ?, ?, 0, 1, ?, 'Active', ?)";
                
                $category = ($benefit['benefit_code'] === 1) ? 'IP' : 'OP';
                $stmt = $conn->prepare($sql);
                $stmt->bind_param('sidss',
                    $member_no,
                    $benefit['benefit_code'],
                    $benefit['limit_amount'],
                    $category,
                    $quotation['corp_id']
                );
                $stmt->execute();
            }
        }

        // Insert member anniversary
        $sql = "INSERT INTO member_anniversary (
            member_no, start_date, end_date, renewal_date,
            anniv, user_id, date_entered, corp_id
        ) VALUES (?, ?, ?, DATE_ADD(?, INTERVAL 1 DAY), 1, ?, CURDATE(), ?)";
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('ssssss',
            $member_no,
            $start_date, 
            $end_date,
            $end_date,
            $_SESSION['user_id'],
            $quotation['corp_id']
        );
        $stmt->execute();
    }

    // 5. Update quotation status
    $sql = "UPDATE quotations SET status = 'ACCEPTED' WHERE quotation_no = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $quotation_no);
    $stmt->execute();

    // 6. Log the conversion
    $sql = "INSERT INTO activity_log (
        user_id, action, table_name, record_id, 
        new_values, ip_address, user_agent, action_date
    ) VALUES (?, 'UPDATE', 'quotations', ?, ?, ?, ?, NOW())";

    $log_details = json_encode([
        'action' => 'Quotation converted to policy',
        'quotation_no' => $quotation_no,
        'corporate' => $quotation['corporate']
    ]);

    $stmt = $conn->prepare($sql);
    $stmt->bind_param('sssss',
        $_SESSION['user_id'],
        $quotation_no,
        $log_details,
        $_SERVER['REMOTE_ADDR'],
        $_SERVER['HTTP_USER_AGENT']
    );
    $stmt->execute();

    // Commit transaction
    $conn->commit();

    echo json_encode([
        'success' => true,
        'message' => 'Quotation successfully converted to policy',
        'family_no' => $family_no
    ]);

} catch (Exception $e) {
    if ($conn->inTransaction()) {
        $conn->rollback();
    }
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage()
    ]);
}

function generateMemberNumber($scheme, $relation, $conn) {
    $sql = "SELECT MAX(CAST(SUBSTRING_INDEX(member_no, '-', -1) AS UNSIGNED)) as max_num 
            FROM member_info WHERE member_no LIKE ?";
    $pattern = "$scheme-%";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $pattern);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $next_num = ($result['max_num'] ?? 0) + 1;
    return $scheme . '-' . str_pad($next_num, 3, '0', STR_PAD_LEFT);
}

function mapRelation($relation) {
    switch ($relation) {
        case 'principal': return 1;
        case 'spouse': return 2;
        case 'child': return 3;
        default: return 1;
    }
}

$conn->close();
?>