<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class PmTemplate extends Model
{
    protected string $table = 'pm_templates';
    protected string $primaryKey = 'template_id';

    public static function listForTenant(int $tenantId): array
    {
        return DB::all(
            'SELECT t.*, at.name AS asset_type_name,
                    (SELECT COUNT(*) FROM pm_template_tasks tt WHERE tt.template_id = t.template_id) AS task_count,
                    (SELECT COUNT(*) FROM pm_schedules s WHERE s.template_id = t.template_id AND s.is_active = 1) AS active_schedules
               FROM pm_templates t
          LEFT JOIN asset_types at ON at.asset_type_id = t.asset_type_id
              WHERE t.tenant_id = :t AND t.deleted_at IS NULL
              ORDER BY t.name',
            ['t' => $tenantId]
        );
    }

    public static function findInTenant(int $tenantId, int $templateId): ?array
    {
        return DB::one(
            'SELECT * FROM pm_templates WHERE tenant_id = :t AND template_id = :id AND deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $templateId]
        );
    }

    public static function tasksFor(int $templateId): array
    {
        return DB::all(
            'SELECT * FROM pm_template_tasks WHERE template_id = :id ORDER BY sort_order',
            ['id' => $templateId]
        );
    }

    public static function create(int $tenantId, array $data): int
    {
        return (int) DB::insert('pm_templates', [
            'tenant_id'     => $tenantId,
            'asset_type_id' => !empty($data['asset_type_id']) ? (int) $data['asset_type_id'] : null,
            'code'          => $data['code']          ?? null,
            'name'          => $data['name'],
            'description'   => $data['description']   ?? null,
            'interval_days' => (int) ($data['interval_days'] ?? 30),
            'lead_days'     => (int) ($data['lead_days']     ?? 3),
            'priority'      => $data['priority']      ?? 'medium',
            'is_active'     => !empty($data['is_active']) ? 1 : 1,
        ]);
    }

    public const TRIGGER_TYPES = [
        'calendar'       => 'Calendar (every N days)',
        'running_hours'  => 'Running hours (from meter reading)',
        'reading_value'  => 'Reading value (any parameter)',
    ];

    /**
     * Calendar-based schedules due for generation. Meter-based schedules are
     * handled separately by meterSchedulesDue() — they can't be answered by
     * a date query alone.
     */
    public static function schedulesDueBefore(string $cutoffIso): array
    {
        return DB::all(
            'SELECT s.*, t.name AS template_name, t.interval_days, t.priority, t.description AS template_desc,
                    a.name AS asset_name
               FROM pm_schedules s
               JOIN pm_templates t ON t.template_id = s.template_id
               JOIN assets a       ON a.asset_id    = s.asset_id
              WHERE s.is_active = 1
                AND s.trigger_type = "calendar"
                AND s.next_due_at IS NOT NULL
                AND s.next_due_at <= :c
                AND t.deleted_at IS NULL
                AND a.deleted_at IS NULL',
            ['c' => $cutoffIso]
        );
    }

    /**
     * Meter-based schedules whose most-recent reading has crossed the
     * threshold. We correlate schedules with the latest plant_readings row
     * for the same (asset, parameter_key) tuple and compare against
     * last_meter_reading + meter_interval.
     */
    public static function meterSchedulesDue(): array
    {
        return DB::all(
            'SELECT s.*, t.name AS template_name, t.interval_days, t.priority, t.description AS template_desc,
                    a.name AS asset_name,
                    latest.value_number AS current_meter,
                    latest.taken_at    AS current_meter_at
               FROM pm_schedules s
               JOIN pm_templates t ON t.template_id = s.template_id
               JOIN assets a       ON a.asset_id    = s.asset_id
               JOIN (
                    /* one row per (asset, parameter_key) — the newest reading */
                    SELECT p1.asset_id, p1.parameter_key, p1.value_number, p1.taken_at
                      FROM plant_readings p1
                      JOIN (SELECT asset_id, parameter_key, MAX(taken_at) AS mx
                              FROM plant_readings
                             WHERE value_number IS NOT NULL
                             GROUP BY asset_id, parameter_key) p2
                        ON p2.asset_id = p1.asset_id
                       AND p2.parameter_key = p1.parameter_key
                       AND p2.mx = p1.taken_at
               ) latest
                    ON latest.asset_id      = s.asset_id
                   AND latest.parameter_key = s.meter_key
              WHERE s.is_active = 1
                AND s.trigger_type IN ("running_hours", "reading_value")
                AND s.meter_key IS NOT NULL
                AND s.meter_interval IS NOT NULL
                AND latest.value_number IS NOT NULL
                AND latest.value_number >= (COALESCE(s.last_meter_reading, 0) + s.meter_interval)
                AND t.deleted_at IS NULL
                AND a.deleted_at IS NULL'
        );
    }

    public static function advanceSchedule(int $scheduleId, int $intervalDays): void
    {
        DB::run(
            "UPDATE pm_schedules
                SET last_generated_at = NOW(),
                    next_due_at = DATE_ADD(COALESCE(next_due_at, NOW()), INTERVAL :d DAY)
              WHERE schedule_id = :id",
            ['d' => $intervalDays, 'id' => $scheduleId]
        );
    }

    /**
     * Meter-based advance: stamp last_meter_reading with the value that
     * triggered this generation, so the next threshold becomes
     * currentReading + meter_interval.
     */
    public static function advanceMeterSchedule(int $scheduleId, float $currentReading): void
    {
        DB::run(
            "UPDATE pm_schedules
                SET last_generated_at   = NOW(),
                    last_meter_reading  = :r
              WHERE schedule_id = :id",
            ['r' => $currentReading, 'id' => $scheduleId]
        );
    }

    public static function update(int $tenantId, int $templateId, array $data): void
    {
        $allowed = ['asset_type_id','code','name','description','interval_days','lead_days','priority','is_active'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $templateId];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if ($col === 'asset_type_id') { $val = !empty($val) ? (int) $val : null; }
            if (in_array($col, ['interval_days','lead_days'], true)) { $val = (int) $val; }
            if ($col === 'is_active') { $val = !empty($val) ? 1 : 0; }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE pm_templates SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND template_id = :id',
            $params
        );
    }

    public static function softDelete(int $tenantId, int $templateId): void
    {
        DB::run(
            'UPDATE pm_templates SET deleted_at = NOW() WHERE tenant_id = :t AND template_id = :id',
            ['t' => $tenantId, 'id' => $templateId]
        );
    }

    public const TASK_INPUT_TYPES        = ['checkbox','options','number','text','photo'];
    public const TASK_PHOTO_REQUIREMENTS = ['n_a', 'optional', 'required'];

    /** Normalise the options field (comma-string / JSON string / array all accepted). */
    public static function normaliseOptions(mixed $raw): ?string
    {
        if ($raw === null || $raw === '' || $raw === []) return null;
        if (is_array($raw)) return json_encode(array_values($raw));
        $decoded = json_decode((string) $raw, true);
        if (is_array($decoded)) return json_encode(array_values($decoded));
        return json_encode(array_map('trim', explode(',', (string) $raw)));
    }

    public static function addTask(int $templateId, array $data): int
    {
        $inputType = in_array($data['input_type'] ?? '', self::TASK_INPUT_TYPES, true)
            ? $data['input_type'] : 'checkbox';
        $photoReq  = in_array($data['photo_requirement'] ?? '', self::TASK_PHOTO_REQUIREMENTS, true)
            ? $data['photo_requirement'] : 'n_a';
        $optionsJson = $inputType === 'options' ? self::normaliseOptions($data['options_json'] ?? null) : null;

        return (int) DB::insert('pm_template_tasks', [
            'template_id'       => $templateId,
            'sort_order'        => (int) ($data['sort_order'] ?? 0),
            'title'             => $data['title'],
            'description'       => $data['description'] ?? null,
            'input_type'        => $inputType,
            'options_json'      => $optionsJson,
            'is_required'       => !empty($data['is_required']) ? 1 : 0,
            'photo_requirement' => $photoReq,
        ]);
    }

    public static function findTask(int $templateTaskId): ?array
    {
        return DB::one(
            'SELECT * FROM pm_template_tasks WHERE template_task_id = :id',
            ['id' => $templateTaskId]
        );
    }

    public static function updateTask(int $templateTaskId, array $data): void
    {
        $current = self::findTask($templateTaskId);
        if (!$current) return;
        $inputType = in_array($data['input_type'] ?? '', self::TASK_INPUT_TYPES, true)
            ? $data['input_type'] : $current['input_type'];
        $photoReq  = in_array($data['photo_requirement'] ?? '', self::TASK_PHOTO_REQUIREMENTS, true)
            ? $data['photo_requirement'] : $current['photo_requirement'];
        $optionsJson = $inputType === 'options' ? self::normaliseOptions($data['options_json'] ?? null) : null;

        DB::run(
            "UPDATE pm_template_tasks
                SET title             = :title,
                    description       = :description,
                    input_type        = :input_type,
                    options_json      = :options_json,
                    is_required       = :is_required,
                    photo_requirement = :photo_requirement,
                    sort_order        = :sort_order
              WHERE template_task_id  = :id",
            [
                'title'             => $data['title'] ?? $current['title'],
                'description'       => $data['description'] ?? null,
                'input_type'        => $inputType,
                'options_json'      => $optionsJson,
                'is_required'       => !empty($data['is_required']) ? 1 : 0,
                'photo_requirement' => $photoReq,
                'sort_order'        => (int) ($data['sort_order'] ?? $current['sort_order']),
                'id'                => $templateTaskId,
            ]
        );
    }

    /**
     * Move a task up or down within its template. Swaps sort_order with the
     * neighbour so the display order updates and stays contiguous.
     */
    public static function moveTask(int $templateTaskId, string $direction): void
    {
        if (!in_array($direction, ['up', 'down'], true)) return;
        $t = self::findTask($templateTaskId);
        if (!$t) return;
        $op = $direction === 'up' ? '<' : '>';
        $orderDir = $direction === 'up' ? 'DESC' : 'ASC';
        $neighbour = DB::one(
            "SELECT * FROM pm_template_tasks
              WHERE template_id = :tpl AND sort_order {$op} :so
              ORDER BY sort_order {$orderDir} LIMIT 1",
            ['tpl' => (int) $t['template_id'], 'so' => (int) $t['sort_order']]
        );
        if (!$neighbour) return;

        DB::tx(function () use ($t, $neighbour) {
            DB::run('UPDATE pm_template_tasks SET sort_order = :so WHERE template_task_id = :id',
                ['so' => (int) $neighbour['sort_order'], 'id' => (int) $t['template_task_id']]);
            DB::run('UPDATE pm_template_tasks SET sort_order = :so WHERE template_task_id = :id',
                ['so' => (int) $t['sort_order'], 'id' => (int) $neighbour['template_task_id']]);
        });
    }

    public static function deleteTask(int $templateTaskId): void
    {
        DB::run('DELETE FROM pm_template_tasks WHERE template_task_id = :id', ['id' => $templateTaskId]);
    }

    public static function taskInTenant(int $tenantId, int $templateTaskId): ?array
    {
        return DB::one(
            'SELECT tt.* FROM pm_template_tasks tt
               JOIN pm_templates t ON t.template_id = tt.template_id
              WHERE tt.template_task_id = :id AND t.tenant_id = :t',
            ['id' => $templateTaskId, 't' => $tenantId]
        );
    }

    public static function schedulesForTemplate(int $tenantId, int $templateId): array
    {
        return DB::all(
            'SELECT s.*, a.name AS asset_name, u.username AS assigned_username
               FROM pm_schedules s
               JOIN assets a ON a.asset_id = s.asset_id
          LEFT JOIN users  u ON u.user_id = s.assigned_to
              WHERE s.tenant_id = :t AND s.template_id = :id
              ORDER BY s.next_due_at IS NULL, s.next_due_at',
            ['t' => $tenantId, 'id' => $templateId]
        );
    }

    public static function addSchedule(int $tenantId, int $templateId, array $data): int
    {
        $trigger = array_key_exists($data['trigger_type'] ?? '', self::TRIGGER_TYPES)
            ? (string) $data['trigger_type'] : 'calendar';
        // For 'running_hours' default the meter_key so the operator doesn't
        // have to type it — matches the plant_readings convention.
        $meterKey = $data['meter_key'] ?? null;
        if ($trigger === 'running_hours' && !$meterKey) $meterKey = 'running_hours';

        return (int) DB::insert('pm_schedules', [
            'tenant_id'           => $tenantId,
            'template_id'         => $templateId,
            'asset_id'            => (int) $data['asset_id'],
            'assigned_to'         => !empty($data['assigned_to']) ? (int) $data['assigned_to'] : null,
            'next_due_at'         => !empty($data['next_due_at']) ? str_replace('T', ' ', $data['next_due_at']) : null,
            'is_active'           => 1,
            'trigger_type'        => $trigger,
            'meter_key'           => $trigger !== 'calendar' ? $meterKey : null,
            'meter_interval'      => $trigger !== 'calendar' && $data['meter_interval'] !== '' ? (float) $data['meter_interval'] : null,
            'last_meter_reading'  => $trigger !== 'calendar' && ($data['last_meter_reading'] ?? '') !== ''
                ? (float) $data['last_meter_reading'] : null,
        ]);
    }

    public static function deleteSchedule(int $tenantId, int $scheduleId): void
    {
        DB::run(
            'DELETE FROM pm_schedules WHERE tenant_id = :t AND schedule_id = :id',
            ['t' => $tenantId, 'id' => $scheduleId]
        );
    }
}
