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

function get_unique_members($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT COUNT(DISTINCT m.id) AS unique_members
        FROM members m
        JOIN attendance a ON m.id = a.member_id
        WHERE m.community_id = ?
        AND a.attendance_date BETWEEN ? AND ?
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    return $stmt->fetch();
}

function get_unique_members_drilldown($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT m.name, m.email, m.join_date, MAX(a.attendance_date) as last_visit
        FROM members m
        JOIN attendance a ON m.id = a.member_id
        WHERE m.community_id = ?
        AND a.attendance_date BETWEEN ? AND ?
        GROUP BY m.id, m.name, m.email, m.join_date
        ORDER BY m.name
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    return $stmt->fetchAll();
}

if (isset($_GET['kpi'])) {
    header('Content-Type: application/json');
    $community_id = $_GET['community_id'] ?? 1;
    $start_date = $_GET['start_date'] ?? date('Y-m-01');
    $end_date = $_GET['end_date'] ?? date('Y-m-t');

    switch ($_GET['kpi']) {
        case 'unique_members':
            echo json_encode(get_unique_members($community_id, $start_date, $end_date));
            break;
        case 'unique_members_drilldown':
            echo json_encode(get_unique_members_drilldown($community_id, $start_date, $end_date));
            break;
        case 'avg_class_attendance':
            echo json_encode(get_avg_class_attendance($community_id, $start_date, $end_date));
            break;
        case 'avg_class_attendance_drilldown':
            echo json_encode(get_avg_class_attendance_drilldown($community_id, $start_date, $end_date));
            break;
        case 'classes_lightly_attended':
            echo json_encode(get_classes_lightly_attended($community_id, $start_date, $end_date));
            break;
        case 'classes_lightly_attended_drilldown':
            echo json_encode(get_classes_lightly_attended_drilldown($community_id, $start_date, $end_date));
            break;
        case 'attendance_trend':
            echo json_encode(get_attendance_trend($community_id, $start_date, $end_date));
            break;
        case 'avg_visits_per_member':
            echo json_encode(get_avg_visits_per_member($community_id, $start_date, $end_date));
            break;
        case 'avg_visits_per_member_drilldown':
            echo json_encode(get_avg_visits_per_member_drilldown($community_id, $start_date, $end_date));
            break;
        case 'high_frequency_members':
            echo json_encode(get_high_frequency_members($community_id, $start_date, $end_date));
            break;
        case 'high_frequency_members_drilldown':
            echo json_encode(get_high_frequency_members_drilldown($community_id, $start_date, $end_date));
            break;
        case 'total_revenue':
            echo json_encode(get_total_revenue($community_id, $start_date, $end_date));
            break;
        case 'total_revenue_drilldown':
            echo json_encode(get_total_revenue_drilldown($community_id, $start_date, $end_date));
            break;
        case 'revenue_per_active_member':
            echo json_encode(get_revenue_per_active_member($community_id, $start_date, $end_date));
            break;
        case 'revenue_per_active_member_drilldown':
            echo json_encode(get_revenue_per_active_member_drilldown($community_id, $start_date, $end_date));
            break;
        case 'inactive_members':
            echo json_encode(get_inactive_members($community_id, $start_date, $end_date));
            break;
        case 'inactive_members_drilldown':
            echo json_encode(get_inactive_members_drilldown($community_id, $start_date, $end_date));
            break;
        case 'visits_dropped':
            echo json_encode(get_visits_dropped($community_id, $start_date, $end_date));
            break;
        case 'attendance_trend_drilldown':
            echo json_encode(get_attendance_trend_drilldown($community_id, $start_date, $end_date));
            break;
        case 'attendance_over_time':
            echo json_encode(get_attendance_over_time($community_id, $start_date, $end_date));
            break;
        case 'visit_frequency_distribution':
            echo json_encode(get_visit_frequency_distribution($community_id, $start_date, $end_date));
            break;
    }
    exit;
}

function get_avg_class_attendance($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT AVG(daily_attendance) as avg_attendance
        FROM (
            SELECT COUNT(member_id) as daily_attendance
            FROM attendance a
            JOIN members m ON a.member_id = m.id
            WHERE m.community_id = ?
            AND a.attendance_date BETWEEN ? AND ?
            GROUP BY a.class_id, a.attendance_date
        ) as daily_counts
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    $result = $stmt->fetch();
    return ['avg_class_attendance' => round($result['avg_attendance'], 2)];
}

function get_avg_class_attendance_drilldown($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT c.name as class_name, DATE(a.attendance_date) as date, COUNT(a.member_id) as attendance_count
        FROM attendance a
        JOIN classes c ON a.class_id = c.id
        JOIN members m ON a.member_id = m.id
        WHERE m.community_id = ?
        AND a.attendance_date BETWEEN ? AND ?
        GROUP BY c.name, DATE(a.attendance_date)
        ORDER BY date, class_name
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    return $stmt->fetchAll();
}

function get_classes_lightly_attended($community_id, $start_date, $end_date, $threshold = 5) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT (CAST(SUM(CASE WHEN attendance_count < ? THEN 1 ELSE 0 END) AS DECIMAL(10,2)) / COUNT(*)) * 100 as percentage
        FROM (
            SELECT COUNT(a.member_id) as attendance_count
            FROM attendance a
            JOIN members m ON a.member_id = m.id
            WHERE m.community_id = ?
            AND a.attendance_date BETWEEN ? AND ?
            GROUP BY a.class_id, a.attendance_date
        ) as class_counts
    """);
    $stmt->execute([$threshold, $community_id, $start_date, $end_date]);
    $result = $stmt->fetch();
    return ['classes_lightly_attended' => round($result['percentage'], 2)];
}

function get_classes_lightly_attended_drilldown($community_id, $start_date, $end_date, $threshold = 5) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT c.name as class_name, DATE(a.attendance_date) as date, COUNT(a.member_id) as attendance_count
        FROM attendance a
        JOIN classes c ON a.class_id = c.id
        JOIN members m ON a.member_id = m.id
        WHERE m.community_id = ?
        AND a.attendance_date BETWEEN ? AND ?
        GROUP BY c.name, DATE(a.attendance_date)
        HAVING COUNT(a.member_id) < ?
        ORDER BY date, class_name
    """);
    $stmt->execute([$community_id, $start_date, $end_date, $threshold]);
    return $stmt->fetchAll();
}

function get_attendance_trend($community_id, $start_date, $end_date) {
    $pdo = db();

    $current_period_stmt = $pdo->prepare("SELECT COUNT(*) as count FROM attendance a JOIN members m ON m.id = a.member_id WHERE m.community_id = ? AND a.attendance_date BETWEEN ? AND ?");
    $current_period_stmt->execute([$community_id, $start_date, $end_date]);
    $current_period_count = $current_period_stmt->fetchColumn();

    $period_days = (new DateTime($end_date))->diff(new DateTime($start_date))->days;
    $previous_start_date = (new DateTime($start_date))->modify("-" . ($period_days + 1) . " days")->format('Y-m-d');
    $previous_end_date = (new DateTime($end_date))->modify("-" . ($period_days + 1) . " days")->format('Y-m-d');

    $previous_period_stmt = $pdo->prepare("SELECT COUNT(*) as count FROM attendance a JOIN members m ON m.id = a.member_id WHERE m.community_id = ? AND a.attendance_date BETWEEN ? AND ?");
    $previous_period_stmt->execute([$community_id, $previous_start_date, $previous_end_date]);
    $previous_period_count = $previous_period_stmt->fetchColumn();

    if ($previous_period_count == 0) {
        $trend = $current_period_count > 0 ? 100 : 0;
    } else {
        $trend = (($current_period_count - $previous_period_count) / $previous_period_count) * 100;
    }

    return ['attendance_trend' => round($trend, 2)];
}

function get_avg_visits_per_member($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT AVG(visit_count) as avg_visits
        FROM (
            SELECT COUNT(a.id) as visit_count
            FROM members m
            JOIN attendance a ON m.id = a.member_id
            WHERE m.community_id = ?
            AND a.attendance_date BETWEEN ? AND ?
            GROUP BY m.id
        ) as member_visits
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    $result = $stmt->fetch();
    return ['avg_visits_per_member' => round($result['avg_visits'], 2)];
}

function get_avg_visits_per_member_drilldown($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT m.name, m.email, COUNT(a.id) as visit_count
        FROM members m
        JOIN attendance a ON m.id = a.member_id
        WHERE m.community_id = ?
        AND a.attendance_date BETWEEN ? AND ?
        GROUP BY m.id, m.name, m.email
        ORDER BY visit_count DESC
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    return $stmt->fetchAll();
}

function get_high_frequency_members($community_id, $start_date, $end_date, $frequency_threshold = 5) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT (CAST(SUM(CASE WHEN visit_count >= ? THEN 1 ELSE 0 END) AS DECIMAL(10,2)) / COUNT(*)) * 100 as percentage
        FROM (
            SELECT COUNT(a.id) as visit_count
            FROM members m
            JOIN attendance a ON m.id = a.member_id
            WHERE m.community_id = ?
            AND a.attendance_date BETWEEN ? AND ?
            GROUP BY m.id
        ) as member_visits
    """);
    $stmt->execute([$frequency_threshold, $community_id, $start_date, $end_date]);
    $result = $stmt->fetch();
    return ['high_frequency_members' => round($result['percentage'], 2)];
}

function get_high_frequency_members_drilldown($community_id, $start_date, $end_date, $frequency_threshold = 5) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT m.name, m.email, COUNT(a.id) as visit_count
        FROM members m
        JOIN attendance a ON m.id = a.member_id
        WHERE m.community_id = ?
        AND a.attendance_date BETWEEN ? AND ?
        GROUP BY m.id, m.name, m.email
        HAVING visit_count >= ?
        ORDER BY visit_count DESC
    """);
    $stmt->execute([$community_id, $start_date, $end_date, $frequency_threshold]);
    return $stmt->fetchAll();
}

function get_total_revenue($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT SUM(r.amount) as total_revenue
        FROM revenue r
        JOIN members m ON r.member_id = m.id
        WHERE m.community_id = ?
        AND r.transaction_date BETWEEN ? AND ?
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    $result = $stmt->fetch();
    return ['total_revenue' => round($result['total_revenue'], 2)];
}

function get_total_revenue_drilldown($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT m.name, m.email, r.amount, r.service_category, r.transaction_date
        FROM revenue r
        JOIN members m ON r.member_id = m.id
        WHERE m.community_id = ?
        AND r.transaction_date BETWEEN ? AND ?
        ORDER BY r.transaction_date DESC
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    return $stmt->fetchAll();
}

function get_revenue_per_active_member($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT SUM(r.amount) / COUNT(DISTINCT r.member_id) as revenue_per_active_member
        FROM revenue r
        JOIN members m ON r.member_id = m.id
        WHERE m.community_id = ?
        AND r.transaction_date BETWEEN ? AND ?
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    $result = $stmt->fetch();
    return ['revenue_per_active_member' => round($result['revenue_per_active_member'], 2)];
}

function get_revenue_per_active_member_drilldown($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT m.name, m.email, SUM(r.amount) as total_revenue, COUNT(r.id) as transaction_count
        FROM revenue r
        JOIN members m ON r.member_id = m.id
        WHERE m.community_id = ?
        AND r.transaction_date BETWEEN ? AND ?
        GROUP BY m.id, m.name, m.email
        ORDER BY total_revenue DESC
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    return $stmt->fetchAll();
}

function get_inactive_members($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT 
            (1 - (COUNT(DISTINCT a.member_id) / COUNT(DISTINCT m.id))) * 100 as inactive_percentage
        FROM members m
        LEFT JOIN attendance a ON m.id = a.member_id AND a.attendance_date BETWEEN ? AND ?
        WHERE m.community_id = ?
    """);
    $stmt->execute([$start_date, $end_date, $community_id]);
    $result = $stmt->fetch();
    return ['inactive_members' => round($result['inactive_percentage'], 2)];
}

function get_inactive_members_drilldown($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT m.name, m.email, m.join_date
        FROM members m
        LEFT JOIN attendance a ON m.id = a.member_id AND a.attendance_date BETWEEN ? AND ?
        WHERE m.community_id = ?
        GROUP BY m.id
        HAVING COUNT(a.id) = 0
    """);
    $stmt->execute([$start_date, $end_date, $community_id]);
    return $stmt->fetchAll();
}

function get_visits_dropped($community_id, $start_date, $end_date) {
    $pdo = db();

    $current_period_stmt = $pdo->prepare("SELECT COUNT(*) as count FROM attendance a JOIN members m ON m.id = a.member_id WHERE m.community_id = ? AND a.attendance_date BETWEEN ? AND ?");
    $current_period_stmt->execute([$community_id, $start_date, $end_date]);
    $current_period_count = $current_period_stmt->fetchColumn();

    $period_days = (new DateTime($end_date))->diff(new DateTime($start_date))->days;
    $previous_start_date = (new DateTime($start_date))->modify("-" . ($period_days + 1) . " days")->format('Y-m-d');
    $previous_end_date = (new DateTime($end_date))->modify("-" . ($period_days + 1) . " days")->format('Y-m-d');

    $previous_period_stmt = $pdo->prepare("SELECT COUNT(*) as count FROM attendance a JOIN members m ON m.id = a.member_id WHERE m.community_id = ? AND a.attendance_date BETWEEN ? AND ?");
    $previous_period_stmt->execute([$community_id, $previous_start_date, $previous_end_date]);
    $previous_period_count = $previous_period_stmt->fetchColumn();

    if ($previous_period_count == 0) {
        $trend = $current_period_count > 0 ? 100 : 0;
    } else {
        $trend = (($current_period_count - $previous_period_count) / $previous_period_count) * 100;
    }

    return ['visits_dropped' => round($trend, 2)];
}

function get_attendance_trend_drilldown($community_id, $start_date, $end_date) {
    $pdo = db();

    $current_period_stmt = $pdo->prepare("SELECT COUNT(*) as count FROM attendance a JOIN members m ON m.id = a.member_id WHERE m.community_id = ? AND a.attendance_date BETWEEN ? AND ?");
    $current_period_stmt->execute([$community_id, $start_date, $end_date]);
    $current_period_count = $current_period_stmt->fetchColumn();

    $period_days = (new DateTime($end_date))->diff(new DateTime($start_date))->days;
    $previous_start_date = (new DateTime($start_date))->modify("-" . ($period_days + 1) . " days")->format('Y-m-d');
    $previous_end_date = (new DateTime($end_date))->modify("-" . ($period_days + 1) . " days")->format('Y-m-d');

    $previous_period_stmt = $pdo->prepare("SELECT COUNT(*) as count FROM attendance a JOIN members m ON m.id = a.member_id WHERE m.community_id = ? AND a.attendance_date BETWEEN ? AND ?");
    $previous_period_stmt->execute([$community_id, $previous_start_date, $previous_end_date]);
    $previous_period_count = $previous_period_stmt->fetchColumn();

    return [
        ['period' => 'Current Period', 'visits' => $current_period_count],
        ['period' => 'Previous Period', 'visits' => $previous_period_count]
    ];
}

function get_attendance_over_time($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT DATE(a.attendance_date) as date, COUNT(a.id) as attendance_count
        FROM attendance a
        JOIN members m ON a.member_id = m.id
        WHERE m.community_id = ?
        AND a.attendance_date BETWEEN ? AND ?
        GROUP BY DATE(a.attendance_date)
        ORDER BY date
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    $data = $stmt->fetchAll();

    $labels = [];
    $values = [];
    foreach ($data as $row) {
        $labels[] = $row['date'];
        $values[] = $row['attendance_count'];
    }

    return ['labels' => $labels, 'data' => $values];
}

function get_visit_frequency_distribution($community_id, $start_date, $end_date) {
    $pdo = db();
    $stmt = $pdo->prepare("""
        SELECT 
            CASE
                WHEN visit_count = 1 THEN '1 visit'
                WHEN visit_count BETWEEN 2 AND 4 THEN '2-4 visits'
                WHEN visit_count BETWEEN 5 AND 9 THEN '5-9 visits'
                ELSE '10+ visits'
            END as frequency_bucket,
            COUNT(*) as member_count
        FROM (
            SELECT COUNT(a.id) as visit_count
            FROM members m
            JOIN attendance a ON m.id = a.member_id
            WHERE m.community_id = ?
            AND a.attendance_date BETWEEN ? AND ?
            GROUP BY m.id
        ) as member_visits
        GROUP BY frequency_bucket
    """);
    $stmt->execute([$community_id, $start_date, $end_date]);
    $data = $stmt->fetchAll();

    $labels = ['1 visit', '2-4 visits', '5-9 visits', '10+ visits'];
    $values = [0, 0, 0, 0];
    foreach ($data as $row) {
        $index = array_search($row['frequency_bucket'], $labels);
        if ($index !== false) {
            $values[$index] = $row['member_count'];
        }
    }

    return ['labels' => $labels, 'data' => $values];
}