<?php
session_start();

header('Access-Control-Allow-Origin: ' . (isset($_SERVER['HTTP_ORIGIN']) ? $_SERVER['HTTP_ORIGIN'] : '*'));
header('Access-Control-Allow-Credentials: true');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}

require_once 'config.php';
require_once 'totp.php';

try {
    $pdo = new PDO(
        "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
        DB_USER, DB_PASS,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false]
    );
} catch (PDOException $e) {
    header('Content-Type: application/json');
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failed']);
    exit;
}

$method = $_SERVER['REQUEST_METHOD'];
$action = $_GET['action'] ?? '';

// For JSON body (non-file requests)
$raw = file_get_contents('php://input');
$input = json_decode($raw, true) ?? [];



function enrich_buyer_from_client_master(PDO $pdo, array $inv) {
    try {
        $client = null;
        if (!empty($inv['client_id'])) {
            $cs = $pdo->prepare("SELECT * FROM clients WHERE id = ? LIMIT 1");
            $cs->execute([(int)$inv['client_id']]);
            $client = $cs->fetch();
        }
        if (!$client) {
            $buyerName = trim((string)($inv['buyer_name'] ?? ''));
            if ($buyerName !== '') {
                $cs = $pdo->prepare("SELECT * FROM clients WHERE LOWER(TRIM(name)) COLLATE utf8mb4_unicode_ci = LOWER(TRIM(?)) COLLATE utf8mb4_unicode_ci LIMIT 1");
                $cs->execute([$buyerName]);
                $client = $cs->fetch();
            }
        }
        if ($client) {
            if (!empty($client['name'])) $inv['buyer_name'] = $client['name'];
            if (!empty($client['name_ar'])) $inv['buyer_name_ar'] = $client['name_ar'];
            if (!empty($client['cr_number'])) $inv['buyer_cr'] = $client['cr_number'];
            if (!empty($client['vat_number'])) $inv['buyer_vat'] = $client['vat_number'];
            $addrParts = array_filter([trim((string)($client['address'] ?? '')), trim((string)($client['city'] ?? ''))]);
            if ($addrParts) $inv['buyer_address'] = implode(', ', $addrParts);
            $contactParts = [];
            if (!empty($client['contact_person'])) $contactParts[] = $client['contact_person'];
            if (!empty($client['phone'])) $contactParts[] = $client['phone'];
            if ($contactParts) $inv['buyer_contact'] = implode(' · ', $contactParts);
            $inv['client_id'] = (int)$client['id'];
            $inv['buyer_synced_from_client'] = true;
        }
    } catch (Throwable $e) {}
    return $inv;
}

function getUserRole($pdo, $userId) {
    try {
        $stmt = $pdo->prepare("SELECT role FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        $row = $stmt->fetch();
        return $row['role'] ?? 'engineer';
    } catch (Exception $e) {
        return 'engineer';
    }
}

function requireRole($pdo, $userId, $allowed) {
    $role = getUserRole($pdo, $userId);
    if ($role === 'super_admin') return $role;
    if (!in_array($role, (array)$allowed, true)) {
        jsonOut(['error' => 'Permission denied'], 403);
    }
    return $role;
}

function canWriteTodos($role) {
    return in_array($role, ['super_admin', 'engineer', 'sales_manager'], true);
}

function canWritePayments($role) {
    return in_array($role, ['super_admin', 'sales_manager'], true);
}
function canWriteLicensing($role) {
    return in_array($role, ['super_admin', 'sales_manager'], true);
}
function canViewLicensing($role) {
    return in_array($role, ['super_admin', 'sales_manager', 'qc'], true);
}

function requireLogin() {
    if (empty($_SESSION['user_id'])) {
        header('Content-Type: application/json');
        http_response_code(401);
        echo json_encode(['error' => 'Please log in']);
        exit;
    }
    return (int)$_SESSION['user_id'];
}

function jsonOut($data, $code = 200) {
    header('Content-Type: application/json');
    http_response_code($code);
    echo json_encode($data);
    exit;
}

function getNextDue(?string $currentDue, string $repeat): ?string {
    $base = $currentDue ? new DateTime($currentDue) : new DateTime();
    $next = clone $base;
    switch ($repeat) {
        case 'daily':   $next->modify('+1 day'); break;
        case 'weekly':  $next->modify('+7 days'); break;
        case 'monthly':
            $day = (int)$next->format('d');
            $next->modify('first day of next month');
            $lastDay = (int)$next->format('t');
            $next->setDate((int)$next->format('Y'), (int)$next->format('m'), min($day, $lastDay));
            $next->setTime((int)$base->format('H'), (int)$base->format('i'), (int)$base->format('s'));
            break;
        case 'yearly':  $next->modify('+1 year'); break;
        default: return null;
    }
    return $next->format('Y-m-d H:i:s');
}

function handleUpload($fieldName, $prefix = 'file') {
    if (empty($_FILES[$fieldName]) || $_FILES[$fieldName]['error'] === UPLOAD_ERR_NO_FILE) {
        return null;
    }
    $file = $_FILES[$fieldName];
    if ($file['error'] !== UPLOAD_ERR_OK) {
        jsonOut(['error' => 'Upload failed'], 400);
    }
    // Max 5MB
    if ($file['size'] > 2 * 1024 * 1024) {
        jsonOut(['error' => 'File too large (max 2MB)'], 400);
    }
    $finfo = new finfo(FILEINFO_MIME_TYPE);
    $mime = $finfo->file($file['tmp_name']);
    $allowed = [
        'application/pdf' => 'pdf',
        'image/jpeg' => 'jpg',
        'image/jpg' => 'jpg'
    ];
    if (!isset($allowed[$mime])) {
        jsonOut(['error' => 'Only PDF and JPEG files are allowed'], 400);
    }
    $ext = $allowed[$mime];
    $name = $prefix . '_' . time() . '_' . bin2hex(random_bytes(4)) . '.' . $ext;
    $dest = __DIR__ . '/uploads/' . $name;
    if (!move_uploaded_file($file['tmp_name'], $dest)) {
        jsonOut(['error' => 'Failed to save file'], 500);
    }
    return $name;
}

function deleteFile($filename) {
    if ($filename && file_exists(__DIR__ . '/uploads/' . $filename)) {
        @unlink(__DIR__ . '/uploads/' . $filename);
    }
}


function createNotification($pdo, $userId, $type, $title, $body = null, $linkType = null, $linkId = null) {
    try {
        $stmt = $pdo->prepare("INSERT INTO notifications (user_id, type, title, body, link_type, link_id) VALUES (?, ?, ?, ?, ?, ?)");
        $stmt->execute([$userId, $type, $title, $body, $linkType, $linkId]);
    } catch (Exception $e) {}
}

function touchLastSeen($pdo, $userId) {
    try {
        $pdo->prepare("UPDATE users SET last_seen_at = NOW() WHERE id = ?")->execute([$userId]);
    } catch (Exception $e) {}
}

try {
    // ==================== AUTH ====================
    if ($action === 'register' && $method === 'POST') {
        $username = trim($input['username'] ?? '');
        $email    = trim($input['email'] ?? '');
        $password = $input['password'] ?? '';
        if (strlen($username) < 3 || strlen($username) > 50) jsonOut(['error' => 'Username must be 3–50 characters'], 400);
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) jsonOut(['error' => 'Invalid email'], 400);
        if (strlen($password) < 6) jsonOut(['error' => 'Password must be at least 6 characters'], 400);

        $stmt = $pdo->prepare("SELECT id FROM users WHERE username = ? OR email = ?");
        $stmt->execute([$username, $email]);
        if ($stmt->fetch()) jsonOut(['error' => 'Username or email already exists'], 400);

        $hash = password_hash($password, PASSWORD_DEFAULT);
        $stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
        $stmt->execute([$username, $email, $hash]);
        $userId = (int)$pdo->lastInsertId();
        $_SESSION['user_id'] = $userId;
        $_SESSION['username'] = $username;
        jsonOut(['success' => true, 'user' => ['id' => $userId, 'username' => $username]]);
    }

    if ($action === 'login' && $method === 'POST') {
        $login = trim($input['login'] ?? '');
        $password = $input['password'] ?? '';
        $totpCode = trim($input['totp_code'] ?? '');
        if ($login === '' || $password === '') jsonOut(['error' => 'Login and password required'], 400);

        $stmt = $pdo->prepare("SELECT id, username, email, password, totp_secret, totp_enabled, backup_codes FROM users WHERE username = ? OR email = ?");
        $stmt->execute([$login, $login]);
        $user = $stmt->fetch();
        if (!$user || !password_verify($password, $user['password'])) jsonOut(['error' => 'Invalid username/email or password'], 401);

        $totpEnabled = !empty($user['totp_enabled']) && !empty($user['totp_secret']);

        // If 2FA enabled and no code yet → ask for code
        if ($totpEnabled && $totpCode === '') {
            $_SESSION['pending_2fa_user_id'] = (int)$user['id'];
            $_SESSION['pending_2fa_username'] = $user['username'];
            jsonOut(['success' => false, 'requires_2fa' => true, 'message' => 'Enter the 6-digit code from your authenticator app']);
        }

        // If 2FA enabled, verify code
        if ($totpEnabled) {
            $valid = TOTP::verify($user['totp_secret'], $totpCode);
            // Also check backup codes
            if (!$valid && !empty($user['backup_codes'])) {
                $codes = json_decode($user['backup_codes'], true) ?: [];
                $codeUpper = strtoupper(preg_replace('/\s+/', '', $totpCode));
                $idx = array_search($codeUpper, $codes, true);
                if ($idx !== false) {
                    $valid = true;
                    unset($codes[$idx]);
                    $stmt = $pdo->prepare("UPDATE users SET backup_codes = ? WHERE id = ?");
                    $stmt->execute([json_encode(array_values($codes)), $user['id']]);
                }
            }
            if (!$valid) jsonOut(['error' => 'Invalid authentication code'], 401);
            unset($_SESSION['pending_2fa_user_id'], $_SESSION['pending_2fa_username']);
        }

        $_SESSION['user_id'] = (int)$user['id'];
        $_SESSION['username'] = $user['username'];
        $extra = [];
        $role = 'engineer';
        try {
            $st = $pdo->prepare("SELECT role, display_name, avatar FROM users WHERE id = ?");
            $st->execute([(int)$user['id']]);
            $extra = $st->fetch() ?: [];
            $role = $extra['role'] ?? 'engineer';
        } catch (Exception $e) {}
        $_SESSION['role'] = $role;
        jsonOut(['success' => true, 'user' => [
            'id' => (int)$user['id'],
            'username' => $user['username'],
            'display_name' => $extra['display_name'] ?? '',
            'avatar' => $extra['avatar'] ?? null,
            'totp_enabled' => $totpEnabled,
            'role' => $role
        ]]);
    }

    if ($action === 'logout' && $method === 'POST') {
        session_destroy();
        jsonOut(['success' => true]);
    }

    if ($action === 'me' && $method === 'GET') {
        if (empty($_SESSION['user_id'])) jsonOut(['user' => null]);
        $role = 'engineer';
        $row = [];
        try {
            $stmt = $pdo->prepare("SELECT username, email, display_name, avatar, totp_enabled, role FROM users WHERE id = ?");
            $stmt->execute([$_SESSION['user_id']]);
            $row = $stmt->fetch() ?: [];
            $role = $row['role'] ?? 'engineer';
        } catch (Exception $e) {
            try {
                $stmt = $pdo->prepare("SELECT username, email, display_name, avatar, totp_enabled FROM users WHERE id = ?");
                $stmt->execute([$_SESSION['user_id']]);
                $row = $stmt->fetch() ?: [];
            } catch (Exception $e2) {}
        }
        $_SESSION['role'] = $role;
        jsonOut(['user' => [
            'id' => (int)$_SESSION['user_id'],
            'username' => $row['username'] ?? $_SESSION['username'] ?? '',
            'email' => $row['email'] ?? '',
            'display_name' => $row['display_name'] ?? '',
            'avatar' => $row['avatar'] ?? null,
            'totp_enabled' => !empty($row['totp_enabled']),
            'role' => $role
        ]]);
    }

    if ($action === 'profile_update' && $method === 'POST') {
        $userId = requireLogin();
        $displayName = trim($_POST['display_name'] ?? $input['display_name'] ?? '');
        if (strlen($displayName) > 100) jsonOut(['error' => 'Display name too long'], 400);

        $avatar = null;
        $stmt = $pdo->prepare("SELECT avatar FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        $cur = $stmt->fetch();
        $avatar = $cur['avatar'] ?? null;

        if (!empty($_FILES['avatar']) && $_FILES['avatar']['error'] !== UPLOAD_ERR_NO_FILE) {
            // Only images for avatar
            $file = $_FILES['avatar'];
            if ($file['error'] !== UPLOAD_ERR_OK) jsonOut(['error' => 'Upload failed'], 400);
            if ($file['size'] > 2 * 1024 * 1024) jsonOut(['error' => 'File too large (max 2MB)'], 400);
            $finfo = new finfo(FILEINFO_MIME_TYPE);
            $mime = $finfo->file($file['tmp_name']);
            if (!in_array($mime, ['image/jpeg', 'image/jpg', 'image/png'])) jsonOut(['error' => 'Avatar must be JPEG or PNG'], 400);
            $ext = $mime === 'image/png' ? 'png' : 'jpg';
            $name = 'avatar_' . time() . '_' . bin2hex(random_bytes(4)) . '.' . $ext;
            if (!move_uploaded_file($file['tmp_name'], __DIR__ . '/uploads/' . $name)) jsonOut(['error' => 'Failed to save avatar'], 500);
            if ($avatar) deleteFile($avatar);
            $avatar = $name;
        }
        if (isset($_POST['remove_avatar']) && $_POST['remove_avatar'] === '1') {
            if ($avatar) deleteFile($avatar);
            $avatar = null;
        }

        $stmt = $pdo->prepare("UPDATE users SET display_name = ?, avatar = ? WHERE id = ?");
        $stmt->execute([$displayName ?: null, $avatar, $userId]);
        jsonOut(['success' => true, 'display_name' => $displayName, 'avatar' => $avatar]);
    }

    if ($action === 'change_password' && $method === 'POST') {
        $userId = requireLogin();
        $current = $input['current_password'] ?? '';
        $newPass = $input['new_password'] ?? '';
        if (strlen($newPass) < 6) jsonOut(['error' => 'New password must be at least 6 characters'], 400);
        $stmt = $pdo->prepare("SELECT password FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        $user = $stmt->fetch();
        if (!$user || !password_verify($current, $user['password'])) jsonOut(['error' => 'Current password is incorrect'], 401);
        $hash = password_hash($newPass, PASSWORD_DEFAULT);
        $stmt = $pdo->prepare("UPDATE users SET password = ? WHERE id = ?");
        $stmt->execute([$hash, $userId]);
        jsonOut(['success' => true]);
    }

    // ---------- 2FA setup ----------
    if ($action === '2fa_setup' && $method === 'POST') {
        $userId = requireLogin();
        $stmt = $pdo->prepare("SELECT email, username, totp_enabled FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        $user = $stmt->fetch();
        if (!$user) jsonOut(['error' => 'User not found'], 404);
        if (!empty($user['totp_enabled'])) jsonOut(['error' => '2FA is already enabled'], 400);

        $secret = TOTP::generateSecret();
        $_SESSION['pending_totp_secret'] = $secret;
        $email = $user['email'] ?: $user['username'];
        $uri = TOTP::getOtpAuthUri($secret, $email, 'Flowtica');
        jsonOut([
            'secret' => $secret,
            'otpauth_uri' => $uri,
            'qr_url' => 'https://api.qrserver.com/v1/create-qr-code/?size=200x200&data=' . rawurlencode($uri)
        ]);
    }

    if ($action === '2fa_enable' && $method === 'POST') {
        $userId = requireLogin();
        $code = trim($input['code'] ?? '');
        $secret = $_SESSION['pending_totp_secret'] ?? '';
        if ($secret === '') jsonOut(['error' => 'Start setup first'], 400);
        if (!TOTP::verify($secret, $code)) jsonOut(['error' => 'Invalid code. Try again.'], 400);

        $backup = TOTP::generateBackupCodes(8);
        $stmt = $pdo->prepare("UPDATE users SET totp_secret = ?, totp_enabled = 1, backup_codes = ? WHERE id = ?");
        $stmt->execute([$secret, json_encode($backup), $userId]);
        unset($_SESSION['pending_totp_secret']);
        jsonOut(['success' => true, 'backup_codes' => $backup]);
    }

    if ($action === '2fa_disable' && $method === 'POST') {
        $userId = requireLogin();
        $password = $input['password'] ?? '';
        $code = trim($input['code'] ?? '');
        $stmt = $pdo->prepare("SELECT password, totp_secret, totp_enabled FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        $user = $stmt->fetch();
        if (!$user || !password_verify($password, $user['password'])) jsonOut(['error' => 'Invalid password'], 401);
        if (!empty($user['totp_enabled']) && !TOTP::verify($user['totp_secret'], $code)) {
            jsonOut(['error' => 'Invalid authenticator code'], 401);
        }
        $stmt = $pdo->prepare("UPDATE users SET totp_secret = NULL, totp_enabled = 0, backup_codes = NULL WHERE id = ?");
        $stmt->execute([$userId]);
        jsonOut(['success' => true]);
    }

    if ($action === '2fa_status' && $method === 'GET') {
        $userId = requireLogin();
        $stmt = $pdo->prepare("SELECT totp_enabled FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        $row = $stmt->fetch();
        jsonOut(['totp_enabled' => !empty($row['totp_enabled'])]);
    }

    // ==================== DASHBOARD ====================
    if ($action === 'dashboard' && $method === 'GET') {
        $userId = requireLogin();

        $stmt = $pdo->prepare("SELECT 
            SUM(CASE WHEN completed = 0 THEN 1 ELSE 0 END) as active,
            SUM(CASE WHEN completed = 0 AND due IS NOT NULL AND due < NOW() THEN 1 ELSE 0 END) as overdue
            FROM todos WHERE user_id = ?");
        $stmt->execute([$userId]);
        $todoStats = $stmt->fetch();

        $stmt = $pdo->prepare("SELECT 
            SUM(CASE WHEN (amount - received_amount) > 0 THEN 1 ELSE 0 END) as pending_count,
            SUM(GREATEST(amount - received_amount, 0)) as pending_amount,
            SUM(CASE WHEN (amount - received_amount) > 0 AND due_on < CURDATE() THEN 1 ELSE 0 END) as overdue_count,
            SUM(CASE WHEN (amount - received_amount) > 0 AND due_on < CURDATE() THEN GREATEST(amount - received_amount, 0) ELSE 0 END) as overdue_amount,
            COALESCE(SUM(amount), 0) as total_receivable,
            COALESCE(SUM(received_amount), 0) as total_received
            FROM payments WHERE user_id = ?");
        $stmt->execute([$userId]);
        $payStats = $stmt->fetch();

        jsonOut([
            'todos' => [
                'active' => (int)($todoStats['active'] ?? 0),
                'overdue' => (int)($todoStats['overdue'] ?? 0)
            ],
            'payments' => [
                'pending_count' => (int)($payStats['pending_count'] ?? 0),
                'pending_amount' => (float)($payStats['pending_amount'] ?? 0),
                'overdue_count' => (int)($payStats['overdue_count'] ?? 0),
                'overdue_amount' => (float)($payStats['overdue_amount'] ?? 0),
                'total_receivable' => (float)($payStats['total_receivable'] ?? 0),
                'total_received' => (float)($payStats['total_received'] ?? 0)
            ]
        ]);
    }

    // ==================== TODOS ====================
    if ($action === 'todos') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);

        if ($method === 'GET') {
            // Own tasks + tasks assigned to me
            $stmt = $pdo->prepare("SELECT t.*, 
                u.username as owner_username, u.display_name as owner_display,
                a.username as assignee_username, a.display_name as assignee_display
              FROM todos t
              LEFT JOIN users u ON u.id = t.user_id
              LEFT JOIN users a ON a.id = t.assigned_to
              WHERE t.user_id = ? OR t.assigned_to = ?
              ORDER BY t.completed ASC, FIELD(t.priority,'high','medium','low'), t.due IS NULL, t.due ASC, t.id DESC");
            $stmt->execute([$userId, $userId]);
            $rows = $stmt->fetchAll();
            $result = array_map(function ($r) use ($userId) {
                return [
                    'id' => (int)$r['id'],
                    'text' => $r['text'],
                    'completed' => (bool)$r['completed'],
                    'due' => $r['due'] ? str_replace(' ', 'T', substr($r['due'], 0, 16)) : null,
                    'repeat' => $r['repeat_type'],
                    'priority' => $r['priority'] ?? 'medium',
                    'category' => $r['category'],
                    'notes' => $r['notes'],
                    'attachment' => $r['attachment'],
                    'user_id' => (int)$r['user_id'],
                    'assigned_to' => $r['assigned_to'] ? (int)$r['assigned_to'] : null,
                    'is_owner' => (int)$r['user_id'] === $userId,
                    'is_assignee' => $r['assigned_to'] && (int)$r['assigned_to'] === $userId,
                    'owner_name' => $r['owner_display'] ?: $r['owner_username'],
                    'assignee_name' => $r['assignee_display'] ?: $r['assignee_username']
                ];
            }, $rows);
            jsonOut($result);
        }

        // Create (supports file upload via FormData) — skip when FormData update
        if ($method === 'POST' && (!isset($_POST['_method']) || $_POST['_method'] !== 'PUT')) {
            if (!canWriteTodos($role)) jsonOut(['error' => 'Permission denied'], 403);
            // Can come as FormData or JSON
            $text = trim($_POST['text'] ?? $input['text'] ?? '');
            if ($text === '') jsonOut(['error' => 'Text is required'], 400);

            $due = !empty($_POST['due'] ?? $input['due'] ?? null) ? str_replace('T', ' ', ($_POST['due'] ?? $input['due'])) . ':00' : null;
            $repeat = in_array($_POST['repeat'] ?? $input['repeat'] ?? null, ['daily','weekly','monthly','yearly']) ? ($_POST['repeat'] ?? $input['repeat']) : null;
            $priority = in_array($_POST['priority'] ?? $input['priority'] ?? 'medium', ['low','medium','high']) ? ($_POST['priority'] ?? $input['priority'] ?? 'medium') : 'medium';
            $category = trim($_POST['category'] ?? $input['category'] ?? '') ?: null;
            $notes = trim($_POST['notes'] ?? $input['notes'] ?? '') ?: null;

            $attachment = handleUpload('attachment', 'todo');
            $assignedTo = (int)($_POST['assigned_to'] ?? $input['assigned_to'] ?? 0) ?: null;
            if ($assignedTo) {
                $chk = $pdo->prepare("SELECT id FROM users WHERE id = ?");
                $chk->execute([$assignedTo]);
                if (!$chk->fetch()) jsonOut(['error' => 'Assignee not found'], 400);
                if ($assignedTo === $userId) $assignedTo = null;
            }

            $stmt = $pdo->prepare("INSERT INTO todos (user_id, assigned_to, text, completed, due, repeat_type, priority, category, notes, attachment) VALUES (?, ?, ?, 0, ?, ?, ?, ?, ?, ?)");
            $stmt->execute([$userId, $assignedTo, $text, $due, $repeat, $priority, $category, $notes, $attachment]);
            $id = (int)$pdo->lastInsertId();

            $assigneeName = null;
            if ($assignedTo) {
                $s = $pdo->prepare("SELECT username, display_name FROM users WHERE id = ?");
                $s->execute([$assignedTo]);
                $a = $s->fetch();
                $assigneeName = $a ? ($a['display_name'] ?: $a['username']) : null;
            }

            if ($assignedTo) {
                $from = $_SESSION['username'] ?? 'Someone';
                createNotification($pdo, $assignedTo, 'task_assigned', 'New task assigned to you', $text, 'todo', $id);
            }

            jsonOut([
                'id' => $id, 'text' => $text, 'completed' => false,
                'due' => $_POST['due'] ?? $input['due'] ?? null,
                'repeat' => $repeat, 'priority' => $priority,
                'category' => $category, 'notes' => $notes, 'attachment' => $attachment,
                'user_id' => $userId, 'assigned_to' => $assignedTo,
                'is_owner' => true, 'is_assignee' => false,
                'assignee_name' => $assigneeName, 'owner_name' => null
            ]);
        }

        // Update
        if ($method === 'POST' && isset($_POST['_method']) && $_POST['_method'] === 'PUT') {
            if (!canWriteTodos($role)) jsonOut(['error' => 'Permission denied'], 403);
            // FormData update (with possible new file)
            $id = (int)($_POST['id'] ?? 0);
            $stmt = $pdo->prepare("SELECT * FROM todos WHERE id = ? AND (user_id = ? OR assigned_to = ?)");
            $stmt->execute([$id, $userId, $userId]);
            $todo = $stmt->fetch();
            if (!$todo) jsonOut(['error' => 'Todo not found'], 404);
            $isOwner = (int)$todo['user_id'] === $userId;

            $text = trim($_POST['text'] ?? $todo['text']);
            $completed = isset($_POST['completed']) ? (int)(bool)$_POST['completed'] : (int)$todo['completed'];
            $due = array_key_exists('due', $_POST) ? (!empty($_POST['due']) ? str_replace('T', ' ', $_POST['due']) . ':00' : null) : $todo['due'];
            $repeat = array_key_exists('repeat', $_POST) ? (in_array($_POST['repeat'], ['daily','weekly','monthly','yearly']) ? $_POST['repeat'] : null) : $todo['repeat_type'];
            $priority = in_array($_POST['priority'] ?? $todo['priority'], ['low','medium','high']) ? ($_POST['priority'] ?? $todo['priority']) : 'medium';
            $category = array_key_exists('category', $_POST) ? (trim($_POST['category']) ?: null) : $todo['category'];
            $notes = array_key_exists('notes', $_POST) ? (trim($_POST['notes']) ?: null) : $todo['notes'];

            $attachment = $todo['attachment'];
            if (!empty($_FILES['attachment']) && $_FILES['attachment']['error'] !== UPLOAD_ERR_NO_FILE) {
                deleteFile($todo['attachment']);
                $attachment = handleUpload('attachment', 'todo');
            }
            if (isset($_POST['remove_attachment']) && $_POST['remove_attachment'] === '1') {
                deleteFile($todo['attachment']);
                $attachment = null;
            }

            $assignedTo = $todo['assigned_to'];
            if ($isOwner && array_key_exists('assigned_to', $_POST)) {
                $assignedTo = (int)$_POST['assigned_to'] ?: null;
                if ($assignedTo === $userId) $assignedTo = null;
            }
            $prevAssigned = $todo['assigned_to'] ? (int)$todo['assigned_to'] : null;
            $stmt = $pdo->prepare("UPDATE todos SET text=?, completed=?, due=?, repeat_type=?, priority=?, category=?, notes=?, attachment=?, assigned_to=? WHERE id=? AND (user_id=? OR assigned_to=?)");
            $stmt->execute([$text, $completed, $due, $repeat, $priority, $category, $notes, $attachment, $assignedTo, $id, $userId, $userId]);
            if ($isOwner && $assignedTo && $assignedTo !== $prevAssigned) {
                createNotification($pdo, $assignedTo, 'task_assigned', 'New task assigned to you', $text, 'todo', $id);
            }
            if (!$todo['completed'] && $completed === 1 && !empty($todo['assigned_to']) && (int)$todo['assigned_to'] === $userId && (int)$todo['user_id'] !== $userId) {
                createNotification($pdo, (int)$todo['user_id'], 'task_completed', 'Assigned task completed', $todo['text'], 'todo', $id);
            }

            // Recurring generate next
            $newTodo = null;
            if (!$todo['completed'] && $completed === 1 && $todo['repeat_type']) {
                $nextDue = getNextDue($todo['due'], $todo['repeat_type']);
                $stmt = $pdo->prepare("INSERT INTO todos (user_id, text, completed, due, repeat_type, priority, category, notes) VALUES (?, ?, 0, ?, ?, ?, ?, ?)");
                $stmt->execute([$userId, $todo['text'], $nextDue, $todo['repeat_type'], $todo['priority'], $todo['category'], $todo['notes']]);
                $newId = (int)$pdo->lastInsertId();
                $newTodo = [
                    'id' => $newId, 'text' => $todo['text'], 'completed' => false,
                    'due' => $nextDue ? str_replace(' ', 'T', substr($nextDue, 0, 16)) : null,
                    'repeat' => $todo['repeat_type'], 'priority' => $todo['priority'],
                    'category' => $todo['category'], 'notes' => $todo['notes'], 'attachment' => null
                ];
            }

            jsonOut([
                'id' => $id, 'text' => $text, 'completed' => (bool)$completed,
                'due' => $due ? str_replace(' ', 'T', substr($due, 0, 16)) : null,
                'repeat' => $repeat, 'priority' => $priority,
                'category' => $category, 'notes' => $notes, 'attachment' => $attachment,
                'newTodo' => $newTodo
            ]);
        }

        // JSON PUT (toggle complete without file)
        if ($method === 'PUT') {
            if (!canWriteTodos($role)) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? 0);
            $stmt = $pdo->prepare("SELECT * FROM todos WHERE id = ? AND (user_id = ? OR assigned_to = ?)");
            $stmt->execute([$id, $userId, $userId]);
            $todo = $stmt->fetch();
            if (!$todo) jsonOut(['error' => 'Todo not found'], 404);

            $completed = isset($input['completed']) ? (int)(bool)$input['completed'] : (int)$todo['completed'];
            $stmt = $pdo->prepare("UPDATE todos SET completed = ? WHERE id = ? AND (user_id = ? OR assigned_to = ?)");
            $stmt->execute([$completed, $id, $userId, $userId]);

            // Notify owner when assignee completes
            if (!$todo['completed'] && $completed === 1 && !empty($todo['assigned_to']) && (int)$todo['assigned_to'] === $userId && (int)$todo['user_id'] !== $userId) {
                createNotification($pdo, (int)$todo['user_id'], 'task_completed', 'Assigned task completed', $todo['text'], 'todo', $id);
            }
            // Notify assignee when someone assigns via... handled on create/update

            $newTodo = null;
            if (!$todo['completed'] && $completed === 1 && $todo['repeat_type']) {
                $nextDue = getNextDue($todo['due'], $todo['repeat_type']);
                $stmt = $pdo->prepare("INSERT INTO todos (user_id, text, completed, due, repeat_type, priority, category, notes) VALUES (?, ?, 0, ?, ?, ?, ?, ?)");
                $stmt->execute([$userId, $todo['text'], $nextDue, $todo['repeat_type'], $todo['priority'], $todo['category'], $todo['notes']]);
                $newId = (int)$pdo->lastInsertId();
                $newTodo = [
                    'id' => $newId, 'text' => $todo['text'], 'completed' => false,
                    'due' => $nextDue ? str_replace(' ', 'T', substr($nextDue, 0, 16)) : null,
                    'repeat' => $todo['repeat_type'], 'priority' => $todo['priority'],
                    'category' => $todo['category'], 'notes' => $todo['notes'], 'attachment' => null
                ];
            }

            jsonOut([
                'id' => $id, 'completed' => (bool)$completed, 'newTodo' => $newTodo
            ]);
        }

        if ($method === 'DELETE') {
            if (!canWriteTodos($role)) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? 0);
            $stmt = $pdo->prepare("SELECT attachment FROM todos WHERE id = ? AND user_id = ?");
            $stmt->execute([$id, $userId]);
            $row = $stmt->fetch();
            if ($row) deleteFile($row['attachment']);

            $stmt = $pdo->prepare("DELETE FROM todos WHERE id = ? AND user_id = ?");
            $stmt->execute([$id, $userId]);
            jsonOut(['success' => true]);
        }
    }

    // ==================== PAYMENTS ====================
    if ($action === 'payments') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        try { $pdo->exec("ALTER TABLE payments ADD COLUMN client_id INT DEFAULT NULL"); } catch (Throwable $e) {}

        if ($method === 'GET') {
            $stmt = $pdo->prepare("SELECT * FROM payments WHERE user_id = ? ORDER BY (amount - received_amount) > 0 DESC, due_on ASC, id DESC");
            $stmt->execute([$userId]);
            $rows = $stmt->fetchAll();
            $result = array_map(function ($r) {
                $remaining = max(0, (float)$r['amount'] - (float)$r['received_amount']);
                $status = 'paid';
                if ($remaining > 0) {
                    $status = ($r['due_on'] < date('Y-m-d')) ? 'overdue' : (($r['received_amount'] > 0) ? 'partial' : 'pending');
                }
                return [
                    'id' => (int)$r['id'],
                    'client_id' => isset($r['client_id']) && $r['client_id'] !== null ? (int)$r['client_id'] : null,
                    'client_name' => $r['client_name'],
                    'amount' => (float)$r['amount'],
                    'received_amount' => (float)$r['received_amount'],
                    'remaining' => $remaining,
                    'project_name' => $r['project_name'],
                    'contact_number' => $r['contact_number'],
                    'due_on' => $r['due_on'],
                    'payment_method' => $r['payment_method'],
                    'notes' => $r['notes'],
                    'receipt' => $r['receipt'],
                    'status' => $status,
                    'paid' => $remaining <= 0
                ];
            }, $rows);
            jsonOut($result);
        }

        if ($method === 'POST' && empty($_POST['_method'])) {
            if (!canWritePayments($role)) jsonOut(['error' => 'Permission denied'], 403);
            $client = trim($_POST['client_name'] ?? $input['client_name'] ?? '');
            $clientId = isset($_POST['client_id']) || isset($input['client_id'])
                ? (int)($_POST['client_id'] ?? $input['client_id'] ?? 0) : 0;
            if ($clientId <= 0) $clientId = null;
            // Resolve name from master if id given
            if ($clientId) {
                $cs = $pdo->prepare("SELECT name, phone FROM clients WHERE id = ?");
                $cs->execute([$clientId]);
                $crow = $cs->fetch();
                if ($crow) {
                    if ($client === '') $client = $crow['name'];
                } else {
                    $clientId = null;
                }
            }
            $amount = floatval($_POST['amount'] ?? $input['amount'] ?? 0);
            $received = floatval($_POST['received_amount'] ?? $input['received_amount'] ?? 0);
            $project = trim($_POST['project_name'] ?? $input['project_name'] ?? '');
            $contact = trim($_POST['contact_number'] ?? $input['contact_number'] ?? '');
            $due_on = $_POST['due_on'] ?? $input['due_on'] ?? '';
            $methodPay = in_array($_POST['payment_method'] ?? $input['payment_method'] ?? null, ['cash','bank','transfer']) ? ($_POST['payment_method'] ?? $input['payment_method']) : null;
            $notes = trim($_POST['notes'] ?? $input['notes'] ?? '') ?: null;

            if ($client === '' || $project === '' || $due_on === '' || $amount <= 0) {
                jsonOut(['error' => 'Client name, amount, project name and due date are required'], 400);
            }
            if ($received < 0) $received = 0;
            if ($received > $amount) $received = $amount;

            $receipt = handleUpload('receipt', 'receipt');

            $stmt = $pdo->prepare("INSERT INTO payments (user_id, client_id, client_name, amount, received_amount, project_name, contact_number, due_on, payment_method, notes, receipt) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->execute([$userId, $clientId, $client, $amount, $received, $project, $contact ?: null, $due_on, $methodPay, $notes, $receipt]);
            $id = (int)$pdo->lastInsertId();

            $remaining = max(0, $amount - $received);
            $status = $remaining <= 0 ? 'paid' : (($due_on < date('Y-m-d')) ? 'overdue' : ($received > 0 ? 'partial' : 'pending'));

            jsonOut([
                'id' => $id, 'client_id' => $clientId, 'client_name' => $client, 'amount' => $amount,
                'received_amount' => $received, 'remaining' => $remaining,
                'project_name' => $project, 'contact_number' => $contact,
                'due_on' => $due_on, 'payment_method' => $methodPay,
                'notes' => $notes, 'receipt' => $receipt, 'status' => $status, 'paid' => $remaining <= 0
            ]);
        }

        // Update (FormData for possible receipt)
        if ($method === 'POST' && isset($_POST['_method']) && $_POST['_method'] === 'PUT') {
            if (!canWritePayments($role)) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($_POST['id'] ?? 0);
            $stmt = $pdo->prepare("SELECT * FROM payments WHERE id = ? AND user_id = ?");
            $stmt->execute([$id, $userId]);
            $pay = $stmt->fetch();
            if (!$pay) jsonOut(['error' => 'Payment not found'], 404);

            $client = trim($_POST['client_name'] ?? $pay['client_name']);
            $clientId = array_key_exists('client_id', $_POST)
                ? ((int)$_POST['client_id'] ?: null)
                : (isset($pay['client_id']) ? $pay['client_id'] : null);
            if ($clientId) {
                $cs = $pdo->prepare("SELECT name FROM clients WHERE id = ?");
                $cs->execute([(int)$clientId]);
                $crow = $cs->fetch();
                if ($crow && $client === '') $client = $crow['name'];
                if (!$crow) $clientId = null;
            }
            $amount = isset($_POST['amount']) ? floatval($_POST['amount']) : (float)$pay['amount'];
            $received = isset($_POST['received_amount']) ? floatval($_POST['received_amount']) : (float)$pay['received_amount'];
            $project = trim($_POST['project_name'] ?? $pay['project_name']);
            $contact = array_key_exists('contact_number', $_POST) ? trim($_POST['contact_number']) : $pay['contact_number'];
            $due_on = $_POST['due_on'] ?? $pay['due_on'];
            $methodPay = array_key_exists('payment_method', $_POST) ? (in_array($_POST['payment_method'], ['cash','bank','transfer']) ? $_POST['payment_method'] : null) : $pay['payment_method'];
            $notes = array_key_exists('notes', $_POST) ? (trim($_POST['notes']) ?: null) : $pay['notes'];

            if ($received < 0) $received = 0;
            if ($received > $amount) $received = $amount;

            $receipt = $pay['receipt'];
            if (!empty($_FILES['receipt']) && $_FILES['receipt']['error'] !== UPLOAD_ERR_NO_FILE) {
                deleteFile($pay['receipt']);
                $receipt = handleUpload('receipt', 'receipt');
            }
            if (isset($_POST['remove_receipt']) && $_POST['remove_receipt'] === '1') {
                deleteFile($pay['receipt']);
                $receipt = null;
            }

            $stmt = $pdo->prepare("UPDATE payments SET client_id=?, client_name=?, amount=?, received_amount=?, project_name=?, contact_number=?, due_on=?, payment_method=?, notes=?, receipt=? WHERE id=? AND user_id=?");
            $stmt->execute([$clientId, $client, $amount, $received, $project, $contact ?: null, $due_on, $methodPay, $notes, $receipt, $id, $userId]);

            $remaining = max(0, $amount - $received);
            $status = $remaining <= 0 ? 'paid' : (($due_on < date('Y-m-d')) ? 'overdue' : ($received > 0 ? 'partial' : 'pending'));

            jsonOut([
                'id' => $id, 'client_name' => $client, 'amount' => $amount,
                'received_amount' => $received, 'remaining' => $remaining,
                'project_name' => $project, 'contact_number' => $contact,
                'due_on' => $due_on, 'payment_method' => $methodPay,
                'notes' => $notes, 'receipt' => $receipt, 'status' => $status, 'paid' => $remaining <= 0
            ]);
        }

        // Quick mark paid / update received via JSON
        if ($method === 'PUT') {
            $id = (int)($input['id'] ?? 0);
            $stmt = $pdo->prepare("SELECT * FROM payments WHERE id = ? AND user_id = ?");
            $stmt->execute([$id, $userId]);
            $pay = $stmt->fetch();
            if (!$pay) jsonOut(['error' => 'Payment not found'], 404);

            $received = isset($input['received_amount']) ? floatval($input['received_amount']) : (float)$pay['received_amount'];
            if (isset($input['mark_paid']) && $input['mark_paid']) {
                $received = (float)$pay['amount'];
            }
            if ($received < 0) $received = 0;
            if ($received > (float)$pay['amount']) $received = (float)$pay['amount'];

            $stmt = $pdo->prepare("UPDATE payments SET received_amount = ? WHERE id = ? AND user_id = ?");
            $stmt->execute([$received, $id, $userId]);

            $remaining = max(0, (float)$pay['amount'] - $received);
            $status = $remaining <= 0 ? 'paid' : (($pay['due_on'] < date('Y-m-d')) ? 'overdue' : ($received > 0 ? 'partial' : 'pending'));

            jsonOut([
                'id' => $id,
                'received_amount' => $received,
                'remaining' => $remaining,
                'status' => $status,
                'paid' => $remaining <= 0
            ]);
        }

        if ($method === 'DELETE') {
            if (!canWritePayments($role)) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? 0);
            $stmt = $pdo->prepare("SELECT receipt FROM payments WHERE id = ? AND user_id = ?");
            $stmt->execute([$id, $userId]);
            $row = $stmt->fetch();
            if ($row) deleteFile($row['receipt']);

            $stmt = $pdo->prepare("DELETE FROM payments WHERE id = ? AND user_id = ?");
            $stmt->execute([$id, $userId]);
            jsonOut(['success' => true]);
        }
    }



    // ==================== CHAT ====================
    

    if ($action === 'chat_users' && $method === 'GET') {
        $userId = requireLogin();
        touchLastSeen($pdo, $userId);
        $stmt = $pdo->prepare("SELECT id, username, display_name, avatar, last_seen_at FROM users WHERE id != ? ORDER BY username ASC");
        $stmt->execute([$userId]);
        $users = $stmt->fetchAll();
        $stmt = $pdo->prepare("SELECT sender_id, COUNT(*) as cnt FROM messages WHERE receiver_id = ? AND is_read = 0 AND is_deleted = 0 GROUP BY sender_id");
        $stmt->execute([$userId]);
        $unread = [];
        foreach ($stmt->fetchAll() as $r) $unread[(int)$r['sender_id']] = (int)$r['cnt'];
        $result = array_map(function($u) use ($unread) {
            return [
                'id' => (int)$u['id'],
                'username' => $u['username'],
                'display_name' => $u['display_name'] ?: $u['username'],
                'avatar' => $u['avatar'],
                'unread' => $unread[(int)$u['id']] ?? 0,
                'last_seen_at' => $u['last_seen_at']
            ];
        }, $users);
        jsonOut($result);
    }

    if ($action === 'chat_messages' && $method === 'GET') {
        $userId = requireLogin();
        touchLastSeen($pdo, $userId);
        $peerId = (int)($_GET['peer_id'] ?? 0);
        $afterId = (int)($_GET['after_id'] ?? 0);
        if ($peerId <= 0) jsonOut(['error' => 'peer_id required'], 400);

        if ($afterId > 0) {
            $stmt = $pdo->prepare("SELECT * FROM messages WHERE id > ? AND ((sender_id = ? AND receiver_id = ?) OR (sender_id = ? AND receiver_id = ?)) ORDER BY id ASC");
            $stmt->execute([$afterId, $userId, $peerId, $peerId, $userId]);
        } else {
            $stmt = $pdo->prepare("SELECT * FROM messages WHERE (sender_id = ? AND receiver_id = ?) OR (sender_id = ? AND receiver_id = ?) ORDER BY id ASC LIMIT 200");
            $stmt->execute([$userId, $peerId, $peerId, $userId]);
        }
        $rows = $stmt->fetchAll();

        $stmt = $pdo->prepare("UPDATE messages SET is_read = 1 WHERE sender_id = ? AND receiver_id = ? AND is_read = 0");
        $stmt->execute([$peerId, $userId]);

        // Peer last seen
        $stmt = $pdo->prepare("SELECT last_seen_at FROM users WHERE id = ?");
        $stmt->execute([$peerId]);
        $peer = $stmt->fetch();

        // Typing?
        $typing = false;
        try {
            $stmt = $pdo->prepare("SELECT updated_at FROM chat_typing WHERE user_id = ? AND peer_id = ? AND updated_at > (NOW() - INTERVAL 5 SECOND)");
            $stmt->execute([$peerId, $userId]);
            $typing = (bool)$stmt->fetch();
        } catch (Exception $e) {}

        $result = array_map(function($r) use ($userId) {
            return [
                'id' => (int)$r['id'],
                'body' => !empty($r['is_deleted']) ? '' : $r['body'],
                'from_me' => (int)$r['sender_id'] === $userId,
                'created_at' => $r['created_at'],
                'is_read' => (bool)$r['is_read'],
                'is_deleted' => !empty($r['is_deleted']),
                'attachment' => !empty($r['is_deleted']) ? null : ($r['attachment'] ?? null)
            ];
        }, $rows);
        jsonOut([
            'messages' => $result,
            'peer_last_seen' => $peer['last_seen_at'] ?? null,
            'peer_typing' => $typing
        ]);
    }

    if ($action === 'chat_send' && $method === 'POST') {
        $userId = requireLogin();
        touchLastSeen($pdo, $userId);
        // Support JSON or FormData
        $peerId = (int)($_POST['peer_id'] ?? $input['peer_id'] ?? 0);
        $body = trim($_POST['body'] ?? $input['body'] ?? '');
        if ($peerId <= 0) jsonOut(['error' => 'peer_id required'], 400);
        if ($body === '' && empty($_FILES['attachment'])) jsonOut(['error' => 'Message or attachment required'], 400);
        if (strlen($body) > 2000) jsonOut(['error' => 'Message too long (max 2000)'], 400);

        $stmt = $pdo->prepare("SELECT id FROM users WHERE id = ?");
        $stmt->execute([$peerId]);
        if (!$stmt->fetch()) jsonOut(['error' => 'User not found'], 404);

        $attachment = null;
        if (!empty($_FILES['attachment'])) {
            $attachment = handleUpload('attachment', 'chat');
        }

        $stmt = $pdo->prepare("INSERT INTO messages (sender_id, receiver_id, body, attachment) VALUES (?, ?, ?, ?)");
        $stmt->execute([$userId, $peerId, $body ?: '', $attachment]);
        $id = (int)$pdo->lastInsertId();

        // Clear typing
        try { $pdo->prepare("DELETE FROM chat_typing WHERE user_id = ? AND peer_id = ?")->execute([$userId, $peerId]); } catch (Exception $e) {}

        jsonOut([
            'id' => $id,
            'body' => $body,
            'from_me' => true,
            'created_at' => date('Y-m-d H:i:s'),
            'is_read' => false,
            'is_deleted' => false,
            'attachment' => $attachment
        ]);
    }

    if ($action === 'chat_delete' && $method === 'POST') {
        $userId = requireLogin();
        $msgId = (int)($input['id'] ?? 0);
        $stmt = $pdo->prepare("SELECT * FROM messages WHERE id = ? AND sender_id = ?");
        $stmt->execute([$msgId, $userId]);
        $msg = $stmt->fetch();
        if (!$msg) jsonOut(['error' => 'Message not found'], 404);
        if (!empty($msg['attachment'])) deleteFile($msg['attachment']);
        $stmt = $pdo->prepare("UPDATE messages SET is_deleted = 1, body = '', attachment = NULL WHERE id = ?");
        $stmt->execute([$msgId]);
        jsonOut(['success' => true, 'id' => $msgId]);
    }

    if ($action === 'chat_typing' && $method === 'POST') {
        $userId = requireLogin();
        $peerId = (int)($input['peer_id'] ?? 0);
        if ($peerId <= 0) jsonOut(['error' => 'peer_id required'], 400);
        try {
            $stmt = $pdo->prepare("INSERT INTO chat_typing (user_id, peer_id, updated_at) VALUES (?, ?, NOW()) ON DUPLICATE KEY UPDATE updated_at = NOW()");
            $stmt->execute([$userId, $peerId]);
        } catch (Exception $e) {}
        touchLastSeen($pdo, $userId);
        jsonOut(['success' => true]);
    }

    if ($action === 'chat_unread' && $method === 'GET') {
        $userId = requireLogin();
        touchLastSeen($pdo, $userId);
        $stmt = $pdo->prepare("SELECT COUNT(*) as cnt FROM messages WHERE receiver_id = ? AND is_read = 0 AND is_deleted = 0");
        $stmt->execute([$userId]);
        $row = $stmt->fetch();
        jsonOut(['unread' => (int)($row['cnt'] ?? 0)]);
    }


    // ==================== NOTIFICATIONS ====================
    if ($action === 'notifications' && $method === 'GET') {
        $userId = requireLogin();
        $stmt = $pdo->prepare("SELECT * FROM notifications WHERE user_id = ? ORDER BY created_at DESC LIMIT 50");
        $stmt->execute([$userId]);
        $rows = $stmt->fetchAll();
        $result = array_map(function($r) {
            return [
                'id' => (int)$r['id'],
                'type' => $r['type'],
                'title' => $r['title'],
                'body' => $r['body'],
                'link_type' => $r['link_type'],
                'link_id' => $r['link_id'] ? (int)$r['link_id'] : null,
                'is_read' => (bool)$r['is_read'],
                'created_at' => $r['created_at']
            ];
        }, $rows);
        jsonOut($result);
    }

    if ($action === 'notifications_unread' && $method === 'GET') {
        $userId = requireLogin();
        $stmt = $pdo->prepare("SELECT COUNT(*) as cnt FROM notifications WHERE user_id = ? AND is_read = 0");
        $stmt->execute([$userId]);
        jsonOut(['unread' => (int)($stmt->fetch()['cnt'] ?? 0)]);
    }

    if ($action === 'notifications_read' && $method === 'POST') {
        $userId = requireLogin();
        $id = (int)($input['id'] ?? 0);
        if ($id > 0) {
            $stmt = $pdo->prepare("UPDATE notifications SET is_read = 1 WHERE id = ? AND user_id = ?");
            $stmt->execute([$id, $userId]);
        } else {
            $stmt = $pdo->prepare("UPDATE notifications SET is_read = 1 WHERE user_id = ?");
            $stmt->execute([$userId]);
        }
        jsonOut(['success' => true]);
    }



    // ==================== ADMIN / ROLES ====================
    if ($action === 'admin_users' && $method === 'GET') {
        $userId = requireLogin();
        requireRole($pdo, $userId, ['super_admin']);
        $stmt = $pdo->query("SELECT id, username, email, display_name, role, created_at FROM users ORDER BY id ASC");
        $rows = $stmt->fetchAll();
        jsonOut(array_map(function($r) {
            return [
                'id' => (int)$r['id'],
                'username' => $r['username'],
                'email' => $r['email'],
                'display_name' => $r['display_name'],
                'role' => $r['role'] ?? 'engineer',
                'created_at' => $r['created_at']
            ];
        }, $rows));
    }

    if ($action === 'admin_set_role' && $method === 'POST') {
        $userId = requireLogin();
        requireRole($pdo, $userId, ['super_admin']);
        $targetId = (int)($input['user_id'] ?? 0);
        $newRole = $input['role'] ?? '';
        $allowedRoles = ['super_admin', 'engineer', 'sales_manager', 'qc'];
        if (!in_array($newRole, $allowedRoles, true)) jsonOut(['error' => 'Invalid role'], 400);
        if ($targetId <= 0) jsonOut(['error' => 'user_id required'], 400);
        // Prevent removing last super_admin
        if ($newRole !== 'super_admin') {
            $stmt = $pdo->prepare("SELECT role FROM users WHERE id = ?");
            $stmt->execute([$targetId]);
            $cur = $stmt->fetch();
            if ($cur && ($cur['role'] ?? '') === 'super_admin') {
                $cnt = (int)$pdo->query("SELECT COUNT(*) FROM users WHERE role = 'super_admin'")->fetchColumn();
                if ($cnt <= 1) jsonOut(['error' => 'Cannot demote the last Super Admin'], 400);
            }
        }
        $stmt = $pdo->prepare("UPDATE users SET role = ? WHERE id = ?");
        $stmt->execute([$newRole, $targetId]);
        $stmt = $pdo->prepare("SELECT role FROM users WHERE id = ?");
        $stmt->execute([$targetId]);
        $saved = $stmt->fetch();
        jsonOut(['success' => true, 'user_id' => $targetId, 'role' => $saved['role'] ?? $newRole]);
    }



    // ==================== LICENSING ====================

    if ($action === 'license_stats' && $method === 'GET') {
        $userId = requireLogin();
        $product = trim($_GET['product'] ?? '');
        // Domains from tenants management
        $domains = 0;
        try {
            $domains = (int)$pdo->query("SELECT COUNT(*) FROM lic_tenant_domains")->fetchColumn();
        } catch (Throwable $e) {
            try {
                $domains = (int)$pdo->query("SELECT COALESCE(SUM((SELECT COUNT(*) FROM lic_tenant_domains d WHERE d.tenant_id = t.id)),0) FROM lic_tenants t")->fetchColumn();
            } catch (Throwable $e2) { $domains = 0; }
        }
        // Licenses for product (or all if product empty)
        $licenses = 0;
        try {
            if ($product !== '') {
                $stmt = $pdo->prepare("SELECT COUNT(*) FROM license_seats s INNER JOIN license_clients c ON c.id = s.client_id WHERE c.product_type = ?");
                $stmt->execute([$product]);
                $licenses = (int)$stmt->fetchColumn();
            } else {
                $licenses = (int)$pdo->query("SELECT COUNT(*) FROM license_seats")->fetchColumn();
            }
        } catch (Throwable $e) { $licenses = 0; }
        $clients = 0;
        try {
            if ($product !== '') {
                $stmt = $pdo->prepare("SELECT COUNT(*) FROM license_clients WHERE product_type = ?");
                $stmt->execute([$product]);
                $clients = (int)$stmt->fetchColumn();
            } else {
                $clients = (int)$pdo->query("SELECT COUNT(*) FROM license_clients")->fetchColumn();
            }
        } catch (Throwable $e) { $clients = 0; }
        $revenue = $licenses * 215;
        jsonOut([
            'domains' => $domains,
            'licenses' => $licenses,
            'clients' => $clients,
            'revenue_sar' => $revenue,
            'price_per_license' => 215,
            'product' => $product,
            'ts' => time()
        ]);
    }

    if ($action === 'license_clients' && $method === 'GET') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        if (!canViewLicensing($role)) jsonOut(['error' => 'Permission denied'], 403);
        $product = $_GET['product'] ?? '';
        $allowed = ['office365','ms365_business','google_workspace'];
        if (!in_array($product, $allowed, true)) jsonOut(['error' => 'Invalid product'], 400);
        $stmt = $pdo->prepare("SELECT c.*, (SELECT COUNT(*) FROM license_seats s WHERE s.client_id = c.id) as seat_count FROM license_clients c WHERE c.product_type = ? ORDER BY c.client_name ASC");
        $stmt->execute([$product]);
        $rows = $stmt->fetchAll();
        jsonOut(array_map(function($r) {
            return [
                'id' => (int)$r['id'],
                'product_type' => $r['product_type'],
                'client_name' => $r['client_name'],
                'contact_person' => $r['contact_person'],
                'phone' => $r['phone'],
                'seat_count' => (int)$r['seat_count'],
                'created_at' => $r['created_at']
            ];
        }, $rows));
    }

    if ($action === 'license_client' && $method === 'GET') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        if (!canViewLicensing($role)) jsonOut(['error' => 'Permission denied'], 403);
        $id = (int)($_GET['id'] ?? 0);
        $stmt = $pdo->prepare("SELECT * FROM license_clients WHERE id = ?");
        $stmt->execute([$id]);
        $c = $stmt->fetch();
        if (!$c) jsonOut(['error' => 'Client not found'], 404);
        $stmt = $pdo->prepare("SELECT * FROM license_seats WHERE client_id = ? ORDER BY id DESC");
        $stmt->execute([$id]);
        $seats = $stmt->fetchAll();
        jsonOut([
            'client' => [
                'id' => (int)$c['id'],
                'product_type' => $c['product_type'],
                'client_name' => $c['client_name'],
                'contact_person' => $c['contact_person'],
                'phone' => $c['phone']
            ],
            'seats' => array_map(function($s) {
                return [
                    'id' => (int)$s['id'],
                    'employee_name' => $s['employee_name'],
                    'pc_name' => $s['pc_name'],
                    'employee_email' => $s['employee_email'],
                    'license_username' => $s['license_username'],
                    'license_password' => $s['license_password'],
                    'mobile_number' => $s['mobile_number']
                ];
            }, $seats)
        ]);
    }

    if ($action === 'license_client_save' && $method === 'POST') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        if (!canWriteLicensing($role)) jsonOut(['error' => 'Permission denied'], 403);
        $id = (int)($input['id'] ?? 0);
        $product = $input['product_type'] ?? '';
        $name = trim($input['client_name'] ?? '');
        $contact = trim($input['contact_person'] ?? '');
        $phone = trim($input['phone'] ?? '');
        $allowed = ['office365','ms365_business','google_workspace'];
        if (!in_array($product, $allowed, true)) jsonOut(['error' => 'Invalid product'], 400);
        if ($name === '') jsonOut(['error' => 'Client name required'], 400);
        if ($id > 0) {
            $stmt = $pdo->prepare("UPDATE license_clients SET client_name=?, contact_person=?, phone=? WHERE id=? AND product_type=?");
            $stmt->execute([$name, $contact ?: null, $phone ?: null, $id, $product]);
            jsonOut(['success' => true, 'id' => $id]);
        } else {
            $stmt = $pdo->prepare("INSERT INTO license_clients (user_id, product_type, client_name, contact_person, phone) VALUES (?,?,?,?,?)");
            $stmt->execute([$userId, $product, $name, $contact ?: null, $phone ?: null]);
            jsonOut(['success' => true, 'id' => (int)$pdo->lastInsertId()]);
        }
    }

    if ($action === 'license_client_delete' && $method === 'POST') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        if (!canWriteLicensing($role)) jsonOut(['error' => 'Permission denied'], 403);
        $id = (int)($input['id'] ?? 0);
        $pdo->prepare("DELETE FROM license_seats WHERE client_id = ?")->execute([$id]);
        $pdo->prepare("DELETE FROM license_clients WHERE id = ?")->execute([$id]);
        jsonOut(['success' => true]);
    }

    if ($action === 'license_seat_save' && $method === 'POST') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        if (!canWriteLicensing($role)) jsonOut(['error' => 'Permission denied'], 403);
        $id = (int)($input['id'] ?? 0);
        $clientId = (int)($input['client_id'] ?? 0);
        $emp = trim($input['employee_name'] ?? '');
        if ($clientId <= 0 || $emp === '') jsonOut(['error' => 'Client and employee name required'], 400);
        $pc = trim($input['pc_name'] ?? '') ?: null;
        $email = trim($input['employee_email'] ?? '') ?: null;
        $user = trim($input['license_username'] ?? '') ?: null;
        $pass = trim($input['license_password'] ?? '') ?: null;
        $mobile = trim($input['mobile_number'] ?? '') ?: null;
        if ($id > 0) {
            $stmt = $pdo->prepare("UPDATE license_seats SET employee_name=?, pc_name=?, employee_email=?, license_username=?, license_password=?, mobile_number=? WHERE id=? AND client_id=?");
            $stmt->execute([$emp, $pc, $email, $user, $pass, $mobile, $id, $clientId]);
            jsonOut(['success' => true, 'id' => $id]);
        } else {
            $stmt = $pdo->prepare("INSERT INTO license_seats (client_id, employee_name, pc_name, employee_email, license_username, license_password, mobile_number) VALUES (?,?,?,?,?,?,?)");
            $stmt->execute([$clientId, $emp, $pc, $email, $user, $pass, $mobile]);
            jsonOut(['success' => true, 'id' => (int)$pdo->lastInsertId()]);
        }
    }


    if ($action === 'license_seat_bulk' && $method === 'POST') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        if (!canWriteLicensing($role)) jsonOut(['error' => 'Permission denied'], 403);
        $clientId = (int)($input['client_id'] ?? 0);
        $rows = $input['rows'] ?? [];
        if ($clientId <= 0) jsonOut(['error' => 'Client required'], 400);
        if (!is_array($rows) || !count($rows)) jsonOut(['error' => 'No rows to import'], 400);
        $chk = $pdo->prepare("SELECT id FROM license_clients WHERE id = ?");
        $chk->execute([$clientId]);
        if (!$chk->fetch()) jsonOut(['error' => 'Client not found'], 404);

        $stmt = $pdo->prepare("INSERT INTO license_seats (client_id, employee_name, pc_name, employee_email, license_username, license_password, mobile_number) VALUES (?,?,?,?,?,?,?)");
        $inserted = 0;
        $skipped = 0;
        $errors = [];
        if (count($rows) > 500) jsonOut(['error' => 'Maximum 500 rows per import'], 400);
        foreach ($rows as $i => $row) {
            if (!is_array($row)) { $skipped++; continue; }
            $emp = trim((string)($row['employee_name'] ?? $row['Employee Name'] ?? ''));
            if ($emp === '' || mb_strlen($emp) > 200) {
                $skipped++;
                $errors[] = 'Row ' . ($i + 1) . ': employee name required (max 200 chars)';
                continue;
            }
            $pc = trim((string)($row['pc_name'] ?? $row['PC Name'] ?? ''));
            $email = trim((string)($row['employee_email'] ?? $row['Employee Email'] ?? ''));
            $user = trim((string)($row['license_username'] ?? $row['License Username'] ?? ''));
            $pass = trim((string)($row['license_password'] ?? $row['Password'] ?? ''));
            $mobile = trim((string)($row['mobile_number'] ?? $row['Mobile Number'] ?? ''));
            if ($email !== '' && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $skipped++;
                $errors[] = 'Row ' . ($i + 1) . ': invalid email';
                continue;
            }
            if ($pc !== '' && mb_strlen($pc) > 200) { $skipped++; $errors[] = 'Row ' . ($i + 1) . ': PC name too long'; continue; }
            if ($email !== '' && mb_strlen($email) > 200) { $skipped++; $errors[] = 'Row ' . ($i + 1) . ': email too long'; continue; }
            if ($user !== '' && mb_strlen($user) > 200) { $skipped++; $errors[] = 'Row ' . ($i + 1) . ': username too long'; continue; }
            if ($pass !== '' && mb_strlen($pass) > 200) { $skipped++; $errors[] = 'Row ' . ($i + 1) . ': password too long'; continue; }
            if ($mobile !== '' && mb_strlen($mobile) > 40) { $skipped++; $errors[] = 'Row ' . ($i + 1) . ': mobile too long'; continue; }
            $pc = $pc !== '' ? $pc : null;
            $email = $email !== '' ? $email : null;
            $user = $user !== '' ? $user : null;
            $pass = $pass !== '' ? $pass : null;
            $mobile = $mobile !== '' ? $mobile : null;
            try {
                $stmt->execute([$clientId, $emp, $pc, $email, $user, $pass, $mobile]);
                $inserted++;
            } catch (Throwable $e) {
                $skipped++;
                $errors[] = 'Row ' . ($i + 1) . ': ' . $e->getMessage();
            }
        }
        jsonOut(['success' => true, 'inserted' => $inserted, 'skipped' => $skipped, 'errors' => array_slice($errors, 0, 20)]);
    }

    if ($action === 'license_seat_delete' && $method === 'POST') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        if (!canWriteLicensing($role)) jsonOut(['error' => 'Permission denied'], 403);
        $id = (int)($input['id'] ?? 0);
        $pdo->prepare("DELETE FROM license_seats WHERE id = ?")->execute([$id]);
        jsonOut(['success' => true]);
    }



    // ---- Payment comments ----
    
    if ($action === 'payment_receipts') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        $pdo->exec("CREATE TABLE IF NOT EXISTS payment_receipts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            payment_id INT NOT NULL,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            received_on DATE NOT NULL,
            payment_method VARCHAR(50) DEFAULT NULL,
            receipt VARCHAR(255) DEFAULT NULL,
            notes VARCHAR(500) DEFAULT NULL,
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_pay_rcpt (payment_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $syncReceived = function($pdo, $paymentId, $ownerUserId = null) {
            $s = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM payment_receipts WHERE payment_id = ?");
            $s->execute([$paymentId]);
            $sum = (float)$s->fetchColumn();
            $pdo->prepare("UPDATE payments SET received_amount = ? WHERE id = ?")->execute([$sum, $paymentId]);
            return $sum;
        };

        if ($method === 'GET') {
            $paymentId = (int)($_GET['payment_id'] ?? 0);
            if ($paymentId <= 0) jsonOut(['error' => 'payment_id required'], 400);
            if (in_array($role, ['super_admin', 'sales_manager'], true)) {
                $chk = $pdo->prepare("SELECT id FROM payments WHERE id = ?");
                $chk->execute([$paymentId]);
            } else {
                $chk = $pdo->prepare("SELECT id FROM payments WHERE id = ? AND user_id = ?");
                $chk->execute([$paymentId, $userId]);
            }
            if (!$chk->fetch()) jsonOut(['error' => 'Not found'], 404);
            $stmt = $pdo->prepare("SELECT * FROM payment_receipts WHERE payment_id = ? ORDER BY received_on DESC, id DESC");
            $stmt->execute([$paymentId]);
            $rows = $stmt->fetchAll();
            foreach ($rows as &$r) {
                $r['id'] = (int)$r['id'];
                $r['payment_id'] = (int)$r['payment_id'];
                $r['amount'] = (float)$r['amount'];
            }
            jsonOut($rows);
        }

        if ($method === 'POST') {
            if (!canWritePayments($role)) jsonOut(['error' => 'Permission denied'], 403);
            $paymentId = (int)($_POST['payment_id'] ?? $input['payment_id'] ?? 0);
            if ($paymentId <= 0) jsonOut(['error' => 'payment_id required'], 400);
            if (in_array($role, ['super_admin', 'sales_manager'], true)) {
                $chk = $pdo->prepare("SELECT id, amount, user_id FROM payments WHERE id = ?");
                $chk->execute([$paymentId]);
            } else {
                $chk = $pdo->prepare("SELECT id, amount, user_id FROM payments WHERE id = ? AND user_id = ?");
                $chk->execute([$paymentId, $userId]);
            }
            $pay = $chk->fetch();
            if (!$pay) jsonOut(['error' => 'Not found'], 404);
            $amount = floatval($_POST['amount'] ?? $input['amount'] ?? 0);
            $receivedOn = trim($_POST['received_on'] ?? $input['received_on'] ?? date('Y-m-d'));
            $methodPay = trim($_POST['payment_method'] ?? $input['payment_method'] ?? '') ?: null;
            $notes = trim($_POST['notes'] ?? $input['notes'] ?? '') ?: null;
            if ($amount <= 0) jsonOut(['error' => 'Amount required'], 400);
            $receiptName = null;
            if (!empty($_FILES['receipt']['name']) && $_FILES['receipt']['error'] === UPLOAD_ERR_OK) {
                $ext = strtolower(pathinfo($_FILES['receipt']['name'], PATHINFO_EXTENSION));
                if (!in_array($ext, ['pdf','jpg','jpeg'], true)) jsonOut(['error' => 'Only PDF/JPEG'], 400);
                if ($_FILES['receipt']['size'] > 2 * 1024 * 1024) jsonOut(['error' => 'Max 2MB'], 400);
                $dir = __DIR__ . '/uploads';
                if (!is_dir($dir)) mkdir($dir, 0755, true);
                $receiptName = 'rcpt_' . $paymentId . '_' . time() . '_' . bin2hex(random_bytes(3)) . '.' . $ext;
                if (!move_uploaded_file($_FILES['receipt']['tmp_name'], $dir . '/' . $receiptName)) {
                    jsonOut(['error' => 'Upload failed'], 500);
                }
            }
            $stmt = $pdo->prepare("INSERT INTO payment_receipts (payment_id, amount, received_on, payment_method, receipt, notes, created_by) VALUES (?,?,?,?,?,?,?)");
            $stmt->execute([$paymentId, $amount, $receivedOn, $methodPay, $receiptName, $notes, $userId]);
            $id = (int)$pdo->lastInsertId();
            $sum = $syncReceived($pdo, $paymentId, $userId);
            jsonOut(['id' => $id, 'payment_id' => $paymentId, 'amount' => $amount, 'received_on' => $receivedOn, 'payment_method' => $methodPay, 'receipt' => $receiptName, 'notes' => $notes, 'total_received' => $sum]);
        }

        if ($method === 'DELETE') {
            $id = (int)($input['id'] ?? $_GET['id'] ?? 0);
            if ($id <= 0) jsonOut(['error' => 'id required'], 400);
            if (!canWritePayments($role)) jsonOut(['error' => 'Permission denied'], 403);
            $stmt = $pdo->prepare("SELECT r.*, p.user_id FROM payment_receipts r JOIN payments p ON p.id = r.payment_id WHERE r.id = ?");
            $stmt->execute([$id]);
            $row = $stmt->fetch();
            if (!$row) jsonOut(['error' => 'Not found'], 404);
            if (!in_array($role, ['super_admin', 'sales_manager'], true) && (int)$row['user_id'] !== (int)$userId) {
                jsonOut(['error' => 'Not found'], 404);
            }
            if (!empty($row['receipt'])) {
                $fp = __DIR__ . '/uploads/' . $row['receipt'];
                if (is_file($fp)) @unlink($fp);
            }
            $pdo->prepare("DELETE FROM payment_receipts WHERE id = ?")->execute([$id]);
            $sum = $syncReceived($pdo, (int)$row['payment_id'], $userId);
            jsonOut(['ok' => true, 'total_received' => $sum]);
        }
        jsonOut(['error' => 'Method not allowed'], 405);
    }

    if ($action === 'payment_comments') {
        $userId = requireLogin();
        // Ensure table exists
        $pdo->exec("CREATE TABLE IF NOT EXISTS payment_comments (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            payment_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            comment TEXT NOT NULL,
            attachment VARCHAR(255) DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_payment (payment_id),
            KEY idx_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        try { $pdo->exec("ALTER TABLE payment_comments ADD COLUMN attachment VARCHAR(255) DEFAULT NULL"); } catch (Throwable $e) {}

        if ($method === 'GET') {
            $paymentId = (int)($_GET['payment_id'] ?? 0);
            if ($paymentId <= 0) jsonOut(['error' => 'payment_id required'], 400);
            // Verify payment belongs to this user (or allow any logged-in viewer with payments access)
            $chk = $pdo->prepare("SELECT id FROM payments WHERE id = ? AND user_id = ?");
            $chk->execute([$paymentId, $userId]);
            if (!$chk->fetch()) {
                // allow if user can see payments module - still restrict to own for now
                jsonOut(['error' => 'Payment not found'], 404);
            }
            $stmt = $pdo->prepare("SELECT c.id, c.payment_id, c.user_id, c.comment, c.attachment, c.created_at, u.username, u.display_name
                FROM payment_comments c
                LEFT JOIN users u ON u.id = c.user_id
                WHERE c.payment_id = ?
                ORDER BY c.created_at ASC, c.id ASC");
            $stmt->execute([$paymentId]);
            $rows = $stmt->fetchAll();
            $out = array_map(function ($r) use ($userId) {
                return [
                    'id' => (int)$r['id'],
                    'payment_id' => (int)$r['payment_id'],
                    'user_id' => (int)$r['user_id'],
                    'comment' => $r['comment'],
                    'attachment' => $r['attachment'] ?? null,
                    'created_at' => $r['created_at'],
                    'username' => $r['username'] ?? '',
                    'display_name' => $r['display_name'] ?? $r['username'] ?? 'User',
                    'is_mine' => (int)$r['user_id'] === $userId,
                ];
            }, $rows);
            jsonOut($out);
        }

        if ($method === 'POST') {
            $paymentId = (int)($input['payment_id'] ?? $_POST['payment_id'] ?? 0);
            $comment = trim($input['comment'] ?? $_POST['comment'] ?? '');
            if ($paymentId <= 0) jsonOut(['error' => 'payment_id required'], 400);
            if ($comment === '' && empty($_FILES['attachment']['name'])) jsonOut(['error' => 'Comment or attachment required'], 400);
            $chk = $pdo->prepare("SELECT id FROM payments WHERE id = ? AND user_id = ?");
            $chk->execute([$paymentId, $userId]);
            if (!$chk->fetch()) jsonOut(['error' => 'Payment not found'], 404);
            $attachment = null;
            if (!empty($_FILES['attachment']['name'])) {
                $attachment = handleUpload('attachment', 'cmt');
                if (!$attachment) jsonOut(['error' => 'Invalid attachment (PDF or JPEG only, max 2MB)'], 400);
            }
            $stmt = $pdo->prepare("INSERT INTO payment_comments (payment_id, user_id, comment, attachment) VALUES (?, ?, ?, ?)");
            $stmt->execute([$paymentId, $userId, $comment !== '' ? $comment : '(attachment)', $attachment]);
            $id = (int)$pdo->lastInsertId();
            $u = $pdo->prepare("SELECT username, display_name FROM users WHERE id = ?");
            $u->execute([$userId]);
            $ur = $u->fetch() ?: [];
            jsonOut([
                'id' => $id,
                'payment_id' => $paymentId,
                'user_id' => $userId,
                'comment' => $comment !== '' ? $comment : '(attachment)',
                'attachment' => $attachment,
                'created_at' => date('Y-m-d H:i:s'),
                'username' => $ur['username'] ?? '',
                'display_name' => $ur['display_name'] ?? $ur['username'] ?? 'User',
                'is_mine' => true,
            ]);
        }

        if ($method === 'DELETE') {
            $id = (int)($input['id'] ?? 0);
            if ($id <= 0) jsonOut(['error' => 'id required'], 400);
            // Only author or super_admin can delete
            $stmt = $pdo->prepare("SELECT * FROM payment_comments WHERE id = ?");
            $stmt->execute([$id]);
            $c = $stmt->fetch();
            if (!$c) jsonOut(['error' => 'Not found'], 404);
            $role = getUserRole($pdo, $userId);
            if ((int)$c['user_id'] !== $userId && $role !== 'super_admin') {
                jsonOut(['error' => 'Permission denied'], 403);
            }
            $pdo->prepare("DELETE FROM payment_comments WHERE id = ?")->execute([$id]);
            jsonOut(['success' => true]);
        }
    }



    // ---- Proforma Invoices (standalone) ----
    if ($action === 'proforma') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        $pdo->exec("CREATE TABLE IF NOT EXISTS proforma_invoices (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            invoice_no VARCHAR(50) NOT NULL,
            invoice_date DATE NOT NULL,
            buyer_name VARCHAR(255) NOT NULL,
            buyer_name_ar VARCHAR(255) DEFAULT NULL,
            buyer_cr VARCHAR(50) DEFAULT NULL,
            buyer_vat VARCHAR(50) DEFAULT NULL,
            buyer_address TEXT DEFAULT NULL,
            buyer_contact VARCHAR(255) DEFAULT NULL,
            seller_name VARCHAR(255) DEFAULT 'IYAADTEC SOLUTIONS LLC',
            seller_cr VARCHAR(50) DEFAULT '7050611693',
            seller_vat VARCHAR(50) DEFAULT '313104808500003',
            seller_address TEXT DEFAULT 'Building No. 5079, King Saud Road, Ar Rabie District, Dammam 32241, Kingdom of Saudi Arabia',
            vat_rate DECIMAL(5,2) DEFAULT 15.00,
            notes TEXT DEFAULT NULL,
            amount_words VARCHAR(500) DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_invoice_no (invoice_no),
            KEY idx_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        try { $pdo->exec("ALTER TABLE proforma_invoices ADD COLUMN client_id INT DEFAULT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE proforma_invoices ADD COLUMN payment_id INT DEFAULT NULL"); } catch (Throwable $e) {}

        try { $pdo->exec("ALTER TABLE proforma_invoices ADD COLUMN template VARCHAR(32) DEFAULT 'classic'"); } catch (Throwable $e) {}
        $pdo->exec("CREATE TABLE IF NOT EXISTS proforma_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            invoice_id INT UNSIGNED NOT NULL,
            description VARCHAR(500) NOT NULL,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            sort_order INT NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_invoice (invoice_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $canWrite = in_array($role, ['super_admin', 'sales_manager'], true);

        if ($method === 'GET') {
            // Next proforma number: PI-YYYY-NNN (seed 2026 at 115)
            if (!empty($_GET['next_no'])) {
                $year = date('Y');
                $prefix = 'PI-' . $year . '-';
                // Starting sequence for 2026; other years start at 1
                $seed = ((int)$year === 2026) ? 114 : 0; // next = max+1 → first is 115 in 2026
                $stmt = $pdo->prepare("SELECT invoice_no FROM proforma_invoices WHERE invoice_no LIKE ? ORDER BY id DESC LIMIT 200");
                $stmt->execute([$prefix . '%']);
                $maxSeq = $seed;
                foreach ($stmt->fetchAll() as $row) {
                    $no = $row['invoice_no'];
                    if (preg_match('/^PI-' . $year . '-(\d+)$/', $no, $m)) {
                        $maxSeq = max($maxSeq, (int)$m[1]);
                    }
                    // also recognize old IYS-PI- prefix if present
                    if (preg_match('/^IYS-PI-' . $year . '-(\d+)$/', $no, $m)) {
                        $maxSeq = max($maxSeq, (int)$m[1]);
                    }
                }
                // scan all invoice_no for either prefix
                $stmt2 = $pdo->query("SELECT invoice_no FROM proforma_invoices");
                foreach ($stmt2->fetchAll() as $row) {
                    $no = $row['invoice_no'];
                    if (preg_match('/^(?:IYS-)?PI-' . $year . '-(\d+)$/', $no, $m)) {
                        $maxSeq = max($maxSeq, (int)$m[1]);
                    }
                }
                $seq = $maxSeq + 1;
                $next = $prefix . str_pad((string)$seq, 3, '0', STR_PAD_LEFT);
                jsonOut(['invoice_no' => $next, 'year' => $year, 'seq' => $seq]);
            }
            $payIdLookup = (int)($_GET['payment_id'] ?? 0);
            if ($payIdLookup > 0) {
                $stmt = $pdo->prepare("SELECT * FROM proforma_invoices WHERE payment_id = ? ORDER BY id DESC LIMIT 1");
                $stmt->execute([$payIdLookup]);
                $inv = $stmt->fetch();

                // Fallback: match unlinked proformas by payment client + project (+ amount)
                if (!$inv) {
                    $payStmt = $pdo->prepare("SELECT * FROM payments WHERE id = ? LIMIT 1");
                    $payStmt->execute([$payIdLookup]);
                    $pay = $payStmt->fetch();
                    if ($pay) {
                        $clientName = trim((string)($pay['client_name'] ?? ''));
                        $project = trim((string)($pay['project_name'] ?? ''));
                        $amount = (float)($pay['amount'] ?? 0);
                        if ($clientName !== '' && $project !== '') {
                            // Prefer exact amount match, then any matching client+project
                            $sql = "SELECT pi.* FROM proforma_invoices pi
                                INNER JOIN proforma_items pit ON pit.invoice_id = pi.id
                                WHERE LOWER(TRIM(pi.buyer_name)) COLLATE utf8mb4_unicode_ci = LOWER(TRIM(?)) COLLATE utf8mb4_unicode_ci
                                  AND LOWER(TRIM(pit.description)) COLLATE utf8mb4_unicode_ci = LOWER(TRIM(?)) COLLATE utf8mb4_unicode_ci
                                  AND (pi.payment_id IS NULL OR pi.payment_id = 0 OR pi.payment_id = ?)
                                ORDER BY
                                  CASE WHEN ABS(pit.amount - ?) < 0.01 THEN 0 ELSE 1 END,
                                  pi.id DESC
                                LIMIT 1";
                            $fs = $pdo->prepare($sql);
                            $fs->execute([$clientName, $project, $payIdLookup, $amount]);
                            $inv = $fs->fetch();
                            // Auto-link for future lookups
                            if ($inv && (empty($inv['payment_id']) || (int)$inv['payment_id'] === 0)) {
                                try {
                                    $pdo->prepare("UPDATE proforma_invoices SET payment_id = ? WHERE id = ?")->execute([$payIdLookup, (int)$inv['id']]);
                                    $inv['payment_id'] = $payIdLookup;
                                    $inv['auto_linked'] = true;
                                } catch (Throwable $e) {}
                            }
                        }
                    }
                }

                if (!$inv) jsonOut(['found' => false]);
                $items = $pdo->prepare("SELECT * FROM proforma_items WHERE invoice_id = ? ORDER BY sort_order ASC, id ASC");
                $items->execute([(int)$inv['id']]);
                $inv['items'] = $items->fetchAll();
                $sub = 0.0;
                foreach ($inv['items'] as &$it) {
                    $it['amount'] = (float)$it['amount'];
                    $sub += $it['amount'];
                }
                $inv['subtotal'] = $sub;
                $inv['vat_amount'] = round($sub * (float)$inv['vat_rate'] / 100, 2);
                $inv['grand_total'] = round($sub + $inv['vat_amount'], 2);
                $inv['found'] = true;
                if (function_exists('enrich_buyer_from_client_master')) {
                    $inv = enrich_buyer_from_client_master($pdo, $inv);
                }
                jsonOut($inv);
            }
            $id = (int)($_GET['id'] ?? 0);
            if ($id > 0) {
                $stmt = $pdo->prepare("SELECT * FROM proforma_invoices WHERE id = ?");
                $stmt->execute([$id]);
                $inv = $stmt->fetch();
                if (!$inv) jsonOut(['error' => 'Not found'], 404);
                $items = $pdo->prepare("SELECT * FROM proforma_items WHERE invoice_id = ? ORDER BY sort_order ASC, id ASC");
                $items->execute([$id]);
                $inv['items'] = $items->fetchAll();
                $sub = 0;
                foreach ($inv['items'] as &$it) {
                    $it['id'] = (int)$it['id'];
                    $it['amount'] = (float)$it['amount'];
                    $sub += $it['amount'];
                }
                $vatRate = (float)$inv['vat_rate'];
                $inv['subtotal'] = $sub;
                $inv['vat_amount'] = round($sub * $vatRate / 100, 2);
                $inv['grand_total'] = round($sub + $inv['vat_amount'], 2);
                $inv = enrich_buyer_from_client_master($pdo, $inv);
                jsonOut($inv);
            }
            $stmt = $pdo->query("SELECT i.*, (SELECT COALESCE(SUM(amount),0) FROM proforma_items WHERE invoice_id = i.id) AS subtotal FROM proforma_invoices i ORDER BY i.invoice_date DESC, i.id DESC");
            $list = $stmt->fetchAll();
            foreach ($list as &$r) {
                $r['id'] = (int)$r['id'];
                $r['subtotal'] = (float)$r['subtotal'];
                $r['vat_amount'] = round($r['subtotal'] * (float)$r['vat_rate'] / 100, 2);
                $r['grand_total'] = round($r['subtotal'] + $r['vat_amount'], 2);
            }
            jsonOut($list);
        }

        if ($method === 'POST') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $invoiceNo = trim($input['invoice_no'] ?? $_POST['invoice_no'] ?? '');
            $invoiceDate = trim($input['invoice_date'] ?? $_POST['invoice_date'] ?? date('Y-m-d'));
            $buyerName = trim($input['buyer_name'] ?? $_POST['buyer_name'] ?? '');
            if ($invoiceNo === '' || $buyerName === '') jsonOut(['error' => 'Invoice No and Buyer Name required'], 400);
            $items = $input['items'] ?? [];
            if (is_string($items)) $items = json_decode($items, true) ?: [];
            if (!is_array($items) || !count($items)) jsonOut(['error' => 'At least one line item required'], 400);

            $id = (int)($input['id'] ?? 0);
            $fields = [
                'invoice_no' => $invoiceNo,
                'invoice_date' => $invoiceDate,
                'buyer_name' => $buyerName,
                'buyer_name_ar' => trim($input['buyer_name_ar'] ?? '') ?: null,
                'buyer_cr' => trim($input['buyer_cr'] ?? '') ?: null,
                'buyer_vat' => trim($input['buyer_vat'] ?? '') ?: null,
                'buyer_address' => trim($input['buyer_address'] ?? '') ?: null,
                'buyer_contact' => trim($input['buyer_contact'] ?? '') ?: null,
                'seller_name' => trim($input['seller_name'] ?? '') ?: 'IYAADTEC SOLUTIONS LLC',
                'seller_cr' => trim($input['seller_cr'] ?? '') ?: '7050611693',
                'seller_vat' => trim($input['seller_vat'] ?? '') ?: '313104808500003',
                'seller_address' => trim($input['seller_address'] ?? '') ?: 'Building No. 5079, King Saud Road, Ar Rabie District, Dammam 32241, Kingdom of Saudi Arabia',
                'vat_rate' => (array_key_exists('vat_rate', $input) && $input['vat_rate'] !== '' && $input['vat_rate'] !== null)
                    ? (float)$input['vat_rate']
                    : 15,
                'notes' => trim($input['notes'] ?? '') ?: null,
                'amount_words' => trim($input['amount_words'] ?? '') ?: null,
                'template' => in_array(($input['template'] ?? 'classic'), ['classic','executive','modern','corporate','elegant'], true) ? ($input['template'] ?? 'classic') : 'classic',
                'client_id' => !empty($input['client_id']) ? (int)$input['client_id'] : null,
                'payment_id' => !empty($input['payment_id']) ? (int)$input['payment_id'] : null,
            ];

            if ($id > 0) {
                $stmt = $pdo->prepare("UPDATE proforma_invoices SET invoice_no=?, invoice_date=?, buyer_name=?, buyer_name_ar=?, buyer_cr=?, buyer_vat=?, buyer_address=?, buyer_contact=?, seller_name=?, seller_cr=?, seller_vat=?, seller_address=?, vat_rate=?, notes=?, amount_words=?, template=? WHERE id=?");
                $stmt->execute([$fields['invoice_no'], $fields['invoice_date'], $fields['buyer_name'], $fields['buyer_name_ar'], $fields['buyer_cr'], $fields['buyer_vat'], $fields['buyer_address'], $fields['buyer_contact'], $fields['seller_name'], $fields['seller_cr'], $fields['seller_vat'], $fields['seller_address'], $fields['vat_rate'], $fields['notes'], $fields['amount_words'], $fields['template'], $id]);
                $pdo->prepare("DELETE FROM proforma_items WHERE invoice_id = ?")->execute([$id]);
            } else {
                $stmt = $pdo->prepare("INSERT INTO proforma_invoices (user_id, invoice_no, invoice_date, buyer_name, buyer_name_ar, buyer_cr, buyer_vat, buyer_address, buyer_contact, seller_name, seller_cr, seller_vat, seller_address, vat_rate, notes, amount_words, template, client_id, payment_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $stmt->execute([$userId, $fields['invoice_no'], $fields['invoice_date'], $fields['buyer_name'], $fields['buyer_name_ar'], $fields['buyer_cr'], $fields['buyer_vat'], $fields['buyer_address'], $fields['buyer_contact'], $fields['seller_name'], $fields['seller_cr'], $fields['seller_vat'], $fields['seller_address'], $fields['vat_rate'], $fields['notes'], $fields['amount_words'], $fields['template'], $fields['client_id'] ?? null, $fields['payment_id'] ?? null]);
                $id = (int)$pdo->lastInsertId();
            }
            $ins = $pdo->prepare("INSERT INTO proforma_items (invoice_id, description, amount, sort_order) VALUES (?,?,?,?)");
            foreach ($items as $i => $it) {
                $desc = trim($it['description'] ?? '');
                $amt = (float)($it['amount'] ?? 0);
                if ($desc === '') continue;
                $ins->execute([$id, $desc, $amt, $i]);
            }
            // return full
            $_GET['id'] = $id;
            // reuse get single - inline
            $stmt = $pdo->prepare("SELECT * FROM proforma_invoices WHERE id = ?");
            $stmt->execute([$id]);
            $inv = $stmt->fetch();
            $itemsQ = $pdo->prepare("SELECT * FROM proforma_items WHERE invoice_id = ? ORDER BY sort_order ASC, id ASC");
            $itemsQ->execute([$id]);
            $inv['items'] = $itemsQ->fetchAll();
            $sub = 0;
            foreach ($inv['items'] as &$it) { $it['amount'] = (float)$it['amount']; $sub += $it['amount']; }
            $inv['subtotal'] = $sub;
            $inv['vat_amount'] = round($sub * (float)$inv['vat_rate'] / 100, 2);
            $inv['grand_total'] = round($sub + $inv['vat_amount'], 2);
            jsonOut($inv);
        }

        if ($method === 'DELETE') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? 0);
            if ($id <= 0) jsonOut(['error' => 'id required'], 400);
            $pdo->prepare("DELETE FROM proforma_items WHERE invoice_id = ?")->execute([$id]);
            $pdo->prepare("DELETE FROM proforma_invoices WHERE id = ?")->execute([$id]);
            jsonOut(['success' => true]);
        }
    }



    // ---- Tax Invoices (internal, pre-ZATCA) ----
    if ($action === 'tax_invoice') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        $pdo->exec("CREATE TABLE IF NOT EXISTS tax_invoices (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            invoice_no VARCHAR(50) NOT NULL,
            invoice_date DATE NOT NULL,
            status ENUM('draft','issued') NOT NULL DEFAULT 'draft',
            buyer_name VARCHAR(255) NOT NULL,
            buyer_name_ar VARCHAR(255) DEFAULT NULL,
            buyer_cr VARCHAR(50) DEFAULT NULL,
            buyer_vat VARCHAR(50) DEFAULT NULL,
            buyer_address TEXT DEFAULT NULL,
            buyer_contact VARCHAR(255) DEFAULT NULL,
            seller_name VARCHAR(255) DEFAULT 'IYAADTEC SOLUTIONS LLC',
            seller_cr VARCHAR(50) DEFAULT '7050611693',
            seller_vat VARCHAR(50) DEFAULT '313104808500003',
            seller_address TEXT DEFAULT 'Building No. 5079, King Saud Road, Ar Rabie District, Dammam 32241, Kingdom of Saudi Arabia',
            vat_rate DECIMAL(5,2) DEFAULT 15.00,
            notes TEXT DEFAULT NULL,
            amount_words VARCHAR(500) DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_tax_invoice_no (invoice_no),
            KEY idx_tax_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        $pdo->exec("CREATE TABLE IF NOT EXISTS tax_invoice_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            invoice_id INT UNSIGNED NOT NULL,
            description VARCHAR(500) NOT NULL,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0,
            sort_order INT NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_tax_inv (invoice_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $canWrite = in_array($role, ['super_admin', 'sales_manager'], true);

        if ($method === 'GET') {
            $id = (int)($_GET['id'] ?? 0);
            if ($id > 0) {
                $stmt = $pdo->prepare("SELECT * FROM tax_invoices WHERE id = ?");
                $stmt->execute([$id]);
                $inv = $stmt->fetch();
                if (!$inv) jsonOut(['error' => 'Not found'], 404);
                $items = $pdo->prepare("SELECT * FROM tax_invoice_items WHERE invoice_id = ? ORDER BY sort_order ASC, id ASC");
                $items->execute([$id]);
                $inv['items'] = $items->fetchAll();
                $sub = 0;
                foreach ($inv['items'] as &$it) {
                    $it['id'] = (int)$it['id'];
                    $it['amount'] = (float)$it['amount'];
                    $sub += $it['amount'];
                }
                $vatRate = (float)$inv['vat_rate'];
                $inv['subtotal'] = $sub;
                $inv['vat_amount'] = round($sub * $vatRate / 100, 2);
                $inv['grand_total'] = round($sub + $inv['vat_amount'], 2);
                jsonOut($inv);
            }
            $stmt = $pdo->query("SELECT i.*, (SELECT COALESCE(SUM(amount),0) FROM tax_invoice_items WHERE invoice_id = i.id) AS subtotal FROM tax_invoices i ORDER BY i.invoice_date DESC, i.id DESC");
            $list = $stmt->fetchAll();
            foreach ($list as &$r) {
                $r['id'] = (int)$r['id'];
                $r['subtotal'] = (float)$r['subtotal'];
                $r['vat_amount'] = round($r['subtotal'] * (float)$r['vat_rate'] / 100, 2);
                $r['grand_total'] = round($r['subtotal'] + $r['vat_amount'], 2);
            }
            jsonOut($list);
        }

        if ($method === 'POST') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $invoiceNo = trim($input['invoice_no'] ?? '');
            $invoiceDate = trim($input['invoice_date'] ?? date('Y-m-d'));
            $buyerName = trim($input['buyer_name'] ?? '');
            $status = trim($input['status'] ?? 'draft');
            if (!in_array($status, ['draft', 'issued'], true)) $status = 'draft';
            if ($invoiceNo === '' || $buyerName === '') jsonOut(['error' => 'Invoice No and Buyer Name required'], 400);
            $items = $input['items'] ?? [];
            if (is_string($items)) $items = json_decode($items, true) ?: [];
            if (!is_array($items) || !count($items)) jsonOut(['error' => 'At least one line item required'], 400);

            $id = (int)($input['id'] ?? 0);
            $fields = [
                'invoice_no' => $invoiceNo,
                'invoice_date' => $invoiceDate,
                'status' => $status,
                'buyer_name' => $buyerName,
                'buyer_name_ar' => trim($input['buyer_name_ar'] ?? '') ?: null,
                'buyer_cr' => trim($input['buyer_cr'] ?? '') ?: null,
                'buyer_vat' => trim($input['buyer_vat'] ?? '') ?: null,
                'buyer_address' => trim($input['buyer_address'] ?? '') ?: null,
                'buyer_contact' => trim($input['buyer_contact'] ?? '') ?: null,
                'seller_name' => trim($input['seller_name'] ?? '') ?: 'IYAADTEC SOLUTIONS LLC',
                'seller_cr' => trim($input['seller_cr'] ?? '') ?: '7050611693',
                'seller_vat' => trim($input['seller_vat'] ?? '') ?: '313104808500003',
                'seller_address' => trim($input['seller_address'] ?? '') ?: 'Building No. 5079, King Saud Road, Ar Rabie District, Dammam 32241, Kingdom of Saudi Arabia',
                'vat_rate' => (array_key_exists('vat_rate', $input) && $input['vat_rate'] !== '' && $input['vat_rate'] !== null)
                    ? (float)$input['vat_rate']
                    : 15,
                'notes' => trim($input['notes'] ?? '') ?: null,
                'amount_words' => trim($input['amount_words'] ?? '') ?: null,
            ];

            if ($id > 0) {
                $stmt = $pdo->prepare("UPDATE tax_invoices SET invoice_no=?, invoice_date=?, status=?, buyer_name=?, buyer_name_ar=?, buyer_cr=?, buyer_vat=?, buyer_address=?, buyer_contact=?, seller_name=?, seller_cr=?, seller_vat=?, seller_address=?, vat_rate=?, notes=?, amount_words=? WHERE id=?");
                $stmt->execute([$fields['invoice_no'], $fields['invoice_date'], $fields['status'], $fields['buyer_name'], $fields['buyer_name_ar'], $fields['buyer_cr'], $fields['buyer_vat'], $fields['buyer_address'], $fields['buyer_contact'], $fields['seller_name'], $fields['seller_cr'], $fields['seller_vat'], $fields['seller_address'], $fields['vat_rate'], $fields['notes'], $fields['amount_words'], $id]);
                $pdo->prepare("DELETE FROM tax_invoice_items WHERE invoice_id = ?")->execute([$id]);
            } else {
                $stmt = $pdo->prepare("INSERT INTO tax_invoices (user_id, invoice_no, invoice_date, status, buyer_name, buyer_name_ar, buyer_cr, buyer_vat, buyer_address, buyer_contact, seller_name, seller_cr, seller_vat, seller_address, vat_rate, notes, amount_words) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $stmt->execute([$userId, $fields['invoice_no'], $fields['invoice_date'], $fields['status'], $fields['buyer_name'], $fields['buyer_name_ar'], $fields['buyer_cr'], $fields['buyer_vat'], $fields['buyer_address'], $fields['buyer_contact'], $fields['seller_name'], $fields['seller_cr'], $fields['seller_vat'], $fields['seller_address'], $fields['vat_rate'], $fields['notes'], $fields['amount_words']]);
                $id = (int)$pdo->lastInsertId();
            }
            $ins = $pdo->prepare("INSERT INTO tax_invoice_items (invoice_id, description, amount, sort_order) VALUES (?,?,?,?)");
            foreach ($items as $i => $it) {
                $desc = trim($it['description'] ?? '');
                $amt = (float)($it['amount'] ?? 0);
                if ($desc === '') continue;
                $ins->execute([$id, $desc, $amt, $i]);
            }
            $stmt = $pdo->prepare("SELECT * FROM tax_invoices WHERE id = ?");
            $stmt->execute([$id]);
            $inv = $stmt->fetch();
            $itemsQ = $pdo->prepare("SELECT * FROM tax_invoice_items WHERE invoice_id = ? ORDER BY sort_order ASC, id ASC");
            $itemsQ->execute([$id]);
            $inv['items'] = $itemsQ->fetchAll();
            $sub = 0;
            foreach ($inv['items'] as &$it) { $it['amount'] = (float)$it['amount']; $sub += $it['amount']; }
            $inv['subtotal'] = $sub;
            $inv['vat_amount'] = round($sub * (float)$inv['vat_rate'] / 100, 2);
            $inv['grand_total'] = round($sub + $inv['vat_amount'], 2);
            jsonOut($inv);
        }

        if ($method === 'DELETE') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? 0);
            if ($id <= 0) jsonOut(['error' => 'id required'], 400);
            $pdo->prepare("DELETE FROM tax_invoice_items WHERE invoice_id = ?")->execute([$id]);
            $pdo->prepare("DELETE FROM tax_invoices WHERE id = ?")->execute([$id]);
            jsonOut(['success' => true]);
        }
    }


    // ---- Client Master ----
    if ($action === 'clients') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        $canWrite = in_array($role, ['super_admin', 'sales_manager'], true);

        $pdo->exec("CREATE TABLE IF NOT EXISTS clients (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            name_ar VARCHAR(255) DEFAULT NULL,
            contact_person VARCHAR(255) DEFAULT NULL,
            phone VARCHAR(50) DEFAULT NULL,
            email VARCHAR(255) DEFAULT NULL,
            cr_number VARCHAR(50) DEFAULT NULL,
            vat_number VARCHAR(50) DEFAULT NULL,
            address TEXT DEFAULT NULL,
            city VARCHAR(100) DEFAULT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'active',
            owner_user_id INT DEFAULT NULL,
            notes TEXT DEFAULT NULL,
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_clients_name (name),
            KEY idx_clients_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        try {
            $pdo->exec("ALTER TABLE clients CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        } catch (Throwable $e) {}

        if ($method === 'GET') {
            $id = (int)($_GET['id'] ?? 0);
            if ($id > 0) {
                $stmt = $pdo->prepare("SELECT c.*, u.username AS owner_name FROM clients c LEFT JOIN users u ON u.id = c.owner_user_id WHERE c.id = ?");
                $stmt->execute([$id]);
                $c = $stmt->fetch();
                if (!$c) jsonOut(['error' => 'Not found'], 404);
                $c['id'] = (int)$c['id'];
                // related counts
                $pay = $pdo->prepare("SELECT COUNT(*) FROM payments WHERE LOWER(TRIM(client_name)) COLLATE utf8mb4_unicode_ci = LOWER(TRIM(?)) COLLATE utf8mb4_unicode_ci");
                $pay->execute([$c['name']]);
                $c['payments_count'] = (int)$pay->fetchColumn();
                $paySum = $pdo->prepare("SELECT COALESCE(SUM(amount),0) AS inv, COALESCE(SUM(received_amount),0) AS rec FROM payments WHERE LOWER(TRIM(client_name)) COLLATE utf8mb4_unicode_ci = LOWER(TRIM(?)) COLLATE utf8mb4_unicode_ci");
                $paySum->execute([$c['name']]);
                $sums = $paySum->fetch() ?: ['inv' => 0, 'rec' => 0];
                $c['total_invoiced'] = (float)$sums['inv'];
                $c['total_received'] = (float)$sums['rec'];
                $c['total_pending'] = max(0, (float)$sums['inv'] - (float)$sums['rec']);
                jsonOut($c);
            }
            $q = trim($_GET['q'] ?? '');
            $status = trim($_GET['status'] ?? '');
            $sql = "SELECT c.*, u.username AS owner_name FROM clients c LEFT JOIN users u ON u.id = c.owner_user_id WHERE 1=1";
            $params = [];
            if ($status !== '' && in_array($status, ['active','inactive'], true)) {
                $sql .= " AND c.status = ?";
                $params[] = $status;
            }
            if ($q !== '') {
                $sql .= " AND (c.name LIKE ? OR c.name_ar LIKE ? OR c.phone LIKE ? OR c.email LIKE ? OR c.contact_person LIKE ? OR c.cr_number LIKE ? OR c.vat_number LIKE ?)";
                $like = '%' . $q . '%';
                $params = array_merge($params, [$like, $like, $like, $like, $like, $like, $like]);
            }
            $sql .= " ORDER BY c.name ASC";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $rows = $stmt->fetchAll();
            foreach ($rows as &$r) {
                $r['id'] = (int)$r['id'];
            }
            jsonOut($rows);
        }

        if ($method === 'POST' && (($input['op'] ?? $_POST['op'] ?? '') !== 'import_csv')) {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? $_POST['id'] ?? 0);
            $name = trim($input['name'] ?? $_POST['name'] ?? '');
            if ($name === '') jsonOut(['error' => 'Client name required'], 400);
            $fields = [
                'name' => $name,
                'name_ar' => trim($input['name_ar'] ?? $_POST['name_ar'] ?? '') ?: null,
                'contact_person' => trim($input['contact_person'] ?? $_POST['contact_person'] ?? '') ?: null,
                'phone' => trim($input['phone'] ?? $_POST['phone'] ?? '') ?: null,
                'email' => trim($input['email'] ?? $_POST['email'] ?? '') ?: null,
                'cr_number' => trim($input['cr_number'] ?? $_POST['cr_number'] ?? '') ?: null,
                'vat_number' => trim($input['vat_number'] ?? $_POST['vat_number'] ?? '') ?: null,
                'address' => trim($input['address'] ?? $_POST['address'] ?? '') ?: null,
                'city' => trim($input['city'] ?? $_POST['city'] ?? '') ?: null,
                'status' => in_array(($input['status'] ?? $_POST['status'] ?? 'active'), ['active','inactive'], true) ? ($input['status'] ?? $_POST['status'] ?? 'active') : 'active',
                'owner_user_id' => isset($input['owner_user_id']) && $input['owner_user_id'] !== '' ? (int)$input['owner_user_id'] : null,
                'notes' => trim($input['notes'] ?? $_POST['notes'] ?? '') ?: null,
            ];
            if ($id > 0) {
                $stmt = $pdo->prepare("UPDATE clients SET name=?, name_ar=?, contact_person=?, phone=?, email=?, cr_number=?, vat_number=?, address=?, city=?, status=?, owner_user_id=?, notes=? WHERE id=?");
                $stmt->execute([$fields['name'], $fields['name_ar'], $fields['contact_person'], $fields['phone'], $fields['email'], $fields['cr_number'], $fields['vat_number'], $fields['address'], $fields['city'], $fields['status'], $fields['owner_user_id'], $fields['notes'], $id]);
            } else {
                $stmt = $pdo->prepare("INSERT INTO clients (name, name_ar, contact_person, phone, email, cr_number, vat_number, address, city, status, owner_user_id, notes, created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $stmt->execute([$fields['name'], $fields['name_ar'], $fields['contact_person'], $fields['phone'], $fields['email'], $fields['cr_number'], $fields['vat_number'], $fields['address'], $fields['city'], $fields['status'], $fields['owner_user_id'], $fields['notes'], $userId]);
                $id = (int)$pdo->lastInsertId();
            }
            $stmt = $pdo->prepare("SELECT c.*, u.username AS owner_name FROM clients c LEFT JOIN users u ON u.id = c.owner_user_id WHERE c.id = ?");
            $stmt->execute([$id]);
            $c = $stmt->fetch();
            $c['id'] = (int)$c['id'];
            jsonOut($c);
        }

        if ($method === 'DELETE') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? $_GET['id'] ?? 0);
            if ($id <= 0) jsonOut(['error' => 'id required'], 400);
            $pdo->prepare("DELETE FROM clients WHERE id = ?")->execute([$id]);
            jsonOut(['success' => true]);
        }

        // Import unique clients from payments (+ license clients if present)
                if ($method === 'POST' && (($input['op'] ?? $_POST['op'] ?? '') === 'import_csv')) {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            if (empty($_FILES['file']) || $_FILES['file']['error'] === UPLOAD_ERR_NO_FILE) {
                jsonOut(['error' => 'CSV file required'], 400);
            }
            $file = $_FILES['file'];
            if ($file['error'] !== UPLOAD_ERR_OK) jsonOut(['error' => 'Upload failed'], 400);
            if ($file['size'] > 2 * 1024 * 1024) jsonOut(['error' => 'File too large (max 2MB)'], 400);

            $tmp = $file['tmp_name'];
            $raw = file_get_contents($tmp);
            if ($raw === false || $raw === '') jsonOut(['error' => 'Empty file'], 400);
            // Strip UTF-8 BOM
            if (substr($raw, 0, 3) === "\xEF\xBB\xBF") $raw = substr($raw, 3);

            $lines = preg_split("/\r\n|\n|\r/", $raw);
            $lines = array_values(array_filter($lines, function ($l) { return trim($l) !== ''; }));
            if (count($lines) < 2) jsonOut(['error' => 'CSV must have a header row and at least one data row'], 400);

            // Parse header
            $header = str_getcsv(array_shift($lines));
            $header = array_map(function ($h) {
                $h = strtolower(trim($h));
                $h = str_replace([' ', '-'], '_', $h);
                return $h;
            }, $header);

            $map = [
                'name' => null, 'client_name' => null, 'name_ar' => null,
                'contact_person' => null, 'contact' => null,
                'phone' => null, 'mobile' => null,
                'email' => null, 'city' => null, 'address' => null,
                'cr_number' => null, 'cr' => null,
                'vat_number' => null, 'vat' => null,
                'status' => null, 'notes' => null
            ];
            foreach ($header as $i => $h) {
                if (array_key_exists($h, $map)) $map[$h] = $i;
            }
            // Aliases
            $nameIdx = $map['name'] !== null ? $map['name'] : $map['client_name'];
            if ($nameIdx === null) {
                jsonOut(['error' => 'CSV must include a "name" (or client_name) column'], 400);
            }
            $col = function ($row, $keys) use ($map) {
                foreach ((array)$keys as $k) {
                    if ($map[$k] === null) continue;
                    $i = $map[$k];
                    if (isset($row[$i]) && trim((string)$row[$i]) !== '') return trim((string)$row[$i]);
                }
                return null;
            };

            $imported = 0;
            $skipped = 0;
            $updated = 0;
            $errors = [];

            $find = $pdo->prepare("SELECT id FROM clients WHERE LOWER(TRIM(name)) COLLATE utf8mb4_unicode_ci = LOWER(TRIM(?)) COLLATE utf8mb4_unicode_ci LIMIT 1");
            $ins = $pdo->prepare("INSERT INTO clients (name, name_ar, contact_person, phone, email, city, address, cr_number, vat_number, status, notes, created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
            $upd = $pdo->prepare("UPDATE clients SET name_ar=COALESCE(?, name_ar), contact_person=COALESCE(?, contact_person), phone=COALESCE(?, phone), email=COALESCE(?, email), city=COALESCE(?, city), address=COALESCE(?, address), cr_number=COALESCE(?, cr_number), vat_number=COALESCE(?, vat_number), status=COALESCE(?, status), notes=COALESCE(?, notes) WHERE id=?");

            foreach ($lines as $lineNo => $line) {
                $row = str_getcsv($line);
                if (!$row || (count($row) === 1 && trim($row[0]) === '')) continue;
                $name = isset($row[$nameIdx]) ? trim((string)$row[$nameIdx]) : '';
                if ($name === '') {
                    $skipped++;
                    continue;
                }
                $nameAr = $col($row, 'name_ar');
                $contact = $col($row, ['contact_person', 'contact']);
                $phone = $col($row, ['phone', 'mobile']);
                $email = $col($row, 'email');
                $city = $col($row, 'city');
                $address = $col($row, 'address');
                $cr = $col($row, ['cr_number', 'cr']);
                $vat = $col($row, ['vat_number', 'vat']);
                $status = $col($row, 'status');
                if ($status !== null) {
                    $status = strtolower($status);
                    if (!in_array($status, ['active', 'inactive'], true)) $status = 'active';
                } else {
                    $status = 'active';
                }
                $notes = $col($row, 'notes');

                try {
                    $find->execute([$name]);
                    $existingId = $find->fetchColumn();
                    if ($existingId) {
                        $upd->execute([
                            $nameAr, $contact, $phone, $email, $city, $address, $cr, $vat, $status, $notes, $existingId
                        ]);
                        $updated++;
                    } else {
                        $ins->execute([
                            $name, $nameAr, $contact, $phone, $email, $city, $address, $cr, $vat, $status, $notes, $userId
                        ]);
                        $imported++;
                    }
                } catch (Throwable $e) {
                    $errors[] = 'Row ' . ($lineNo + 2) . ': ' . $e->getMessage();
                    if (count($errors) > 10) break;
                }
            }

            jsonOut([
                'success' => true,
                'imported' => $imported,
                'updated' => $updated,
                'skipped' => $skipped,
                'errors' => $errors
            ]);
        }

jsonOut(['error' => 'Method not allowed'], 405);
    }


    // ---- Notes (Trello-style boards) ----
    if ($action === 'notes') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        $canWrite = !in_array($role, ['qc'], true); // QC view-only; others can edit

        $pdo->exec("CREATE TABLE IF NOT EXISTS notes_boards (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            color VARCHAR(20) DEFAULT '#0f172a',
            position INT NOT NULL DEFAULT 0,
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_nb_pos (position)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $pdo->exec("CREATE TABLE IF NOT EXISTS notes_lists (
            id INT AUTO_INCREMENT PRIMARY KEY,
            board_id INT NOT NULL,
            title VARCHAR(200) NOT NULL,
            position INT NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_nl_board (board_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $pdo->exec("CREATE TABLE IF NOT EXISTS notes_cards (
            id INT AUTO_INCREMENT PRIMARY KEY,
            list_id INT NOT NULL,
            title VARCHAR(500) NOT NULL,
            description TEXT DEFAULT NULL,
            label_color VARCHAR(20) DEFAULT NULL,
            position INT NOT NULL DEFAULT 0,
            start_date DATE DEFAULT NULL,
            due_date DATE DEFAULT NULL,
            location VARCHAR(255) DEFAULT NULL,
            cover_color VARCHAR(20) DEFAULT NULL,
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_nc_list (list_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        try { $pdo->exec("ALTER TABLE notes_cards ADD COLUMN start_date DATE DEFAULT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE notes_cards ADD COLUMN due_date DATE DEFAULT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE notes_cards ADD COLUMN location VARCHAR(255) DEFAULT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE notes_cards ADD COLUMN cover_color VARCHAR(20) DEFAULT NULL"); } catch (Throwable $e) {}

        $pdo->exec("CREATE TABLE IF NOT EXISTS notes_checklist_items (
            id INT AUTO_INCREMENT PRIMARY KEY,
            card_id INT NOT NULL,
            title VARCHAR(500) NOT NULL,
            done TINYINT(1) NOT NULL DEFAULT 0,
            position INT NOT NULL DEFAULT 0,
            KEY idx_nci_card (card_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $pdo->exec("CREATE TABLE IF NOT EXISTS notes_card_attachments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            card_id INT NOT NULL,
            filename VARCHAR(255) NOT NULL,
            original_name VARCHAR(255) DEFAULT NULL,
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_nca_card (card_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $pdo->exec("CREATE TABLE IF NOT EXISTS notes_card_members (
            card_id INT NOT NULL,
            user_id INT NOT NULL,
            PRIMARY KEY (card_id, user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $pdo->exec("CREATE TABLE IF NOT EXISTS notes_card_comments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            card_id INT NOT NULL,
            user_id INT NOT NULL,
            body TEXT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_ncc_card (card_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $op = $input['op'] ?? $_GET['op'] ?? $_POST['op'] ?? 'boards';


        // Single card full detail
        if ($method === 'GET' && $op === 'card') {
            $cardId = (int)($_GET['id'] ?? 0);
            if ($cardId <= 0) jsonOut(['error' => 'id required'], 400);
            $st = $pdo->prepare("SELECT c.*, l.title AS list_title, l.board_id, b.title AS board_title
                FROM notes_cards c
                JOIN notes_lists l ON l.id = c.list_id
                JOIN notes_boards b ON b.id = l.board_id
                WHERE c.id = ?");
            $st->execute([$cardId]);
            $card = $st->fetch();
            if (!$card) jsonOut(['error' => 'Not found'], 404);
            $card['id'] = (int)$card['id'];
            $card['list_id'] = (int)$card['list_id'];
            $card['board_id'] = (int)$card['board_id'];

            $ci = $pdo->prepare("SELECT * FROM notes_checklist_items WHERE card_id = ? ORDER BY position ASC, id ASC");
            $ci->execute([$cardId]);
            $card['checklist'] = array_map(function($r) {
                $r['id'] = (int)$r['id']; $r['done'] = (int)$r['done']; return $r;
            }, $ci->fetchAll());

            $atq = $pdo->prepare("SELECT * FROM notes_card_attachments WHERE card_id = ? ORDER BY id DESC");
            $atq->execute([$cardId]);
            $card['attachments'] = array_map(function($r) {
                $r['id'] = (int)$r['id']; return $r;
            }, $atq->fetchAll());

            $mq = $pdo->prepare("SELECT u.id, u.username, u.display_name FROM notes_card_members m JOIN users u ON u.id = m.user_id WHERE m.card_id = ?");
            $mq->execute([$cardId]);
            $card['members'] = array_map(function($r) {
                $r['id'] = (int)$r['id']; return $r;
            }, $mq->fetchAll());

            $cq = $pdo->prepare("SELECT c.*, u.username, u.display_name FROM notes_card_comments c JOIN users u ON u.id = c.user_id WHERE c.card_id = ? ORDER BY c.id ASC");
            $cq->execute([$cardId]);
            $card['comments'] = array_map(function($r) {
                $r['id'] = (int)$r['id']; $r['user_id'] = (int)$r['user_id']; return $r;
            }, $cq->fetchAll());

            jsonOut($card);
        }

        // List boards
        if ($method === 'GET' && $op === 'boards') {
            $stmt = $pdo->query("SELECT b.*, (SELECT COUNT(*) FROM notes_lists l WHERE l.board_id = b.id) AS list_count,
                (SELECT COUNT(*) FROM notes_cards c INNER JOIN notes_lists l ON l.id = c.list_id WHERE l.board_id = b.id) AS card_count
                FROM notes_boards b ORDER BY b.position ASC, b.id ASC");
            $rows = $stmt->fetchAll();
            foreach ($rows as &$r) {
                $r['id'] = (int)$r['id'];
                $r['list_count'] = (int)$r['list_count'];
                $r['card_count'] = (int)$r['card_count'];
            }
            jsonOut($rows);
        }

        // Full board with lists + cards
        if ($method === 'GET' && $op === 'board') {
            $boardId = (int)($_GET['id'] ?? 0);
            if ($boardId <= 0) jsonOut(['error' => 'id required'], 400);
            $b = $pdo->prepare("SELECT * FROM notes_boards WHERE id = ?");
            $b->execute([$boardId]);
            $board = $b->fetch();
            if (!$board) jsonOut(['error' => 'Board not found'], 404);
            $board['id'] = (int)$board['id'];

            $ls = $pdo->prepare("SELECT * FROM notes_lists WHERE board_id = ? ORDER BY position ASC, id ASC");
            $ls->execute([$boardId]);
            $lists = $ls->fetchAll();
            $cs = $pdo->prepare("SELECT * FROM notes_cards WHERE list_id = ? ORDER BY position ASC, id ASC");
            foreach ($lists as &$list) {
                $list['id'] = (int)$list['id'];
                $list['board_id'] = (int)$list['board_id'];
                $cs->execute([$list['id']]);
                $cards = $cs->fetchAll();
                foreach ($cards as &$c) {
                    $c['id'] = (int)$c['id'];
                    $c['list_id'] = (int)$c['list_id'];
                    $c['position'] = (int)$c['position'];
                }
                $list['cards'] = $cards;
            }
            $board['lists'] = $lists;
            jsonOut($board);
        }

        if ($method === 'POST') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $op = $input['op'] ?? $_POST['op'] ?? '';

            if ($op === 'create_board') {
                $title = trim($input['title'] ?? '');
                if ($title === '') jsonOut(['error' => 'Title required'], 400);
                $color = trim($input['color'] ?? '#1e293b') ?: '#1e293b';
                $pos = (int)$pdo->query("SELECT COALESCE(MAX(position),0)+1 FROM notes_boards")->fetchColumn();
                $pdo->prepare("INSERT INTO notes_boards (title, color, position, created_by) VALUES (?,?,?,?)")
                    ->execute([$title, $color, $pos, $userId]);
                $id = (int)$pdo->lastInsertId();
                // default lists
                $defaults = ['To Do', 'Doing', 'Done'];
                $ins = $pdo->prepare("INSERT INTO notes_lists (board_id, title, position) VALUES (?,?,?)");
                foreach ($defaults as $i => $t) $ins->execute([$id, $t, $i]);
                jsonOut(['id' => $id, 'title' => $title, 'color' => $color]);
            }

            if ($op === 'update_board') {
                $id = (int)($input['id'] ?? 0);
                $title = trim($input['title'] ?? '');
                if ($id <= 0 || $title === '') jsonOut(['error' => 'Invalid'], 400);
                $color = trim($input['color'] ?? '');
                if ($color !== '') {
                    $pdo->prepare("UPDATE notes_boards SET title=?, color=? WHERE id=?")->execute([$title, $color, $id]);
                } else {
                    $pdo->prepare("UPDATE notes_boards SET title=? WHERE id=?")->execute([$title, $id]);
                }
                jsonOut(['success' => true]);
            }

            if ($op === 'delete_board') {
                $id = (int)($input['id'] ?? 0);
                if ($id <= 0) jsonOut(['error' => 'id required'], 400);
                $pdo->prepare("DELETE FROM notes_cards WHERE list_id IN (SELECT id FROM notes_lists WHERE board_id = ?)")->execute([$id]);
                $pdo->prepare("DELETE FROM notes_lists WHERE board_id = ?")->execute([$id]);
                $pdo->prepare("DELETE FROM notes_boards WHERE id = ?")->execute([$id]);
                jsonOut(['success' => true]);
            }

            if ($op === 'create_list') {
                $boardId = (int)($input['board_id'] ?? 0);
                $title = trim($input['title'] ?? '');
                if ($boardId <= 0 || $title === '') jsonOut(['error' => 'Board and title required'], 400);
                $pos = $pdo->prepare("SELECT COALESCE(MAX(position),0)+1 FROM notes_lists WHERE board_id = ?");
                $pos->execute([$boardId]);
                $p = (int)$pos->fetchColumn();
                $pdo->prepare("INSERT INTO notes_lists (board_id, title, position) VALUES (?,?,?)")->execute([$boardId, $title, $p]);
                jsonOut(['id' => (int)$pdo->lastInsertId(), 'board_id' => $boardId, 'title' => $title, 'position' => $p, 'cards' => []]);
            }

            if ($op === 'update_list') {
                $id = (int)($input['id'] ?? 0);
                $title = trim($input['title'] ?? '');
                if ($id <= 0 || $title === '') jsonOut(['error' => 'Invalid'], 400);
                $pdo->prepare("UPDATE notes_lists SET title=? WHERE id=?")->execute([$title, $id]);
                jsonOut(['success' => true]);
            }

            if ($op === 'delete_list') {
                $id = (int)($input['id'] ?? 0);
                if ($id <= 0) jsonOut(['error' => 'id required'], 400);
                $pdo->prepare("DELETE FROM notes_cards WHERE list_id = ?")->execute([$id]);
                $pdo->prepare("DELETE FROM notes_lists WHERE id = ?")->execute([$id]);
                jsonOut(['success' => true]);
            }

            if ($op === 'create_card') {
                $listId = (int)($input['list_id'] ?? 0);
                $title = trim($input['title'] ?? '');
                if ($listId <= 0 || $title === '') jsonOut(['error' => 'List and title required'], 400);
                $desc = trim($input['description'] ?? '') ?: null;
                $label = trim($input['label_color'] ?? '') ?: null;
                $pos = $pdo->prepare("SELECT COALESCE(MAX(position),0)+1 FROM notes_cards WHERE list_id = ?");
                $pos->execute([$listId]);
                $p = (int)$pos->fetchColumn();
                $pdo->prepare("INSERT INTO notes_cards (list_id, title, description, label_color, position, created_by) VALUES (?,?,?,?,?,?)")
                    ->execute([$listId, $title, $desc, $label, $p, $userId]);
                $id = (int)$pdo->lastInsertId();
                jsonOut(['id' => $id, 'list_id' => $listId, 'title' => $title, 'description' => $desc, 'label_color' => $label, 'position' => $p]);
            }

            if ($op === 'update_card') {
                $id = (int)($input['id'] ?? 0);
                if ($id <= 0) jsonOut(['error' => 'id required'], 400);
                $title = trim($input['title'] ?? '');
                if ($title === '') jsonOut(['error' => 'Title required'], 400);
                $desc = array_key_exists('description', $input) ? (trim((string)$input['description']) ?: null) : null;
                $label = array_key_exists('label_color', $input) ? (trim((string)$input['label_color']) ?: null) : null;
                $start = array_key_exists('start_date', $input) ? (trim((string)$input['start_date']) ?: null) : null;
                $due = array_key_exists('due_date', $input) ? (trim((string)$input['due_date']) ?: null) : null;
                $loc = array_key_exists('location', $input) ? (trim((string)$input['location']) ?: null) : null;
                $cover = array_key_exists('cover_color', $input) ? (trim((string)$input['cover_color']) ?: null) : null;
                $pdo->prepare("UPDATE notes_cards SET title=?, description=?, label_color=?, start_date=?, due_date=?, location=?, cover_color=? WHERE id=?")
                    ->execute([$title, $desc, $label, $start, $due, $loc, $cover, $id]);
                jsonOut(['success' => true]);
            }

            if ($op === 'checklist_add') {
                $cardId = (int)($input['card_id'] ?? 0);
                $title = trim($input['title'] ?? '');
                if ($cardId <= 0 || $title === '') jsonOut(['error' => 'Invalid'], 400);
                $pos = $pdo->prepare("SELECT COALESCE(MAX(position),0)+1 FROM notes_checklist_items WHERE card_id = ?");
                $pos->execute([$cardId]);
                $p = (int)$pos->fetchColumn();
                $pdo->prepare("INSERT INTO notes_checklist_items (card_id, title, position) VALUES (?,?,?)")->execute([$cardId, $title, $p]);
                jsonOut(['id' => (int)$pdo->lastInsertId(), 'title' => $title, 'done' => 0]);
            }
            if ($op === 'checklist_toggle') {
                $id = (int)($input['id'] ?? 0);
                $done = !empty($input['done']) ? 1 : 0;
                $pdo->prepare("UPDATE notes_checklist_items SET done=? WHERE id=?")->execute([$done, $id]);
                jsonOut(['success' => true]);
            }
            if ($op === 'checklist_delete') {
                $id = (int)($input['id'] ?? 0);
                $pdo->prepare("DELETE FROM notes_checklist_items WHERE id=?")->execute([$id]);
                jsonOut(['success' => true]);
            }

            if ($op === 'comment_add') {
                $cardId = (int)($input['card_id'] ?? 0);
                $body = trim($input['body'] ?? '');
                if ($cardId <= 0 || $body === '') jsonOut(['error' => 'Invalid'], 400);
                $pdo->prepare("INSERT INTO notes_card_comments (card_id, user_id, body) VALUES (?,?,?)")->execute([$cardId, $userId, $body]);
                jsonOut(['id' => (int)$pdo->lastInsertId(), 'body' => $body, 'user_id' => $userId]);
            }

            if ($op === 'member_toggle') {
                $cardId = (int)($input['card_id'] ?? 0);
                $uid = (int)($input['user_id'] ?? 0);
                if ($cardId <= 0 || $uid <= 0) jsonOut(['error' => 'Invalid'], 400);
                $chk = $pdo->prepare("SELECT 1 FROM notes_card_members WHERE card_id=? AND user_id=?");
                $chk->execute([$cardId, $uid]);
                if ($chk->fetch()) {
                    $pdo->prepare("DELETE FROM notes_card_members WHERE card_id=? AND user_id=?")->execute([$cardId, $uid]);
                    jsonOut(['added' => false]);
                } else {
                    $pdo->prepare("INSERT INTO notes_card_members (card_id, user_id) VALUES (?,?)")->execute([$cardId, $uid]);
                    jsonOut(['added' => true]);
                }
            }

            if ($op === 'attach') {
                $cardId = (int)($_POST['card_id'] ?? $input['card_id'] ?? 0);
                if ($cardId <= 0) jsonOut(['error' => 'card_id required'], 400);
                $file = handleUpload('file', 'note');
                if (!$file) jsonOut(['error' => 'File required (PDF/JPEG max 2MB)'], 400);
                $orig = $_FILES['file']['name'] ?? $file;
                $pdo->prepare("INSERT INTO notes_card_attachments (card_id, filename, original_name, created_by) VALUES (?,?,?,?)")
                    ->execute([$cardId, $file, $orig, $userId]);
                jsonOut(['id' => (int)$pdo->lastInsertId(), 'filename' => $file, 'original_name' => $orig]);
            }
            if ($op === 'attach_delete') {
                $id = (int)($input['id'] ?? 0);
                $st = $pdo->prepare("SELECT * FROM notes_card_attachments WHERE id=?");
                $st->execute([$id]);
                $row = $st->fetch();
                if ($row) {
                    $fp = __DIR__ . '/uploads/' . $row['filename'];
                    if (is_file($fp)) @unlink($fp);
                    $pdo->prepare("DELETE FROM notes_card_attachments WHERE id=?")->execute([$id]);
                }
                jsonOut(['success' => true]);
            }

            if ($op === 'move_card') {
                $id = (int)($input['id'] ?? 0);
                $listId = (int)($input['list_id'] ?? 0);
                $position = (int)($input['position'] ?? 0);
                if ($id <= 0 || $listId <= 0) jsonOut(['error' => 'Invalid'], 400);
                $pdo->prepare("UPDATE notes_cards SET list_id=?, position=? WHERE id=?")->execute([$listId, $position, $id]);
                jsonOut(['success' => true]);
            }

            if ($op === 'delete_card') {
                $id = (int)($input['id'] ?? 0);
                if ($id <= 0) jsonOut(['error' => 'id required'], 400);
                $pdo->prepare("DELETE FROM notes_cards WHERE id = ?")->execute([$id]);
                jsonOut(['success' => true]);
            }

            jsonOut(['error' => 'Unknown op'], 400);
        }

        jsonOut(['error' => 'Method not allowed'], 405);
    }


    // ==================== LIC TENANTS ====================
    if ($action === 'lic_tenants') {
        $userId = requireLogin();
        $role = getUserRole($pdo, $userId);
        $canWrite = canWriteLicensing($role);

        $pdo->exec("CREATE TABLE IF NOT EXISTS lic_tenants (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            admin_username VARCHAR(255) NOT NULL,
            password_plain TEXT DEFAULT NULL,
            email VARCHAR(255) DEFAULT NULL,
            phone VARCHAR(64) DEFAULT NULL,
            two_fa TEXT DEFAULT NULL,
            backup_code TEXT DEFAULT NULL,
            notes TEXT DEFAULT NULL,
            created_by INT UNSIGNED DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $pdo->exec("CREATE TABLE IF NOT EXISTS lic_tenant_domains (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            tenant_id INT UNSIGNED NOT NULL,
            domain_name VARCHAR(255) NOT NULL,
            company_name VARCHAR(255) DEFAULT NULL,
            domain_expiry DATE DEFAULT NULL,
            contact_name VARCHAR(255) DEFAULT NULL,
            contact_email VARCHAR(255) DEFAULT NULL,
            contact_phone VARCHAR(64) DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_tenant (tenant_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");


        if ($method === 'GET') {
            $id = (int)($_GET['id'] ?? 0);
            if ($id > 0) {
                $stmt = $pdo->prepare("SELECT * FROM lic_tenants WHERE id = ?");
                $stmt->execute([$id]);
                $row = $stmt->fetch();
                if (!$row) jsonOut(['error' => 'Not found'], 404);
                $row['id'] = (int)$row['id'];
                $ds = $pdo->prepare("SELECT * FROM lic_tenant_domains WHERE tenant_id = ? ORDER BY domain_name ASC, id ASC");
                $ds->execute([$id]);
                $domains = $ds->fetchAll();
                foreach ($domains as &$d) { $d['id'] = (int)$d['id']; $d['tenant_id'] = (int)$d['tenant_id']; }
                $row['domains'] = $domains;
                $row['domain_count'] = count($domains);
                jsonOut($row);
            }
            $rows = $pdo->query("SELECT t.*, (SELECT COUNT(*) FROM lic_tenant_domains d WHERE d.tenant_id = t.id) AS domain_count FROM lic_tenants t ORDER BY t.updated_at DESC, t.id DESC")->fetchAll();
            $ds = $pdo->query("SELECT tenant_id, domain_name, company_name, contact_name, contact_email, contact_phone FROM lic_tenant_domains")->fetchAll();
            $byTenant = [];
            foreach ($ds as $d) {
                $tid = (int)$d['tenant_id'];
                if (!isset($byTenant[$tid])) $byTenant[$tid] = [];
                $byTenant[$tid][] = $d;
            }
            foreach ($rows as &$r) {
                $r['id'] = (int)$r['id'];
                $r['domain_count'] = (int)($r['domain_count'] ?? 0);
                $r['domains'] = $byTenant[(int)$r['id']] ?? [];
            }
            jsonOut($rows);
        }

        // Domain ops first (must run before generic tenant POST)
        $op = $input['op'] ?? $_GET['op'] ?? '';
        if ($method === 'POST' && $op === 'save_domain') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $tenantId = (int)($input['tenant_id'] ?? 0);
            $domainId = (int)($input['id'] ?? 0);
            $domainName = trim($input['domain_name'] ?? '');
            if ($tenantId <= 0 || $domainName === '') jsonOut(['error' => 'Tenant and domain name required'], 400);
            $chk = $pdo->prepare("SELECT id FROM lic_tenants WHERE id = ?");
            $chk->execute([$tenantId]);
            if (!$chk->fetch()) jsonOut(['error' => 'Tenant not found'], 404);
            $dn = $domainName;
            $cn = trim((string)($input['company_name'] ?? '')) ?: null;
            $de = trim((string)($input['domain_expiry'] ?? '')) ?: null;
            $ctn = trim((string)($input['contact_name'] ?? '')) ?: null;
            $cte = trim((string)($input['contact_email'] ?? '')) ?: null;
            $ctp = trim((string)($input['contact_phone'] ?? '')) ?: null;
            if ($domainId > 0) {
                $stmt = $pdo->prepare("UPDATE lic_tenant_domains SET domain_name=?, company_name=?, domain_expiry=?, contact_name=?, contact_email=?, contact_phone=? WHERE id=? AND tenant_id=?");
                $stmt->execute([$dn, $cn, $de, $ctn, $cte, $ctp, $domainId, $tenantId]);
            } else {
                $stmt = $pdo->prepare("INSERT INTO lic_tenant_domains (tenant_id, domain_name, company_name, domain_expiry, contact_name, contact_email, contact_phone) VALUES (?,?,?,?,?,?,?)");
                $stmt->execute([$tenantId, $dn, $cn, $de, $ctn, $cte, $ctp]);
                $domainId = (int)$pdo->lastInsertId();
            }
            $stmt = $pdo->prepare("SELECT * FROM lic_tenant_domains WHERE id = ?");
            $stmt->execute([$domainId]);
            $d = $stmt->fetch();
            $d['id'] = (int)$d['id'];
            $d['tenant_id'] = (int)$d['tenant_id'];
            jsonOut($d);
        }
        if (($method === 'POST' || $method === 'DELETE') && $op === 'delete_domain') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $domainId = (int)($input['id'] ?? 0);
            if ($domainId <= 0) jsonOut(['error' => 'id required'], 400);
            $pdo->prepare("DELETE FROM lic_tenant_domains WHERE id = ?")->execute([$domainId]);
            jsonOut(['success' => true]);
        }

        if ($method === 'POST') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? 0);
            $admin = trim($input['admin_username'] ?? '');
            if ($admin === '') jsonOut(['error' => 'Admin Username required'], 400);
            $fields = [
                'admin_username' => $admin,
                'password_plain' => trim((string)($input['password'] ?? $input['password_plain'] ?? '')) ?: null,
                'email' => trim((string)($input['email'] ?? '')) ?: null,
                'phone' => trim((string)($input['phone'] ?? '')) ?: null,
                'two_fa' => trim((string)($input['two_fa'] ?? '')) ?: null,
                'backup_code' => trim((string)($input['backup_code'] ?? '')) ?: null,
                'notes' => trim((string)($input['notes'] ?? '')) ?: null,
            ];
            if ($id > 0) {
                $stmt = $pdo->prepare("UPDATE lic_tenants SET admin_username=?, password_plain=?, email=?, phone=?, two_fa=?, backup_code=?, notes=? WHERE id=?");
                $stmt->execute([$fields['admin_username'], $fields['password_plain'], $fields['email'], $fields['phone'], $fields['two_fa'], $fields['backup_code'], $fields['notes'], $id]);
            } else {
                $stmt = $pdo->prepare("INSERT INTO lic_tenants (admin_username, password_plain, email, phone, two_fa, backup_code, notes, created_by) VALUES (?,?,?,?,?,?,?,?)");
                $stmt->execute([$fields['admin_username'], $fields['password_plain'], $fields['email'], $fields['phone'], $fields['two_fa'], $fields['backup_code'], $fields['notes'], $userId]);
                $id = (int)$pdo->lastInsertId();
            }
            $stmt = $pdo->prepare("SELECT * FROM lic_tenants WHERE id = ?");
            $stmt->execute([$id]);
            $row = $stmt->fetch();
            $row['id'] = (int)$row['id'];
            jsonOut($row);
        }

        if ($method === 'DELETE') {
            if (!$canWrite) jsonOut(['error' => 'Permission denied'], 403);
            $id = (int)($input['id'] ?? $_GET['id'] ?? 0);
            if ($id <= 0) jsonOut(['error' => 'id required'], 400);
            $pdo->prepare("DELETE FROM lic_tenant_domains WHERE tenant_id = ?")->execute([$id]);
            $pdo->prepare("DELETE FROM lic_tenants WHERE id = ?")->execute([$id]);
            jsonOut(['success' => true]);
        }

jsonOut(['error' => 'Method not allowed'], 405);
    }



    jsonOut(['error' => 'Method not allowed'], 405);


} catch (Throwable $e) {
    jsonOut(['error' => 'Server error: ' . $e->getMessage()], 500);
}
