<?php
namespace DayTidy\Modules\Notifications;

use DayTidy\Db;
use DayTidy\Db\CreateTables;
use DayTidy\Posts\Booking as BookingPost;

class DeliveryLogRepository
{
    private Db $db;

    public function __construct(?Db $db = null)
    {
        $this->db = $db ?: new Db();
    }

    /**
     * @param mixed[] $data
     */
    public function insert(array $data): int
    {
        CreateTables::ensureSchema();
        if (!$this->tableExists('daytidy_notification_delivery_logs')) CreateTables::run();

        $now = current_time('mysql', true);
        $status = sanitize_key((string) ($data['status'] ?? DeliveryLog::STATUS_SKIPPED));
        if (!in_array($status, DeliveryLog::statuses(), true)) $status = DeliveryLog::STATUS_SKIPPED;

        $source = sanitize_key((string) ($data['source'] ?? DeliveryLog::SOURCE_EVENT));
        if (!in_array($source, DeliveryLog::sources(), true)) $source = DeliveryLog::SOURCE_EVENT;

        $record = [
            'notification_id' => absint($data['notification_id'] ?? 0),
            'booking_id' => absint($data['booking_id'] ?? 0),
            'trigger_key' => sanitize_key((string) ($data['trigger_key'] ?? '')),
            'recipient_context' => sanitize_key((string) ($data['recipient_context'] ?? '')),
            'recipient_email' => sanitize_email((string) ($data['recipient_email'] ?? '')),
            'recipient_name' => sanitize_text_field((string) ($data['recipient_name'] ?? '')),
            'recipient_snapshot' => isset($data['recipient_snapshot']) ? wp_json_encode($data['recipient_snapshot']) : null,
            'subject' => sanitize_text_field((string) ($data['subject'] ?? '')),
            'body' => wp_kses_post((string) ($data['body'] ?? '')),
            'status' => $status,
            'source' => $source,
            'attempt' => max(1, absint($data['attempt'] ?? 1)),
            'parent_log_id' => !empty($data['parent_log_id']) ? absint($data['parent_log_id']) : null,
            'error_message' => sanitize_textarea_field((string) ($data['error_message'] ?? '')),
            'created_at' => (string) ($data['created_at'] ?? $now),
            'updated_at' => (string) ($data['updated_at'] ?? $now),
        ];

        $record = (array) apply_filters(
            'DayTidy/F__DayTidy/Notifications/DeliveryLogRepository__insert/data',
            $record,
            $data
        );

        $inserted = $this->db->insert('daytidy_notification_delivery_logs', $record);
        if (!$inserted) return 0;

        $id = $this->db->insertId();

        do_action('DayTidy/A__DayTidy/Notifications/DeliveryLog__created', $id, $record);

        return $id;
    }

    /**
     * @return object|null
     */
    public function find(int $id)
    {
        if (!$id) return null;

        return $this->db->getRow($this->db->prepare(
            "SELECT * FROM `#__daytidy_notification_delivery_logs` WHERE `id` = %d",
            $id
        ));
    }

    /**
     * @param mixed[] $filters
     * @return object[]
     */
    public function query(array $filters = []): array
    {
        [$where, $values] = $this->where($filters);
        $limit = max(1, min(200, absint($filters['limit'] ?? 20)));
        $offset = max(0, absint($filters['offset'] ?? 0));
        $orderby = $this->orderby((string) ($filters['orderby'] ?? 'created_at'));
        $order = strtolower((string) ($filters['order'] ?? 'desc')) === 'asc' ? 'ASC' : 'DESC';

        $sql = "SELECT * FROM `#__daytidy_notification_delivery_logs` $where ORDER BY `$orderby` $order, `id` $order LIMIT %d OFFSET %d";
        $values[] = $limit;
        $values[] = $offset;

        return $this->db->getResults($this->db->prepare($sql, $values));
    }

    /**
     * @param mixed[] $filters
     */
    public function count(array $filters = []): int
    {
        [$where, $values] = $this->where($filters);
        $sql = "SELECT COUNT(*) FROM `#__daytidy_notification_delivery_logs` $where";

        return (int) $this->db->getVar(count($values) ? $this->db->prepare($sql, $values) : $sql);
    }

    /**
     * @return array<string, int>
     */
    public function statusCounts(): array
    {
        $counts = [
            DeliveryLog::STATUS_SENT => 0,
            DeliveryLog::STATUS_FAILED => 0,
            DeliveryLog::STATUS_SKIPPED => 0,
        ];

        $rows = $this->db->getResults(
            "SELECT `status`, COUNT(*) AS total FROM `#__daytidy_notification_delivery_logs` GROUP BY `status`"
        );

        foreach ($rows as $row)
        {
            $status = (string) ($row->status ?? '');
            if (!isset($counts[$status])) continue;

            $counts[$status] = (int) ($row->total ?? 0);
        }

        return $counts;
    }

    /**
     * @param mixed[] $filters
     * @return array{0: string, 1: mixed[]}
     */
    private function where(array $filters): array
    {
        $where = [];
        $values = [];

        foreach (['status', 'source'] as $key)
        {
            $value = sanitize_key((string) ($filters[$key] ?? ''));
            if ($value === '') continue;

            $where[] = "`$key` = %s";
            $values[] = $value;
        }

        $exclude_status = array_filter(array_map('sanitize_key', (array) ($filters['exclude_status'] ?? [])));
        if ($exclude_status)
        {
            $placeholders = implode(', ', array_fill(0, count($exclude_status), '%s'));
            $where[] = "`status` NOT IN ($placeholders)";
            array_push($values, ...array_values($exclude_status));
        }

        $booking_ref = sanitize_text_field((string) ($filters['booking_ref'] ?? ''));
        if ($booking_ref !== '')
        {
            $booking_ids = $this->bookingIds($booking_ref);
            if (!$booking_ids)
            {
                $where[] = '0 = 1';
            }
            else
            {
                $placeholders = implode(', ', array_fill(0, count($booking_ids), '%d'));
                $where[] = "`booking_id` IN ($placeholders)";
                array_push($values, ...$booking_ids);
            }
        }

        $notification_id = absint($filters['notification_id'] ?? 0);
        if ($notification_id)
        {
            $where[] = '`notification_id` = %d';
            $values[] = $notification_id;
        }

        $recipient = sanitize_text_field((string) ($filters['recipient'] ?? ''));
        if ($recipient !== '')
        {
            $where[] = '`recipient_email` LIKE %s';
            $values[] = '%' . $this->db->escLike($recipient) . '%';
        }

        $date = preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) ($filters['date'] ?? ''))
            ? (string) $filters['date']
            : '';
        if ($date !== '')
        {
            $where[] = '`created_at` >= %s AND `created_at` < %s';
            $values[] = $date . ' 00:00:00';
            $values[] = gmdate('Y-m-d H:i:s', strtotime($date . ' 00:00:00 UTC') + DAY_IN_SECONDS);
        }

        return [
            count($where) ? 'WHERE ' . implode(' AND ', $where) : '',
            $values,
        ];
    }

    private function orderby(string $orderby): string
    {
        $allowed = [
            'id',
            'notification_id',
            'booking_id',
            'recipient_email',
            'status',
            'source',
            'created_at',
        ];

        return in_array($orderby, $allowed, true) ? $orderby : 'created_at';
    }

    /**
     * @return int[]
     */
    private function bookingIds(string $search): array
    {
        $ids = [];
        if (is_numeric($search)) $ids[] = absint($search);

        $ref_ids = $this->db->getCol($this->db->prepare(
            "SELECT `p`.`ID`
            FROM `#__posts` `p`
            INNER JOIN `#__postmeta` `pm`
                ON `pm`.`post_id` = `p`.`ID`
                AND `pm`.`meta_key` = %s
                AND `pm`.`meta_value` = %s
            WHERE `p`.`post_type` = %s",
            'daytidy_ref',
            $search,
            BookingPost::PT
        ));

        foreach ($ref_ids as $ref_id) $ids[] = absint($ref_id);

        return array_values(array_filter(array_unique($ids)));
    }

    private function tableExists(string $table): bool
    {
        $existing = $this->db->getVar($this->db->prepare(
            'SHOW TABLES LIKE %s',
            $this->db->table($table)
        ));

        return is_string($existing) && $existing !== '';
    }
}