<?php
ini_set('display_errors', 1);
ini_set('memory_limit', '512M');
ini_set('max_execution_time', 0);
ini_set('allow_url_fopen', '1');

date_default_timezone_set('Asia/Kolkata');

require_once '../../../../../resources/base_path_include.php';
require_once $base_path_to_learning_resources . "includes/connect.php";
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';
require_once $base_path_to_learning . 'user_management/faculty/functions.php';
require_once $base_path_to_learning . 'functions/common_functions.php';
require_once $base_path_to_learning_resources . 'lib/common_modules/import/student_data/function.php';



$institution_id = "34";
$userid = "30";
$role = "superadmin";
$workspace_id = "19";
$username = "kunalpadam9988@gmail.com";

// Validate user and get specific DB
$specific_db_name = validateUserAccess(
    $conn_rjllearning,
    $userid,
    $role,
    $workspace_id,
    ["superadmin", "admin", "principal", "teacher"]
);

if (!$specific_db_name) {
    errorreturn('Access denied.');
}

$conn_rjllearning_specific = dynamicDbConnection($specific_db_name);

// Check academic session
$academic_id = CheckAcademicSession($conn_rjllearning_specific, $institution_id);
if (!$academic_id) {
    errorreturn("Access denied. Academic session not active.");
}

// Setup log files
$logTime = date("Y-m-d_H-i-s");
$logPath = __DIR__ . "/logs/item_import_$logTime";
@mkdir($logPath, 0777, true);
$branchSkipFile = "$logPath/branch_skipped.txt";
$studentSkipFile = "$logPath/student_skipped.txt";
$generalSkipFile = "$logPath/general_skipped.txt";
$successLogFile = "$logPath/success.txt";

try {

    // Fetch all unprocessed records
    $fetchStmt = $conn_rjllearning_specific->prepare("
        SELECT id, branch, item_id, uniqueadm,
               authorised_by, date, time, authorisation_mode,
               otp_mobile, otp_email
        FROM  items_distributed
        WHERE status = 0
    ");
    $fetchStmt->execute();
    $records = $fetchStmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$records) {
        errorreturn("No new records to import.");
    }

    // Prepare statements
    $transactionStmt = $conn_rjllearning_specific->prepare("
        INSERT INTO inventory_transactions (
            institution_id, branch_id, transaction_type, item_id, quantity,
            created_by, created_at, otp_authorisation, date
        ) VALUES (?, ?, 'sales', ?, ?, ?, ?, ?, ?)
    ");

    $studentStmt = $conn_rjllearning_specific->prepare("
        SELECT id FROM students 
        WHERE admission_number = ? AND institution_id = ? AND branch_id = ?
    ");

    $groupStmt = $conn_rjllearning_specific->prepare("
        SELECT id FROM unmarked_transaction_type_group 
        WHERE institution_id = ? AND group_name = 'sales_category'
    ");

    $insertGroup = $conn_rjllearning_specific->prepare("
        INSERT INTO unmarked_transaction_type_group 
        (institution_id, group_name, created_by, date_created, time_created, status)
        VALUES (?, 'sales_category', ?, CURDATE(), CURTIME(), 1)
    ");

    $insertUnmarked = $conn_rjllearning_specific->prepare("
        INSERT INTO unmarked_transaction (
            institution_id, branch_id, category_id, transaction_type_group, amount,
            vendor_name, first_party_type, remarks, date, attachment,
            inventory_transaction_id, date_created, time_created, last_edit,
            created_by, last_edit_at, approved_by, recorded_in_finware, status
        ) VALUES (?, ?, ?, 'sales', 0, ?, 'student', 'Imported from item_distributed', ?, NULL,
                  ?, CURDATE(), CURTIME(), CURDATE(), ?, NOW(), ?, 0, 1)
    ");

    $updateStmt = $conn_rjllearning_specific->prepare("
        UPDATE item_distributed SET status = 1 WHERE id = ?
    ");

    $categoryIdCache = [];

    $branchFacultyCache = []; // Cache created_by per branch

    foreach ($records as $row) {
        try {
            $item_distributed_id = $row['id'];
            $branch_name = $row['branch'];
            $item_id = $row['item_id'];
            $qty = 0;
            $admission_no = $row['uniqueadm'];
            $otp_authorisation = $row['authorisation_mode'] ?: 'self';
            $date = $row['date'];
            $created_at = $row['date'] . " " . $row['time'];

            // Get branch ID
            $branch_id = getBranchId($conn_rjllearning_specific, $institution_id, $branch_name);
            if (!$branch_id) {
                file_put_contents($branchSkipFile, "Skipped: Branch '$branch_name' not found (Institution ID: $institution_id, ID: $item_distributed_id)\n", FILE_APPEND);
                continue;
            }

            // Get created_by from cache or fetch from users table
            if (!isset($branchFacultyCache[$branch_id])) {
                $userStmt = $conn_rjllearning_specific->prepare("
                SELECT id FROM users 
                WHERE institution_id = ? AND branch_id = ? AND role = 'faculty' AND status = 1 
                ORDER BY id ASC LIMIT 1
            ");
                $userStmt->execute([$institution_id, $branch_id]);
                $userRow = $userStmt->fetch(PDO::FETCH_ASSOC);

                if (!$userRow) {
                    file_put_contents($generalSkipFile, "Error (ID: $item_distributed_id): No faculty user found for Branch ID: $branch_id\n", FILE_APPEND);
                    continue;
                }

                $branchFacultyCache[$branch_id] = $userRow['id'];
            }
            $created_by = $branchFacultyCache[$branch_id];
            

            // Insert inventory transaction
            $transactionStmt->execute([
                $institution_id,
                $branch_id,
                $item_id,
                $qty,
                $created_by,
                $created_at,
                $otp_authorisation,
                $date
            ]);
            $inventory_transaction_id = $conn_rjllearning_specific->lastInsertId();

            // Get student ID
            $studentStmt->execute([$admission_no, $institution_id, $branch_id]);
            $student = $studentStmt->fetch(PDO::FETCH_ASSOC);
            if (!$student) {
                file_put_contents($studentSkipFile, "Skipped: Student '$admission_no' not found (Branch ID: $branch_id, Institution ID: $institution_id, ID: $item_distributed_id)\n", FILE_APPEND);
                continue;
            }
            $first_party_student = $student['id'];

            // Get or insert sales_category
            if (!isset($categoryIdCache[$institution_id])) {
                $groupStmt->execute([$institution_id]);
                $group = $groupStmt->fetch(PDO::FETCH_ASSOC);

                if (!$group) {
                    $insertGroup->execute([$institution_id, $created_by]);
                    $category_id = $conn_rjllearning_specific->lastInsertId();
                } else {
                    $category_id = $group['id'];
                }

                $categoryIdCache[$institution_id] = $category_id;
            } else {
                $category_id = $categoryIdCache[$institution_id];
            }

            // Insert unmarked_transaction
            $insertUnmarked->execute([
                $institution_id,
                $branch_id,
                $category_id,
                $first_party_student,
                $date,
                $inventory_transaction_id,
                $created_by,
                $created_by
            ]);

            // Mark item_distributed as processed
            $updateStmt->execute([$item_distributed_id]);

            // Log success
            file_put_contents($successLogFile, "Success: Processed ID $item_distributed_id\n", FILE_APPEND);

        } catch (Exception $innerEx) {
            file_put_contents($generalSkipFile, "Error (ID: $item_distributed_id): " . $innerEx->getMessage() . "\n", FILE_APPEND);
        }
    }


    successreturn(['status' => 1, 'message' => 'Item distribution data processed successfully.']);

} catch (Exception $e) {
    file_put_contents($generalSkipFile, "Critical Error: " . $e->getMessage() . "\n", FILE_APPEND);
    errorreturn(['status' => 0, 'message' => 'Import failed: ' . $e->getMessage()]);
}
