<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\Database;

/**
 * AudienceSegmentService
 *
 * Turns a segment DEFINITION into a real, deduplicated recipient list
 * (email, first_name, last_name, lead_id|customer_id, company_name).
 * A definition is a small JSON-able array:
 *
 *   ['type' => 'leads', 'business_line' => 'homecare']       // Homecare Leads
 *   ['type' => 'leads', 'business_line' => 'janitorial']     // Cleaner Leads
 *   ['type' => 'leads', 'business_line' => 'both']           // All Leads (both brands)
 *   ['type' => 'customers']                                  // Customers
 *   ['type' => 'leads', 'business_line' => 'both',
 *    'stage_slug' => 'quoted', 'priority' => 'high']          // Filtered Audience
 *
 * Every resolved list is filtered against `email_unsubscribes`
 * (global suppression — see schema.sql Section 15) before being
 * returned, so a suppressed address is never re-added to a new send by
 * accident.
 */
final class AudienceSegmentService
{
    /** @return array<int,array{email:string,first_name:string,last_name:?string,lead_id:?int,customer_id:?int,company_name:?string}> */
    public function resolve(array $definition): array
    {
        $type = $definition['type'] ?? 'leads';

        $recipients = match ($type) {
            'leads' => $this->resolveLeads($definition),
            'customers' => $this->resolveCustomers($definition),
            default => [],
        };

        return $this->excludeUnsubscribed($recipients);
    }

    public function resolveSavedSegment(int $segmentId): array
    {
        $stmt = Database::connection()->prepare('SELECT filter_definition FROM audience_segments WHERE id = :id AND deleted_at IS NULL');
        $stmt->execute(['id' => $segmentId]);
        $json = $stmt->fetchColumn();

        if ($json === false) {
            return [];
        }

        return $this->resolve(json_decode((string) $json, true) ?? []);
    }

    /** Human-readable count preview shown in the Campaign Builder before sending — same resolution logic, just returns the count. */
    public function previewCount(array $definition): int
    {
        return count($this->resolve($definition));
    }

    private function resolveLeads(array $definition): array
    {
        $conditions = ['l.deleted_at IS NULL', 'l.merged_into_lead_id IS NULL', 'l.email IS NOT NULL'];
        $bindings = [];

        $businessLine = $definition['business_line'] ?? 'both';
        if ($businessLine !== 'both') {
            $conditions[] = 'bl.slug = :bl_slug';
            $bindings['bl_slug'] = $businessLine === 'homecare' ? 'homecare' : 'janitorial';
        }

        if (!empty($definition['stage_slug'])) {
            $conditions[] = 'ls.slug = :stage_slug';
            $bindings['stage_slug'] = $definition['stage_slug'];
        }
        if (!empty($definition['priority'])) {
            $conditions[] = 'l.priority = :priority';
            $bindings['priority'] = $definition['priority'];
        }
        if (!empty($definition['lead_source_id'])) {
            $conditions[] = 'l.lead_source_id = :source_id';
            $bindings['source_id'] = $definition['lead_source_id'];
        }
        if (!empty($definition['tag_id'])) {
            $conditions[] = 'EXISTS (SELECT 1 FROM taggables tg WHERE tg.entity_type = "lead" AND tg.entity_id = l.id AND tg.tag_id = :tag_id)';
            $bindings['tag_id'] = $definition['tag_id'];
        }

        $sql = 'SELECT l.id AS lead_id, NULL AS customer_id, l.email, l.first_name, l.last_name, c.name AS company_name, l.business_line_id
                FROM leads l
                LEFT JOIN business_lines bl ON bl.id = l.business_line_id
                LEFT JOIN lead_stages ls ON ls.id = l.lead_stage_id
                LEFT JOIN companies c ON c.id = l.company_id
                WHERE ' . implode(' AND ', $conditions);

        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($bindings);

        return $stmt->fetchAll();
    }

    private function resolveCustomers(array $definition): array
    {
        $conditions = ['cu.deleted_at IS NULL', 'cu.email IS NOT NULL'];
        $bindings = [];

        $businessLine = $definition['business_line'] ?? 'both';
        if ($businessLine !== 'both') {
            $conditions[] = 'bl.slug = :bl_slug';
            $bindings['bl_slug'] = $businessLine === 'homecare' ? 'homecare' : 'janitorial';
        }

        if (!empty($definition['status'])) {
            $conditions[] = 'cu.status = :status';
            $bindings['status'] = $definition['status'];
        }
        if (!empty($definition['tag_id'])) {
            $conditions[] = 'EXISTS (SELECT 1 FROM taggables tg WHERE tg.entity_type = "customer" AND tg.entity_id = cu.id AND tg.tag_id = :tag_id)';
            $bindings['tag_id'] = $definition['tag_id'];
        }

        $sql = 'SELECT NULL AS lead_id, cu.id AS customer_id, cu.email, cu.first_name, cu.last_name, co.name AS company_name, cu.business_line_id
                FROM customers cu
                LEFT JOIN business_lines bl ON bl.id = cu.business_line_id
                LEFT JOIN companies co ON co.id = cu.company_id
                WHERE ' . implode(' AND ', $conditions);

        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($bindings);

        return $stmt->fetchAll();
    }

    private function excludeUnsubscribed(array $recipients): array
    {
        if ($recipients === []) {
            return [];
        }

        $emails = array_unique(array_column($recipients, 'email'));
        $placeholders = implode(',', array_fill(0, count($emails), '?'));

        $stmt = Database::connection()->prepare("SELECT LOWER(email) FROM email_unsubscribes WHERE LOWER(email) IN ({$placeholders})");
        $stmt->execute(array_map('strtolower', $emails));
        $suppressed = array_flip($stmt->fetchAll(\PDO::FETCH_COLUMN));

        return array_values(array_filter($recipients, fn ($r) => !isset($suppressed[strtolower($r['email'])])));
    }
}
