<?php
namespace App\Models;

use App\Core\Model;
use PDO;

class Client extends Model
{
    public function getAll(): array
    {
        $stmt = $this->query("SELECT * FROM clients ORDER BY full_name");
        return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->query("SELECT * FROM clients WHERE id = ?", [$id]);
        return $stmt->fetch();
    }

    public function create(array $data): int
    {
        $data['client_code'] = $this->generateClientCode();
        return $this->insert('clients', $data);
    }

    /**
     * Update client balance – caller manages transaction.
     */
    public function updateBalance(int $clientId, float $amount, string $operation = 'add'): float
    {
        $pdo = self::getPDO();
        $stmt = $pdo->prepare("SELECT balance FROM clients WHERE id = ? FOR UPDATE");
        $stmt->execute([$clientId]);
        $current = $stmt->fetchColumn();

        $newBalance = ($operation === 'add') ? $current + $amount : $current - $amount;

        $stmt = $pdo->prepare("UPDATE clients SET balance = ? WHERE id = ?");
        $stmt->execute([$newBalance, $clientId]);

        return $newBalance;
    }

    public function canDebit(int $clientId, float $debitAmount): bool
    {
        $client = $this->find($clientId);
        if (!$client) return false;

        $newBalance = $client['balance'] - $debitAmount;
        $overdraftLimit = $client['overdraft_limit'] ?? 0;

        return $newBalance >= -$overdraftLimit;
    }

    public function updateOverdraftSettings(int $clientId, float $limit): bool
    {
        return $this->update('clients',
            ['overdraft_limit' => $limit],
            'id = :id',
            [':id' => $clientId]
        ) > 0;
    }

    public function getStatement(int $clientId, ?string $fromDate = null, ?string $toDate = null): ?array
    {
        $client = $this->find($clientId);
        if (!$client) return null;

        $dateFilter = '';
        $params = [$clientId];
        if ($fromDate) {
            $dateFilter .= " AND DATE(created_at) >= ?";
            $params[] = $fromDate;
        }
        if ($toDate) {
            $dateFilter .= " AND DATE(created_at) <= ?";
            $params[] = $toDate;
        }

        $deposits = $this->query(
            "SELECT 'Deposit' as type, deposit_date as date, amount, reference, payment_method as method, notes
             FROM deposits
             WHERE client_id = ? $dateFilter
             ORDER BY deposit_date",
            $params
        )->fetchAll();

        $transactions = $this->query(
            "SELECT
                CASE transaction_type
                    WHEN 'debit' THEN 'Debit'
                    ELSE 'Credit'
                END as type,
                transaction_date as date,
                CASE transaction_type WHEN 'debit' THEN -amount ELSE amount END as amount,
                description as reference,
                '' as method,
                '' as notes
             FROM client_transactions
             WHERE client_id = ? $dateFilter
             ORDER BY transaction_date",
            $params
        )->fetchAll();

        $entries = array_merge($deposits, $transactions);
        usort($entries, function($a, $b) {
            return strtotime($a['date']) - strtotime($b['date']);
        });

        $runningBalance = 0;
        $statement = [];
        foreach ($entries as $entry) {
            $runningBalance += $entry['amount'];
            $statement[] = array_merge($entry, ['balance' => $runningBalance]);
        }

        return [
            'client' => $client,
            'entries' => $statement,
            'current_balance' => $client['balance']
        ];
    }

    public function findByCode(string $clientCode): ?array
    {
        $stmt = $this->query("SELECT * FROM clients WHERE client_code = ?", [$clientCode]);
        return $stmt->fetch();
    }

    public function verifyPassword(array $client, string $password): bool
    {
        return password_verify($password, $client['password_hash']);
    }

    public function findByUsername(string $username): ?array
    {
        // Try client_username first, then fallback to client_code
        $stmt = $this->query("SELECT * FROM clients WHERE client_username = ?", [$username]);
        $client = $stmt->fetch();
        if (!$client) {
            $stmt = $this->query("SELECT * FROM clients WHERE client_code = ?", [$username]);
            $client = $stmt->fetch();
        }
        return $client;
    }

    public function findByEmail(string $email): ?array
    {
        $stmt = $this->query("SELECT * FROM clients WHERE email = ?", [$email]);
        return $stmt->fetch();
    }

    public function setResetToken(int $clientId, string $token, string $expiry): int
    {
        return $this->update('clients', [
            'reset_token' => $token,
            'reset_token_expires' => $expiry
        ], 'id = :id', [':id' => $clientId]);
    }

    public function findByResetToken(string $token): ?array
    {
        $stmt = $this->query("SELECT * FROM clients WHERE reset_token = ? AND reset_token_expires > NOW()", [$token]);
        return $stmt->fetch();
    }

    public function updatePassword(int $clientId, string $newPassword): int
    {
        $hash = password_hash($newPassword, PASSWORD_DEFAULT);
        return $this->update('clients', [
            'password_hash' => $hash,
            'reset_token' => null,
            'reset_token_expires' => null
        ], 'id = :id', [':id' => $clientId]);
    }

    public function setPassword(int $clientId, string $password): int
    {
        $hash = password_hash($password, PASSWORD_DEFAULT);
        return $this->update('clients', ['password_hash' => $hash], 'id = :id', [':id' => $clientId]);
    }

    public function updateLastLogin(int $clientId): int
    {
        return $this->update('clients', ['last_login' => date('Y-m-d H:i:s')], 'id = :id', [':id' => $clientId]);
    }

    private function generateClientCode(): string
    {
        $year = date('y');
        $stmt = $this->query("SELECT MAX(id) as max_id FROM clients");
        $row = $stmt->fetch();
        $next = ($row['max_id'] ?? 0) + 1;
        return "C{$year}" . str_pad($next, 4, '0', STR_PAD_LEFT);
    }
}