<?php
/**
 * ClientService — the salon CRM read/write layer.
 *
 * A "client" IS an instinctor_customers row. This service aggregates the full
 * salon profile around it: lifetime stats, a merged visit timeline (bookings +
 * orders, both already carry customer_id), before/after photo sets, color
 * formulas, and soft fields (notes / tags / birthday / allergies) kept in
 * customers.meta so no core table is altered.
 *
 * Static, DB-only, additive. Nothing here runs unless a client profile is opened.
 */

namespace Instinctor\Services;

use Instinctor\Core\DB;
use Instinctor\Core\Tenant;

class ClientService
{
    /** Decode the customers.meta JSON into an array. */
    private static function meta(?string $raw): array
    {
        if (!$raw) return [];
        $d = json_decode($raw, true);
        return is_array($d) ? $d : [];
    }

    /** Owner (tenant) that a customer belongs to, or null (vendor / single-tenant WP). */
    private static function ownerOfCustomer(int $id): ?int
    {
        $oid = DB::get_var(DB::prepare(
            "SELECT owner_id FROM " . DB::prefix() . "instinctor_customers WHERE id = %d", $id
        ));
        return ($oid === null || $oid === '') ? null : (int) $oid;
    }

    // ── Admin list ──────────────────────────────────────────────────────────
    /** Clients for the admin list: identity + last visit + visit count + spend + tags. */
    public static function listClients(string $search = '', int $limit = 100, int $offset = 0): array
    {
        $p = DB::prefix();
        $limit  = max(1, min(500, $limit));
        $offset = max(0, $offset);
        $conds  = [Tenant::scopeSql('c.owner_id')];
        $args   = [];
        if (trim($search) !== '') {
            $like    = '%' . $search . '%';
            $conds[] = "(c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s
                         OR CONCAT(c.first_name,' ',c.last_name) LIKE %s)";
            $args    = [$like, $like, $like, $like, $like];
        }
        $where = 'WHERE ' . implode(' AND ', $conds);
        $sql = "SELECT c.id, c.first_name, c.last_name, c.email, c.phone, c.total_spent, c.order_count, c.meta,
                    (SELECT MAX(b.start_time) FROM {$p}instinctor_bookings b WHERE b.customer_id = c.id) AS last_visit,
                    (SELECT COUNT(*) FROM {$p}instinctor_bookings b WHERE b.customer_id = c.id) AS visit_count
                FROM {$p}instinctor_customers c
                {$where}
                ORDER BY (last_visit IS NULL), last_visit DESC, c.id DESC
                LIMIT %d OFFSET %d";
        $args[] = $limit;
        $args[] = $offset;
        $rows = DB::get_results(DB::prepare($sql, ...$args), 'ARRAY_A') ?: [];
        foreach ($rows as &$r) {
            $m = self::meta($r['meta'] ?? null);
            unset($r['meta']);
            $r['name']  = trim(($r['first_name'] ?? '') . ' ' . ($r['last_name'] ?? '')) ?: ($r['email'] ?? 'Client');
            $r['tags']  = isset($m['tags']) && is_array($m['tags']) ? $m['tags'] : [];
        }
        return $rows;
    }

    /** Total client count (for pagination), honouring the same search. */
    public static function count(string $search = ''): int
    {
        $p = DB::prefix();
        $scope = Tenant::scopeSql('c.owner_id');
        if (trim($search) !== '') {
            $like = '%' . $search . '%';
            return (int) DB::get_var(DB::prepare(
                "SELECT COUNT(*) FROM {$p}instinctor_customers c
                 WHERE {$scope} AND (c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s
                        OR CONCAT(c.first_name,' ',c.last_name) LIKE %s)",
                $like, $like, $like, $like, $like
            ));
        }
        return (int) DB::get_var("SELECT COUNT(*) FROM {$p}instinctor_customers c WHERE {$scope}");
    }

    // ── Full profile ────────────────────────────────────────────────────────
    public static function getProfile(int $id): ?array
    {
        $p = DB::prefix();
        $c = DB::get_row(DB::prepare(
            "SELECT * FROM {$p}instinctor_customers WHERE id = %d AND " . Tenant::scopeSql('owner_id'), $id
        ), 'ARRAY_A');
        if (!$c) return null;

        $m = self::meta($c['meta'] ?? null);
        return [
            'id'          => (int) $c['id'],
            'name'        => trim(($c['first_name'] ?? '') . ' ' . ($c['last_name'] ?? '')) ?: ($c['email'] ?? 'Client'),
            'first_name'  => $c['first_name'] ?? '',
            'last_name'   => $c['last_name'] ?? '',
            'email'       => $c['email'] ?? '',
            'phone'       => $c['phone'] ?? '',
            'total_spent' => (float) ($c['total_spent'] ?? 0),
            'order_count' => (int) ($c['order_count'] ?? 0),
            'created_at'  => $c['created_at'] ?? null,
            'notes'       => (string) ($m['notes'] ?? ''),
            'tags'        => isset($m['tags']) && is_array($m['tags']) ? $m['tags'] : [],
            'birthday'    => (string) ($m['birthday'] ?? ''),
            'allergies'   => (string) ($m['allergies'] ?? ''),
            'visits'      => self::getVisits($id),
            'photos'      => self::listPhotos($id),
            'formulas'    => self::listFormulas($id),
        ];
    }

    /** Merged, date-descending timeline of appointments + purchases. */
    private static function getVisits(int $id, int $limit = 60): array
    {
        $p = DB::prefix();
        $sql = "
            (SELECT 'appointment' AS kind, b.id AS ref_id, b.start_time AS dt, b.status,
                    COALESCE(s.name, pr.name, 'Appointment') AS label, NULL AS amount, b.customer_note AS note
             FROM {$p}instinctor_bookings b
             LEFT JOIN {$p}instinctor_products pr ON b.product_id = pr.id
             LEFT JOIN {$p}instinctor_services s ON b.product_id = s.id
             WHERE b.customer_id = %d)
            UNION ALL
            (SELECT 'purchase' AS kind, o.id AS ref_id, o.created_at AS dt, o.status,
                    CONCAT('Order ', o.order_number) AS label, o.total AS amount, NULL AS note
             FROM {$p}instinctor_orders o
             WHERE o.customer_id = %d)
            ORDER BY dt DESC
            LIMIT %d";
        $rows = DB::get_results(DB::prepare($sql, $id, $id, $limit), 'ARRAY_A') ?: [];
        foreach ($rows as &$r) {
            $r['ref_id'] = (int) $r['ref_id'];
            $r['amount'] = $r['amount'] !== null ? (float) $r['amount'] : null;
        }
        return $rows;
    }

    // ── Soft fields (notes / tags / birthday / allergies) → customers.meta ────
    public static function saveMeta(int $id, array $fields): bool
    {
        $p = DB::prefix();
        $raw = DB::get_var(DB::prepare("SELECT meta FROM {$p}instinctor_customers WHERE id = %d", $id));
        $m = self::meta($raw);
        foreach (['notes', 'birthday', 'allergies'] as $k) {
            if (array_key_exists($k, $fields)) $m[$k] = (string) $fields[$k];
        }
        if (array_key_exists('tags', $fields)) {
            $tags = is_array($fields['tags']) ? $fields['tags'] : preg_split('/\s*,\s*/', (string) $fields['tags'], -1, PREG_SPLIT_NO_EMPTY);
            $m['tags'] = array_values(array_unique(array_map('trim', $tags)));
        }
        return DB::update($p . 'instinctor_customers', ['meta' => wp_json_encode($m)], ['id' => $id]) !== false;
    }

    // ── Before/after photos ──────────────────────────────────────────────────
    public static function listPhotos(int $id): array
    {
        $p = DB::prefix();
        $rows = DB::get_results(DB::prepare(
            "SELECT * FROM {$p}instinctor_client_photos WHERE customer_id = %d ORDER BY (taken_at IS NULL), taken_at DESC, id DESC", $id
        ), 'ARRAY_A') ?: [];
        return $rows;
    }

    public static function addPhoto(int $id, array $d): int
    {
        $p = DB::prefix();
        DB::insert($p . 'instinctor_client_photos', [
            'customer_id'  => $id,
            'booking_id'   => !empty($d['booking_id']) ? (int) $d['booking_id'] : null,
            'staff_id'     => !empty($d['staff_id']) ? (int) $d['staff_id'] : null,
            'service'      => (string) ($d['service'] ?? ''),
            'before_url'   => (string) ($d['before_url'] ?? ''),
            'before_thumb' => (string) ($d['before_thumb'] ?? ''),
            'after_url'    => (string) ($d['after_url'] ?? ''),
            'after_thumb'  => (string) ($d['after_thumb'] ?? ''),
            'taken_at'     => !empty($d['taken_at']) ? (string) $d['taken_at'] : null,
            'note'         => (string) ($d['note'] ?? ''),
        ]);
        $photoId = (int) DB::insert_id();
        // Meter the before/after bytes against this client's tenant.
        $bytes = \Instinctor\Core\MediaUsage::bytesForUrl((string) ($d['before_url'] ?? ''))
               + \Instinctor\Core\MediaUsage::bytesForUrl((string) ($d['after_url'] ?? ''));
        \Instinctor\Core\MediaUsage::add(self::ownerOfCustomer($id), $bytes, 1);
        return $photoId;
    }

    public static function deletePhoto(int $photoId): bool
    {
        $p = DB::prefix();
        $row = DB::get_row(DB::prepare(
            "SELECT customer_id, before_url, after_url FROM {$p}instinctor_client_photos WHERE id = %d", $photoId
        ), 'ARRAY_A');
        $ok = DB::query(DB::prepare("DELETE FROM {$p}instinctor_client_photos WHERE id = %d", $photoId)) !== false;
        if ($ok && $row) {
            $bytes = \Instinctor\Core\MediaUsage::bytesForUrl((string) ($row['before_url'] ?? ''))
                   + \Instinctor\Core\MediaUsage::bytesForUrl((string) ($row['after_url'] ?? ''));
            \Instinctor\Core\MediaUsage::release(self::ownerOfCustomer((int) $row['customer_id']), $bytes, 1);
        }
        return $ok;
    }

    // ── Color formulas ────────────────────────────────────────────────────────
    public static function listFormulas(int $id): array
    {
        $p = DB::prefix();
        return DB::get_results(DB::prepare(
            "SELECT * FROM {$p}instinctor_client_formulas WHERE customer_id = %d ORDER BY (used_at IS NULL), used_at DESC, id DESC", $id
        ), 'ARRAY_A') ?: [];
    }

    public static function addFormula(int $id, array $d): int
    {
        $p = DB::prefix();
        DB::insert($p . 'instinctor_client_formulas', [
            'customer_id' => $id,
            'staff_id'    => !empty($d['staff_id']) ? (int) $d['staff_id'] : null,
            'title'       => (string) ($d['title'] ?? ''),
            'formula'     => (string) ($d['formula'] ?? ''),
            'brand'       => (string) ($d['brand'] ?? ''),
            'service'     => (string) ($d['service'] ?? ''),
            'used_at'     => !empty($d['used_at']) ? (string) $d['used_at'] : null,
        ]);
        return DB::insert_id();
    }

    public static function updateFormula(int $formulaId, array $d): bool
    {
        $p = DB::prefix();
        $fields = [];
        foreach (['title', 'formula', 'brand', 'service'] as $k) {
            if (array_key_exists($k, $d)) $fields[$k] = (string) $d[$k];
        }
        if (array_key_exists('used_at', $d)) $fields['used_at'] = !empty($d['used_at']) ? (string) $d['used_at'] : null;
        if (!$fields) return true;
        return DB::update($p . 'instinctor_client_formulas', $fields, ['id' => $formulaId]) !== false;
    }

    public static function deleteFormula(int $formulaId): bool
    {
        $p = DB::prefix();
        return DB::query(DB::prepare("DELETE FROM {$p}instinctor_client_formulas WHERE id = %d", $formulaId)) !== false;
    }
}
