<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Asset extends Model
{
    protected string $table = 'assets';
    protected string $primaryKey = 'asset_id';

    public static function listForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT a.*, s.name AS site_name, at.name AS asset_type_name, at.icon, at.color,
                       p.name AS parent_name
                  FROM assets a
             LEFT JOIN sites s        ON s.site_id       = a.site_id
             LEFT JOIN asset_types at ON at.asset_type_id = a.asset_type_id
             LEFT JOIN assets p       ON p.asset_id      = a.parent_asset_id
                 WHERE a.tenant_id = :t AND a.deleted_at IS NULL';
        $params = ['t' => $tenantId];

        if (!empty($filters['site_id'])) {
            $sql .= ' AND a.site_id = :sid';
            $params['sid'] = (int) $filters['site_id'];
        }
        if (!empty($filters['asset_type_id'])) {
            $sql .= ' AND a.asset_type_id = :tid';
            $params['tid'] = (int) $filters['asset_type_id'];
        }
        if (!empty($filters['status'])) {
            $sql .= ' AND a.status = :st';
            $params['st'] = $filters['status'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (a.name LIKE :q1 OR a.serial_number LIKE :q2 OR a.asset_tag LIKE :q3)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
            $params['q3'] = $q;
        }
        $sql .= ' ORDER BY a.name';
        return DB::all($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $assetId): ?array
    {
        return DB::one(
            'SELECT a.*, s.name AS site_name, at.name AS asset_type_name, at.code AS asset_type_code
               FROM assets a
          LEFT JOIN sites s        ON s.site_id       = a.site_id
          LEFT JOIN asset_types at ON at.asset_type_id = a.asset_type_id
              WHERE a.tenant_id = :t AND a.asset_id = :id AND a.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $assetId]
        );
    }

    public static function optionsForTenant(int $tenantId, ?int $exclude = null): array
    {
        $sql = 'SELECT asset_id, name FROM assets
                 WHERE tenant_id = :t AND deleted_at IS NULL AND status = "active"';
        $params = ['t' => $tenantId];
        if ($exclude !== null) {
            $sql .= ' AND asset_id <> :ex';
            $params['ex'] = $exclude;
        }
        $sql .= ' ORDER BY name';
        return DB::all($sql, $params);
    }

    public const CRITICALITIES = ['A', 'B', 'C'];

    public static function create(int $tenantId, int $userId, array $data): int
    {
        $payload = [
            'tenant_id'           => $tenantId,
            'site_id'             => !empty($data['site_id'])         ? (int) $data['site_id']         : null,
            'asset_type_id'       => (int) $data['asset_type_id'],
            'parent_asset_id'     => !empty($data['parent_asset_id']) ? (int) $data['parent_asset_id'] : null,
            'code'                => $data['code']          ?? null,
            'name'                => $data['name'],
            'serial_number'       => $data['serial_number'] ?? null,
            'asset_tag'           => $data['asset_tag']     ?? null,
            'meta_json'           => !empty($data['meta_json']) ? json_encode($data['meta_json']) : null,
            'notes'               => $data['notes']         ?? null,
            'status'              => $data['status']        ?? 'active',
            'criticality'         => in_array($data['criticality'] ?? '', self::CRITICALITIES, true) ? $data['criticality'] : 'C',
            'installed_at'        => !empty($data['installed_at']) ? $data['installed_at'] : null,
            'warranty_expires_at' => !empty($data['warranty_expires_at']) ? $data['warranty_expires_at'] : null,
            'created_by'          => $userId,
        ];
        return (int) DB::insert('assets', $payload);
    }

    public static function update(int $tenantId, int $assetId, array $data): void
    {
        $allowed = ['site_id','asset_type_id','parent_asset_id','code','name','serial_number','asset_tag','meta_json','notes','status','criticality','installed_at','warranty_expires_at'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $assetId];
        foreach ($allowed as $col) {
            if (array_key_exists($col, $data)) {
                $val = $data[$col];
                if (in_array($col, ['site_id','asset_type_id','parent_asset_id'], true)) {
                    $val = !empty($val) ? (int) $val : null;
                }
                if ($col === 'meta_json' && is_array($val)) {
                    $val = json_encode($val);
                }
                if ($col === 'installed_at' && $val === '') { $val = null; }
                $set[] = "`{$col}` = :{$col}";
                $params[$col] = $val;
            }
        }
        if (!$set) return;
        DB::run(
            'UPDATE assets SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND asset_id = :id',
            $params
        );
    }

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

    /** Direct children of an asset (used on show page). */
    public static function childrenOf(int $tenantId, int $parentAssetId): array
    {
        return DB::all(
            'SELECT a.asset_id, a.name, a.code, a.status, a.criticality, at.name AS asset_type_name, at.icon, at.color
               FROM assets a
          LEFT JOIN asset_types at ON at.asset_type_id = a.asset_type_id
              WHERE a.tenant_id = :t AND a.parent_asset_id = :p AND a.deleted_at IS NULL
              ORDER BY a.name',
            ['t' => $tenantId, 'p' => $parentAssetId]
        );
    }

    /** Full ancestry chain leaf → root, so we can render breadcrumbs. */
    public static function ancestorsOf(int $tenantId, int $assetId): array
    {
        $chain = [];
        $current = $assetId;
        // Cap depth to avoid loops from corrupt data.
        for ($i = 0; $i < 20; $i++) {
            $row = DB::one(
                'SELECT asset_id, name, parent_asset_id FROM assets
                  WHERE tenant_id = :t AND asset_id = :id AND deleted_at IS NULL',
                ['t' => $tenantId, 'id' => $current]
            );
            if (!$row) break;
            $chain[] = ['asset_id' => (int) $row['asset_id'], 'name' => (string) $row['name']];
            if (empty($row['parent_asset_id'])) break;
            $current = (int) $row['parent_asset_id'];
        }
        return array_reverse($chain);
    }

    /**
     * Flat list ordered so parents come before their children. Used by the
     * tree view and by the parent-picker to show indented options.
     */
    public static function treeForTenant(int $tenantId): array
    {
        $rows = DB::all(
            'SELECT a.asset_id, a.parent_asset_id, a.name, a.code, a.status, a.criticality,
                    at.name AS asset_type_name, at.icon, at.color
               FROM assets a
          LEFT JOIN asset_types at ON at.asset_type_id = a.asset_type_id
              WHERE a.tenant_id = :t AND a.deleted_at IS NULL
              ORDER BY a.name',
            ['t' => $tenantId]
        );
        $byParent = [];
        foreach ($rows as $r) {
            $byParent[(int) ($r['parent_asset_id'] ?? 0)][] = $r;
        }
        $out = [];
        $walk = function (int $parentId, int $depth) use (&$walk, &$out, $byParent): void {
            foreach ($byParent[$parentId] ?? [] as $node) {
                $node['depth'] = $depth;
                $out[] = $node;
                $walk((int) $node['asset_id'], $depth + 1);
            }
        };
        $walk(0, 0);
        return $out;
    }

    /** Assets whose warranty ends within N days (positive = future, 0 = today). */
    public static function warrantyExpiringSoon(int $tenantId, int $days = 60): array
    {
        return DB::all(
            'SELECT a.asset_id, a.name, a.warranty_expires_at, a.criticality, s.name AS site_name
               FROM assets a
          LEFT JOIN sites s ON s.site_id = a.site_id
              WHERE a.tenant_id = :t AND a.deleted_at IS NULL
                AND a.warranty_expires_at IS NOT NULL
                AND a.warranty_expires_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL :d DAY)
              ORDER BY a.warranty_expires_at',
            ['t' => $tenantId, 'd' => $days]
        );
    }

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(status = "active")    AS active,
                    SUM(status = "inactive")  AS inactive,
                    SUM(status = "scrapped")  AS scrapped
               FROM assets
              WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total'=>0,'active'=>0,'inactive'=>0,'scrapped'=>0];
    }
}
