<?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, "
                . 'SUM(CASE WHEN created_by > 0 THEN 1 ELSE 0 END) AS admin_created, '
                . 'SUM(CASE WHEN created_by = 0 THEN 1 ELSE 0 END) AS trip_reminders',
                false
            )
            ->where('deleted_at IS NULL', null, 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),
            'admin_created'  => (int) ($row['admin_created'] ?? 0),
            'trip_reminders' => (int) ($row['trip_reminders'] ?? 0),
        ];
    }

    /**
     * Apply admin list scope filter to a query builder.
     */
    public static function applyScopeFilter($builder, string $scope): void
    {
        if ($scope === 'admin') {
            $builder->where('notifications.created_by >', 0);
        } elseif ($scope === 'trip_reminders') {
            $builder->where('notifications.created_by', 0);
        }
    }

    /**
     * Apply status filter to a query builder.
     */
    public static function applyStatusFilter($builder, string $status): void
    {
        if ($status !== 'all' && in_array($status, ['sent', 'scheduled', 'cancelled', 'draft'], true)) {
            $builder->where('notifications.status', $status);
        }
    }

    /**
     * Base admin list query (no message column — keeps rows small).
     */
    public static function indexListSelect($builder): void
    {
        $builder->select(
            'notifications.id, notifications.subject, notifications.target_group, '
            . 'notifications.notification_date, notifications.status, notifications.notification_type, '
            . 'notifications.is_push_notification, notifications.created_at, notifications.created_by, '
            . 'CONCAT(user_details.first_name, " ", user_details.last_name) AS created_by_name, '
            . 'companies.name AS trip_name',
            false
        )
            ->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')
            ->where('notifications.deleted_at IS NULL', null, false);
    }
}
