<?php

/**

 * Deduplicator - Finds and merges duplicate records

 * Helps clean data before import

 */



class Deduplicator {

    private $conn;

    

    public function __construct($conn) {

        $this->conn = $conn;

    }

    

    /**

     * Find duplicate students based on multiple criteria

     */

    public function findDuplicateStudents($data) {

        $duplicates = [];

        $processed = [];

        

        foreach ($data as $index => $record) {

            if (in_array($index, $processed)) continue;

            

            $matches = [];

            

            // Check by student_id if provided

            if (!empty($record['student_id'])) {

                $matches = array_filter($data, function($r, $i) use ($record, $index, &$processed) {

                    if ($i == $index) return false;

                    if (in_array($i, $processed)) return false;

                    

                    $isDuplicate = ($r['student_id'] == $record['student_id']);

                    if ($isDuplicate) $processed[] = $i;

                    return $isDuplicate;

                }, ARRAY_FILTER_USE_BOTH);

            }

            

            // If no ID match, check by name + class

            if (empty($matches) && !empty($record['first_name']) && !empty($record['last_name']) && !empty($record['class'])) {

                $matches = array_filter($data, function($r, $i) use ($record, $index, &$processed) {

                    if ($i == $index) return false;

                    if (in_array($i, $processed)) return false;

                    

                    $nameMatch = (strtolower($r['first_name'] ?? '') == strtolower($record['first_name']) &&

                                 strtolower($r['last_name'] ?? '') == strtolower($record['last_name']));

                    $classMatch = ($r['class'] ?? '') == $record['class'];

                    

                    $isDuplicate = $nameMatch && $classMatch;

                    if ($isDuplicate) $processed[] = $i;

                    return $isDuplicate;

                }, ARRAY_FILTER_USE_BOTH);

            }

            

            if (!empty($matches)) {

                $duplicates[] = [

                    'original_index' => $index,

                    'original' => $record,

                    'duplicates' => $matches,

                    'count' => count($matches) + 1

                ];

            }

            

            $processed[] = $index;

        }

        

        return $duplicates;

    }

    

    /**

     * Find duplicate staff

     */

    public function findDuplicateStaff($data) {

        $duplicates = [];

        $processed = [];

        

        foreach ($data as $index => $record) {

            if (in_array($index, $processed)) continue;

            

            $matches = [];

            

            // Check by staff_id

            if (!empty($record['staff_id'])) {

                $matches = array_filter($data, function($r, $i) use ($record, $index, &$processed) {

                    if ($i == $index) return false;

                    if (in_array($i, $processed)) return false;

                    

                    $isDuplicate = ($r['staff_id'] == $record['staff_id']);

                    if ($isDuplicate) $processed[] = $i;

                    return $isDuplicate;

                }, ARRAY_FILTER_USE_BOTH);

            }

            

            // If no ID match, check by name + phone

            if (empty($matches) && !empty($record['full_name']) && !empty($record['phone'])) {

                $matches = array_filter($data, function($r, $i) use ($record, $index, &$processed) {

                    if ($i == $index) return false;

                    if (in_array($i, $processed)) return false;

                    

                    $nameMatch = (strtolower($r['full_name'] ?? '') == strtolower($record['full_name']));

                    $phoneMatch = ($r['phone'] ?? '') == $record['phone'];

                    

                    $isDuplicate = $nameMatch && $phoneMatch;

                    if ($isDuplicate) $processed[] = $i;

                    return $isDuplicate;

                }, ARRAY_FILTER_USE_BOTH);

            }

            

            if (!empty($matches)) {

                $duplicates[] = [

                    'original_index' => $index,

                    'original' => $record,

                    'duplicates' => $matches,

                    'count' => count($matches) + 1

                ];

            }

            

            $processed[] = $index;

        }

        

        return $duplicates;

    }

    

    /**

     * Find duplicate fee structures

     */

    public function findDuplicateFees($data) {

        $duplicates = [];

        $seen = [];

        

        foreach ($data as $index => $record) {

            $key = $record['class_name'] . '|' . $record['fee_type'] . '|' . $record['academic_year'];

            

            if (isset($seen[$key])) {

                $duplicates[] = [

                    'original_index' => $seen[$key],

                    'original' => $data[$seen[$key]],

                    'duplicate_index' => $index,

                    'duplicate' => $record

                ];

            } else {

                $seen[$key] = $index;

            }

        }

        

        return $duplicates;

    }

    

    /**

     * Find duplicate payments

     */

    public function findDuplicatePayments($data) {

        $duplicates = [];

        $seen = [];

        

        foreach ($data as $index => $record) {

            $key = $record['student_id'] . '|' . ($record['fee_structure_id'] ?? '') . '|' . ($record['payment_date'] ?? '');

            

            if (isset($seen[$key])) {

                $duplicates[] = [

                    'original_index' => $seen[$key],

                    'original' => $data[$seen[$key]],

                    'duplicate_index' => $index,

                    'duplicate' => $record

                ];

            } else {

                $seen[$key] = $index;

            }

        }

        

        return $duplicates;

    }

    

    /**

     * Find duplicate subjects

     */

    public function findDuplicateSubjects($data) {

        $duplicates = [];

        $seen = [];

        

        foreach ($data as $index => $record) {

            $key = $record['subject_code'] ?? $record['subject_name'];

            

            if (isset($seen[$key])) {

                $duplicates[] = [

                    'original_index' => $seen[$key],

                    'original' => $data[$seen[$key]],

                    'duplicate_index' => $index,

                    'duplicate' => $record

                ];

            } else {

                $seen[$key] = $index;

            }

        }

        

        return $duplicates;

    }

    

    /**

     * Merge duplicate student records (keep most complete)

     */

    public function mergeStudentRecords($records) {

        $merged = [];

        $fields = ['student_id', 'first_name', 'last_name', 'sex', 'date_of_birth', 'place_of_birth',

                  'nationality', 'house_no', 'father_name', 'father_occupation', 'father_address',

                  'father_phone', 'mother_name', 'mother_occupation', 'mother_address', 'mother_phone',

                  'religion', 'parents_live_together', 'guardian_relationship', 'has_handicap',

                  'handicap_description', 'languages_spoken', 'last_school', 'last_class',

                  'emergency_contact_name', 'emergency_contact_address', 'emergency_contact_phone',

                  'class', 'section', 'parent_name', 'phone', 'email', 'address'];

        

        foreach ($fields as $field) {

            // Take the first non-empty value from any record

            foreach ($records as $record) {

                if (!empty($record[$field])) {

                    $merged[$field] = $record[$field];

                    break;

                }

            }

        }

        

        return $merged;

    }

    

    /**

     * Generate duplicate report HTML

     */

    public function generateDuplicateReport($duplicates, $type) {

        if (empty($duplicates)) {

            return '<div class="alert alert-success"><i class="fas fa-check-circle me-2"></i>No duplicates found!</div>';

        }

        

        $html = '<div class="alert alert-warning">';

        $html .= '<i class="fas fa-exclamation-triangle me-2"></i>';

        $html .= 'Found ' . count($duplicates) . ' duplicate ' . $type . ' groups</div>';

        

        $html .= '<div class="duplicate-list">';

        

        foreach ($duplicates as $group) {

            $html .= '<div class="card mb-3">';

            $html .= '<div class="card-header bg-warning text-white">';

            $html .= '<strong>Group with ' . $group['count'] . ' records</strong>';

            $html .= '</div>';

            $html .= '<div class="card-body">';

            

            // Show original

            $html .= '<div class="alert alert-success mb-2">';

            $html .= '<strong>Primary Record (Row ' . ($group['original_index'] + 1) . '):</strong><br>';

            $html .= $this->formatRecordPreview($group['original'], $type);

            $html .= '</div>';

            

            // Show duplicates

            foreach ($group['duplicates'] as $dupIndex => $dup) {

                $html .= '<div class="alert alert-danger mb-2">';

                $html .= '<strong>Duplicate Record (Row ' . ($dupIndex + 1) . '):</strong><br>';

                $html .= $this->formatRecordPreview($dup, $type);

                $html .= '</div>';

            }

            

            // Merge option

            $html .= '<div class="text-center mt-2">';

            $html .= '<button class="btn btn-sm btn-success" onclick="mergeDuplicates(' . $group['original_index'] . ', ' . json_encode(array_keys($group['duplicates'])) . ')">';

            $html .= '<i class="fas fa-compress-alt me-2"></i>Merge These Records</button>';

            $html .= '</div>';

            

            $html .= '</div></div>';

        }

        

        $html .= '</div>';

        

        return $html;

    }

    

    /**

     * Format record preview

     */

    private function formatRecordPreview($record, $type) {

        $preview = '';

        $fields = [];

        

        switch ($type) {

            case 'students':

                $fields = ['student_id', 'first_name', 'last_name', 'class', 'phone', 'email'];

                break;

            case 'staff':

                $fields = ['staff_id', 'full_name', 'class_taught', 'phone', 'email'];

                break;

            case 'fees':

                $fields = ['class_name', 'fee_type', 'amount', 'due_date'];

                break;

            case 'payments':

                $fields = ['student_id', 'fee_structure_id', 'amount_paid', 'payment_date'];

                break;

            case 'subjects':

                $fields = ['subject_name', 'subject_code', 'class_name'];

                break;

        }

        

        foreach ($fields as $field) {

            if (isset($record[$field]) && !empty($record[$field])) {

                $preview .= "<strong>{$field}:</strong> {$record[$field]}<br>";

            }

        }

        

        return $preview;

    }

    

    /**

     * Check for existing records in database

     */

   /**

 * Check for existing records in database - ADD THESE CASES

 */

public function checkExistingInDatabase($data, $type) {

    $existing = [];

    

    foreach ($data as $index => $record) {

        switch ($type) {

            case 'students':

                if (!empty($record['student_id'])) {

                    $result = $this->conn->query("SELECT id, student_id, first_name, last_name FROM students WHERE student_id = '{$record['student_id']}'");

                    if ($result && $row = $result->fetch_assoc()) {

                        $existing[] = [

                            'row' => $index + 1,

                            'record' => $record,

                            'existing' => $row,

                            'message' => "Student ID {$record['student_id']} already exists in database"

                        ];

                    }

                }

                break;

                

            case 'staff':

                if (!empty($record['staff_id'])) {

                    $result = $this->conn->query("SELECT id, staff_id, full_name FROM staff WHERE staff_id = '{$record['staff_id']}'");

                    if ($result && $row = $result->fetch_assoc()) {

                        $existing[] = [

                            'row' => $index + 1,

                            'record' => $record,

                            'existing' => $row,

                            'message' => "Staff ID {$record['staff_id']} already exists in database"

                        ];

                    }

                }

                break;

                

            case 'fees':

                if (!empty($record['class_name']) && !empty($record['fee_type']) && !empty($record['academic_year'])) {

                    $result = $this->conn->query("SELECT id FROM fee_structures 

                                                  WHERE class_name = '{$record['class_name']}' 

                                                  AND fee_type = '{$record['fee_type']}'

                                                  AND academic_year = '{$record['academic_year']}'");

                    if ($result && $result->num_rows > 0) {

                        $existing[] = [

                            'row' => $index + 1,

                            'record' => $record,

                            'message' => "Fee structure for {$record['class_name']} - {$record['fee_type']} ({$record['academic_year']}) already exists"

                        ];

                    }

                }

                break;

                

            case 'payments':

                if (!empty($record['student_id']) && !empty($record['fee_structure_id']) && !empty($record['payment_date'])) {

                    // First get student internal ID

                    $studentResult = $this->conn->query("SELECT id FROM students WHERE student_id = '{$record['student_id']}'");

                    if ($studentRow = $studentResult->fetch_assoc()) {

                        $studentId = $studentRow['id'];

                        $result = $this->conn->query("SELECT id FROM payments 

                                                      WHERE student_id = $studentId 

                                                      AND fee_structure_id = {$record['fee_structure_id']}

                                                      AND payment_date = '{$record['payment_date']}'");

                        if ($result && $result->num_rows > 0) {

                            $existing[] = [

                                'row' => $index + 1,

                                'record' => $record,

                                'message' => "Payment for student {$record['student_id']} on {$record['payment_date']} already exists"

                            ];

                        }

                    }

                }

                break;

                

            case 'subjects':

                if (!empty($record['subject_code'])) {

                    $result = $this->conn->query("SELECT id, subject_name, subject_code FROM subjects WHERE subject_code = '{$record['subject_code']}'");

                    if ($result && $row = $result->fetch_assoc()) {

                        $existing[] = [

                            'row' => $index + 1,

                            'record' => $record,

                            'existing' => $row,

                            'message' => "Subject code {$record['subject_code']} already exists in database"

                        ];

                    }

                }

                break;

        }

    }

    

    return $existing;

}

}

?>