<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use App\Models\AuditLogModel;
use PDO;

/**
 * Customer Model with ledger and multi-tenant search
 */
class CustomerModel extends Model
{
    protected string $table = 'customers';
    private AuditLogModel $auditLogger;

    public function __construct()
    {
        parent::__construct();
        $this->auditLogger = new AuditLogModel();
    }

    /**
     * Get all customers for a tenant with filtering and pagination
     */
    public function getAll(int $tenantId, array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;
        $sql = "SELECT id, name, display_name, company_name, email, work_phone as phone, gstin, status, credit_limit, opening_balance, opening_balance_type, customer_type, created_at 
                FROM customers 
                WHERE tenant_id = :tenant_id";

        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['search'])) {
            $sql .= " AND (name LIKE :search1 OR company_name LIKE :search2 OR email LIKE :search3 OR work_phone LIKE :search4 OR gstin LIKE :search5 OR customer_type LIKE :search6)";
            $params[':search1'] = '%' . $filters['search'] . '%';
            $params[':search2'] = '%' . $filters['search'] . '%';
            $params[':search3'] = '%' . $filters['search'] . '%';
            $params[':search4'] = '%' . $filters['search'] . '%';
            $params[':search5'] = '%' . $filters['search'] . '%';
            $params[':search6'] = '%' . $filters['search'] . '%';
        }

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $sql .= " AND status = :status";
            $params[':status'] = $filters['status'];
        }

        $sort = $filters['sort'] ?? 'name';
        $order = isset($filters['order']) && strtoupper($filters['order']) === 'DESC' ? 'DESC' : 'ASC';

        // Whitelist sort columns
        $allowedSort = ['name', 'company_name', 'email', 'status', 'created_at'];
        if (!in_array($sort, $allowedSort))
            $sort = 'name';

        $sql .= " ORDER BY $sort $order LIMIT $perPage OFFSET $offset";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $customers = $stmt->fetchAll();

        foreach ($customers as &$c) {
            $c['outstanding_balance'] = $this->getOutstandingBalance($c['id'], $tenantId);
        }

        return $customers;
    }

    public function countAll(int $tenantId, array $filters = []): int
    {
        $sql = "SELECT COUNT(*) FROM customers WHERE tenant_id = :tenant_id";
        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['search'])) {
            $sql .= " AND (name LIKE :search1 OR company_name LIKE :search2 OR email LIKE :search3 OR work_phone LIKE :search4 OR gstin LIKE :search5 OR customer_type LIKE :search6)";
            $params[':search1'] = '%' . $filters['search'] . '%';
            $params[':search2'] = '%' . $filters['search'] . '%';
            $params[':search3'] = '%' . $filters['search'] . '%';
            $params[':search4'] = '%' . $filters['search'] . '%';
            $params[':search5'] = '%' . $filters['search'] . '%';
            $params[':search6'] = '%' . $filters['search'] . '%';
        }

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $sql .= " AND status = :status";
            $params[':status'] = $filters['status'];
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

    public function getById(int $id, int $tenantId): ?array
    {
        $sql = "SELECT * FROM customers WHERE id = :id AND tenant_id = :tenant_id LIMIT 1";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id, ':tenant_id' => $tenantId]);
        $customer = $stmt->fetch();

        if ($customer) {
            $customer['outstanding_balance'] = $this->getOutstandingBalance($id, $tenantId);
            $customer['contacts'] = $this->getContacts($id, $tenantId);
        }

        return $customer ?: null;
    }

    public function getContacts(int $customerId, int $tenantId): array
    {
        $stmt = $this->db->prepare("SELECT * FROM customer_contacts WHERE customer_id = ? AND tenant_id = ?");
        $stmt->execute([$customerId, $tenantId]);
        return $stmt->fetchAll();
    }

    public function saveContacts(int $customerId, int $tenantId, array $contacts): void
    {
        // Simple sync: delete and re-insert
        $stmt = $this->db->prepare("DELETE FROM customer_contacts WHERE customer_id = ? AND tenant_id = ?");
        $stmt->execute([$customerId, $tenantId]);

        if (empty($contacts)) return;

        $sql = "INSERT INTO customer_contacts (customer_id, tenant_id, salutation, first_name, last_name, email, work_phone, mobile_phone) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $this->db->prepare($sql);

        foreach ($contacts as $c) {
            if (empty($c['first_name'])) continue;
            $stmt->execute([
                $customerId,
                $tenantId,
                $c['salutation'] ?? null,
                $c['first_name'],
                $c['last_name'] ?? null,
                $c['email'] ?? null,
                $c['work_phone'] ?? null,
                $c['mobile_phone'] ?? null
            ]);
        }
    }

    /**
     * Calculate Outstanding Balance (Total Invoiced - Total Paid)
     * For now, returns opening balance as placeholder until Invoices/Payments tables are populated
     */
    public function getOutstandingBalance(int $id, int $tenantId): float
    {
        // SQL will be: (SELECT SUM(grand_total) FROM invoices WHERE customer_id = :id) - (SELECT SUM(amount) FROM payments WHERE customer_id = :id)
        // For foundation, return opening balance + 0
        $stmt = $this->db->prepare("SELECT opening_balance, opening_balance_type FROM customers WHERE id = ? AND tenant_id = ?");
        $stmt->execute([$id, $tenantId]);
        $row = $stmt->fetch();

        if (!$row)
            return 0.00;

        $balance = (float) $row['opening_balance'];
        return $row['opening_balance_type'] === 'debit' ? $balance : -$balance;
    }

    /**
     * Soft delete by setting status to inactive
     */
    public function softDelete(int $id, int $tenantId, int $userId): bool
    {
        $old = $this->getById($id, $tenantId);
        if (!$old)
            return false;

        $stmt = $this->db->prepare("UPDATE customers SET status = 'inactive' WHERE id = :id AND tenant_id = :tenant_id");
        $result = $stmt->execute([':id' => $id, ':tenant_id' => $tenantId]);

        if ($result) {
            $this->auditLogger->log($tenantId, $userId, 'delete', 'customers', $id, $old, ['status' => 'inactive']);
        }

        return $result;
    }

    /**
     * Bulk Import from array (CSV handler converts file to array)
     */
    public function import(int $tenantId, int $userId, array $rows): array
    {
        $imported = 0;
        $failed = 0;
        $errors = [];

        foreach ($rows as $index => $row) {
            try {
                // Basic mapping and validation
                if (empty($row['name']))
                    throw new \Exception("Name is required");

                $id = $this->create([
                    'tenant_id' => $tenantId,
                    'name' => $row['name'],
                    'company_name' => $row['company'] ?? null,
                    'gstin' => $row['gstin'] ?? null,
                    'email' => $row['email'] ?? null,
                    'phone' => $row['phone'] ?? null,
                    'billing_address' => $row['address'] ?? null,
                    'created_by' => $userId
                ]);
                $imported++;
            } catch (\Exception $e) {
                $failed++;
                $errors[] = ['row' => $index + 2, 'error' => $e->getMessage()];
            }
        }

        return ['imported' => $imported, 'failed' => $failed, 'errors' => $errors];
    }
}
