<?php
ob_start();
require_once __DIR__ . '/../core/auth.php';
require_once __DIR__ . '/../core/response.php';
cors();
$user = Auth::requireToken();
$db   = DB::get();
$act  = $_GET['action'] ?? $_POST['action'] ?? 'list';

try {
    switch ($act) {

        case 'list':
            $search = '%' . ($db->esc($_GET['q'] ?? '')) . '%';
            $rows = $db->select(
                "SELECT c.*, p.province_name, co.country_name
                 FROM clients c
                 LEFT JOIN province p ON p.record_id = c.province_id
                 LEFT JOIN country co ON co.record_id = c.country_id
                 WHERE c.clients_name LIKE ? OR c.email LIKE ? OR c.cell LIKE ?
                 ORDER BY c.clients_name ASC",
                'sss', $search, $search, $search
            );
            json_ok($rows);

        case 'get':
            $id = (int)($_GET['id'] ?? 0);
            $row = $db->selectOne("SELECT * FROM clients WHERE record_id = ?", 'i', $id);
            if (!$row) json_err('Not found', 404);
            json_ok($row);

        case 'add':
            $name     = trim($_POST['clients_name'] ?? '');
            $email    = trim($_POST['email'] ?? '');
            $cell     = trim($_POST['cell'] ?? '');
            $address  = trim($_POST['address'] ?? '');
            $province = (int)($_POST['province_id'] ?? 0);
            $country  = (int)($_POST['country_id'] ?? 1);
            $vat      = trim($_POST['vat'] ?? '0');
            if (!$name) json_err('Client name required');
            $id = $db->execute(
                "INSERT INTO clients (clients_name, email, cell, address, province_id, country_id, vat) VALUES (?,?,?,?,?,?,?)",
                'ssssiis', $name, $email, $cell, $address, $province, $country, $vat
            );
            Auth::log((int)$user['user_id'], "ADDED CLIENT: $name", 'clients', $id);
            json_ok(['id' => $id], 'Client added');

        case 'update':
            $id       = (int)($_POST['id'] ?? 0);
            $name     = trim($_POST['clients_name'] ?? '');
            $email    = trim($_POST['email'] ?? '');
            $cell     = trim($_POST['cell'] ?? '');
            $address  = trim($_POST['address'] ?? '');
            $province = (int)($_POST['province_id'] ?? 0);
            $country  = (int)($_POST['country_id'] ?? 1);
            $vat      = trim($_POST['vat'] ?? '0');
            if (!$id || !$name) json_err('ID and name required');
            $db->execute(
                "UPDATE clients SET clients_name=?, email=?, cell=?, address=?, province_id=?, country_id=?, vat=? WHERE record_id=?",
                'ssssiisi', $name, $email, $cell, $address, $province, $country, $vat, $id
            );
            Auth::log((int)$user['user_id'], "UPDATED CLIENT: $name", 'clients', $id);
            json_ok(null, 'Client updated');

        case 'provinces':
            json_ok($db->select("SELECT * FROM province ORDER BY province_name ASC"));

        case 'countries':
            json_ok($db->select("SELECT * FROM country ORDER BY country_name ASC"));

        case 'statement':
            $id   = (int)($_GET['id'] ?? 0);
            $from = $db->esc($_GET['from'] ?? '');
            $to   = $db->esc($_GET['to']   ?? '');
            $client = $db->selectOne("SELECT * FROM clients WHERE record_id = ?", 'i', $id);
            if (!$client) json_err('Not found', 404);

            $dateWhere = '';
            if ($from) $dateWhere .= " AND date_created >= '$from'";
            if ($to)   $dateWhere .= " AND date_created <= '$to'";

            $invoices = $db->select(
                "SELECT * FROM invoices WHERE clients_id = ? $dateWhere ORDER BY date_created ASC", 'i', $id
            );

            // Attach payments (from income table) to each invoice
            foreach ($invoices as &$inv) {
                $inv['payments'] = $db->select(
                    "SELECT inc.*, a.accounts_name FROM income inc
                     LEFT JOIN accounts a ON a.record_id = inc.accounts_id
                     WHERE inc.invoices_id = ? ORDER BY inc.record_id ASC",
                    'i', (int)$inv['record_id']
                );
                $inv['amount_paid'] = array_reduce($inv['payments'], fn($s, $p) => $s + (float)$p['amount'], 0.0);
            }
            unset($inv);

            json_ok(['client' => $client, 'invoices' => $invoices]);

        default:
            json_err('Unknown action');
    }
} catch (Exception $e) {
    json_err($e->getMessage(), 500);
}