<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

/**
 * Safety Data Sheet library. One row per controlled substance / material;
 * the physical SDS PDF lives as an attachment (owner_type='sds').
 */
final class Sds extends Model
{
    protected string $table = 'sds';
    protected string $primaryKey = 'sds_id';

    public const HAZARD_CLASSES = [
        'flammable'   => 'Flammable',
        'toxic'       => 'Toxic',
        'corrosive'   => 'Corrosive',
        'oxidiser'    => 'Oxidiser',
        'health'      => 'Health hazard',
        'environment' => 'Environmental hazard',
        'other'       => 'Other',
    ];

    public static function listForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT s.*, DATEDIFF(s.review_at, CURDATE()) AS days_to_review
                  FROM sds s
                 WHERE s.tenant_id = :t AND s.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['q'])) {
            $sql .= ' AND (s.name LIKE :q1 OR s.code LIKE :q2 OR s.manufacturer LIKE :q3 OR s.cas_number LIKE :q4)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
            $params['q3'] = $q;
            $params['q4'] = $q;
        }
        if (!empty($filters['hazard_class'])) {
            $sql .= ' AND s.hazard_class = :h';
            $params['h'] = $filters['hazard_class'];
        }
        if (!empty($filters['due_soon'])) {
            $sql .= ' AND s.review_at IS NOT NULL AND s.review_at <= DATE_ADD(CURDATE(), INTERVAL 60 DAY)';
        }
        $sql .= ' ORDER BY s.name';
        return DB::all($sql, $params);
    }

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

    public static function create(int $tenantId, array $data): int
    {
        return (int) DB::insert('sds', [
            'tenant_id'      => $tenantId,
            'code'           => $data['code']         ?? null,
            'name'           => $data['name'],
            'manufacturer'   => $data['manufacturer'] ?? null,
            'supplier'       => $data['supplier']     ?? null,
            'cas_number'     => $data['cas_number']   ?? null,
            'hazard_class'   => array_key_exists($data['hazard_class'] ?? '', self::HAZARD_CLASSES) ? $data['hazard_class'] : null,
            'storage_notes'  => $data['storage_notes'] ?? null,
            'first_aid'      => $data['first_aid']     ?? null,
            'disposal_notes' => $data['disposal_notes']?? null,
            'ppe_required'   => $data['ppe_required']  ?? null,
            'hazards'        => $data['hazards']       ?? null,
            'issued_at'      => !empty($data['issued_at']) ? $data['issued_at'] : null,
            'review_at'      => !empty($data['review_at']) ? $data['review_at'] : null,
        ]);
    }

    public static function update(int $tenantId, int $sdsId, array $data): void
    {
        $allowed = ['code','name','manufacturer','supplier','cas_number','hazard_class',
                    'storage_notes','first_aid','disposal_notes','ppe_required','hazards',
                    'issued_at','review_at'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $sdsId];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if (in_array($col, ['issued_at', 'review_at'], true) && $val === '') $val = null;
            if ($col === 'hazard_class' && !array_key_exists($val, self::HAZARD_CLASSES)) $val = null;
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE sds SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND sds_id = :id',
            $params
        );
    }

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

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(review_at IS NOT NULL AND review_at < CURDATE()) AS overdue,
                    SUM(review_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)) AS due_soon,
                    SUM(review_at IS NULL) AS no_review_date
               FROM sds
              WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total' => 0, 'overdue' => 0, 'due_soon' => 0, 'no_review_date' => 0];
    }
}
