<?php
namespace local_academic_ticket_system\external;

use core_external\external_api;
use core_external\external_function_parameters;
use core_external\external_single_structure;
use core_external\external_value;
use core_external\external_description;
use context_system;

defined('MOODLE_INTERNAL') || die();

class get_business_stats extends external_api {

    public static function execute_parameters(): external_function_parameters {
        return new external_function_parameters([]);
    }

    public static function execute() {
        global $DB;

        self::validate_context(context_system::instance());

        $total = $DB->count_records('local_acadtkt_tickets');
        $open = $DB->count_records('local_acadtkt_tickets', ['status' => 'open']);
        $closed = $DB->count_records('local_acadtkt_tickets', ['status' => 'closed']);
        $unassigned = $DB->count_records('local_acadtkt_tickets', ['assigned_to' => 0, 'status' => 'open']);

        $avg_rating = $DB->get_field_sql("SELECT AVG(rating) FROM {local_acadtkt_feedback}");
        $avg_rating = $avg_rating ? round($avg_rating, 1) : 0;

        $sql_res_time = "SELECT AVG(updated_at - created_at) 
                         FROM {local_acadtkt_tickets} 
                         WHERE status = 'closed' AND updated_at > created_at";
        $avg_res_time = $DB->get_field_sql($sql_res_time);
        $avg_res_time_hours = $avg_res_time ? round($avg_res_time / 3600, 1) : 0;

        $sql_top_cat = "SELECT c.title, COUNT(t.id) as count
                        FROM {local_acadtkt_categories} c
                        JOIN {local_acadtkt_tickets} t ON t.category_id = c.id
                        GROUP BY c.id, c.title
                        ORDER BY count DESC";
        $top_cat_record = $DB->get_record_sql($sql_top_cat, null, IGNORE_MULTIPLE);
        $top_category = $top_cat_record ? $top_cat_record->title : 'N/A';

        return [
            'total' => (int)$total,
            'open' => (int)$open,
            'closed' => (int)$closed,
            'unassigned' => (int)$unassigned,
            'avg_rating' => (float)$avg_rating,
            'avg_res_time_hours' => (float)$avg_res_time_hours,
            'top_category' => (string)$top_category
        ];
    }

    public static function execute_returns(): external_description {
        return new external_single_structure([
            'total' => new external_value(PARAM_INT, 'Total tickets'),
            'open' => new external_value(PARAM_INT, 'Open tickets'),
            'closed' => new external_value(PARAM_INT, 'Closed tickets'),
            'unassigned' => new external_value(PARAM_INT, 'Unassigned tickets'),
            'avg_rating' => new external_value(PARAM_FLOAT, 'Average customer rating'),
            'avg_res_time_hours' => new external_value(PARAM_FLOAT, 'Average resolution time in hours'),
            'top_category' => new external_value(PARAM_TEXT, 'Category with most tickets'),
        ]);
    }
}