<?php
namespace App\Copilot\Tools;

use App\Copilot\CopilotTool;
use App\Core\Database;
use App\Core\Tenant;

class QuerySales implements CopilotTool
{
    public function name(): string { return 'query_sales'; }

    public function description(): string
    {
        return 'Query sales totals, counts, and breakdowns over a date range.';
    }

    public function parameters(): array
    {
        return [
            'type' => 'object',
            'properties' => [
                'from' => ['type' => 'string', 'description' => 'YYYY-MM-DD'],
                'to'   => ['type' => 'string', 'description' => 'YYYY-MM-DD'],
                'group_by' => ['type' => 'string', 'enum' => ['day','product','category','cashier','payment_method']],
            ],
            'required' => ['from', 'to'],
        ];
    }

    public function execute(array $args): array
    {
        $tenantId = Tenant::id();
        $from = $args['from'];
        $to = $args['to'];
        $group = $args['group_by'] ?? 'day';

        $summary = Database::fetch(
            "SELECT COUNT(*) orders, COALESCE(SUM(total),0) total, COALESCE(AVG(total),0) avg_ticket
             FROM sales WHERE tenant_id = ? AND status <> 'void' AND DATE(created_at) BETWEEN ? AND ?",
            [$tenantId, $from, $to]
        );

        $groupClause = match($group) {
            'product'        => "SELECT si.product_name label, SUM(si.qty) qty, SUM(si.line_total) total
                                 FROM sale_items si JOIN sales s ON s.id = si.sale_id
                                 WHERE s.tenant_id = ? AND DATE(s.created_at) BETWEEN ? AND ?
                                 GROUP BY si.product_name ORDER BY total DESC LIMIT 20",
            'category'       => "SELECT COALESCE(c.name,'Uncategorized') label, SUM(si.line_total) total
                                 FROM sale_items si JOIN sales s ON s.id = si.sale_id
                                 LEFT JOIN products p ON p.id = si.product_id
                                 LEFT JOIN categories c ON c.id = p.category_id
                                 WHERE s.tenant_id = ? AND DATE(s.created_at) BETWEEN ? AND ?
                                 GROUP BY c.name ORDER BY total DESC LIMIT 20",
            'cashier'        => "SELECT u.name label, COUNT(s.id) orders, SUM(s.total) total
                                 FROM sales s JOIN users u ON u.id = s.user_id
                                 WHERE s.tenant_id = ? AND DATE(s.created_at) BETWEEN ? AND ?
                                 GROUP BY u.id ORDER BY total DESC",
            'payment_method' => "SELECT payment_method label, COUNT(*) orders, SUM(total) total
                                 FROM sales WHERE tenant_id = ? AND DATE(created_at) BETWEEN ? AND ?
                                 GROUP BY payment_method",
            default          => "SELECT DATE(created_at) label, COUNT(*) orders, SUM(total) total
                                 FROM sales WHERE tenant_id = ? AND DATE(created_at) BETWEEN ? AND ?
                                 GROUP BY DATE(created_at) ORDER BY label",
        };

        $breakdown = Database::fetchAll($groupClause, [$tenantId, $from, $to]);

        return [
            'range'    => ['from' => $from, 'to' => $to],
            'summary'  => [
                'orders'    => (int)$summary['orders'],
                'total'     => round((float)$summary['total'], 2),
                'avg_ticket'=> round((float)$summary['avg_ticket'], 2),
            ],
            'breakdown'=> $breakdown,
        ];
    }

    public function apply(array $payload): void {}
}