<?php

namespace Bot\Database;

trait TourDbTrait
{

    public function getUserTourRegistration($chatId, $tourId)
    {
        $query = "SELECT * FROM registers WHERE explorer_id = ? AND tour_id = ? LIMIT 1";
        $stmt = $this->mysqli->prepare($query);
        $stmt->bind_param("ii", $chatId, $tourId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $result ?: null;
    }


    public function updateTourIsComplete($tourId)
    {
        $query = "UPDATE tours SET is_full = 1 WHERE id = ?";
        $stmt = $this->mysqli->prepare($query);
        if (!$stmt) {
            error_log("Database Error: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param("i", $tourId);

        if (!$stmt->execute()) {
            error_log("Error updating status for tour ID: $tourId");
            $stmt->close();
            return false;
        }

        $affectedRows = $stmt->affected_rows;
        $stmt->close();

        return $affectedRows > 0;
    }


    public function getTourById(int $tourId): ?array
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM tours WHERE id = ? LIMIT 1");
        $stmt->bind_param("i", $tourId);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($row = $result->fetch_assoc()) {
            // دیکود کردن فیلدهای JSON با بررسی اول
            $row['pricing'] = $this->decodeJsonField($row['pricing']);
            $row['transport_options'] = $this->decodeJsonField($row['transport_options']);
            $row['services_options'] = $this->decodeJsonField($row['services_options']);
            return $row;
        }

        return null;
    }


    public function getTourDetailsWithPrice($tourId)
    {
        $stmt = $this->mysqli->prepare("
        SELECT id,chat_id, title,pricing,price_star
        FROM tours 
        WHERE id = ?
    ");
        $stmt->bind_param("i", $tourId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result ?: null;
    }


    public function updateTourField($tourId, $field, $value): bool
    {
        // ایمن‌سازی فیلدهای مجاز
        $allowedFields = [
            'title',
            'description',
            'difficulty',
            'origin_address',
            'region',
            'latitude',
            'longitude',
            'start_date',
            'end_date',
            'start_time',
            'end_time',
            'transport_options',
            'pricing',
            'services_options',
            'meal_details',
            'required_items',
            'capacity',
            'notes',
            'banner',
            'price_star',
            'channel_id',
            'channel_message_id',
            'group_id',
            'group_message_id',
            'is_full',
            'is_complete'
        ];

        if (!in_array($field, $allowedFields)) {
            error_log("updateTourField: Invalid field '{$field}'.");
            return false;
        }

        $type = 's'; // پیش‌فرض: رشته (string)
        if (in_array($field, ['capacity', 'price_star', 'is_full', 'is_complete', 'channel_message_id', 'group_message_id'])) {
            $type = 'i'; // عدد صحیح (integer)
        } elseif (in_array($field, ['latitude', 'longitude'])) {
            $type = 'd'; // عدد اعشاری (double)
        }

        $stmt = $this->mysqli->prepare("UPDATE tours SET `$field` = ? WHERE id = ?");
        if (!$stmt) {
            error_log("updateTourField: Prepare failed: " . $this->mysqli->error);
            return false;
        }

        $stmt->bind_param($type . "i", $value, $tourId);
        $result = $stmt->execute();

        if (!$result) {
            error_log("updateTourField: Execute failed: " . $stmt->error);
        }

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


    public function insertCompleteTour($chatId, array $tour): ?int
    {
        // --- بخش ۱: آماده‌سازی متغیرها ---
        $leader_id = (int) $chatId;
        $title = (string) ($tour['title'] ?? '');
        $description = (string) ($tour['description'] ?? '');
        $difficulty = (string) ($tour['difficulty'] ?? '');
        $region = (string) ($tour['region'] ?? '');
        $origin_address = (string) ($tour['origin_address'] ?? '');
        $latitude = isset($tour['location']['latitude']) ? (float) $tour['location']['latitude'] : null;
        $longitude = isset($tour['location']['longitude']) ? (float) $tour['location']['longitude'] : null;

        // ترکیب تاریخ و ساعت برای ذخیره در ستون DATETIME
        $start_datetime = (!empty($tour['start_date']) && !empty($tour['start_time']))
            ? $tour['start_date'] . ' ' . $tour['start_time'] . ':00'
            : null;
        $end_datetime = (!empty($tour['end_date']) && !empty($tour['end_time']))
            ? $tour['end_date'] . ' ' . $tour['end_time'] . ':00'
            : null;

        $transport_options = json_encode($tour['transport_options'] ?? [], JSON_UNESCAPED_UNICODE);
        $pricing = json_encode($tour['pricing'] ?? [], JSON_UNESCAPED_UNICODE);
        $services_options = json_encode($tour['services_options'] ?? [], JSON_UNESCAPED_UNICODE);
        $meal_details = !empty($tour['meal_details']) ? (string) $tour['meal_details'] : null;
        $required_items = !empty($tour['required_items']) ? (string) $tour['required_items'] : null;
        $capacity = (int) ($tour['capacity'] ?? 0);
        $notes = !empty($tour['notes']) ? (string) $tour['notes'] : null;
        $banner = !empty($tour['banner']) ? (string) $tour['banner'] : null;

        // ---  (۱۸ ستون و ۱۸ پارامتر) ---
        $sql = "INSERT INTO `tours` (
                `chat_id`, `leader_id`, `title`, `description`, `difficulty`,`origin_address`, `region`, 
                `latitude`, `longitude`, `start_date`, `end_date`, 
                `transport_options`, `pricing`, `services_options`, `meal_details`, 
                `required_items`, `capacity`, `notes`, `banner`
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            error_log("❌ [DB PREPARE ERROR] " . $this->mysqli->error);
            return null;
        }

        // ---  (۱۸ کاراکتر) ---
        $types = "iisssssddsssssssiss";


        $stmt->bind_param(
            $types,
            $chatId,
            $leader_id,
            $title,
            $description,
            $difficulty,
            $origin_address,
            $region,
            $latitude,
            $longitude,
            $start_datetime,
            $end_datetime,
            $transport_options,
            $pricing,
            $services_options,
            $meal_details,
            $required_items,
            $capacity,
            $notes,
            $banner
        );

        if ($stmt->execute()) {
            error_log("✅ [DB SUCCESS] Tour inserted with ID: " . $stmt->insert_id);
            return $stmt->insert_id;
        } else {
            error_log("❌ [DB EXECUTE ERROR] " . $stmt->error);
            return null;
        }
    }


    public function insertTour($chatId, array $tour): ?int
    {
        $stmt = $this->mysqli->prepare("
        INSERT INTO tours 
        (chat_id, title, description, origin_city, origin_address, destination_city, destination_address, start_date, end_date, start_hour, end_hour, capacity, price)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");

        $stmt->bind_param(
            "isssssssssiii",
            $chatId,
            $tour['title'],
            $tour['description'],
            $tour['origin_city'],
            $tour['origin_address'],
            $tour['destination_city'],
            $tour['destination_address'],
            $tour['start_date'],
            $tour['end_date'],
            $tour['start_hour'],
            $tour['end_hour'],
            $tour['capacity'],
            $tour['price']
        );

        if ($stmt->execute()) {
            $insertId = $stmt->insert_id;
            $stmt->close();
            return $insertId;
        } else {
            error_log("❌ DB Insert Error: " . $stmt->error);
            $stmt->close();
            return null;
        }
    }


    public function getTourBytourId($tourId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM tours WHERE id = ? ");
        $stmt->bind_param("i", $tourId);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_assoc();
    }


    public function deleteTourById($tourId): bool
    {
        $stmt = $this->mysqli->prepare("DELETE FROM tours WHERE id = ? ");
        $stmt->bind_param("i", $tourId);

        return $stmt->execute();
    }


    public function AdmindeleteTourById($tourId): bool
    {
        $stmt = $this->mysqli->prepare("DELETE FROM tours WHERE id = ? ");
        $stmt->bind_param("i", $tourId);

        return $stmt->execute();
    }


    public function getAllTours(): array
    {
        $tours = [];

        $query = "SELECT id, chat_id, title, start_date, end_date, capacity, banner FROM tours ORDER BY id DESC";
        $result = $this->mysqli->query($query);

        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $tours[] = $row;
            }
        }

        return $tours;
    }


    public function getAllToursPaginated(int $limit, int $offset): array
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM tours ORDER BY start_date DESC LIMIT ? OFFSET ?");
        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getAllToursPaginated: " . $this->mysqli->error);
            return [];
        }
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        $tours = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $tours;
    }


    public function getAllToursCount(): int
    {
        $result = $this->mysqli->query("SELECT COUNT(*) as count FROM tours");
        $row = $result->fetch_assoc();
        return $row['count'] ?? 0;
    }


    public function getActiveTours(int $limit, int $offset)
    {

        $stmt = $this->mysqli->prepare("
        SELECT * FROM tours 
        WHERE start_date >= CURDATE()
        ORDER BY start_date ASC 
        LIMIT ? OFFSET ?
    ");
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        $tours = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $tours;
    }


    public function getActiveToursCount(): int
    {
        $result = $this->mysqli->query("SELECT COUNT(*) as count FROM tours WHERE start_date >= CURDATE()");
        $row = $result->fetch_assoc();
        return $row['count'] ?? 0;
    }


    public function getToursByChatId($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT id,title FROM tours WHERE chat_id = ? ");


        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getToursByChatId: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("i", $chatId);
        if (!$stmt->execute()) {
            error_log("❌ Failed to execute statement in getToursByChatId: " . $stmt->error);
            $stmt->close();
            return [];
        }

        $result = $stmt->get_result();
        $tours = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $tours;
    }


    public function getRegisteredTours($chatId)
    {
        $stmt = $this->mysqli->prepare("
        SELECT r.*, t.title, t.start_date, t.end_date, t.pricing 
        FROM registers r 
        JOIN tours t ON r.tour_id = t.id 
        WHERE r.explorer_id = ?
    ");

        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getRegisteredTours: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("i", $chatId);
        if (!$stmt->execute()) {
            error_log("❌ Failed to execute statement in getRegisteredTours: " . $stmt->error);
            $stmt->close();
            return [];
        }

        $result = $stmt->get_result();
        $tours = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $tours;
    }


    public function isUserRegisteredToTour($chatId, $tourId): bool
    {
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) FROM registers WHERE explorer_id = ? AND tour_id = ? ");
        $stmt->bind_param("ii", $chatId, $tourId);
        $stmt->execute();
        $stmt->bind_result($count);
        $stmt->fetch();
        return $count > 0;
    }


    public function isUserPayedComplete($chatId, $tourId): bool
    {
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) FROM registers WHERE explorer_id = ? AND tour_id = ? AND payment_status='completed'");
        $stmt->bind_param("ii", $chatId, $tourId);
        $stmt->execute();
        $stmt->bind_result($count);
        $stmt->fetch();
        return $count > 0;
    }


    public function registerUserToTour($chatId, $explorer_id, $tourId, $username, $paymentStatus)
    {
        $paymentRefId = null;

        $stmt = $this->mysqli->prepare("
        INSERT INTO `registers` 
        (`chat_id`,`explorer_id`, `tour_id`, `username`, `payment_status`, `payment_ref_id`)
        VALUES (?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE 
            `username` = VALUES(`username`),
            `payment_status` = VALUES(`payment_status`),
            `payment_ref_id` = VALUES(`payment_ref_id`),
            `updated_at` = CURRENT_TIMESTAMP
    ");

        $stmt->bind_param(
            "iiisss",
            $chatId,
            $explorer_id,
            $tourId,
            $username,
            $paymentStatus,
            $paymentRefId
        );

        return $stmt->execute();
    }


    public function UsersRegisteredToTour($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM registers WHERE chat_id = ? ");

        if (!$stmt) {
            error_log("❌ Failed to prepare statement in UsersRegisteredToTour: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("i", $chatId);
        if (!$stmt->execute()) {
            error_log("❌ Failed to execute statement in UsersRegisteredToTour: " . $stmt->error);
            $stmt->close();
            return [];
        }

        $result = $stmt->get_result();
        $users = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $users;
    }


    public function getRegisteredUsersByTourId($tourId)
    {
        $query = "SELECT DISTINCT explorer_id, username
                    FROM registers
                    WHERE tour_id = ? AND payment_status='completed';
                    ";
        $stmt = $this->mysqli->prepare($query);
        $stmt->bind_param("i", $tourId);
        $stmt->execute();
        $result = $stmt->get_result();
        $users = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $users;
    }


    public function countRegisters($tourId)
    {
        $stmt = $this->mysqli->prepare("
        SELECT SUM(quantity) as total FROM registers WHERE tour_id=? AND payment_status='completed'
            ");
        $stmt->bind_param("i", $tourId);
        if (!$stmt->execute()) {
            error_log("❌ Failed to execute countRegisters: " . $stmt->error);
            $stmt->close();
            return 0;
        }

        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();

        return $row ? (int)$row['total'] : 0;
    }


    public function getCapacityTour($tourId)
    {
        $stmt = $this->mysqli->prepare("
   SELECT capacity FROM tours WHERE id=? 
    ");
        $stmt->bind_param("i", $tourId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();

        return $result['capacity'] ?: null;
    }


    public function checkCapacityTour($tourId)
    {
        $stmt = $this->mysqli->prepare("
        SELECT SUM(quantity) as total FROM registers 
        WHERE tour_id = ? AND payment_status = 'completed'
    ");

        if (!$stmt) {
            return 0;
        }

        $stmt->bind_param("i", $tourId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return (int)($result['total'] ?? 0);
    }


    public function getToursByChatIdPaginated(int $chatId, int $limit, int $offset): array
    {
        $stmt = $this->mysqli->prepare("
        SELECT * FROM tours 
        WHERE chat_id = ?
        ORDER BY start_date DESC 
        LIMIT ? OFFSET ?
    ");
        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getToursByChatIdPaginated: " . $this->mysqli->error);
            return [];
        }

        $stmt->bind_param("iii", $chatId, $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        $tours = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();

        return $tours;
    }


    public function getToursCountByChatId(int $chatId): int
    {
        $stmt = $this->mysqli->prepare("SELECT COUNT(*) as count FROM tours WHERE chat_id = ?");
        if (!$stmt) {
            error_log("❌ Failed to prepare statement in getToursCountByChatId: " . $this->mysqli->error);
            return 0;
        }

        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['count'] ?? 0;
    }


    public function getLastTour($chatId)
    {
        $stmt = $this->mysqli->prepare("SELECT * FROM tours WHERE leader_id = ? ORDER BY id DESC LIMIT 1");
        $stmt->bind_param("i", $chatId);
        $stmt->execute();
        $res = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $res;
    }


    public function getTourStats()
    {
        $totalResult = $this->mysqli->query("SELECT COUNT(*) as total_tours FROM tours");
        if ($totalResult) {
            $stats['total_tours'] = $totalResult->fetch_assoc()['total_tours'];
        }
        return $stats;
    }

}
