<?php

namespace PloverCRM\Modules\Reminders\Services;

use PloverCRM\Common\CrmUserManager;
use PloverCRM\Core\Plugin;
use PloverCRM\Modules\Reminders\Database\Schema;

/**
 * Reminder Manager Service
 */
class ReminderManager
{
    const STATUS_PENDING = 'pending';
    const STATUS_COMPLETED = 'completed';

    public static function getByCrmUserId(int $crmUserId, array $args = []): array
    {
        global $wpdb;
        $table = Schema::getTableName();

        $status = $args['status'] ?? self::STATUS_PENDING;
        $contactId = $args['contact_id'] ?? null;
        $from = $args['from'] ?? null;
        $to = $args['to'] ?? null;
        $page = max(1, (int) ($args['page'] ?? 1));
        $perPage = min(100, max(1, (int) ($args['per_page'] ?? 20)));
        $offset = ($page - 1) * $perPage;

        $where = ['crm_user_id = %d'];
        $params = [$crmUserId];

        if ($status !== 'all') {
            $where[] = 'status = %s';
            $params[] = $status;
        }

        if ($contactId !== null) {
            $where[] = 'contact_id = %d';
            $params[] = (int) $contactId;
        }

        if ($from !== null) {
            $where[] = 'scheduled_at >= %s';
            $params[] = $from;
        }
        if ($to !== null) {
            $where[] = 'scheduled_at <= %s';
            $params[] = $to;
        }

        $whereClause = implode(' AND ', $where);

        $countQuery = $wpdb->prepare(
            "SELECT COUNT(*) FROM $table WHERE $whereClause",
            ...$params
        );
        $total = (int) $wpdb->get_var($countQuery);

        $query = $wpdb->prepare(
            "SELECT * FROM $table WHERE $whereClause ORDER BY scheduled_at ASC LIMIT %d OFFSET %d",
            ...array_merge($params, [$perPage, $offset])
        );
        $results = $wpdb->get_results($query, ARRAY_A);

        $reminders = array_map([self::class, 'enrichWithContact'], $results ?: []);

        return [
            'reminders' => $reminders,
            'total' => $total,
        ];
    }

    public static function getById(int $id, ?int $crmUserId = null): ?array
    {
        global $wpdb;
        $table = Schema::getTableName();

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

        if (!$result) {
            return null;
        }

        if ($crmUserId !== null && (int) $result['crm_user_id'] !== $crmUserId) {
            return null;
        }

        return self::enrichWithContact($result);
    }

    public static function create(array $data): ?array
    {
        global $wpdb;
        $table = Schema::getTableName();

        $now = current_time('mysql', true);

        $insertData = [
            'contact_id' => (int) $data['contact_id'],
            'crm_user_id' => (int) $data['crm_user_id'],
            'title' => isset($data['title']) ? sanitize_text_field($data['title']) : null,
            'note' => isset($data['note']) ? sanitize_textarea_field($data['note']) : null,
            'scheduled_at' => $data['scheduled_at'],
            'status' => self::STATUS_PENDING,
            'created_at' => $now,
            'updated_at' => $now,
        ];

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

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

        return self::getById($wpdb->insert_id);
    }

    public static function update(int $id, array $data, ?int $crmUserId = null): ?array
    {
        global $wpdb;
        $table = Schema::getTableName();

        $existing = self::getById($id, $crmUserId);
        if (!$existing) {
            return null;
        }

        $updateData = [
            'updated_at' => current_time('mysql', true),
        ];

        if (array_key_exists('title', $data)) {
            $updateData['title'] = $data['title'] !== null ? sanitize_text_field($data['title']) : null;
        }
        if (array_key_exists('note', $data)) {
            $updateData['note'] = $data['note'] !== null ? sanitize_textarea_field($data['note']) : null;
        }
        if (isset($data['scheduled_at'])) {
            $updateData['scheduled_at'] = $data['scheduled_at'];
        }
        if (isset($data['status'])) {
            $updateData['status'] = in_array($data['status'], [self::STATUS_PENDING, self::STATUS_COMPLETED])
                ? $data['status']
                : self::STATUS_PENDING;

            if ($data['status'] === self::STATUS_COMPLETED) {
                $updateData['completed_at'] = current_time('mysql', true);
            } else {
                $updateData['completed_at'] = null;
            }
        }

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

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

        return self::getById($id);
    }

    public static function delete(int $id, ?int $crmUserId = null): bool
    {
        global $wpdb;
        $table = Schema::getTableName();

        $existing = self::getById($id, $crmUserId);
        if (!$existing) {
            return false;
        }

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

        return $result !== false;
    }

    public static function deleteByContactId(int $contactId): int
    {
        global $wpdb;
        $table = Schema::getTableName();

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

        return $result !== false ? $result : 0;
    }

    public static function getCompletedHistory(int $limit = 100): array
    {
        global $wpdb;
        $table = Schema::getTableName();

        $query = $wpdb->prepare(
            "SELECT * FROM $table WHERE status = %s ORDER BY completed_at DESC LIMIT %d",
            self::STATUS_COMPLETED,
            $limit
        );

        $results = $wpdb->get_results($query, ARRAY_A);

        return array_map([self::class, 'enrichWithContactAndUser'], $results ?: []);
    }

    public static function clearCompletedHistory(): int
    {
        global $wpdb;
        $table = Schema::getTableName();

        $result = $wpdb->delete($table, ['status' => self::STATUS_COMPLETED]);

        return $result !== false ? $result : 0;
    }

    private static function enrichWithContactAndUser(array $reminder): array
    {
        $reminder = self::enrichWithContact($reminder);

        $user = CrmUserManager::getPublicProfileById((int) $reminder['crm_user_id']);
        $reminder['user'] = $user ? [
            'id' => (int) $user['id'],
            'crm_user_id' => (int) $user['id'],
            'wp_user_id' => (int) $user['wp_user_id'],
            'display_name' => $user['display_name'],
            'username' => $user['username'],
            'email' => $user['email'],
        ] : null;

        return $reminder;
    }

    private static function enrichWithContact(array $reminder): array
    {
        $crm = Plugin::getInstance()->getContainer()->get('crm');

        $contact = null;
        if ($crm->isAvailable()) {
            $contactData = $crm->getContact((int) $reminder['contact_id']);
            if ($contactData) {
                $contact = [
                    'id' => $contactData['id'],
                    'first_name' => $contactData['first_name'] ?? '',
                    'last_name' => $contactData['last_name'] ?? '',
                    'email' => $contactData['email'] ?? '',
                    'phone' => $contactData['phone'] ?? '',
                ];
            }
        }

        $reminder['contact'] = $contact;
        $reminder['id'] = (int) $reminder['id'];
        $reminder['contact_id'] = (int) $reminder['contact_id'];
        $reminder['crm_user_id'] = (int) $reminder['crm_user_id'];

        return $reminder;
    }
}
