<?php
/**
 * ComptaFlow - Service de gestion des clients
 */

namespace ComptaFlowPro\Services;

defined('ABSPATH') || exit;

class ClientService {

    /**
     * Creer un nouveau client
     */
    public static function create(array $data): int|false {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        $insert_data = [
            'uuid' => wp_generate_uuid4(),
            'name' => sanitize_text_field($data['name'] ?? ''),
            'address' => sanitize_text_field($data['address'] ?? ''),
            'postal_code' => sanitize_text_field($data['postal_code'] ?? ''),
            'city' => sanitize_text_field($data['city'] ?? ''),
            'country' => sanitize_text_field($data['country'] ?? 'France'),
            'siret' => preg_replace('/[^0-9]/', '', $data['siret'] ?? ''),
            'vat_number' => sanitize_text_field($data['vat_number'] ?? ''),
            'email' => sanitize_email($data['email'] ?? ''),
            'phone' => sanitize_text_field($data['phone'] ?? ''),
            'notes' => sanitize_textarea_field($data['notes'] ?? ''),
            'created_at' => current_time('mysql'),
            'created_by' => get_current_user_id(),
        ];

        if (empty($insert_data['name'])) {
            return false;
        }

        if (empty($insert_data['email']) || !is_email($insert_data['email'])) {
            return false;
        }

        $result = $wpdb->insert($table, $insert_data);

        if ($result === false) {
            return false;
        }

        return $wpdb->insert_id;
    }

    /**
     * Mettre a jour un client
     */
    public static function update(int $id, array $data): bool {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        if (!self::exists($id)) {
            return false;
        }

        $update_data = [];

        if (isset($data['name'])) {
            $update_data['name'] = sanitize_text_field($data['name']);
        }
        if (isset($data['address'])) {
            $update_data['address'] = sanitize_text_field($data['address']);
        }
        if (isset($data['postal_code'])) {
            $update_data['postal_code'] = sanitize_text_field($data['postal_code']);
        }
        if (isset($data['city'])) {
            $update_data['city'] = sanitize_text_field($data['city']);
        }
        if (isset($data['country'])) {
            $update_data['country'] = sanitize_text_field($data['country']);
        }
        if (isset($data['siret'])) {
            $update_data['siret'] = preg_replace('/[^0-9]/', '', $data['siret']);
        }
        if (isset($data['vat_number'])) {
            $update_data['vat_number'] = sanitize_text_field($data['vat_number']);
        }
        if (isset($data['email'])) {
            $email = sanitize_email($data['email']);
            if (is_email($email)) {
                $update_data['email'] = $email;
            }
        }
        if (isset($data['phone'])) {
            $update_data['phone'] = sanitize_text_field($data['phone']);
        }
        if (isset($data['notes'])) {
            $update_data['notes'] = sanitize_textarea_field($data['notes']);
        }

        if (empty($update_data)) {
            return false;
        }

        $result = $wpdb->update($table, $update_data, ['id' => $id]);

        return $result !== false;
    }

    /**
     * Supprimer un client
     */
    public static function delete(int $id): bool|string {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        if (!self::exists($id)) {
            return 'Client introuvable.';
        }

        if (self::hasInvoices($id)) {
            return 'Impossible de supprimer ce client car il a des factures associees.';
        }

        $result = $wpdb->delete($table, ['id' => $id]);

        return $result !== false;
    }

    /**
     * Obtenir un client par ID
     */
    public static function getById(int $id): ?array {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        $client = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ), ARRAY_A);

        return $client ?: null;
    }

    /**
     * Obtenir tous les clients
     */
    public static function getAll(array $args = []): array {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        $orderby = in_array($args['orderby'] ?? '', ['name', 'email', 'city', 'created_at'])
            ? $args['orderby']
            : 'name';
        $order = strtoupper($args['order'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';
        $limit = isset($args['limit']) ? absint($args['limit']) : 100;
        $offset = isset($args['offset']) ? absint($args['offset']) : 0;

        $sql = "SELECT * FROM {$table} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

        return $wpdb->get_results($wpdb->prepare($sql, $limit, $offset), ARRAY_A);
    }

    /**
     * Rechercher des clients
     */
    public static function search(string $search, int $limit = 20): array {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        $search = '%' . $wpdb->esc_like($search) . '%';

        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table}
             WHERE name LIKE %s
                OR email LIKE %s
                OR siret LIKE %s
                OR city LIKE %s
             ORDER BY name ASC
             LIMIT %d",
            $search, $search, $search, $search, $limit
        ), ARRAY_A);
    }

    /**
     * Compter le nombre total de clients
     */
    public static function count(): int {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
    }

    /**
     * Verifier si un client existe
     */
    public static function exists(int $id): bool {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        $count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE id = %d",
            $id
        ));

        return (int) $count > 0;
    }

    /**
     * Verifier si un client a des factures
     */
    public static function hasInvoices(int $client_id): bool {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_invoices';

        $count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE client_id = %d",
            $client_id
        ));

        return (int) $count > 0;
    }

    /**
     * Obtenir les statistiques d'un client
     */
    public static function getStats(int $client_id): array {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';

        $invoices_total = $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(total_ttc) FROM {$prefix}invoices WHERE client_id = %d AND status != 'CANCELLED'",
            $client_id
        ));

        $invoices_unpaid = $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(total_ttc) FROM {$prefix}invoices WHERE client_id = %d AND status IN ('PENDING', 'OVERDUE')",
            $client_id
        ));

        $invoices_count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$prefix}invoices WHERE client_id = %d AND status != 'CANCELLED'",
            $client_id
        ));

        return [
            'invoices_total' => (float) ($invoices_total ?? 0),
            'invoices_unpaid' => (float) ($invoices_unpaid ?? 0),
            'invoices_count' => (int) $invoices_count,
        ];
    }

    /**
     * Obtenir les clients pour un select dropdown
     */
    public static function getForDropdown(): array {
        global $wpdb;
        $table = $wpdb->prefix . 'cfp_clients';

        $clients = $wpdb->get_results(
            "SELECT id, name, city FROM {$table} ORDER BY name ASC",
            ARRAY_A
        );

        $result = [];
        foreach ($clients as $client) {
            $label = $client['name'];
            if (!empty($client['city'])) {
                $label .= ' (' . $client['city'] . ')';
            }
            $result[$client['id']] = $label;
        }

        return $result;
    }
}
