<?php
require __DIR__ . '/bootstrap.php';

use App\Core\Auth;
use App\Core\Csrf;
use App\Core\Database;
use App\Core\Response;
use App\Core\Security;

$user = Auth::requireRole(['super_admin','event_manager','walk_leader']);
$pdo = Database::connection();
$input = request_input();
$method = $_SERVER['REQUEST_METHOD'];
$action = $input['action'] ?? 'list';


function pyrk_group_has_column(PDO $pdo, string $column): bool
{
    static $cache = [];
    if (array_key_exists($column, $cache)) return $cache[$column];
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = "member_groups" AND column_name = ?');
        $stmt->execute([$column]);
        return $cache[$column] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable) {
        return $cache[$column] = false;
    }
}

function pyrk_group_code_clean(?string $code): string
{
    $code = strtoupper(trim((string)$code));
    $code = preg_replace('/[^A-Z0-9_-]+/', '', $code);
    return substr($code, 0, 64);
}

function pyrk_group_generate_code(string $name): string
{
    $base = strtoupper(preg_replace('/[^A-Z0-9]+/', '', $name));
    $base = substr($base ?: 'GROUP', 0, 10);
    return $base . '-' . strtoupper(bin2hex(random_bytes(3)));
}

function pyrk_can_manage_groups(array $user): void
{
    if (!in_array($user['role_name'] ?? '', ['super_admin','event_manager'], true)) {
        Response::error('You do not have permission to manage member groups.', 403);
    }
}

function pyrk_group_member_rows(PDO $pdo, int $groupId): array
{
    $stmt = $pdo->prepare('SELECT u.id, u.full_name, u.email, u.mobile, u.profile_photo_path, COALESCE(mp.total_walks,0) AS total_walks, COALESCE(mp.total_kilometers,0) AS total_kilometers, COALESCE(mp.loyalty_points,0) AS loyalty_points FROM member_group_members mgm JOIN users u ON u.id = mgm.user_id LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE mgm.group_id = ? ORDER BY u.full_name ASC');
    $stmt->execute([$groupId]);
    return $stmt->fetchAll();
}

if ($method === 'GET' && $action === 'list') {
    $groups = $pdo->query('SELECT g.*, u.full_name AS created_by_name, (SELECT COUNT(*) FROM member_group_members mgm WHERE mgm.group_id = g.id) AS member_count FROM member_groups g LEFT JOIN users u ON u.id = g.created_by ORDER BY g.status ASC, g.group_name ASC')->fetchAll();
    $members = $pdo->query('SELECT u.id, u.full_name, u.email, u.mobile, u.profile_photo_path, COALESCE(mp.total_walks,0) AS total_walks, COALESCE(mp.total_kilometers,0) AS total_kilometers, COALESCE(mp.loyalty_points,0) AS loyalty_points FROM users u JOIN roles r ON r.id = u.role_id LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE r.name = "member" AND u.status = "active" ORDER BY u.full_name ASC')->fetchAll();

    $membershipRows = $pdo->query('SELECT mgm.group_id, mgm.user_id FROM member_group_members mgm JOIN users u ON u.id = mgm.user_id JOIN roles r ON r.id = u.role_id JOIN member_groups g ON g.id = mgm.group_id WHERE r.name = "member" AND u.status = "active" AND g.status = "active" ORDER BY mgm.group_id, u.full_name')->fetchAll();
    $groupMembers = [];
    $memberGroups = [];
    foreach ($membershipRows as $row) {
        $groupId = (int)$row['group_id'];
        $userId = (int)$row['user_id'];
        if (!isset($groupMembers[$groupId])) $groupMembers[$groupId] = [];
        if (!isset($memberGroups[$userId])) $memberGroups[$userId] = [];
        $groupMembers[$groupId][] = $userId;
        $memberGroups[$userId][] = $groupId;
    }

    foreach ($groups as &$group) {
        $gid = (int)$group['id'];
        $group['member_ids'] = $groupMembers[$gid] ?? [];
    }
    unset($group);

    foreach ($members as &$member) {
        $mid = (int)$member['id'];
        $member['group_ids'] = $memberGroups[$mid] ?? [];
    }
    unset($member);

    Response::ok(['groups' => $groups, 'members' => $members, 'group_members' => $groupMembers, 'member_groups' => $memberGroups]);
}

if ($method === 'GET' && $action === 'detail') {
    $groupId = (int)($input['id'] ?? 0);
    if (!$groupId) Response::error('Missing group id.');
    $stmt = $pdo->prepare('SELECT g.*, (SELECT COUNT(*) FROM member_group_members mgm WHERE mgm.group_id = g.id) AS member_count FROM member_groups g WHERE g.id = ? LIMIT 1');
    $stmt->execute([$groupId]);
    $group = $stmt->fetch();
    if (!$group) Response::error('Group not found.', 404);
    Response::ok(['group' => $group, 'members' => pyrk_group_member_rows($pdo, $groupId)]);
}

if ($method === 'POST' && $action === 'save_group') {
    pyrk_can_manage_groups($user);
    Csrf::validateFromRequest();

    $id = (int)($input['id'] ?? 0);
    $name = trim((string)($input['group_name'] ?? ''));
    if ($name === '') Response::error('Group name is required.');

    $status = in_array(($input['status'] ?? 'active'), ['active','inactive'], true) ? $input['status'] : 'active';
    $description = trim((string)($input['description'] ?? ''));

    $hasCodeCols = pyrk_group_has_column($pdo, 'signup_code') && pyrk_group_has_column($pdo, 'signup_code_enabled');
    $signupCodeEnabled = !empty($input['signup_code_enabled']) ? 1 : 0;
    $signupCode = pyrk_group_code_clean($input['signup_code'] ?? '');

    if ($hasCodeCols) {
        if ($signupCodeEnabled && $signupCode === '') {
            $signupCode = pyrk_group_generate_code($name);
        }

        if ($signupCode !== '') {
            $dup = $pdo->prepare('SELECT id, group_name FROM member_groups WHERE signup_code = ? AND id <> ? LIMIT 1');
            $dup->execute([$signupCode, $id]);
            $duplicate = $dup->fetch(PDO::FETCH_ASSOC);
            if ($duplicate) {
                Response::error('This signup code already belongs to group: ' . $duplicate['group_name']);
            }
        }
    }

    try {
        if ($id > 0) {
            if ($hasCodeCols) {
                $stmt = $pdo->prepare('UPDATE member_groups SET group_name = ?, description = ?, status = ?, signup_code = ?, signup_code_enabled = ?, updated_at = NOW() WHERE id = ?');
                $stmt->execute([$name, $description, $status, $signupCode ?: null, $signupCodeEnabled, $id]);
            } else {
                $stmt = $pdo->prepare('UPDATE member_groups SET group_name = ?, description = ?, status = ?, updated_at = NOW() WHERE id = ?');
                $stmt->execute([$name, $description, $status, $id]);
            }

            Security::audit((int)$user['id'], 'member_group_updated', 'member_groups', $id, [
                'group_name' => $name,
                'signup_code_enabled' => $signupCodeEnabled,
            ]);

            Response::ok(['group_id' => $id, 'signup_code' => $signupCode], 'Group updated.');
        }

        if ($hasCodeCols) {
            $stmt = $pdo->prepare('INSERT INTO member_groups (group_name, description, status, signup_code, signup_code_enabled, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())');
            $stmt->execute([$name, $description, $status, $signupCode ?: null, $signupCodeEnabled, (int)$user['id']]);
        } else {
            $stmt = $pdo->prepare('INSERT INTO member_groups (group_name, description, status, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())');
            $stmt->execute([$name, $description, $status, (int)$user['id']]);
        }

        $newId = (int)$pdo->lastInsertId();
        Security::audit((int)$user['id'], 'member_group_created', 'member_groups', $newId, [
            'group_name' => $name,
            'signup_code_enabled' => $signupCodeEnabled,
        ]);

        Response::ok(['group_id' => $newId, 'signup_code' => $signupCode], 'Group created.');
    } catch (Throwable $e) {
        if (strpos($e->getMessage(), 'Duplicate') !== false) Response::error('A group with this name or signup code already exists.');
        Response::error('Could not save group: ' . $e->getMessage());
    }
}

if ($method === 'POST' && $action === 'sync_members') {
    pyrk_can_manage_groups($user);
    Csrf::validateFromRequest();
    $groupId = (int)($input['group_id'] ?? 0);
    if (!$groupId) Response::error('Missing group id.');
    $members = $input['member_ids'] ?? [];
    if (!is_array($members)) $members = array_filter(array_map('intval', explode(',', (string)$members)));
    $memberIds = array_values(array_unique(array_filter(array_map('intval', $members))));
    $pdo->beginTransaction();
    try {
        $pdo->prepare('DELETE FROM member_group_members WHERE group_id = ?')->execute([$groupId]);
        $stmt = $pdo->prepare('INSERT INTO member_group_members (group_id, user_id, created_by, created_at) VALUES (?, ?, ?, NOW())');
        foreach ($memberIds as $memberId) {
            $stmt->execute([$groupId, $memberId, (int)$user['id']]);
        }
        $pdo->commit();
        Security::audit((int)$user['id'], 'member_group_members_synced', 'member_groups', $groupId, ['member_count' => count($memberIds)]);
        Response::ok(['member_count' => count($memberIds)], 'Group members updated.');
    } catch (Throwable $e) {
        $pdo->rollBack();
        Response::error('Could not update group members: ' . $e->getMessage());
    }
}

Response::error('Unsupported groups action.', 404);
