<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Expense extends Model
{
    protected string $table = 'expenses';
    protected string $primaryKey = 'expense_id';

    public const STATUSES = ['draft','submitted','approved','rejected','paid','cancelled'];

    public static function listForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT e.*, c.name AS category_name,
                       us.username AS submitter_username,
                       ua.username AS approver_username
                  FROM expenses e
             LEFT JOIN expense_categories c ON c.category_id = e.category_id
             LEFT JOIN users us             ON us.user_id = e.submitted_by
             LEFT JOIN users ua             ON ua.user_id = e.approved_by
                 WHERE e.tenant_id = :t AND e.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['status']))       { $sql .= ' AND e.status = :st';       $params['st']  = $filters['status']; }
        if (!empty($filters['category_id']))  { $sql .= ' AND e.category_id = :cid'; $params['cid'] = (int) $filters['category_id']; }
        if (!empty($filters['submitted_by'])) { $sql .= ' AND e.submitted_by = :sb'; $params['sb']  = (int) $filters['submitted_by']; }
        if (!empty($filters['q'])) {
            $sql .= ' AND (e.title LIKE :q1 OR e.vendor LIKE :q2 OR e.code LIKE :q3)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
            $params['q3'] = $q;
        }
        $sql .= ' ORDER BY e.expense_date DESC, e.created_at DESC';
        return DB::all($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $id): ?array
    {
        return DB::one(
            'SELECT e.*, c.name AS category_name,
                    us.username AS submitter_username, ua.username AS approver_username,
                    w.title AS wo_title, s.name AS site_name, a.name AS asset_name
               FROM expenses e
          LEFT JOIN expense_categories c ON c.category_id = e.category_id
          LEFT JOIN users us ON us.user_id = e.submitted_by
          LEFT JOIN users ua ON ua.user_id = e.approved_by
          LEFT JOIN work_orders w ON w.work_order_id = e.work_order_id
          LEFT JOIN sites s ON s.site_id = e.site_id
          LEFT JOIN assets a ON a.asset_id = e.asset_id
              WHERE e.tenant_id = :t AND e.expense_id = :id AND e.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $id]
        );
    }

    public static function categoriesFor(int $tenantId): array
    {
        return DB::all(
            'SELECT * FROM expense_categories
              WHERE tenant_id = :t AND is_active = 1
              ORDER BY name',
            ['t' => $tenantId]
        );
    }

    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,
            'category_id'    => !empty($data['category_id'])   ? (int) $data['category_id']   : null,
            'work_order_id'  => !empty($data['work_order_id']) ? (int) $data['work_order_id'] : null,
            'site_id'        => !empty($data['site_id'])       ? (int) $data['site_id']       : null,
            'asset_id'       => !empty($data['asset_id'])      ? (int) $data['asset_id']      : null,
            'submitted_by'   => $userId,
            'expense_date'   => !empty($data['expense_date'])  ? $data['expense_date']       : date('Y-m-d'),
            'amount'         => (float) ($data['amount'] ?? 0),
            'currency'       => $data['currency']       ?? 'TZS',
            'vendor'         => $data['vendor']         ?? null,
            'receipt_ref'    => $data['receipt_ref']    ?? null,
            'payment_method' => $data['payment_method'] ?? null,
            'status'         => in_array($data['status'] ?? '', self::STATUSES, true) ? $data['status'] : 'draft',
        ];
        return (int) DB::insert('expenses', $payload);
    }

    public static function update(int $tenantId, int $id, array $data): void
    {
        $allowed = ['code','title','description','category_id','work_order_id','site_id','asset_id',
                    'expense_date','amount','currency','vendor','receipt_ref','payment_method','status','approval_note'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $id];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if (in_array($col, ['category_id','work_order_id','site_id','asset_id'], true)) {
                $val = !empty($val) ? (int) $val : null;
            }
            if ($col === 'amount') { $val = (float) $val; }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE expenses SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND expense_id = :id',
            $params
        );
    }

    public static function transition(int $tenantId, int $id, string $status, int $actorId, ?string $note = null): void
    {
        $status = in_array($status, self::STATUSES, true) ? $status : null;
        if ($status === null) return;

        $set = ['`status` = :st'];
        $params = ['st' => $status, 't' => $tenantId, 'id' => $id];
        if ($status === 'submitted') { $set[] = '`submitted_at` = NOW()'; }
        if (in_array($status, ['approved','rejected'], true)) {
            $set[] = '`approved_by` = :ab';
            $set[] = '`approved_at` = NOW()';
            $params['ab'] = $actorId;
        }
        if ($status === 'paid') { $set[] = '`paid_at` = NOW()'; }
        if ($note !== null) { $set[] = '`approval_note` = :note'; $params['note'] = $note; }

        DB::run(
            'UPDATE expenses SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND expense_id = :id',
            $params
        );
    }

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

    public static function totalsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS n,
                    COALESCE(SUM(CASE WHEN status = "approved" OR status = "paid" THEN amount ELSE 0 END), 0) AS approved_amount,
                    COALESCE(SUM(CASE WHEN status = "submitted"                    THEN amount ELSE 0 END), 0) AS pending_amount
               FROM expenses
              WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['n'=>0,'approved_amount'=>0,'pending_amount'=>0];
    }
}
