<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class WorkOrder extends Model
{
    protected string $table = 'work_orders';
    protected string $primaryKey = 'work_order_id';

    public const TYPES      = ['preventive','corrective','adhoc','inspection'];
    public const PRIORITIES = ['low','medium','high','urgent'];
    public const STATUSES   = ['draft','assigned','in_progress','on_hold','completed','cancelled'];

    /**
     * Build the WHERE + params for a filter set. Shared between listForTenant
     * (paged rows) and countForTenant (total for pagination controls) so the
     * two can never drift out of sync.
     */
    private static function filterClause(int $tenantId, array $filters): array
    {
        $sql = ' WHERE w.tenant_id = :t AND w.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        foreach (['wo_type','priority','status'] as $col) {
            if (!empty($filters[$col])) {
                $sql .= " AND w.{$col} = :{$col}";
                $params[$col] = $filters[$col];
            }
        }
        if (!empty($filters['assigned_to'])) {
            $sql .= ' AND w.assigned_to = :aid';
            $params['aid'] = (int) $filters['assigned_to'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (w.title LIKE :q1 OR w.code LIKE :q2)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
        }
        return [$sql, $params];
    }

    private static function orderClause(string $sort): string
    {
        switch ($sort) {
            case 'oldest':       return ' ORDER BY w.created_at ASC';
            case 'due_asc':      return ' ORDER BY w.due_at IS NULL, w.due_at ASC, w.created_at DESC';
            case 'priority':     return " ORDER BY FIELD(w.priority, 'urgent', 'high', 'medium', 'low'), w.due_at IS NULL, w.due_at ASC";
            case 'status_group': return " ORDER BY FIELD(w.status, 'in_progress', 'assigned', 'draft', 'on_hold', 'completed', 'cancelled'), w.due_at IS NULL, w.due_at, w.created_at DESC";
            default:             return ' ORDER BY w.created_at DESC';
        }
    }

    public static function listForTenant(int $tenantId, array $filters = [], ?int $limit = null, int $offset = 0, string $sort = 'newest'): array
    {
        [$where, $params] = self::filterClause($tenantId, $filters);
        $sql = 'SELECT w.*, a.name AS asset_name, s.name AS site_name,
                       u.username AS assigned_username, i.title AS incident_title,
                       (SELECT COUNT(*) FROM work_order_tasks t WHERE t.work_order_id = w.work_order_id) AS task_total,
                       (SELECT COUNT(*) FROM work_order_tasks t WHERE t.work_order_id = w.work_order_id AND t.is_done = 1) AS task_done
                  FROM work_orders w
             LEFT JOIN assets a    ON a.asset_id     = w.asset_id
             LEFT JOIN sites s     ON s.site_id      = w.site_id
             LEFT JOIN users u     ON u.user_id      = w.assigned_to
             LEFT JOIN incidents i ON i.incident_id  = w.incident_id'
             . $where
              . self::orderClause($sort);
        if ($limit !== null) {
            // Cast + interpolate the LIMIT/OFFSET rather than binding — PDO
            // in emulated=false mode types them as strings and MySQL rejects.
            $sql .= sprintf(' LIMIT %d OFFSET %d', max(1, $limit), max(0, $offset));
        }
        return DB::all($sql, $params);
    }

    public static function countForTenant(int $tenantId, array $filters = []): int
    {
        [$where, $params] = self::filterClause($tenantId, $filters);
        return (int) DB::value('SELECT COUNT(*) FROM work_orders w' . $where, $params);
    }

    public static function findInTenant(int $tenantId, int $woId): ?array
    {
        return DB::one(
            'SELECT w.*, a.name AS asset_name, s.name AS site_name,
                    u.username AS assigned_username, i.title AS incident_title
               FROM work_orders w
          LEFT JOIN assets a    ON a.asset_id    = w.asset_id
          LEFT JOIN sites s     ON s.site_id     = w.site_id
          LEFT JOIN users u     ON u.user_id     = w.assigned_to
          LEFT JOIN incidents i ON i.incident_id = w.incident_id
              WHERE w.tenant_id = :t AND w.work_order_id = :id AND w.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $woId]
        );
    }

    public static function tasksFor(int $woId): array
    {
        return DB::all(
            'SELECT t.*, u.username AS done_username
               FROM work_order_tasks t
          LEFT JOIN users u ON u.user_id = t.done_by
              WHERE t.work_order_id = :w
              ORDER BY t.sort_order, t.task_id',
            ['w' => $woId]
        );
    }

    public static function create(int $tenantId, int $userId, array $data): int
    {
        $payload = [
            'tenant_id'       => $tenantId,
            'code'            => $data['code']        ?? null,
            'title'           => $data['title'],
            'description'     => $data['description'] ?? null,
            'wo_type'         => in_array($data['wo_type']  ?? '', self::TYPES,      true) ? $data['wo_type']  : 'corrective',
            'priority'        => in_array($data['priority'] ?? '', self::PRIORITIES, true) ? $data['priority'] : 'medium',
            'status'          => in_array($data['status']   ?? '', self::STATUSES,   true) ? $data['status']   : 'draft',
            'permit_required' => !empty($data['permit_required']) ? 1 : 0,
            'site_id'         => !empty($data['site_id'])     ? (int) $data['site_id']     : null,
            'asset_id'        => !empty($data['asset_id'])    ? (int) $data['asset_id']    : null,
            'incident_id'     => !empty($data['incident_id']) ? (int) $data['incident_id'] : null,
            'assigned_to'     => !empty($data['assigned_to']) ? (int) $data['assigned_to'] : null,
            'created_by'      => $userId > 0 ? $userId : null,
            'scheduled_at'    => !empty($data['scheduled_at']) ? str_replace('T', ' ', $data['scheduled_at']) : null,
            'due_at'          => !empty($data['due_at'])       ? str_replace('T', ' ', $data['due_at'])       : null,
            'estimated_hours' => !empty($data['estimated_hours']) ? (float) $data['estimated_hours'] : null,
            'notes'           => $data['notes'] ?? null,
        ];
        return (int) DB::insert('work_orders', $payload);
    }

    public static function update(int $tenantId, int $woId, array $data): void
    {
        // Permit gate: if the WO needs a permit and the caller is trying to
        // move it out of assigned/draft, require at least one approved/active
        // permit linked back via permits.work_order_id.
        if (isset($data['status']) && in_array($data['status'], ['in_progress','completed'], true)) {
            $wo = self::findInTenant($tenantId, $woId);
            if ($wo && !empty($wo['permit_required'])
                && !\App\Models\Permit::hasApprovedForWo($tenantId, $woId)) {
                \Core\Session::flash('wo_msg', 'Permit required — request/approve a permit before starting or completing this WO.');
                return;
            }
        }

        $allowed = ['code','title','description','wo_type','priority','status','permit_required','site_id','asset_id','incident_id',
                    'assigned_to','scheduled_at','due_at','estimated_hours','actual_hours','notes'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $woId];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if (in_array($col, ['site_id','asset_id','incident_id','assigned_to'], true)) {
                $val = !empty($val) ? (int) $val : null;
            }
            if ($col === 'permit_required') {
                $val = !empty($val) ? 1 : 0;
            }
            if (in_array($col, ['scheduled_at','due_at'], true)) {
                $val = !empty($val) ? str_replace('T', ' ', (string) $val) : null;
            }
            if (in_array($col, ['estimated_hours','actual_hours'], true)) {
                $val = ($val === '' || $val === null) ? null : (float) $val;
            }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!empty($data['status'])) {
            if ($data['status'] === 'in_progress') { $set[] = '`started_at` = COALESCE(started_at, NOW())'; }
            if ($data['status'] === 'completed')   { $set[] = '`completed_at` = COALESCE(completed_at, NOW())'; }
        }
        if (!$set) return;
        DB::run(
            'UPDATE work_orders SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND work_order_id = :id',
            $params
        );
    }

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

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

    public static function addTask(int $woId, 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 = null;
        if ($inputType === 'options' && !empty($data['options_json'])) {
            $raw = $data['options_json'];
            if (is_array($raw)) {
                $optionsJson = json_encode(array_values($raw));
            } else {
                $decoded = json_decode((string) $raw, true);
                $optionsJson = is_array($decoded)
                    ? json_encode(array_values($decoded))
                    : json_encode(array_map('trim', explode(',', (string) $raw)));
            }
        }
        return (int) DB::insert('work_order_tasks', [
            'work_order_id'     => $woId,
            '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,
        ]);
    }

    /**
     * Complete a task with a full response payload. Any field can be omitted
     * to preserve the checkbox-only flow used by simple tasks.
     */
    public static function completeTask(int $taskId, int $userId, ?string $note, ?string $responseValue = null, ?string $remedy = null): void
    {
        DB::run(
            'UPDATE work_order_tasks
                SET is_done        = 1,
                    done_by        = :u,
                    done_at        = NOW(),
                    result_note    = :n,
                    response_value = :rv,
                    remedy_note    = :rm
              WHERE task_id = :id',
            ['u' => $userId, 'n' => $note, 'rv' => $responseValue, 'rm' => $remedy, 'id' => $taskId]
        );
    }

    public static function reopenTask(int $taskId): void
    {
        DB::run(
            'UPDATE work_order_tasks
                SET is_done = 0, done_by = NULL, done_at = NULL,
                    result_note = NULL, response_value = NULL, remedy_note = NULL
              WHERE task_id = :id',
            ['id' => $taskId]
        );
    }

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

    public static function taskInTenant(int $tenantId, int $taskId): ?array
    {
        return DB::one(
            'SELECT t.*, w.tenant_id, w.work_order_id
               FROM work_order_tasks t
               JOIN work_orders w ON w.work_order_id = t.work_order_id
              WHERE t.task_id = :id AND w.tenant_id = :t',
            ['id' => $taskId, 't' => $tenantId]
        );
    }

    /**
     * Find an in-progress work order that was created from the given template
     * by the given user (or any user if userId is null). Returns null if none.
     */
    public static function findInProgressByTemplate(int $tenantId, int $templateId, ?int $userId = null): ?array
    {
        $sql = 'SELECT w.* FROM work_orders w
                 WHERE w.tenant_id = :t
                   AND w.deleted_at IS NULL
                   AND w.status = \'in_progress\'
                   AND w.title = :title';
        $params = ['t' => $tenantId, 'title' => 'Checklist: ' . DB::value(
            'SELECT name FROM pm_templates WHERE template_id = :tid AND tenant_id = :t2',
            ['tid' => $templateId, 't2' => $tenantId]
        )];
        if ($userId !== null) {
            $sql .= ' AND w.assigned_to = :uid';
            $params['uid'] = $userId;
        }
        $sql .= ' ORDER BY w.created_at DESC LIMIT 1';
        return DB::one($sql, $params);
    }

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(status IN ("draft","assigned","in_progress","on_hold")) AS open_count,
                    SUM(status = "completed")  AS completed,
                    SUM(due_at IS NOT NULL AND due_at < NOW() AND status NOT IN ("completed","cancelled")) AS overdue
               FROM work_orders
              WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total'=>0,'open_count'=>0,'completed'=>0,'overdue'=>0];
    }
}