<?php

function validateMemberData($row) {
    // Required fields validation
    $required_fields = ['SURNAME', 'FIRST_NAME', 'DOB', 'GENDER', 'RELATIONSHIP'];
    foreach ($required_fields as $field) {
        if (empty($row[$field])) {
            throw new Exception("Missing required field: $field");
        }
    }

    // Date format validation
    if (!validateDate($row['DOB'])) {
        throw new Exception("Invalid date format for DOB. Use YYYY-MM-DD");
    }

    // Gender validation
    if (!in_array(strtoupper($row['GENDER']), ['M', 'F'])) {
        throw new Exception("Invalid gender value. Use M or F");
    }

    // Relationship validation
    $valid_relationships = ['PRINCIPAL', '1', 'SPOUSE', '2', 'CHILD', '3', 'PARENT', '4'];
    if (!in_array(strtoupper($row['RELATIONSHIP']), $valid_relationships)) {
        throw new Exception("Invalid relationship value");
    }

    return true;
}

function validateDate($date, $format = 'Y-m-d') {
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) === $date;
}

function savePrincipalMember($data, $conn) {
    try {
        // Extract data
        $row = $data['data'];
        
        // Prepare principal applicant SQL
        $sql = "INSERT INTO principal_applicant (
            family_no,
            member_no,
            surname,
            first_name,
            other_names,
            agent_id,
            corp_id,
            employer,
            tel_no,
            mobile_no,
            postal_add,
            email,
            phy_loc,
            family_size,
            relation_to_family,
            individual,
            date_form_received,
            user_id,
            date_entered,
            form_filled,
            marital_status,
            pin_no,
            bank_branch,
            account_no,
            smart_cat_change
        ) VALUES (?, ?, ?, ?, ?, NULL, ?, NULL, NULL, ?, ?, ?, NULL, 1, 1, 0, CURDATE(), ?, CURDATE(), 1, ?, ?, ?, ?, 0)";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Failed to prepare principal statement: " . $conn->error);
        }

        $user_id = $_SESSION['user_id'];
        $marital_status = $row['MARITAL_STATUS'] ?? 1;

        $stmt->bind_param(
            'sssssssssssssss',
            $data['family_no'],
            $data['member_no'],
            $row['SURNAME'],
            $row['FIRST_NAME'],
            $row['OTHER_NAMES'],
            $data['corp_id'],
            $row['MOBILE_NO'],
            $row['POSTAL_ADD'],
            $row['EMAIL'],
            $user_id,
            $marital_status,
            $row['PIN_NO'],
            $row['BANK_BRANCH'],
            $row['ACCOUNT_NO']
        );

        if (!$stmt->execute()) {
            throw new Exception("Failed to save principal member: " . $stmt->error);
        }

        // Save member info
        return saveMemberInfo([
            'corp_id' => $data['corp_id'],
            'member_no' => $data['member_no'],
            'family_no' => $data['family_no'],
            'data' => $row,
            'relation_to_principal' => 1
        ], $conn);

    } catch (Exception $e) {
        error_log("Error in savePrincipalMember: " . $e->getMessage());
        throw new Exception("Failed to save principal member: " . $e->getMessage());
    }
}

function saveDependentMember($data, $conn) {
    try {
        return saveMemberInfo([
            'corp_id' => $data['corp_id'],
            'member_no' => $data['member_no'],
            'family_no' => $data['family_no'],
            'data' => $data['data'],
            'relation_to_principal' => mapRelationship($data['data']['RELATIONSHIP'])
        ], $conn);

    } catch (Exception $e) {
        error_log("Error in saveDependentMember: " . $e->getMessage());
        throw new Exception("Failed to save dependent member: " . $e->getMessage());
    }
}

function saveMemberInfo($data, $conn) {
    try {
        $row = $data['data'];
        
        $sql = "INSERT INTO member_info (
            family_no,
            member_no,
            surname,
            first_name,
            other_names,
            dob,
            id_no,
            relation_to_principal,
            user_id,
            date_entered,
            family_title,
            cancelled,
            employment_no,
            gender,
            status,
            corp_id,
            smart_picked,
            smart_sync
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURDATE(), NULL, 0, ?, ?, 1, ?, 0, 0)";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Failed to prepare member info statement: " . $conn->error);
        }

        $user_id = $_SESSION['user_id'];
        $gender = mapGender($row['GENDER']);
        $employment_no = $row['EMPLOYMENT_NO'] ?? null;

        $stmt->bind_param(
            'sssssssissssi',
            $data['family_no'],
            $data['member_no'],
            $row['SURNAME'],
            $row['FIRST_NAME'],
            $row['OTHER_NAMES'],
            $row['DOB'],
            $row['ID_NO'],
            $data['relation_to_principal'],
            $user_id,
            $employment_no,
            $gender,
            $data['corp_id']
        );

        if (!$stmt->execute()) {
            throw new Exception("Failed to save member info: " . $stmt->error);
        }

        return true;

    } catch (Exception $e) {
        error_log("Error in saveMemberInfo: " . $e->getMessage());
        throw new Exception("Failed to save member info: " . $e->getMessage());
    }
}

function assignCorporateBenefits($member_no, $corp_id, $family_no, $conn) {
    try {
        // Get corporate group benefits
        $sql = "SELECT benefit, category, `limit`, sharing 
                FROM corp_groups 
                WHERE corp_id = ? 
                AND anniv = (
                    SELECT MAX(anniv) 
                    FROM corp_groups 
                    WHERE corp_id = ?
                )";
                
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('ss', $corp_id, $corp_id);
        $stmt->execute();
        $benefits = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

        if (empty($benefits)) {
            throw new Exception("No benefits defined for corporate");
        }

        // Insert member benefits
        $sql = "INSERT INTO member_benefits (
            member_no,
            benefit,
            `limit`,
            sharing,
            anniv,
            category,
            status,
            corp_id,
            smart_sync,
            sync
        ) VALUES (?, ?, ?, ?, 1, ?, 'Active', ?, 0, 0)";

        $stmt = $conn->prepare($sql);
        
        foreach ($benefits as $benefit) {
            $stmt->bind_param(
                'sidiss',
                $member_no,
                $benefit['benefit'],
                $benefit['limit'],
                $benefit['sharing'],
                $benefit['category'],
                $corp_id
            );

            if (!$stmt->execute()) {
                throw new Exception("Failed to assign benefit: " . $stmt->error);
            }
        }

        return true;

    } catch (Exception $e) {
        error_log("Error assigning corporate benefits: " . $e->getMessage());
        throw new Exception("Failed to assign corporate benefits: " . $e->getMessage());
    }
}

function saveAnniversary($data, $conn) {
    try {
        // Get corporate anniversary dates
        $sql = "SELECT start_date, end_date, renewal_date 
                FROM corp_anniversary 
                WHERE corp_id = ? 
                AND YEAR(start_date) = YEAR(CURDATE())";

        $stmt = $conn->prepare($sql);
        $stmt->bind_param("s", $data['corp_id']);
        $stmt->execute();
        $anniversary = $stmt->get_result()->fetch_assoc();

        if (!$anniversary) {
            throw new Exception("No valid corporate anniversary found");
        }

        $sql = "INSERT INTO member_anniversary (
            member_no,
            start_date,
            end_date,
            renewal_date,
            anniv,
            user_id,
            date_entered,
            sync,
            renewal_notified,
            smart_sync,
            corp_id
        ) VALUES (?, ?, ?, ?, 1, ?, CURDATE(), 0, 0, 0, ?)";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Failed to prepare anniversary statement: " . $conn->error);
        }

        $user_id = $_SESSION['user_id'];

        $stmt->bind_param(
            'ssssss',
            $data['member_no'],
            $anniversary['start_date'],
            $anniversary['end_date'],
            $anniversary['renewal_date'],
            $user_id,
            $data['corp_id']
        );

        if (!$stmt->execute()) {
            throw new Exception("Failed to save anniversary: " . $stmt->error);
        }

        return true;

    } catch (Exception $e) {
        error_log("Error in saveAnniversary: " . $e->getMessage());
        throw new Exception("Failed to save anniversary: " . $e->getMessage());
    }
}

function generateMemberNumber($scheme, $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 generateFamilyNumber($scheme, $conn) {
    $sql = "SELECT MAX(CAST(SUBSTRING_INDEX(family_no, '-', -1) AS UNSIGNED)) as max_num 
            FROM principal_applicant WHERE family_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 sendWelcomeNotification($member_no, $data, $conn) {
    try {
        // Get member details for notification
        $sql = "SELECT m.*, c.corporate 
                FROM member_info m 
                JOIN corporate c ON m.corp_id = c.corp_id 
                WHERE m.member_no = ?";
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('s', $member_no);
        $stmt->execute();
        $member = $stmt->get_result()->fetch_assoc();

        if (!$member) {
            throw new Exception("Member not found for notification");
        }

        // Send email if available
        if (!empty($data['EMAIL'])) {
            $to = $data['EMAIL'];
            $subject = "Welcome to {$member['corporate']} Healthcare Program";
            
            $message = "Dear {$data['FIRST_NAME']},\n\n";
            $message .= "Welcome to {$member['corporate']}'s Healthcare Program. ";
            $message .= "Your membership details are:\n\n";
            $message .= "Member Number: {$member_no}\n";
            $message .= "Family Number: {$member['family_no']}\n\n";
            $message .= "Your digital member card will be sent separately.\n\n";
            $message .= "Best regards,\nHealthcare Team";
            
            $headers = "From: noreply@medisafe360.com\r\n";
            $headers .= "Reply-To: support@medisafe360.com\r\n";
            $headers .= "X-Mailer: PHP/" . phpversion();
            
            mail($to, $subject, $message, $headers);
        }

        // Log notification
        $sql = "INSERT INTO notifications (
            user_id,
            type,
            content,
            reference_id,
            created_at
        ) VALUES (?, 'Booking Confirmation', ?, ?, NOW())";

        $stmt = $conn->prepare($sql);
        $content = "Welcome email sent to member {$member_no}";

        $stmt->bind_param('sss', 
            $_SESSION['user_id'],
            $content,
            $member_no
        );
        $stmt->execute();

        return true;

    } catch (Exception $e) {
        error_log("Error sending welcome notification: " . $e->getMessage());
        return false; // Don't throw - notification failure shouldn't stop import
    }
}

function generateMemberCard($member_no, $conn) {
    try {
        // Get member details
        $sql = "SELECT m.*, c.corporate, c.scheme
                FROM member_info m 
                JOIN corporate c ON m.corp_id = c.corp_id
                WHERE m.member_no = ?";
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('s', $member_no);
        $stmt->execute();
        $member = $stmt->get_result()->fetch_assoc();

        if (!$member) {
            throw new Exception("Member not found for card generation");
        }

        // Generate card number
        $card_serial = $member['scheme'] . str_pad(mt_rand(1, 999999), 6, '0', STR_PAD_LEFT);

        // Update member with card details
        $sql = "UPDATE member_info SET 
                card_serial_no = ?,
                card_to_member = CURDATE()
                WHERE member_no = ?";
                
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('ss', $card_serial, $member_no);
        
        if (!$stmt->execute()) {
            throw new Exception("Failed to update member card details");
        }

        // Log card generation
        $sql = "INSERT INTO activity_log (
            user_id,
            action,
            table_name,
            record_id,
            new_values,
            action_date
        ) VALUES (?, 'CREATE', 'member_info', ?, ?, NOW())";

        $stmt = $conn->prepare($sql);
        $new_values = json_encode(['card_serial_no' => $card_serial]);

        $stmt->bind_param('sss', 
            $_SESSION['user_id'],
            $member_no,
            $new_values
        );
        $stmt->execute();

        return true;

    } catch (Exception $e) {
        error_log("Error generating member card: " . $e->getMessage());
        return false; // Don't throw - card generation failure shouldn't stop import
    }
}

function mapGender($gender) {
    return strtoupper(trim($gender)) === 'M' ? 1 : 2;
}

function mapRelationship($relationship) {
    $relationship = strtoupper(trim($relationship));
    switch ($relationship) {
        case 'PRINCIPAL':
        case '1':
            return 1;
        case 'SPOUSE':
        case '2':
            return 2;
        case 'CHILD':
        case '3':
            return 3;
        case 'PARENT':
        case '4':
            return 4;
        default:
            return 1;
    }
}

function formatMobileNumber($number) {
    // Remove non-numeric characters
    $number = preg_replace('/[^0-9]/', '', $number);
    
    // Convert local format to international
    if (substr($number, 0, 1) === '0') {
        $number = '254' . substr($number, 1);
    }
    
    // Add + prefix if missing
    if (substr($number, 0, 1) !== '+') {
        $number = '+' . $number;
    }
    
    return $number;
}
?>