<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\Database;

/**
 * ReportService
 *
 * One method per report type, each returning plain structured data
 * (never pre-rendered HTML) so the exact same report can be: shown on
 * a Reports page, summarized in natural language by the AI Assistant's
 * "Generate Reports" tool, or exported to CSV later — "prepare the
 * project for reporting" per the Phase 5 brief means this shape, not a
 * one-off view.
 */
final class ReportService
{
    public function leadsPipelineReport(?int $businessLineId = null, int $days = 30): array
    {
        $pdo = Database::connection();
        $bindings = ['days' => $days];
        $blClause = '';

        if ($businessLineId !== null) {
            $blClause = ' AND l.business_line_id = :bl';
            $bindings['bl'] = $businessLineId;
        }

        $stmt = $pdo->prepare(
            "SELECT ls.name AS stage, COUNT(l.id) AS count, COALESCE(SUM(l.estimated_value), 0) AS total_value
             FROM lead_stages ls
             LEFT JOIN leads l ON l.lead_stage_id = ls.id AND l.deleted_at IS NULL AND l.merged_into_lead_id IS NULL
                 AND l.created_at > DATE_SUB(NOW(), INTERVAL :days DAY) {$blClause}
             WHERE ls.is_active = 1
             GROUP BY ls.id, ls.name, ls.sort_order
             ORDER BY ls.sort_order"
        );
        $stmt->execute($bindings);

        return ['type' => 'leads_pipeline', 'period_days' => $days, 'stages' => $stmt->fetchAll()];
    }

    public function revenueReport(?int $businessLineId = null, int $days = 30): array
    {
        $pdo = Database::connection();
        $bindings = ['days' => $days];
        $blClause = '';

        if ($businessLineId !== null) {
            $blClause = ' AND cu.business_line_id = :bl';
            $bindings['bl'] = $businessLineId;
        }

        $stmt = $pdo->prepare(
            "SELECT COALESCE(SUM(p.amount), 0) AS total_revenue, COUNT(DISTINCT p.invoice_id) AS invoices_paid
             FROM payments p
             JOIN invoices i ON i.id = p.invoice_id
             JOIN customers cu ON cu.id = i.customer_id
             WHERE p.paid_at > DATE_SUB(NOW(), INTERVAL :days DAY) {$blClause}"
        );
        $stmt->execute($bindings);
        $totals = $stmt->fetch();

        return ['type' => 'revenue', 'period_days' => $days, 'total_revenue' => (float) $totals['total_revenue'], 'invoices_paid' => (int) $totals['invoices_paid']];
    }

    public function appointmentsReport(?int $businessLineId = null, int $days = 30): array
    {
        $pdo = Database::connection();
        $bindings = ['days' => $days];
        $blClause = '';

        if ($businessLineId !== null) {
            $blClause = ' AND business_line_id = :bl';
            $bindings['bl'] = $businessLineId;
        }

        $stmt = $pdo->prepare(
            "SELECT status, COUNT(*) AS count
             FROM appointments
             WHERE deleted_at IS NULL AND scheduled_start > DATE_SUB(NOW(), INTERVAL :days DAY) {$blClause}
             GROUP BY status"
        );
        $stmt->execute($bindings);

        return ['type' => 'appointments', 'period_days' => $days, 'by_status' => $stmt->fetchAll()];
    }

    public function conversationsReport(int $days = 30): array
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare(
            "SELECT channel, COUNT(*) AS total,
                    SUM(outcome = 'lead_created') AS leads_captured,
                    SUM(outcome = 'booked') AS appointments_booked,
                    SUM(outcome = 'escalated') AS escalated_to_human
             FROM conversations
             WHERE started_at > DATE_SUB(NOW(), INTERVAL :days DAY)
             GROUP BY channel"
        );
        $stmt->bindValue('days', $days, \PDO::PARAM_INT);
        $stmt->execute();

        return ['type' => 'conversations', 'period_days' => $days, 'by_channel' => $stmt->fetchAll()];
    }

    /** Customer Reports: status mix, lifetime value totals, and churn within the period. */
    public function customerReport(?int $businessLineId = null, int $days = 30): array
    {
        $pdo = Database::connection();
        $bindings = ['days' => $days];
        $blClause = '';
        if ($businessLineId !== null) {
            $blClause = ' AND business_line_id = :bl';
            $bindings['bl'] = $businessLineId;
        }

        $stmt = $pdo->prepare(
            "SELECT status, COUNT(*) AS count, COALESCE(SUM(lifetime_value), 0) AS total_lifetime_value
             FROM customers WHERE deleted_at IS NULL {$blClause}
             GROUP BY status"
        );
        $stmt->execute(array_diff_key($bindings, ['days' => '']));

        $newStmt = $pdo->prepare(
            "SELECT COUNT(*) FROM customers WHERE deleted_at IS NULL AND customer_since > DATE_SUB(NOW(), INTERVAL :days DAY) {$blClause}"
        );
        $newStmt->execute($bindings);

        return [
            'type' => 'customers', 'period_days' => $days,
            'by_status' => $stmt->fetchAll(),
            'new_customers' => (int) $newStmt->fetchColumn(),
        ];
    }

    /** Marketing/Campaign Reports: performance across every campaign sent in the period. */
    public function marketingReport(int $days = 30): array
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare(
            "SELECT COUNT(*) AS total_campaigns,
                    COALESCE(SUM(status = 'sent'), 0) AS completed_campaigns,
                    COALESCE(SUM(recipients_count), 0) AS total_recipients,
                    COALESCE(SUM(opens_count), 0) AS total_opens,
                    COALESCE(SUM(clicks_count), 0) AS total_clicks,
                    COALESCE(SUM(unsubscribes_count), 0) AS total_unsubscribes
             FROM campaigns WHERE deleted_at IS NULL AND created_at > DATE_SUB(NOW(), INTERVAL :days DAY)"
        );
        $stmt->bindValue('days', $days, \PDO::PARAM_INT);
        $stmt->execute();
        $totals = $stmt->fetch();

        $topStmt = $pdo->prepare(
            "SELECT name, recipients_count, opens_count, clicks_count, sent_at
             FROM campaigns WHERE deleted_at IS NULL AND status = 'sent'
             ORDER BY sent_at DESC LIMIT 10"
        );
        $topStmt->execute();

        return ['type' => 'marketing', 'period_days' => $days, 'totals' => $totals, 'recent_campaigns' => $topStmt->fetchAll()];
    }

    /** AI Reports: usage across all three channels (chatbot/voice/assistant), reusing conversationsReport. */
    public function aiReport(int $days = 30): array
    {
        $conversations = $this->conversationsReport($days);

        $pdo = Database::connection();
        $stmt = $pdo->prepare(
            "SELECT COUNT(*) AS total_runs, COALESCE(SUM(status = 'completed'), 0) AS completed, COALESCE(SUM(status = 'failed'), 0) AS failed
             FROM automation_runs WHERE started_at > DATE_SUB(NOW(), INTERVAL :days DAY)"
        );
        $stmt->bindValue('days', $days, \PDO::PARAM_INT);
        $stmt->execute();

        return ['type' => 'ai', 'period_days' => $days, 'conversations' => $conversations['by_channel'], 'automation_runs' => $stmt->fetch()];
    }

    /** Staff Performance: per-user leads owned/won, appointments completed, conversations handled. */
    public function staffPerformanceReport(int $days = 30): array
    {
        $pdo = Database::connection();

        $stmt = $pdo->prepare(
            "SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) AS name,
                    (SELECT COUNT(*) FROM leads l WHERE l.owner_id = u.id AND l.deleted_at IS NULL AND l.created_at > DATE_SUB(NOW(), INTERVAL :days1 DAY)) AS leads_owned,
                    (SELECT COUNT(*) FROM leads l JOIN lead_stages ls ON ls.id = l.lead_stage_id
                        WHERE l.owner_id = u.id AND l.deleted_at IS NULL AND ls.is_won = 1 AND l.updated_at > DATE_SUB(NOW(), INTERVAL :days2 DAY)) AS leads_won,
                    (SELECT COUNT(*) FROM appointments a WHERE a.assigned_staff_id = u.id AND a.status = 'completed' AND a.scheduled_start > DATE_SUB(NOW(), INTERVAL :days3 DAY)) AS appointments_completed,
                    (SELECT COUNT(*) FROM conversations c WHERE c.handled_by_user_id = u.id AND c.started_at > DATE_SUB(NOW(), INTERVAL :days4 DAY)) AS conversations_handled
             FROM users u
             WHERE u.deleted_at IS NULL AND u.status = 'active'
             ORDER BY leads_won DESC, leads_owned DESC"
        );
        $stmt->execute(['days1' => $days, 'days2' => $days, 'days3' => $days, 'days4' => $days]);

        return ['type' => 'staff_performance', 'period_days' => $days, 'staff' => $stmt->fetchAll()];
    }

    /** System Logs summary: activity_logs volume by category — the numbers behind the System Logs page. */
    public function systemLogsReport(int $days = 30): array
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare(
            "SELECT category, COUNT(*) AS count FROM activity_logs
             WHERE created_at > DATE_SUB(NOW(), INTERVAL :days DAY)
             GROUP BY category"
        );
        $stmt->bindValue('days', $days, \PDO::PARAM_INT);
        $stmt->execute();

        return ['type' => 'system_logs', 'period_days' => $days, 'by_category' => $stmt->fetchAll()];
    }

    /** Everything at once — what the AI Assistant's generate_report tool calls when asked for "a summary" without specifics. */
    public function overviewReport(?int $businessLineId = null, int $days = 30): array
    {
        return [
            'type' => 'overview',
            'period_days' => $days,
            'leads_pipeline' => $this->leadsPipelineReport($businessLineId, $days),
            'revenue' => $this->revenueReport($businessLineId, $days),
            'appointments' => $this->appointmentsReport($businessLineId, $days),
            'conversations' => $this->conversationsReport($days),
        ];
    }
}
