<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Site extends Model
{
    protected string $table = 'sites';
    protected string $primaryKey = 'site_id';

    public static function listForTenant(int $tenantId, string $status = ''): array
    {
        $sql = 'SELECT s.*, p.name AS parent_name,
                       (SELECT COUNT(*) FROM assets a WHERE a.site_id = s.site_id AND a.deleted_at IS NULL) AS asset_count
                  FROM sites s
             LEFT JOIN sites p ON p.site_id = s.parent_site_id
                 WHERE s.tenant_id = :t AND s.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if ($status !== '') {
            $sql .= ' AND s.status = :st';
            $params['st'] = $status;
        }
        $sql .= ' ORDER BY s.name';
        return DB::all($sql, $params);
    }

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

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

    public static function create(int $tenantId, int $userId, array $data): int
    {
        $payload = [
            'tenant_id'      => $tenantId,
            'parent_site_id' => !empty($data['parent_site_id']) ? (int) $data['parent_site_id'] : null,
            'code'           => $data['code']        ?? null,
            'name'           => $data['name'],
            'description'    => $data['description'] ?? null,
            'address'        => $data['address']     ?? null,
            'city'           => $data['city']        ?? null,
            'region'         => $data['region']      ?? null,
            'country'        => $data['country']     ?? 'Tanzania',
            'latitude'       => $data['latitude']    !== '' ? $data['latitude']  : null,
            'longitude'      => $data['longitude']   !== '' ? $data['longitude'] : null,
            'status'         => $data['status']      ?? 'active',
            'created_by'     => $userId,
        ];
        return (int) DB::insert('sites', $payload);
    }

    public static function update(int $tenantId, int $siteId, array $data): void
    {
        $allowed = ['parent_site_id','code','name','description','address','city','region','country','latitude','longitude','status'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $siteId];
        foreach ($allowed as $col) {
            if (array_key_exists($col, $data)) {
                $val = $data[$col];
                if ($col === 'parent_site_id') { $val = !empty($val) ? (int) $val : null; }
                if (in_array($col, ['latitude','longitude'], true) && $val === '') { $val = null; }
                $set[] = "`{$col}` = :{$col}";
                $params[$col] = $val;
            }
        }
        if (!$set) return;
        DB::run(
            'UPDATE sites SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND site_id = :id',
            $params
        );
    }

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