<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

/**
 * Operator Rounds. A round_template is a route through a set of assets;
 * a round_run is one execution of that route, and round_run_stops hold the
 * per-asset check + reading collected during the walk.
 */
final class Round extends Model
{
    protected string $table = 'round_templates';
    protected string $primaryKey = 'round_template_id';

    public const FREQUENCIES = ['daily', 'shift', 'weekly', 'ad_hoc'];
    public const RUN_STATUSES = ['in_progress', 'completed', 'abandoned'];

    // ── Templates ────────────────────────────────────────────────────────
    public static function templatesForTenant(int $tenantId): array
    {
        return DB::all(
            'SELECT rt.*,
                    (SELECT COUNT(*) FROM round_template_stops s WHERE s.round_template_id = rt.round_template_id) AS stop_count,
                    (SELECT MAX(rr.started_at) FROM round_runs rr WHERE rr.round_template_id = rt.round_template_id) AS last_run_at
               FROM round_templates rt
              WHERE rt.tenant_id = :t AND rt.deleted_at IS NULL
              ORDER BY rt.name',
            ['t' => $tenantId]
        );
    }

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

    public static function createTemplate(int $tenantId, array $data): int
    {
        return (int) DB::insert('round_templates', [
            'tenant_id'   => $tenantId,
            'name'        => $data['name'],
            'description' => $data['description'] ?? null,
            'frequency'   => in_array($data['frequency'] ?? '', self::FREQUENCIES, true) ? $data['frequency'] : 'daily',
            'is_active'   => !empty($data['is_active']) ? 1 : 0,
        ]);
    }

    public static function updateTemplate(int $tenantId, int $templateId, array $data): void
    {
        DB::run(
            'UPDATE round_templates
                SET name = :n, description = :d, frequency = :f, is_active = :a
              WHERE tenant_id = :t AND round_template_id = :id',
            [
                'n'  => $data['name'],
                'd'  => $data['description'] ?? null,
                'f'  => in_array($data['frequency'] ?? '', self::FREQUENCIES, true) ? $data['frequency'] : 'daily',
                'a'  => !empty($data['is_active']) ? 1 : 0,
                't'  => $tenantId,
                'id' => $templateId,
            ]
        );
    }

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

    // ── Stops ────────────────────────────────────────────────────────────
    public static function stopsFor(int $templateId): array
    {
        return DB::all(
            'SELECT s.*, a.name AS asset_name, at.name AS asset_type_name
               FROM round_template_stops s
               JOIN assets a         ON a.asset_id       = s.asset_id
          LEFT JOIN asset_types at   ON at.asset_type_id = a.asset_type_id
              WHERE s.round_template_id = :id
              ORDER BY s.sort_order, s.stop_id',
            ['id' => $templateId]
        );
    }

    public static function addStop(int $templateId, array $data): int
    {
        $nextOrder = (int) DB::value(
            'SELECT COALESCE(MAX(sort_order), 0) + 1 FROM round_template_stops WHERE round_template_id = :id',
            ['id' => $templateId]
        );
        return (int) DB::insert('round_template_stops', [
            'round_template_id' => $templateId,
            'asset_id'          => (int) $data['asset_id'],
            'sort_order'        => (int) ($data['sort_order'] ?? $nextOrder),
            'reading_key'       => $data['reading_key']  ?? null,
            'reading_unit'      => $data['reading_unit'] ?? null,
            'min_ok'            => ($data['min_ok'] ?? '') !== '' ? $data['min_ok'] : null,
            'max_ok'            => ($data['max_ok'] ?? '') !== '' ? $data['max_ok'] : null,
            'instruction'       => $data['instruction'] ?? null,
        ]);
    }

    public static function deleteStop(int $stopId): void
    {
        DB::run('DELETE FROM round_template_stops WHERE stop_id = :id', ['id' => $stopId]);
    }

    // ── Runs ─────────────────────────────────────────────────────────────
    public static function startRun(int $tenantId, int $templateId, int $operatorId): int
    {
        $runId = (int) DB::insert('round_runs', [
            'tenant_id'         => $tenantId,
            'round_template_id' => $templateId,
            'operator_id'       => $operatorId,
            'started_at'        => date('Y-m-d H:i:s'),
            'status'            => 'in_progress',
        ]);
        // Pre-populate one run_stop per template stop so the operator has a
        // fixed list to walk down; each row's `checked_at` starts NULL.
        foreach (self::stopsFor($templateId) as $s) {
            DB::insert('round_run_stops', [
                'run_id'   => $runId,
                'stop_id'  => (int) $s['stop_id'],
                'asset_id' => (int) $s['asset_id'],
            ]);
        }
        return $runId;
    }

    public static function findRun(int $tenantId, int $runId): ?array
    {
        return DB::one(
            'SELECT rr.*, rt.name AS template_name, u.username AS operator_username
               FROM round_runs rr
               JOIN round_templates rt ON rt.round_template_id = rr.round_template_id
          LEFT JOIN users u             ON u.user_id           = rr.operator_id
              WHERE rr.tenant_id = :t AND rr.run_id = :id',
            ['t' => $tenantId, 'id' => $runId]
        );
    }

    public static function runStopsFor(int $runId): array
    {
        // photo_count is a per-row subquery — cheap on the small stop-count
        // per run, and keeps the mobile from having to make a second call to
        // /attachments/list for every stop.
        return DB::all(
            'SELECT rrs.*, s.reading_key, s.reading_unit, s.min_ok, s.max_ok, s.instruction, s.sort_order,
                    a.name AS asset_name,
                    (SELECT COUNT(*) FROM attachments att
                       WHERE att.owner_type = "round_run_stop"
                         AND att.owner_id   = rrs.run_stop_id
                         AND att.deleted_at IS NULL) AS photo_count
               FROM round_run_stops rrs
               JOIN round_template_stops s ON s.stop_id  = rrs.stop_id
               JOIN assets a               ON a.asset_id = rrs.asset_id
              WHERE rrs.run_id = :id
              ORDER BY s.sort_order, rrs.run_stop_id',
            ['id' => $runId]
        );
    }

    public static function checkOffStop(int $runStopId, array $data): void
    {
        $reading = $data['reading_value'];
        $reading = ($reading === '' || $reading === null) ? null : (float) $reading;

        // Auto-flag out-of-range readings as not-ok if the stop has thresholds.
        $meta = DB::one(
            'SELECT s.min_ok, s.max_ok FROM round_run_stops rrs
               JOIN round_template_stops s ON s.stop_id = rrs.stop_id
              WHERE rrs.run_stop_id = :id',
            ['id' => $runStopId]
        );
        $isOk = array_key_exists('is_ok', $data) ? !empty($data['is_ok']) : true;
        if ($reading !== null && $meta) {
            if ($meta['min_ok'] !== null && $reading < (float) $meta['min_ok']) $isOk = false;
            if ($meta['max_ok'] !== null && $reading > (float) $meta['max_ok']) $isOk = false;
        }

        DB::run(
            'UPDATE round_run_stops
                SET reading_value = :rv, reading_text = :rt, is_ok = :ok, note = :n, checked_at = NOW()
              WHERE run_stop_id = :id',
            [
                'rv' => $reading,
                'rt' => $data['reading_text'] ?? null,
                'ok' => $isOk ? 1 : 0,
                'n'  => $data['note'] ?? null,
                'id' => $runStopId,
            ]
        );
    }

    public static function completeRun(int $tenantId, int $runId, ?string $notes = null): void
    {
        DB::run(
            'UPDATE round_runs
                SET status = "completed", finished_at = NOW(), notes = :n
              WHERE tenant_id = :t AND run_id = :id',
            ['n' => $notes ?: null, 't' => $tenantId, 'id' => $runId]
        );
    }

    public static function abandonRun(int $tenantId, int $runId): void
    {
        DB::run(
            'UPDATE round_runs SET status = "abandoned", finished_at = NOW() WHERE tenant_id = :t AND run_id = :id',
            ['t' => $tenantId, 'id' => $runId]
        );
    }

    /**
     * Grace window (hours) beyond which a completed run stops counting toward
     * "we're on time." Derived from the template's frequency:
     *   daily  → 24h,  shift → 12h (covers a shift changeover),
     *   weekly → 168h, ad_hoc → null (never overdue)
     */
    private static function graceHoursForFrequency(string $freq): ?int
    {
        return ['daily' => 24, 'shift' => 12, 'weekly' => 168, 'ad_hoc' => null][$freq] ?? 24;
    }

    /**
     * Per-template scheduling status. For each active template, look up the
     * most recent completed/in-progress run and decide whether we're inside
     * the grace window, past it, or the template has never been run.
     * Ad-hoc templates are always 'on_time' — they're intentionally
     * on-demand and shouldn't page anyone.
     */
    public static function dueTodayStatus(int $tenantId): array
    {
        $rows = DB::all(
            'SELECT rt.round_template_id, rt.name, rt.frequency,
                    (SELECT MAX(started_at) FROM round_runs rr
                       WHERE rr.round_template_id = rt.round_template_id
                         AND rr.status IN ("in_progress", "completed")) AS last_run_at
               FROM round_templates rt
              WHERE rt.tenant_id = :t AND rt.deleted_at IS NULL AND rt.is_active = 1
              ORDER BY rt.name',
            ['t' => $tenantId]
        );

        $now = time();
        foreach ($rows as &$r) {
            $grace = self::graceHoursForFrequency((string) $r['frequency']);
            if ($grace === null) { $r['status'] = 'on_time'; $r['hours_since'] = null; continue; }

            if (empty($r['last_run_at'])) { $r['status'] = 'never_run'; $r['hours_since'] = null; continue; }

            $hoursSince = ($now - strtotime((string) $r['last_run_at'])) / 3600;
            $r['hours_since'] = round($hoursSince, 1);
            $r['status']      = $hoursSince > $grace ? 'overdue' : 'on_time';
        }
        return $rows;
    }

    /**
     * Reading history per stop across the last N runs of a given template.
     * Returns an array of stops, each with a `points` array of
     * { run_id, checked_at, value, is_ok } — chronological, most-recent last.
     * Only stops that have a numeric `reading_value` on at least one run
     * are returned (a "check-a-box" stop has nothing to trend).
     */
    public static function readingHistoryForTemplate(int $tenantId, int $templateId, int $limitRuns = 30): array
    {
        // Recent runs (any status, so a mid-shift in-progress reading still shows up)
        $runs = DB::all(
            'SELECT run_id, started_at
               FROM round_runs
              WHERE tenant_id = :t AND round_template_id = :tpl
              ORDER BY started_at DESC
              LIMIT ' . (int) $limitRuns,
            ['t' => $tenantId, 'tpl' => $templateId]
        );
        if (empty($runs)) return [];
        $runIds = array_map(static fn($r) => (int) $r['run_id'], $runs);

        // Stop metadata (title, unit, thresholds) for the template
        $stops = DB::all(
            'SELECT s.stop_id, s.sort_order, s.reading_key, s.reading_unit,
                    s.min_ok, s.max_ok, a.name AS asset_name
               FROM round_template_stops s
               JOIN assets a ON a.asset_id = s.asset_id
              WHERE s.round_template_id = :tpl
              ORDER BY s.sort_order',
            ['tpl' => $templateId]
        );

        // All reading points for the runs, indexed by stop
        $ph = implode(',', array_fill(0, count($runIds), '?'));
        $points = DB::all(
            "SELECT rrs.stop_id, rrs.run_id, rrs.reading_value, rrs.is_ok, rrs.checked_at
               FROM round_run_stops rrs
              WHERE rrs.run_id IN ({$ph})
                AND rrs.reading_value IS NOT NULL
                AND rrs.checked_at IS NOT NULL
              ORDER BY rrs.checked_at",
            $runIds
        );
        $byStop = [];
        foreach ($points as $p) {
            $byStop[(int) $p['stop_id']][] = [
                'run_id'     => (int) $p['run_id'],
                'checked_at' => $p['checked_at'],
                'value'      => (float) $p['reading_value'],
                'is_ok'      => (int) $p['is_ok'],
            ];
        }

        $out = [];
        foreach ($stops as $s) {
            $pts = $byStop[(int) $s['stop_id']] ?? [];
            if (empty($pts)) continue;
            $s['points'] = $pts;
            $out[] = $s;
        }
        return $out;
    }

    public static function recentRuns(int $tenantId, int $limit = 20): array
    {
        return DB::all(
            'SELECT rr.*, rt.name AS template_name, u.username AS operator_username,
                    (SELECT COUNT(*) FROM round_run_stops rrs WHERE rrs.run_id = rr.run_id) AS stops_total,
                    (SELECT COUNT(*) FROM round_run_stops rrs WHERE rrs.run_id = rr.run_id AND rrs.checked_at IS NOT NULL) AS stops_done,
                    (SELECT COUNT(*) FROM round_run_stops rrs WHERE rrs.run_id = rr.run_id AND rrs.is_ok = 0 AND rrs.checked_at IS NOT NULL) AS stops_flagged
               FROM round_runs rr
               JOIN round_templates rt ON rt.round_template_id = rr.round_template_id
          LEFT JOIN users u             ON u.user_id           = rr.operator_id
              WHERE rr.tenant_id = :t
              ORDER BY rr.started_at DESC
              LIMIT ' . (int) $limit,
            ['t' => $tenantId]
        );
    }
}
