<?php

namespace Modules\Notification\Models;

use CodeIgniter\Model;

class NotificationModel extends Model
{
    protected $table      = 'notifications';
    protected $primaryKey = 'id';

    protected $allowedFields = [
        'subject',
        'message',
        'notification_type',
        'target_group',
        'trip_id',
        'notification_date',
        'journey_date',
        'repeat_option',
        'status',
        'is_push_notification',
        'created_by',
        'created_at',
        'updated_at',
        'deleted_at'
    ];

    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    /**
     * Get notifications by status
     */
    public function getByStatus($status, int $limit = 500)
    {
        return $this->select(
            'notifications.id, notifications.subject, notifications.target_group, '
            . 'notifications.notification_date, notifications.status, notifications.notification_type, '
            . 'notifications.is_push_notification, notifications.created_at, notifications.message, '
            . 'notifications.repeat_option, notifications.trip_id'
        )
            ->where('status', $status)
            ->orderBy('notifications.created_at', 'DESC')
            ->limit($limit)
            ->findAll();
    }

    /**
     * Get scheduled notifications that are ready to be sent
     */
    public function getScheduledForSending()
    {
        return $this->where('status', 'scheduled')
                    ->where('notification_date <=', date('Y-m-d H:i:s'))
                    ->findAll();
    }

    /**
     * Get notifications with trip details
     */
    public function getWithTripDetails()
    {
        return $this->select('notifications.*, trips.trip_name, trips.departure_time, trips.departure_date')
                    ->join('trips', 'trips.id = notifications.trip_id', 'left')
                    ->findAll();
    }

    /**
     * Notification list for admin index (bounded + only columns needed for the table).
     */
    public function getIndexList(int $limit = 500): array
    {
        return $this->select(
            'notifications.id, notifications.subject, notifications.target_group, '
            . 'notifications.notification_date, notifications.status, notifications.notification_type, '
            . 'notifications.is_push_notification, notifications.created_at, '
            . 'CONCAT(user_details.first_name, " ", user_details.last_name) AS created_by_name, '
            . 'companies.name AS trip_name'
        )
            ->join('users', 'users.id = notifications.created_by', 'left')
            ->join('user_details', 'user_details.user_id = users.id', 'left')
            ->join('trips', 'trips.id = notifications.trip_id', 'left')
            ->join('companies', 'companies.id = trips.company_id', 'left')
            ->orderBy('notifications.created_at', 'DESC')
            ->limit($limit)
            ->findAll();
    }

    /**
     * Single-query status counts (avoids 5 full table scans).
     */
    public function getStatistics(): array
    {
        $row = $this->builder()
            ->select(
                'COUNT(*) AS total, '
                . "SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) AS sent, "
                . "SUM(CASE WHEN status = 'scheduled' THEN 1 ELSE 0 END) AS scheduled, "
                . "SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled, "
                . "SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) AS draft",
                false
            )
            ->get()
            ->getRowArray();

        return [
            'total'     => (int) ($row['total'] ?? 0),
            'sent'      => (int) ($row['sent'] ?? 0),
            'scheduled' => (int) ($row['scheduled'] ?? 0),
            'cancelled' => (int) ($row['cancelled'] ?? 0),
            'draft'     => (int) ($row['draft'] ?? 0),
        ];
    }
}
