<?php

function getUserRoles($pdo, $userId) {
    $sql = "
        SELECT r.id, r.name 
        FROM user_roles ur 
        JOIN roles r ON ur.role_id = r.id 
        WHERE ur.user_id = :userId 
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':userId' => $userId]);

    $roles = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $roles[$row['id']] = $row['name'];
    }
    
    return $roles;
}

function getPropertyUserRoleDirect(PDO $pdo, $userId) {
    $sql = "
        SELECT r.id, r.name 
        FROM property_users pu 
        JOIN roles r ON pu.role = r.id 
        WHERE pu.id = :userId 
          AND pu.deleted_at IS NULL
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':userId' => $userId]);

    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function getMultiPropUserRoleDirect(PDO $pdo, $userId) {
    $sql = "
        SELECT r.id, r.name 
        FROM multi_prop_users mpu 
        JOIN roles r ON mpu.role = r.id 
        WHERE mpu.user_assoc = :userId 
        AND mpu.deleted_at IS NULL
        AND mpu.property_id = :property_id
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':userId' => $userId, ':property_id' => $_SESSION['property']]);

    return $stmt->fetch(PDO::FETCH_ASSOC);
}


function getPropertyUserRoles($pdo, $roleId) {
    $sql = "SELECT id, name FROM roles WHERE id = :roleId";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':roleId' => $roleId]);

    $roles = $stmt->fetch(PDO::FETCH_ASSOC);
    // while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    //     $roles[$row['id']] = $row['name'];
    // }
    return $roles;
}

function getCustomUserRoles(PDO $pdo, $roleId) {
    
    $sql = "SELECT * FROM custom_roles WHERE id = :roleId";
    // echo $roleId;
    // exit();
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':roleId' => $roleId]);

    $roles = $stmt->fetch(PDO::FETCH_ASSOC);
    // while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    //     $roles[$row['id']] = $row['name'];
    // }
    return $roles;
}
// Original
// function hasAccess($pdo, $userId, $resourceName, $actionName) {

//     if ($_SESSION['custom'] === 0 && !isset($_SESSION['role'])){
//         var_dump('in first');
//         exit();
//         $roles = getUserRoles($pdo, $userId);
//         if (empty($roles)) {
//             return false;
//         }
        
//         $placeholders = implode(',', array_fill(0, count($roles), '?'));
//         $sql = "
//             SELECT COUNT(*) 
//             FROM role_resources rr
//             JOIN resources res ON rr.resource_id = res.id
//             JOIN actions act ON rr.action_id = act.id
//             WHERE res.name = ?
//             AND act.name = ?
//             AND rr.role_id IN ($placeholders)
//         ";
        
//         $stmt = $pdo->prepare($sql);
//         $params = array_merge([$resourceName, $actionName], array_keys($roles));
//         $stmt->execute($params);
//         $count = $stmt->fetchColumn();
//         return $count > 0;
//     }else if ($_SESSION['custom'] === 0 && isset($_SESSION['role'])){

//         var_dump('in 2nd');
//         exit();
//         $role = getPropertyUserRoles($pdo, $_SESSION['role']);

//         if (empty($role)) {
//             return false;
//         }

//         $roleid = $role['id'];

//         $sql = "
//             SELECT COUNT(*) 
//             FROM role_resources rr
//             JOIN resources res ON rr.resource_id = res.id
//             JOIN actions act ON rr.action_id = act.id
//             WHERE res.name = ?
//             AND act.name = ?
//             AND rr.role_id = ?
//         ";

//         $stmt = $pdo->prepare($sql);

//         $params = [$resourceName, $actionName, $roleid];

//         $stmt->execute($params);
//         $count = $stmt->fetchColumn();

//         return $count > 0;

//     }else {

//         var_dump('in 3rd');
//         exit();

//         $role = getCustomUserRoles($pdo, $_SESSION['role']);
    
//         if (empty($role)) {
//             return false;
//         }

//         $roleid = $role['id'];

//         $sql = "
//             SELECT COUNT(*) 
//             FROM custom_role_resources rr
//             JOIN resources res ON rr.resource_id = res.id
//             JOIN actions act ON rr.action_id = act.id
//             WHERE res.name = ?
//             AND act.name = ?
//             AND rr.role_id = ?
//         ";

//         $stmt = $pdo->prepare($sql);
//         $params = [$resourceName, $actionName, $roleid];
//         $stmt->execute($params);
//         $count = $stmt->fetchColumn();

//         return $count > 0;
//     }
// }
function hasAccess($pdo, $userId, $resourceName, $actionName) {
    // var_dump($_SESSION);
    // $_SESSION['isPropertyUser'] = false;
    // var_dump($_SESSION);
    // exit();
    // Super Admin, Admin, Dealer, and Organization Admin roles
    if ($_SESSION['custom'] === 0 && !isset($_SESSION['role'])) {
        $roles = getUserRoles($pdo, $userId);
        if (empty($roles)) {
            return false;
        }

        $placeholders = implode(',', array_fill(0, count($roles), '?'));
        $sql = "
            SELECT COUNT(*) 
            FROM role_resources rr
            JOIN resources res ON rr.resource_id = res.id
            JOIN actions act ON rr.action_id = act.id
            WHERE res.name = ?
            AND act.name = ?
            AND rr.role_id IN ($placeholders)
        ";

        $stmt = $pdo->prepare($sql);
        $params = array_merge([$resourceName, $actionName], array_keys($roles));
        $stmt->execute($params);
        $count = $stmt->fetchColumn();

        return $count > 0;

    // Property-level users (property_users)
    }else if ($_SESSION['layer'] === 'sub_dealer' || $_SESSION['layer'] === 'organization admin') {
        // var_dump('here');
        // exit();
        $roles = getUserRoles($pdo, $userId);
        if (empty($roles)) {
            return false;
        }

        $placeholders = implode(',', array_fill(0, count($roles), '?'));
        $sql = "
            SELECT COUNT(*) 
            FROM role_resources rr
            JOIN resources res ON rr.resource_id = res.id
            JOIN actions act ON rr.action_id = act.id
            WHERE res.name = ?
            AND act.name = ?
            AND rr.role_id IN ($placeholders)
        ";

        $stmt = $pdo->prepare($sql);
        $params = array_merge([$resourceName, $actionName], array_keys($roles));
        $stmt->execute($params);
        $count = $stmt->fetchColumn();

        return $count > 0;
    } else if ($_SESSION['custom'] === 0 && isset($_SESSION['role']) && $_SESSION['role'] <= 4) {

        $role = getPropertyUserRoles($pdo, $_SESSION['role']);

        if (empty($role)) {
            return false;
        }

        $roleid = $role['id'];

        $sql = "
            SELECT COUNT(*) 
            FROM role_resources rr
            JOIN resources res ON rr.resource_id = res.id
            JOIN actions act ON rr.action_id = act.id
            WHERE res.name = ?
            AND act.name = ?
            AND rr.role_id = ?
        ";

        $stmt = $pdo->prepare($sql);

        $params = [$resourceName, $actionName, $roleid];

        $stmt->execute($params);
        $count = $stmt->fetchColumn();

        return $count > 0;
     }else if ($_SESSION['custom'] === 0 && isset($_SESSION['role']) && $_SESSION['isPropertyUser'] === true) {
        
        $role = getPropertyUserRoleDirect($pdo, $userId);
        if (empty($role)) {
            return false;
        }

        $roleid = $role['id'];

        $sql = "
            SELECT COUNT(*) 
            FROM role_resources rr
            JOIN resources res ON rr.resource_id = res.id
            JOIN actions act ON rr.action_id = act.id
            WHERE res.name = ?
            AND act.name = ?
            AND rr.role_id = ?
        ";

        $stmt = $pdo->prepare($sql);
        $params = [$resourceName, $actionName, $roleid];
        $stmt->execute($params);
        $count = $stmt->fetchColumn();

        return $count > 0;

        // Multi-property users (multi_prop_users)
    }else if ($_SESSION['custom'] === 0 && isset($_SESSION['isMultiPropUser'])) {
        // var_dump("here");
        // exit();
        $role = getMultiPropUserRoleDirect($pdo, $userId);
        if (empty($role)) {
            return false;
        }

        $roleid = $role['id'];

        $sql = "
            SELECT COUNT(*) 
            FROM role_resources rr
            JOIN resources res ON rr.resource_id = res.id
            JOIN actions act ON rr.action_id = act.id
            WHERE res.name = ?
            AND act.name = ?
            AND rr.role_id = ?
        ";

        $stmt = $pdo->prepare($sql);
        $params = [$resourceName, $actionName, $roleid];
        $stmt->execute($params);
        $count = $stmt->fetchColumn();

        return $count > 0;

    // Custom roles
    } else if ($_SESSION['custom'] === 1) {
        // var_dump("here");
        // exit();
        $role = getCustomUserRoles($pdo, $_SESSION['role']);
        if (empty($role)) {
            return false;
        }

        $roleid = $role['id'];

        $sql = "
            SELECT COUNT(*) 
            FROM custom_role_resources rr
            JOIN resources res ON rr.resource_id = res.id
            JOIN actions act ON rr.action_id = act.id
            WHERE res.name = ?
            AND act.name = ?
            AND rr.role_id = ?
        ";

        $stmt = $pdo->prepare($sql);
        $params = [$resourceName, $actionName, $roleid];
        $stmt->execute($params);
        $count = $stmt->fetchColumn();

        return $count > 0;
    }
    // Default deny
    return false;
}

// function getMenuByUser($pdo, $userId)
// {
//     $currentProperty = $_SESSION['property'];
//     $userRole = null;

//     if (isset($_SESSION["isPropertyUser"]) && isset($_SESSION["propertyUserRole"])) {

//         $stmt = $pdo->prepare("
//             SELECT role 
//             FROM multi_prop_users 
//             WHERE user_assoc = :userId AND property_id = :propertyId AND status = 'Active'
//         ");
//         $stmt->execute([
//             'userId' => $userId,
//             'propertyId' => $currentProperty
//         ]);
//         $multiPropRole = $stmt->fetch(PDO::FETCH_ASSOC);

//         if ($multiPropRole) {
//             $userRole = $multiPropRole['role'];
//         } else {
//             $userRole = $_SESSION["propertyUserRole"];
//         }
//     } else {

//         $stmt = $pdo->prepare("
//             SELECT ur.role_id 
//             FROM users u
//             LEFT JOIN user_roles ur ON u.id = ur.user_id
//             WHERE u.id = :userId
//         ");
//         $stmt->execute(['userId' => $userId]);
//         $result = $stmt->fetch(PDO::FETCH_ASSOC);
//         $userRole = $result['role_id'] ?? null;
//     }

//     if (!$userRole) {
//         return [];
//     }

//     $sql = "
//         SELECT rr.*, r.* 
//         FROM role_resources rr
//         JOIN resources r ON rr.resource_id = r.id
//         WHERE rr.role_id = :roleId AND rr.action_id = 1
//     ";

//     try {
//         $stmt = $pdo->prepare($sql);
//         $stmt->execute(['roleId' => $userRole]);
//         $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//         return $result;
//     } catch (PDOException $e) {
//         error_log("Error fetching menu: " . $e->getMessage());
//         return [];
//     }
// }

function getMenuByUser($pdo, $userId)
{
    $currentProperty = $_SESSION['property'];
    $userRole = null;

    if ($_SESSION['layer'] === 'sub_dealer' || $_SESSION['layer'] === 'organization admin') {
        // Fetch the role from `dealer_admins`
        $stmt = $pdo->prepare("
            SELECT id
            FROM roles 
            WHERE name = :layer
        ");
        $stmt->execute(['layer' => $_SESSION['layer']]);
        $dealerAdminRole = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($dealerAdminRole) {
            $userRole = $dealerAdminRole['id'];
        }
    } elseif (isset($_SESSION["isPropertyUser"]) && isset($_SESSION["propertyUserRole"])) {
        // Fetch role for property users
        $stmt = $pdo->prepare("
            SELECT role 
            FROM multi_prop_users 
            WHERE user_assoc = :userId AND property_id = :propertyId AND status = 'Active'
        ");
        $stmt->execute([
            'userId' => $userId,
            'propertyId' => $currentProperty
        ]);
        $multiPropRole = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($multiPropRole) {
            $userRole = $multiPropRole['role'];
        } else {
            $userRole = $_SESSION["propertyUserRole"];
        }
    } else {
        // Fetch role for regular users
        $stmt = $pdo->prepare("
            SELECT ur.role_id 
            FROM users u
            LEFT JOIN user_roles ur ON u.id = ur.user_id
            WHERE u.id = :userId
        ");
        $stmt->execute(['userId' => $userId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        $userRole = $result['role_id'] ?? null;
    }

    if (!$userRole) {
        return [];
    }

    $sql = "
        SELECT rr.*, r.* 
        FROM role_resources rr
        JOIN resources r ON rr.resource_id = r.id
        WHERE rr.role_id = :roleId AND rr.action_id = 1
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['roleId' => $userRole]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    } catch (PDOException $e) {
        error_log("Error fetching menu: " . $e->getMessage());
        return [];
    }
}



function currentUserHasAccess($pdo, $resourceName, $actionName) {
    return isset($_SESSION['userid']) && hasAccess($pdo, $_SESSION['userid'], $resourceName, $actionName);
}

function getUserAssociations(PDO $pdo, $userId) {
    $sql = "
        SELECT 
            u.id AS user_id, u.username AS user_name, u.layer AS user_layer,
            p.id AS property_id, p.name AS property_name,
            o.id AS organization_id, o.name AS organization_name,
            d.id AS dealer_id, d.username AS dealer_name
        FROM users u
        LEFT JOIN properties p ON u.property_id = p.id
        LEFT JOIN organizations o ON p.organization_id = o.id OR u.organization_id = o.id
        LEFT JOIN users d ON o.dealer_id = d.id OR u.dealer_id = d.id
        WHERE u.id = ?
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$userId]);
    
    $associations = [];
    if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $associations = [
            'user_id' => $row['user_id'],
            'user_name' => $row['user_name'],
            'user_layer' => $row['user_layer'],
            'property_id' => $row['property_id'],
            'property_name' => $row['property_name'],
            'organization_id' => $row['organization_id'],
            'organization_name' => $row['organization_name'],
            'dealer_id' => $row['dealer_id'],
            'dealer_name' => $row['dealer_name']
        ];
        
        // If the user is a dealer, fetch all associated properties
        if ($row['user_layer'] == 'dealer') {
            $associations['properties'] = getDealerProperties($pdo, $userId);
        }
    }
    
    return $associations;
}

function getDealerProperties(PDO $pdo, $dealerId) {
    $sql = "
        SELECT p.id AS property_id, p.name AS property_name
        FROM dealer_properties dp
        JOIN properties p ON dp.property_id = p.id
        WHERE dp.dealer_id = ?
    ";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$dealerId]);
    
    $properties = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $properties[] = [
            'property_id' => $row['property_id'],
            'property_name' => $row['property_name']
        ];
    }
    
    return $properties;
}

function softDeleteUser($userid, $property_id, $del_user_type)
{
    global $pdo;
    try {
        isSessionExpiredMiddleware();
        $user_data = fetchDataSingle('property_users_data', ['property_user' => $userid, 'property_id' => $property_id]);
        $user_name = $user_data['firstname'] . $user_data['lastname'];

        $tables = [
            ['table' => 'audit_trail_login', 'column' => 'property_user_id'],
            ['table' => 'property_users_data', 'column' => 'property_user'],
            ['table' => 'profile_photo', 'column' => 'user_id'],
            ['table' => 'property_users', 'column' => 'id']
        ];

        if (!$pdo) {
            require_once realpath(dirname(__FILE__) . '/../../env.php');
            require_once realpath(dirname(__FILE__) . '/../../db.php');
            $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
        }

        // $pdo->beginTransaction();

        $deleted_at = date('Y-m-d H:i:s');

        foreach ($tables as $tableInfo) {
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $tableInfo['table']);
            $column = preg_replace('/[^a-zA-Z0-9_]+/', '', $tableInfo['column']);

            if ($table === 'profile_photo') {
                $sql = "UPDATE $table SET deleted_at = :deleted_at WHERE $column = :userid";
                $stmt = $pdo->prepare($sql);
                $stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
            } else {
                $sql = "UPDATE $table SET deleted_at = :deleted_at WHERE $column = :userid AND property_id = :property_id";
                $stmt = $pdo->prepare($sql);
                $stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
                $stmt->bindParam(':property_id', $property_id, PDO::PARAM_INT);
            }

            $stmt->bindParam(':deleted_at', $deleted_at, PDO::PARAM_STR);
            $stmt->execute();
            if($table == "audit_trail_login"){
                $tab_name = "Audit trail log";
            }
            if($table == "property_users_data"){
                $tab_name = "user detail";
            }
            if($table == "profile_photo"){
                $tab_name = "profile_photo";
            }
            if($table == "property_users"){
                if($del_user_type == "property_user"){
                    $deleted_user_type = "user";
                }
                else{
                    $deleted_user_type = "admin";
                }
                $tab_name = "property " . $deleted_user_type;
            }
            createAuditLog($_SESSION["userid"], "Deleted " . $tab_name, $table, $userid, $_SESSION["email"]??null, $property_id, null, $deleted_at, "Deleted " . $tab_name, "high", "Soft delete for user $userid in table $table");
            // logAudit($_SESSION['userid'], 'delete', $table, $userid, $property_id, null, $deleted_at, "Soft delete for user $userid in table $table");
        }
            
        // $pdo->commit();
        return true;
    } catch (PDOException $e) {
        // if ($pdo->inTransaction()) {
        //     $pdo->rollBack();
        // }
        throw new Exception("Error soft deleting user: " . $e->getMessage());
    }
}

function softDeleteMultiPropertyUser($userid, $property_id, $del_user_type = "")
{
    global $pdo;
    try {
        $user_data = fetchDataSingle('multi_prop_users', ['user_assoc' => $userid, 'property_id' => $property_id, 'deleted_at' => null]);
        $user_name = $user_data['firstname'] . $user_data['lastname'];
        $deleted_at = date('Y-m-d H:i:s');
        $stmt = $pdo->prepare("
            UPDATE multi_prop_users SET deleted_at = :deleted_at, status = :status WHERE user_assoc = :userid AND property_id = :property_id AND deleted_at IS NULL");
        $stmt->execute([
            'deleted_at' => $deleted_at,
            'status' => 'Inactive',
            'userid' => $userid,
            'property_id' => $property_id
        ]);
        $stmt1 = $pdo->prepare("
            UPDATE profile_photo SET deleted_at = :deleted_at WHERE user_id = :userid AND property_id = :property_id AND deleted_at IS NULL");
        $stmt1->execute([
            'deleted_at' => $deleted_at,
            'userid' => $userid,
            'property_id' => $property_id
        ]);
        if($del_user_type == "property_user"){
            $deleted_user_type = "user";
        }
        else{
            $deleted_user_type = "admin";
        }
        createAuditLog($_SESSION["userid"], "Deleted multi property " . $deleted_user_type, "multi_prop_users", $userid, $_SESSION["email"]??null, $property_id, $user_name, $deleted_at, "Deleted multi property " . $deleted_user_type, "high", "multi_prop_users");
        // logAudit($_SESSION['userid'], 'delete', 'multi_prop_users', $userid, $property_id, null, $deleted_at, "Soft delete for user $userid in property $property_id");
        return true;
    } catch (PDOException $e) {
        throw new Exception("Error soft deleting multi-property user: " . $e->getMessage());
    }
}

// function handlePrimaryPropertyDeletion($userid, $property_id)
// {
//     global $pdo;

//     try {
//         $pdo->beginTransaction(); // Start transaction

//         // Determine creator type based on session layer
//         $creator_type = ($_SESSION['layer'] === 'super_admin' || $_SESSION['layer'] === 'admin' || $_SESSION['layer'] === 'dealer') ? 'users' : 'property_users';

//         // Find the first available property from multi_prop_users
//         $stmt = $pdo->prepare("
//             SELECT * 
//             FROM multi_prop_users 
//             WHERE user_assoc = :userid AND deleted_at IS NULL
//             ORDER BY property_id ASC 
//             LIMIT 1
//         ");
//         $stmt->execute(['userid' => $userid]);
//         $newPrimaryProperty = $stmt->fetch(PDO::FETCH_ASSOC);

//         if ($newPrimaryProperty) {
//             // Fetch current primary property data
//             $stmt = $pdo->prepare("
//                 SELECT u.role, u.property_id, d.firstname, d.lastname, d.mobile_number, d.home_number, d.unit, d.street, d.custom, d.photo_loc
//                 FROM property_users u
//                 JOIN property_users_data d ON u.id = d.property_user
//                 WHERE u.id = :userid AND u.property_id = :property_id
//             ");
//             $stmt->execute(['userid' => $userid, 'property_id' => $property_id]);
//             $currentPrimaryData = $stmt->fetch(PDO::FETCH_ASSOC);

//             if ($currentPrimaryData) {
//                 // Overwrite the existing multi_prop_users entry with the original primary data
//                 $updateMultiProp = $pdo->prepare("
//                     UPDATE multi_prop_users
//                     SET role = :role, role_type = :role_type, property_id = :property_id, firstname = :firstname, lastname = :lastname, 
//                         mobile_number = :mobile_number, home_number = :home_number, unit = :unit, 
//                         street = :street, photo_loc = :photo_loc, created_by = :created_by, creator_type = :creator_type
//                     WHERE user_assoc = :userid AND property_id = :property_id
//                 ");
//                 $updateMultiProp->execute([
//                     'role' => $currentPrimaryData['role'],
//                     'role_type' => $currentPrimaryData['custom'], // Custom maps to role_type
//                     'firstname' => $currentPrimaryData['firstname'],
//                     'lastname' => $currentPrimaryData['lastname'],
//                     'mobile_number' => $currentPrimaryData['mobile_number'],
//                     'home_number' => $currentPrimaryData['home_number'],
//                     'unit' => $currentPrimaryData['unit'],
//                     'street' => $currentPrimaryData['street'],
//                     'photo_loc' => $currentPrimaryData['photo_loc'],
//                     'created_by' => $_SESSION['userid'],
//                     'creator_type' => $creator_type,
//                     'userid' => $userid,
//                     'property_id' => $currentPrimaryData['property_id']
//                 ]);
//             }

//             // Update property_users table with new property data
//             $updatePropertyUsers = $pdo->prepare("
//                 UPDATE property_users 
//                 SET property_id = :new_property_id, role = :new_role, role_type = :new_role_type
//                 WHERE id = :userid
//             ");
//             $updatePropertyUsers->execute([
//                 'new_property_id' => $newPrimaryProperty['property_id'],
//                 'new_role' => $newPrimaryProperty['role'],
//                 'new_role_type' => $newPrimaryProperty['role_type'], // From multi_prop_users
//                 'userid' => $userid
//             ]);

//             // Update property_users_data table with new property data
//             $updatePropertyUsersData = $pdo->prepare("
//                 UPDATE property_users_data 
//                 SET property_id = :new_property_id, firstname = :firstname, lastname = :lastname, 
//                     mobile_number = :mobile_number, home_number = :home_number, unit = :unit, street = :street, 
//                     custom = :custom, photo_loc = :photo_loc
//                 WHERE property_user = :userid
//             ");
//             $updatePropertyUsersData->execute([
//                 'new_property_id' => $newPrimaryProperty['property_id'],
//                 'firstname' => $newPrimaryProperty['firstname'],
//                 'lastname' => $newPrimaryProperty['lastname'],
//                 'mobile_number' => $newPrimaryProperty['mobile_number'],
//                 'home_number' => $newPrimaryProperty['home_number'],
//                 'unit' => $newPrimaryProperty['unit'],
//                 'street' => $newPrimaryProperty['street'],
//                 'custom' => $newPrimaryProperty['role_type'], // Map role_type back to custom
//                 'photo_loc' => $newPrimaryProperty['photo_loc'], // Ensure photo_loc is included
//                 'userid' => $userid
//             ]);

//             // Soft delete the new primary property entry from multi_prop_users
//             $softDeleteMultiProp = $pdo->prepare("
//                 UPDATE multi_prop_users 
//                 SET deleted_at = NOW() 
//                 WHERE user_assoc = :userid AND property_id = :property_id
//             ");
//             $softDeleteMultiProp->execute([
//                 'userid' => $userid,
//                 'property_id' => $property_id
//             ]);
//         } else {
//             // If no other properties exist, soft delete the user
//             softDeleteUser($userid, $property_id);
//         }

//         $pdo->commit(); // Commit transaction
//         return true;
//     } catch (PDOException $e) {
//         if ($pdo->inTransaction()) {
//             $pdo->rollBack(); // Roll back on failure
//         }
//         throw new Exception("Error handling primary property deletion: " . $e->getMessage());
//     }
// }

function handlePrimaryPropertyDeletion($userid, $property_id, $user_type="")
{
    global $pdo;

    try {
        $pdo->beginTransaction(); // Start transaction
        // error_log("Transaction started for user $userid on property $property_id");
        if ($_SESSION['layer'] === 'super_admin' || $_SESSION['layer'] === 'admin' || $_SESSION['layer'] === 'dealer') {
            $creator_type = 'users';
        }else if ($_SESSION['layer'] === 'sub_dealer' || $_SESSION['layer'] === 'organization admin') {
            $creator_type = 'dealer_admins';
        }else {
            $creator_type = 'property_users';
        }

        // Log the current state
        // error_log("Deleting primary property for user: $userid, property_id: $property_id");

        // Find the first available property from multi_prop_users
        $stmt = $pdo->prepare("
            SELECT * 
            FROM multi_prop_users 
            WHERE user_assoc = :userid AND deleted_at IS NULL
            ORDER BY property_id ASC 
            LIMIT 1
        ");
        $stmt->execute(['userid' => $userid]);
        $newPrimaryProperty = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($newPrimaryProperty) {
            // error_log("New primary property found: " . print_r($newPrimaryProperty, true));

            $stmt = $pdo->prepare("
                SELECT u.role, u.property_id, d.firstname, d.lastname, d.mobile_number, d.home_number, d.unit, d.street, d.custom, d.photo_loc
                FROM property_users u
                JOIN property_users_data d ON u.id = d.property_user
                WHERE u.id = :userid AND u.property_id = :property_id
            ");
            $stmt->execute(['userid' => $userid, 'property_id' => $property_id]);
            $currentPrimaryData = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($currentPrimaryData) {
                // error_log("Current primary property data: " . print_r($currentPrimaryData, true));

                $updateMultiProp = $pdo->prepare("
                    UPDATE multi_prop_users
                    SET role = :role, role_type = :role_type, property_id = :new_property_id, firstname = :firstname, lastname = :lastname,
                        mobile_number = :mobile_number, home_number = :home_number, unit = :unit, 
                        street = :street, photo_loc = :photo_loc, created_by = :created_by, creator_type = :creator_type
                    WHERE user_assoc = :userid AND property_id = :property_id
                ");
                $updateMultiProp->execute([
                    'role' => $currentPrimaryData['role'],
                    'role_type' => $currentPrimaryData['custom'],
                    'firstname' => $currentPrimaryData['firstname'],
                    'lastname' => $currentPrimaryData['lastname'],
                    'mobile_number' => $currentPrimaryData['mobile_number'],
                    'home_number' => $currentPrimaryData['home_number'],
                    'unit' => $currentPrimaryData['unit'],
                    'street' => $currentPrimaryData['street'],
                    'photo_loc' => $currentPrimaryData['photo_loc'],
                    'created_by' => $_SESSION['userid'],
                    'creator_type' => $creator_type,
                    'userid' => $userid,
                    'new_property_id' => $currentPrimaryData['property_id'],
                    'property_id' => $newPrimaryProperty['property_id'], // Use new property ID
                ]);

                // error_log("Updated multi_prop_users table for property_id: " . $newPrimaryProperty['property_id']);
            }

            $updatePropertyUsers = $pdo->prepare("
                UPDATE property_users 
                SET property_id = :new_property_id, role = :new_role, role_type = :new_role_type
                WHERE id = :userid
            ");
            $updatePropertyUsers->execute([
                'new_property_id' => $newPrimaryProperty['property_id'],
                'new_role' => $newPrimaryProperty['role'],
                'new_role_type' => $newPrimaryProperty['role_type'],
                'userid' => $userid
            ]);

            // error_log("Updated property_users table for user: $userid");

            $updatePropertyUsersData = $pdo->prepare("
                UPDATE property_users_data 
                SET role = :new_role, custom = :new_role_type, property_id = :new_property_id, firstname = :firstname, lastname = :lastname, 
                    mobile_number = :mobile_number, home_number = :home_number, unit = :unit, street = :street, 
                    custom = :custom, photo_loc = :photo_loc
                WHERE property_user = :userid
            ");
            $updatePropertyUsersData->execute([
                'new_role' => $newPrimaryProperty['role'],
                'new_role_type' => $newPrimaryProperty['role_type'],
                'new_property_id' => $newPrimaryProperty['property_id'],
                'firstname' => $newPrimaryProperty['firstname'],
                'lastname' => $newPrimaryProperty['lastname'],
                'mobile_number' => $newPrimaryProperty['mobile_number'],
                'home_number' => $newPrimaryProperty['home_number'],
                'unit' => $newPrimaryProperty['unit'],
                'street' => $newPrimaryProperty['street'],
                'custom' => $newPrimaryProperty['role_type'],
                'photo_loc' => $newPrimaryProperty['photo_loc'],
                'userid' => $userid
            ]);

            // error_log("Updated property_users_data table for user: $userid");

            $softDeleteMultiProp = $pdo->prepare("
                UPDATE multi_prop_users 
                SET deleted_at = NOW(),
                status = :status
                WHERE user_assoc = :userid AND property_id = :property_id
            ");
            $softDeleteMultiProp->execute([
                'userid' => $userid,
                'property_id' => $currentPrimaryData['property_id'],
                'status' => "Inactive"
            ]);

            $softDeleteMultiPropPhoto = $pdo->prepare("
                UPDATE profile_photo 
                SET deleted_at = NOW()
                WHERE user_id = :userid AND property_id = :property_id
            ");
            $softDeleteMultiPropPhoto->execute([
                'userid' => $userid,
                'property_id' => $currentPrimaryData['property_id']
            ]);

            // error_log("Soft-deleted multi_prop_users entry for user: $userid");
        } else {
            // error_log("No other properties found. Soft-deleting user.");
            softDeleteUser($userid, $property_id, $user_type);
        }

        $pdo->commit(); // Commit transaction
        return true;
    } catch (PDOException $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        error_log("Error handling primary property deletion: " . $e->getMessage());
        throw new Exception("Error handling primary property deletion: " . $e->getMessage());
    }
}


function isPersistLogin() {
    if (!empty($_COOKIE['remember_me'])) {
        $decodedData = base64_decode($_COOKIE['remember_me']);
        $sessionData = json_decode($decodedData, true);

        if (is_array($sessionData)) {
            if (session_status() === PHP_SESSION_NONE) session_start();
            $_SESSION = $sessionData;
        } else {
            clearRememberMeCookie();
            redirectToLogin();
        }
    } elseif (empty($_SESSION['userid'])) {
        redirectToLogin();
    }
}

function clearRememberMeCookie() {
    setcookie('remember_me', '', time() - 3600, "/", "", false, true);
}

function redirectToLogin() {
    if (!headers_sent()) {
        header("Location: /login.php");
    }
    exit;
}

function encryptFtpPassword($password) {
    
    global $ftp_encryption_key, $ftp_encryption_iv;

    if (!isset($ftp_encryption_key)) {
        require realpath(dirname(__FILE__) . '/../../env.php');
    }

    if (!is_string($ftp_encryption_key) || strlen($ftp_encryption_key) < 32) {
        throw new Exception("Invalid encryption key format.");
    }

    if (!$ftp_encryption_key || !$ftp_encryption_iv) {
        throw new Exception("Encryption key or IV not defined");
    }

    $encrypted = openssl_encrypt(
        $password,
        'AES-256-CBC',
        $ftp_encryption_key,
        0,
        $ftp_encryption_iv
    );

    return base64_encode($encrypted);
}

function decryptFtpPassword($encryptedPassword) {

    global $ftp_encryption_key, $ftp_encryption_iv;

    if (!isset($ftp_encryption_key)) {
        require realpath(dirname(__FILE__) . '/../../env.php');
    }

    if (!is_string($ftp_encryption_key) || strlen($ftp_encryption_key) < 32) {
        throw new Exception("Invalid encryption key format.");
    }

    if (!$ftp_encryption_key || !$ftp_encryption_iv) {
        throw new Exception("Encryption key or IV not defined");
    }

    $decoded = base64_decode($encryptedPassword);

    return openssl_decrypt(
        $decoded,
        'AES-256-CBC',
        $ftp_encryption_key,
        0,
        $ftp_encryption_iv
    );
}

function getEnvVars() {
    static $env = null;

    if ($env === null) {
        
        if (!isset($ftp_env)) {
            require_once realpath(dirname(__FILE__) . '/../../env.php');
        }

        global $ftp_env, $ftp_cred_key, $ftp_cred_user, $ftp_fold_key, $ftp_fold_user, $ftp_vcred_key, $ftp_vcred_user, $ftp_snap_key, $ftp_snap_user, $ftp_del_key, $ftp_del_user;

        $env = [
            'ftp_env'       => $ftp_env,
            'ftp_cred_key'  => $ftp_cred_key,
            'ftp_cred_user' => $ftp_cred_user,
            'ftp_fold_key'  => $ftp_fold_key,
            'ftp_fold_user' => $ftp_fold_user,
            'ftp_vcred_key'  => $ftp_vcred_key,
            'ftp_vcred_user' => $ftp_vcred_user,
            'ftp_snap_key'  => $ftp_snap_key,
            'ftp_snap_user' => $ftp_snap_user,
            'ftp_del_key' => $ftp_del_key,
            'ftp_del_user' => $ftp_del_user,
        ];
    }

    return $env;
}

function deleteCameraAndFtpArtifacts(int $cameraId, int $propertyId): array {

    $env = getEnvVars();

    $cam = fetchDataSingle('live_camera_streaming_data', ['id'=>$cameraId, 'property_id'=>$propertyId]);
    if (!$cam || empty($cam['camera_ftp_folder'])) {
        return ['ok'=>false, 'error'=>'Camera or FTP folder not found'];
    }
    $cameraFolder = $cam['camera_ftp_folder'];

    $propFtpUser = $env['ftp_env'] . '_ftp_prop_' . $propertyId;

    $camCred = fetchDataSingle('ftp_credentials', ['property_id'=>$propertyId, 'role'=>'camera']);
    if (!$camCred || empty($camCred['ftp_server_id'])) {
        return ['ok'=>false, 'error'=>'FTP server mapping not found for property'];
    }
    $server = fetchDataSingle('ftp_servers', ['id'=>(int)$camCred['ftp_server_id']]);
    if (!$server || empty($server['url'])) {
        return ['ok'=>false, 'error'=>'FTP server host not found'];
    }
    $ftpHost = $server['url'];

    $trace = bin2hex(random_bytes(8));
    $cmd = sprintf(
        'ssh -i %s -o IdentitiesOnly=yes -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -T %s@%s',
        escapeshellarg($env['ftp_vcred_key'] ?? '/var/www/.ssh/vftpdel_trigger_dev_id'),
        escapeshellarg($env['ftp_vcred_user'] ?? 'vftpdel_trigger_dev'),
        escapeshellarg($ftpHost)
    );

    $desc = [0=>['pipe','r'], 1=>['pipe','w'], 2=>['pipe','w']];
    $proc = proc_open($cmd, $desc, $pipes, null, null, ['bypass_shell'=>true]);
    if (!is_resource($proc)) {
        error_log("[$trace] delete_camera: proc_open failed host=$ftpHost");
        return ['ok'=>false, 'error'=>"Delete failed (trace $trace)"];
    }

    $stdin = $propFtpUser.' '.$cameraFolder.' '.$env['ftp_env'].' '.$propertyId."\n";
    fwrite($pipes[0], $stdin);
    fclose($pipes[0]);

    $stdout = stream_get_contents($pipes[1]); fclose($pipes[1]);
    $stderr = stream_get_contents($pipes[2]); fclose($pipes[2]);
    $exit   = proc_close($proc);

    if ($exit !== 0) {
        error_log("[$trace] delete_camera failed (exit=$exit) host=$ftpHost STDERR: $stderr | STDOUT: $stdout");
        return ['ok'=>false, 'error'=>"Delete failed (trace $trace)"];
    }

    deleteDataAdvanced('live_camera_streaming_data', ['id'=>$cameraId,'property_id'=>$propertyId]);

    if (!empty($camCred['ftp_server_id'])) {

        $sql = "
            SELECT o.dealer_id
            FROM properties p
            JOIN organizations o ON p.organization_id = o.id
            WHERE p.id = :property_id
        ";
        $res = fetchSingleDataWithJoins($sql, ['property_id' => $propertyId]);
        $dealerId = (int)($res['dealer_id'] ?? 0);
        if ($dealerId) {
            $alloc = fetchDataSingle('dealer_ftp_allocations', [
                'dealer_id'    => $dealerId,
                'ftp_server_id'=> (int)$camCred['ftp_server_id']
            ]);
            if ($alloc && $alloc['camera_slots_used'] > 0) {
                updateTable('dealer_ftp_allocations',
                    ['camera_slots_used' => $alloc['camera_slots_used'] - 1],
                    'id = :id', ['id' => $alloc['id']]
                );
            }
        }
    }

    return ['ok'=>true, 'trace'=>$trace, 'stdout'=>$stdout];
}

