<?php
// ============================================================
//  MediCore — Customers API
//  File: api/customers.php
// ============================================================
require_once '../includes/db.php';
require_once '../includes/auth.php';
require_login();
session_write_close(); // release session lock — prevents XAMPP request queuing

header('Content-Type: application/json');

// ── Defensive migration: UNIQUE INDEX on customers.phone ───────
// Only attempted if it's actually safe to add (no duplicate non-empty
// phones already in the table, e.g. from before phone was mandatory).
// Best-effort — silently skipped if unsafe or insufficient privileges,
// so existing data is never broken by this check.
try {
    $hasUnique = db_val(
        "SELECT COUNT(*) FROM information_schema.STATISTICS
          WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME   = 'customers'
            AND COLUMN_NAME  = 'phone'
            AND NON_UNIQUE   = 0"
    );
    if (!$hasUnique) {
        $dupCount = db_val(
            "SELECT COUNT(*) FROM (
                SELECT phone FROM customers
                 WHERE phone != '' AND id > 1
                 GROUP BY phone HAVING COUNT(*) > 1
             ) t"
        );
        if (!$dupCount) {
            db_run("ALTER TABLE customers ADD UNIQUE INDEX uq_customers_phone (phone)");
        }
    }
} catch (Throwable $e) { /* best-effort */ }

ensure_soft_delete('customers');

$body   = json_decode(file_get_contents('php://input'), true) ?? [];
$action = trim($body['action'] ?? '');

try {
    match($action) {
        'list'         => api_list($body),
        'detail'       => api_detail($body),
        'add'          => api_add($body),
        'edit'         => api_edit($body),
        'collect'      => api_collect($body),
        'delete'       => api_delete($body),
        'find_by_name' => api_find_by_name($body),
        'search'       => api_search($body),
        default        => fail('Unknown action')
    };
} catch (Throwable $e) {
    fail($e->getMessage());
}

// ── List / Search ─────────────────────────────────────────────
function api_list(array $b): void {
    $q      = trim($b['q'] ?? '');
    $where  = 'WHERE c.id > 1 AND c.is_deleted = 0'; // exclude Walk-in Customer (id=1)
    $params = [];

    if ($q) {
        $where   .= ' AND (c.name LIKE ? OR c.phone LIKE ? OR c.address LIKE ?)';
        $params[] = "%$q%";
        $params[] = "%$q%";
        $params[] = "%$q%";
    }

    $rows = db_rows(
        "SELECT c.*,
                COUNT(s.id)                    AS total_invoices,
                COALESCE(SUM(s.final_price),0) AS total_purchase,
                COALESCE(SUM(s.paid),       0) AS total_paid
           FROM customers c
      LEFT JOIN sales s ON s.customer_id = c.id
         $where
         GROUP BY c.id
         ORDER BY c.name",
        $params
    );
    ok(['data' => $rows]);
}

// ── Detail ────────────────────────────────────────────────────
function api_detail(array $b): void {
    $id = (int)($b['id'] ?? 0);
    if (!$id) fail('Missing id');

    $row = db_row("SELECT * FROM customers WHERE id = ?", [$id]);
    if (!$row) fail('Customer not found');
    ok(['data' => $row]);
}

// ── Add ───────────────────────────────────────────────────────
function api_add(array $b): void {
    $name = trim($b['name'] ?? '');
    if (!$name) fail('Customer name is required');

    // Mobile number is mandatory — Bangladesh format 01XXXXXXXXX
    $phone = trim($b['phone'] ?? '');
    if (!$phone) fail('Mobile number is required to create a customer.');
    if (!preg_match('/^01[0-9]{9}$/', $phone)) {
        fail('Invalid mobile number. Use Bangladesh format: 01XXXXXXXXX (11 digits).');
    }

    $existing = db_row(
        "SELECT id, name FROM customers WHERE phone = ? AND id > 1",
        [$phone]
    );
    if ($existing) fail("Phone already used by customer \"{$existing['name']}\"");

    $due     = max(0, (float)($b['due']     ?? 0));
    $advance = max(0, (float)($b['advance'] ?? 0));

    $id = db_insert(
        "INSERT INTO customers (name, phone, address, due, advance)
         VALUES (?, ?, ?, ?, ?)",
        [
            $name,
            $phone,
            trim($b['address'] ?? ''),
            $due,
            $advance,
        ]
    );

    // Log opening due as a cash_ledger 'out' (you gave credit)
    if ($due > 0) {
        db_run(
            "INSERT INTO cash_ledger
               (date, description, account, type, amount, ref)
             VALUES (?, ?, 'Cash', 'out', ?, ?)",
            [date('Y-m-d'), "Opening due — $name", $due, "CUS-{$id}-OPEN-DUE"]
        );
    }
    // Log opening advance as a cash_ledger 'in' (they paid ahead)
    if ($advance > 0) {
        db_run(
            "INSERT INTO cash_ledger
               (date, description, account, type, amount, ref)
             VALUES (?, ?, 'Cash', 'in', ?, ?)",
            [date('Y-m-d'), "Opening advance — $name", $advance, "CUS-{$id}-OPEN-ADV"]
        );
    }

    ok(['id' => $id, 'msg' => 'Customer added']);
}

// ── Edit ──────────────────────────────────────────────────────
function api_edit(array $b): void {
    $id   = (int)($b['id']   ?? 0);
    $name = trim($b['name']  ?? '');
    if (!$id)   fail('Missing id');
    if (!$name) fail('Customer name is required');
    if ($id === 1) fail('Walk-in Customer cannot be edited');

    $phone = trim($b['phone'] ?? '');
    if (!$phone) fail('Mobile number is required to create a customer.');
    if (!preg_match('/^01[0-9]{9}$/', $phone)) {
        fail('Invalid mobile number. Use Bangladesh format: 01XXXXXXXXX (11 digits).');
    }
    $existing = db_row(
        "SELECT id, name FROM customers WHERE phone = ? AND id != ? AND id > 1",
        [$phone, $id]
    );
    if ($existing) fail("Phone already used by customer \"{$existing['name']}\"");

    db_run(
        "UPDATE customers
            SET name    = ?,
                phone   = ?,
                address = ?
          WHERE id = ?",
        [
            $name,
            $phone,
            trim($b['address'] ?? ''),
            $id,
        ]
    );
    ok(['msg' => 'Customer updated']);
}

// ── Collect Due ───────────────────────────────────────────────
function api_collect(array $b): void {
    $id      = (int)($b['id']      ?? 0);
    $amount  = (float)($b['amount']   ?? 0);
    $account = trim($b['account']  ?? 'Cash');
    $note    = trim($b['note']     ?? '');

    if (!$id)         fail('Missing customer id');
    if ($amount <= 0) fail('Amount must be greater than zero');

    $validAccounts = ['Cash', 'Bank', 'MFS', 'Card'];
    if (!in_array($account, $validAccounts)) fail('Invalid account');

    $cus = db_row("SELECT * FROM customers WHERE id = ?", [$id]);
    if (!$cus) fail('Customer not found');

    $due = (float) $cus['due'];
    if ($due <= 0)           fail('This customer has no outstanding due');
    if ($amount > $due + 0.001) fail("Amount ({$amount}) exceeds due ({$due})");

    db()->beginTransaction();
    try {
        // 1. Reduce customer due
        db_run(
            "UPDATE customers
                SET due = GREATEST(0, due - ?)
              WHERE id = ?",
            [$amount, $id]
        );

        // 2. Add to cash_ledger as income (type = in)
        $ref  = 'CUS-' . $id . '-' . date('YmdHis');
        $desc = "Due collected — {$cus['name']}" . ($note ? " ($note)" : '');
        db_run(
            "INSERT INTO cash_ledger
               (date, description, account, type, amount, ref)
             VALUES (?, ?, ?, 'in', ?, ?)",
            [date('Y-m-d'), $desc, $account, $amount, $ref]
        );

        db()->commit();
        ok(['msg' => 'Due collected successfully', 'ref' => $ref]);

    } catch (Throwable $e) {
        db()->rollBack();
        fail('Collection failed: ' . $e->getMessage());
    }
}

// ── Delete ────────────────────────────────────────────────────
function api_delete(array $b): void {
    require_admin();
    $id  = (int)($b['id'] ?? 0);
    if (!$id) fail('Missing id');

    $rec = db_row("SELECT * FROM `customers` WHERE id = ? AND is_deleted = 0", [$id]);
    if (!$rec) fail('Record not found');

    $uid = current_user_id();
    db_run(
        "UPDATE `customers`
            SET is_deleted = 1,
                deleted_at = NOW(),
                deleted_by = ?
          WHERE id = ?",
        [$uid, $id]
    );

    log_action('delete', 'customers', $id,
        'Soft-deleted: ' . ($rec['name'] ?? '#' . $id));

    ok(['msg' => 'Moved to Recycle Bin']);
}


// ── Helpers ───────────────────────────────────────────────────
// ── Find by name (for autocomplete exact / close match) ───────
// ── Search by name, phone, or ID (for Sales Entry AJAX dropdown) ──
function api_search(array $b): void {
    $q = trim($b['q'] ?? '');
    if (!$q) { ok(['data' => []]); return; }

    $isId   = ctype_digit($q);
    $like   = '%' . $q . '%';

    $rows = db_rows(
        "SELECT id, name, phone, due, advance
           FROM customers
          WHERE is_deleted = 0 AND id > 1
            AND is_active = 1
            AND (
              name  LIKE ?
           OR phone LIKE ?
           " . ($isId ? "OR id = ?" : "") . "
            )
          ORDER BY name ASC
          LIMIT 8",
        $isId ? [$like, $like, (int)$q] : [$like, $like]
    );

    ok(['data' => $rows]);
}

function api_find_by_name(array $b): void {
    $name = trim($b['name'] ?? '');
    if (!$name) fail('Name is required');

    // Exact match first
    $cus = db_row(
        "SELECT id, name, phone, address, due, advance
           FROM customers WHERE is_deleted = 0 AND LOWER(name) = LOWER(?) LIMIT 1",
        [$name]
    );

    // Partial match fallback
    if (!$cus) {
        $cus = db_row(
            "SELECT id, name, phone, address, due, advance
               FROM customers
              WHERE is_deleted = 0 AND name LIKE ? AND id > 1
              ORDER BY name LIMIT 1",
            ['%' . $name . '%']
        );
    }

    if ($cus) ok(['data' => $cus]);
    else ok(['data' => null]); // no match — caller will show new-customer form
}

function ok(array $extra = []): void {
    echo json_encode(array_merge(['success' => true], $extra));
    exit;
}
function fail(string $msg): never {
    echo json_encode(['success' => false, 'error' => $msg]);
    exit;
}
