<?php
namespace DayTidy\Modules\Notifications;

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

class ReminderJobRepository
{
    private Db $db;

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

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

        $now = current_time('mysql', true);
        $record = [
            'notification_id' => absint($data['notification_id'] ?? 0),
            'booking_id' => absint($data['booking_id'] ?? 0),
            'trigger_key' => sanitize_key((string) ($data['trigger_key'] ?? Registry::TRIGGER_BOOKING_REMINDER_BEFORE_START)),
            'recipient_context' => sanitize_key((string) ($data['recipient_context'] ?? '')),
            'status' => ReminderJob::STATUS_PENDING,
            'due_at' => sanitize_text_field((string) ($data['due_at'] ?? $now)),
            'session_starts_at' => sanitize_text_field((string) ($data['session_starts_at'] ?? $now)),
            'attempts' => 0,
            'delivery_log_id' => null,
            'last_error' => '',
            'created_at' => $now,
            'updated_at' => $now,
        ];

        $sql = $this->db->prepare(
            "INSERT INTO `#__daytidy_notification_reminder_jobs`
                (`notification_id`, `booking_id`, `trigger_key`, `recipient_context`, `status`, `due_at`,
                 `session_starts_at`, `attempts`, `delivery_log_id`, `last_error`, `created_at`, `updated_at`)
             VALUES (%d, %d, %s, %s, %s, %s, %s, %d, NULL, %s, %s, %s)
             ON DUPLICATE KEY UPDATE `id` = `id` + LAST_INSERT_ID(0)",
            $record['notification_id'],
            $record['booking_id'],
            $record['trigger_key'],
            $record['recipient_context'],
            $record['status'],
            $record['due_at'],
            $record['session_starts_at'],
            $record['attempts'],
            $record['last_error'],
            $record['created_at'],
            $record['updated_at']
        );
        $inserted = $this->db->query($sql);
        if ($inserted === false) return 0;

        // A duplicate sets insert_id to zero even when CLIENT_FOUND_ROWS reports one affected row.
        $id = $this->db->insertId();
        if (!$id)
        {
            $existing = $this->findExisting($record['notification_id'], $record['booking_id'], $record['recipient_context']);
            if ($existing && (string) ($existing->status ?? '') === ReminderJob::STATUS_SENT && empty($data['refresh_sent'])) return 0;

            return $existing ? $this->refresh((int) $existing->id, $record) : 0;
        }

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

        return $id;
    }

    /**
     * @param mixed[] $record
     */
    private function refresh(int $id, array $record): int
    {
        $updated = $this->db->update(
            'daytidy_notification_reminder_jobs',
            [
                'trigger_key' => $record['trigger_key'],
                'status' => ReminderJob::STATUS_PENDING,
                'due_at' => $record['due_at'],
                'session_starts_at' => $record['session_starts_at'],
                'attempts' => 0,
                'delivery_log_id' => null,
                'last_error' => '',
                'updated_at' => $record['updated_at'],
            ],
            ['id' => $id]
        );

        return $updated !== false ? $id : 0;
    }

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

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

    /**
     * @return object|null
     */
    public function findExisting(int $notification_id, int $booking_id, string $context)
    {
        return $this->db->getRow($this->db->prepare(
            "SELECT * FROM `#__daytidy_notification_reminder_jobs`
             WHERE `notification_id` = %d AND `booking_id` = %d AND `recipient_context` = %s",
            $notification_id,
            $booking_id,
            sanitize_key($context)
        ));
    }

    /**
     * @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'] ?? 'due_at'));
        $order = strtolower((string) ($filters['order'] ?? 'asc')) === 'desc' ? 'DESC' : 'ASC';

        $sql = "SELECT * FROM `#__daytidy_notification_reminder_jobs` $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_reminder_jobs` $where";

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

    /**
     * @return object[]
     */
    public function due(int $limit = 20): array
    {
        return $this->db->getResults($this->db->prepare(
            "SELECT * FROM `#__daytidy_notification_reminder_jobs`
             WHERE `status` IN (%s, %s) AND `due_at` <= %s
             ORDER BY `due_at` ASC, `id` ASC
             LIMIT %d",
            ReminderJob::STATUS_PENDING,
            ReminderJob::STATUS_FAILED,
            current_time('mysql', true),
            max(1, min(100, $limit))
        ));
    }

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

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

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

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

        return $counts;
    }

    public function markProcessing(int $id): bool
    {
        return (bool) $this->db->update(
            'daytidy_notification_reminder_jobs',
            [
                'status' => ReminderJob::STATUS_PROCESSING,
                'attempts' => $this->attemptsSql($id),
                'updated_at' => current_time('mysql', true),
            ],
            ['id' => $id]
        );
    }

    public function finish(int $id, string $status, int $delivery_log_id = 0, string $error = ''): bool
    {
        if (!in_array($status, ReminderJob::statuses(), true)) $status = ReminderJob::STATUS_FAILED;

        return (bool) $this->db->update(
            'daytidy_notification_reminder_jobs',
            [
                'status' => $status,
                'delivery_log_id' => $delivery_log_id ?: null,
                'last_error' => sanitize_textarea_field($error),
                'updated_at' => current_time('mysql', true),
            ],
            ['id' => $id]
        );
    }

    public function settleByDeliveryLogId(int $delivery_log_id, int $replacement_log_id = 0): bool
    {
        if (!$delivery_log_id) return false;

        return (bool) $this->db->update(
            'daytidy_notification_reminder_jobs',
            [
                'status' => ReminderJob::STATUS_SENT,
                'delivery_log_id' => $replacement_log_id ?: $delivery_log_id,
                'last_error' => '',
                'updated_at' => current_time('mysql', true),
            ],
            ['delivery_log_id' => $delivery_log_id]
        );
    }

    public function settleByIdentity(int $notification_id, int $booking_id, string $recipient_context, int $replacement_log_id = 0): bool
    {
        if (!$notification_id || !$booking_id) return false;

        return (bool) $this->db->update(
            'daytidy_notification_reminder_jobs',
            [
                'status' => ReminderJob::STATUS_SENT,
                'delivery_log_id' => $replacement_log_id ?: null,
                'last_error' => '',
                'updated_at' => current_time('mysql', true),
            ],
            [
                'notification_id' => $notification_id,
                'booking_id' => $booking_id,
                'recipient_context' => sanitize_key($recipient_context),
            ]
        );
    }

    private function attemptsSql(int $id): int
    {
        return (int) $this->db->getVar($this->db->prepare(
            "SELECT `attempts` + 1 FROM `#__daytidy_notification_reminder_jobs` WHERE `id` = %d",
            $id
        ));
    }

    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 !== '';
    }

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

        $status = sanitize_key((string) ($filters['status'] ?? ''));
        if ($status !== '')
        {
            $where[] = '`status` = %s';
            $values[] = $status;
        }

        foreach (['notification_id', 'booking_id'] as $key)
        {
            $value = absint($filters[$key] ?? 0);
            if (!$value) continue;

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

        $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);
            }
        }

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

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

        return in_array($orderby, $allowed, true) ? $orderby : 'due_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)));
    }
}