<?php
class Customers extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function m($v): float  { return (float)str_replace(',', '', (string)$v); }
    private function n($v): ?int   { $v = preg_replace('/[^0-9\-]/', '', (string)$v); return $v === '' ? null : (int)$v; }

    /**
     * Paginated + filtered list.
     * Filters: q (LIKE fname/lname/customer_no/tin/vrn/city/POC),
     *          tier, sector, status
     */
    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['c.deleted = 0'];
        $params = [];

        if (!empty($filters['q'])) {
            $where[] = '(c.fname LIKE :q OR c.lname LIKE :q OR c.customer_no LIKE :q OR c.customer_tin LIKE :q OR c.customer_vrn LIKE :q OR c.city LIKE :q OR c.customer_poc LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['tier']))   { $where[] = 'c.customer_tier   = :tier';   $params[':tier']   = $this->s($filters['tier']); }
        if (!empty($filters['sector'])) { $where[] = 'c.customer_sector = :sector'; $params[':sector'] = $this->s($filters['sector']); }
        if (!empty($filters['status'])) { $where[] = 'c.status          = :status'; $params[':status'] = $this->s($filters['status']); }

        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM customers c WHERE $whereSql", $params);
        $total   = (int)($countRs[0]['n'] ?? 0);

        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT c.c0d, c.customer_no, c.customer_code, c.fname, c.lname,
                    c.email, c.phone_1, c.city,
                    c.customer_tin, c.customer_vrn, c.customer_sector, c.customer_tier,
                    c.customer_poc, c.credit_limit, c.payment_terms_days, c.currency, c.status
               FROM customers c
              WHERE $whereSql
           ORDER BY c.fname ASC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function allActive(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, customer_no, fname, lname, phone_1, city, customer_sector, customer_tier, status
               FROM customers WHERE deleted = 0 AND status = 'ACTIVE' ORDER BY fname ASC",
            []
        ) ?: [];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT * FROM customers WHERE c0d = :id AND deleted = 0 LIMIT 1", [':id' => $id]);
        return $rs[0] ?? [];
    }

    public function nextCustomerNo(): string
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT customer_no FROM customers
              WHERE customer_no REGEXP '^[0-9]+$'
           ORDER BY CAST(customer_no AS UNSIGNED) DESC LIMIT 1", []
        );
        $last = (int)($rs[0]['customer_no'] ?? 0);
        return str_pad((string)($last + 1), 5, '0', STR_PAD_LEFT);
    }

    public function create(array $d, int $uid): array
    {
        $name = $this->s($d['fname'] ?? '');
        if ($name === '') return ['ok' => false, 'msg' => 'Company name is required.', 'id' => 0];

        $db = new DB();
        $r  = $db->insertUpdate(
            "INSERT INTO customers
                (tenant_id, customer_no, customer_code, fname, lname, email, phone_1, phone_2,
                 physical_address, po_box, city, country, customer_tin, customer_vrn,
                 customer_sector, customer_tier, customer_poc, customer_poc_role,
                 customer_poc_phone, customer_poc_email, status,
                 credit_limit, payment_terms_days, currency, opening_balance,
                 billing_email, notes, created_by)
             VALUES
                (:tenant, :cn, :cc, :fn, :ln, :em, :p1, :p2,
                 :ad, :pb, :ci, :co, :tin, :vrn,
                 :sec, :tier, :poc, :pocr,
                 :pocp, :poce, :status,
                 :credit, :terms, :cur, :open,
                 :bem, :notes, :uid)",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':cn'   => $this->nextCustomerNo(),
                ':cc'   => $this->s($d['customer_code'] ?? ''),
                ':fn'   => $name,
                ':ln'   => $this->s($d['lname'] ?? ''),
                ':em'   => $this->s($d['email'] ?? ''),
                ':p1'   => $this->s($d['phone_1'] ?? ''),
                ':p2'   => $this->s($d['phone_2'] ?? ''),
                ':ad'   => $this->s($d['physical_address'] ?? ''),
                ':pb'   => $this->s($d['po_box'] ?? ''),
                ':ci'   => $this->s($d['city'] ?? ''),
                ':co'   => $this->s(($d['country'] ?? '') ?: 'TANZANIA'),
                ':tin'  => $this->s($d['customer_tin'] ?? ''),
                ':vrn'  => $this->s($d['customer_vrn'] ?? ''),
                ':sec'  => $this->s($d['customer_sector'] ?? ''),
                ':tier' => $this->s(($d['customer_tier'] ?? '') ?: 'CORPORATE'),
                ':poc'  => $this->s($d['customer_poc'] ?? ''),
                ':pocr' => $this->s($d['customer_poc_role'] ?? ''),
                ':pocp' => $this->s($d['customer_poc_phone'] ?? ''),
                ':poce' => $this->s($d['customer_poc_email'] ?? ''),
                ':status' => $this->s(($d['status'] ?? '') ?: 'ACTIVE'),
                ':credit' => $this->m($d['credit_limit']       ?? 0),
                ':terms'  => (int)($d['payment_terms_days']    ?? 0),
                ':cur'    => $this->s(($d['currency'] ?? '') ?: (defined('DEFAULT_CURRENCY') ? DEFAULT_CURRENCY : 'TZS')),
                ':open'   => $this->m($d['opening_balance']    ?? 0),
                ':bem'    => $this->s($d['billing_email']      ?? ''),
                ':notes'  => $this->s($d['notes']              ?? ''),
                ':uid'    => $uid,
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Customer added.' : 'Save failed.', 'id' => (int)$r['last_id']];
    }

    public function update(int $id, array $d, int $uid): array
    {
        if (!$this->byId($id)) return ['ok' => false, 'msg' => 'Customer not found.', 'id' => 0];
        $name = $this->s($d['fname'] ?? '');
        if ($name === '') return ['ok' => false, 'msg' => 'Company name is required.', 'id' => 0];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE customers SET
                customer_code = :cc, fname = :fn, lname = :ln,
                email = :em, phone_1 = :p1, phone_2 = :p2,
                physical_address = :ad, po_box = :pb, city = :ci, country = :co,
                customer_tin = :tin, customer_vrn = :vrn,
                customer_sector = :sec, customer_tier = :tier,
                customer_poc = :poc, customer_poc_role = :pocr,
                customer_poc_phone = :pocp, customer_poc_email = :poce,
                status = :status,
                credit_limit = :credit, payment_terms_days = :terms,
                currency = :cur, opening_balance = :open,
                billing_email = :bem, notes = :notes,
                edited_by = :uid
              WHERE c0d = :id AND deleted = 0",
            [
                ':cc' => $this->s($d['customer_code'] ?? ''),
                ':fn' => $name,
                ':ln' => $this->s($d['lname'] ?? ''),
                ':em' => $this->s($d['email'] ?? ''),
                ':p1' => $this->s($d['phone_1'] ?? ''),
                ':p2' => $this->s($d['phone_2'] ?? ''),
                ':ad' => $this->s($d['physical_address'] ?? ''),
                ':pb' => $this->s($d['po_box'] ?? ''),
                ':ci' => $this->s($d['city'] ?? ''),
                ':co' => $this->s(($d['country'] ?? '') ?: 'TANZANIA'),
                ':tin' => $this->s($d['customer_tin'] ?? ''),
                ':vrn' => $this->s($d['customer_vrn'] ?? ''),
                ':sec' => $this->s($d['customer_sector'] ?? ''),
                ':tier'=> $this->s(($d['customer_tier'] ?? '') ?: 'CORPORATE'),
                ':poc' => $this->s($d['customer_poc'] ?? ''),
                ':pocr'=> $this->s($d['customer_poc_role']  ?? ''),
                ':pocp'=> $this->s($d['customer_poc_phone'] ?? ''),
                ':poce'=> $this->s($d['customer_poc_email'] ?? ''),
                ':status' => $this->s(($d['status'] ?? '') ?: 'ACTIVE'),
                ':credit' => $this->m($d['credit_limit']       ?? 0),
                ':terms'  => (int)($d['payment_terms_days']    ?? 0),
                ':cur'    => $this->s(($d['currency'] ?? '') ?: 'TZS'),
                ':open'   => $this->m($d['opening_balance']    ?? 0),
                ':bem'    => $this->s($d['billing_email']      ?? ''),
                ':notes'  => $this->s($d['notes']              ?? ''),
                ':uid'    => $uid,
                ':id'     => $id,
            ]
        );
        return ['ok' => true, 'msg' => 'Customer updated.', 'id' => $id];
    }

    public function softDelete(int $id, int $uid): array
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE customers SET deleted = 1, edited_by = :uid WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true];
    }
}
