<?php

//require_once 'db.php'; // Database connection
require('../../lib/dbclass.php');
// Debugging: Show file upload status


function readExcelXML($filename)
{
    $zip = new ZipArchive();

    if ($zip->open($filename) == true) {
        $sharedStrings = [];
        $data          = [];

        // Read shared strings (for text values)
        if (($xml = $zip->getFromName('xl/sharedStrings.xml')) != false) {
            $dom = new DOMDocument();
            $dom->loadXML($xml);

            foreach ($dom->getElementsByTagName('t') as $t) {
                $sharedStrings[] = $t->nodeValue;
            }
        }

        // Read worksheet data
        if (($xml = $zip->getFromName('xl/worksheets/sheet1.xml')) != false) {
            $dom = new DOMDocument();
            $dom->loadXML($xml);
            $rows = $dom->getElementsByTagName('row');

            foreach ($rows as $rowIndex => $row) {
                if ($rowIndex == 0) {
                    continue;
                } // Skip header row
                $rowData = [];

                foreach ($row->getElementsByTagName('c') as $c) {
                    $value = '';

                    if ($c->getElementsByTagName('v')->length > 0) {
                        $v = $c->getElementsByTagName('v')->item(0)->nodeValue;

                        if ($c->hasAttribute('t') && $c->getAttribute('t') == 's') {
                            $value = $sharedStrings[$v] ?? '';
                        } else {
                            $value = $v;
                        }
                    }
                    $rowData[] = $value;
                }
                $data[] = $rowData;
            }
        }

        $zip->close();

        return $data;
    } else {
        echo 'Error opening .xlsx file.';

        return false;
    }
}

// File upload handling
if ($_FILES['csv_file']['error'][0] == UPLOAD_ERR_OK) {
    $filePath = $_FILES['csv_file']['tmp_name'][0];

    // Read the Excel file
    $data = readExcelXML($filePath);

    if ($data) {
        // Check and print the data before inserting


        // Database connection
        try {
            //$pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
            //$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // Insert data into database
            foreach ($data as $row) {
                $saveArray                   = [];
                $saveArray['panchayat_name'] = $row[0];
                $saveArray['status']         = $row['2'];




                if ($saveArray['status'] == '') {
                    $saveArray['status'] = 0;
                }

                $name = $row['0'];

                $assembly = $row['1'];

                $assemblyexist = findQuery("select id from tbl_assembly where name='$assembly'");



                if ($assemblyexist) {
                    $assemblyid    = $assemblyexist[0]['id'];
                    $totalassembly = findQuery("select COUNT(*) nos from tbl_panchayat where panchayat_name='$name' and assembly_id=$assemblyid and  status=0");
                    $count         = $totalassembly[0]['nos'];

                    if ($count > 0) {
                    } else {
                        $saveArray['assembly_id'] = $assemblyid;
                        $cguid                    = saveArray('tbl_panchayat', $saveArray, 0);
                    }
                }
            }

            if ($cguid) {
                $message = 'Imported Successfullly';



                $encodedMessage = urlencode($message);



                $url = "../panchayat_management.php?message={$encodedMessage}";

                // Redirect to the new URL
                echo "<script>window.location.href='{$url}';</script>";
            } else {
                $message = 'Something went wrong, please try again.';



                $encodedMessage = urlencode($message);



                $url = "../panchayat_management.php?message={$encodedMessage}";

                // Redirect to the new URL
                echo "<script>window.location.href='{$url}';</script>";
            }
        } catch (PDOException $e) {
            echo 'Error: ' . $e->getMessage();
        }
    } else {
        echo 'Error reading Excel file.';
    }
} else {
    echo 'File upload error!';
}
