<?php
// File: /public_html/erp.jewellersbd.com/api/cash/transfer.php
// Cash Account Transfer — Direct insert version
// Fix: Cash category missing issue
// Audit: cash_transactions INSERT for transfer OUT + IN

header('Content-Type: application/json; charset=UTF-8');

require_once __DIR__ . '/../../config/session.php';
require_once __DIR__ . '/../../config/db.php';

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

function send_json(bool $success, string $message, array $extra = [], int $code = 200): void
{
    http_response_code($code);
    echo json_encode(
        array_merge([
            'success' => $success,
            'message' => $message
        ], $extra),
        JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES
    );
    exit;
}

/**
 * Safe Audit Logger
 * Audit fail হলেও transfer বন্ধ হবে না।
 */
function transfer_safe_audit_log($pdo, $tableName, $recordId, $action, $changedBy, $note = '')
{
    try {
        if (!$pdo || !($pdo instanceof PDO)) {
            return;
        }

        $ipAddress = $_SERVER['REMOTE_ADDR'] ?? '';

        $stmt = $pdo->prepare("
            INSERT INTO audit_log
                (table_name, record_id, action, changed_by, changed_at, ip_address, note)
            VALUES
                (:table_name, :record_id, :action, :changed_by, NOW(), :ip_address, :note)
        ");

        $stmt->execute([
            ':table_name' => $tableName,
            ':record_id'  => (int)$recordId,
            ':action'     => $action,
            ':changed_by' => (int)$changedBy,
            ':ip_address' => $ipAddress,
            ':note'       => $note
        ]);

    } catch (Throwable $e) {
        return;
    }
}

/**
 * যেকোনো active cash category পাওয়া।
 */
function get_any_active_category_id($pdo): int
{
    try {
        $stmt = $pdo->query("
            SELECT id
            FROM cash_categories
            WHERE is_active = 1
            ORDER BY id ASC
            LIMIT 1
        ");

        $id = $stmt->fetchColumn();
        return $id ? (int)$id : 0;

    } catch (Throwable $e) {
        return 0;
    }
}

/**
 * Transfer category খোঁজা।
 * Column না থাকলে fallback যাবে।
 */
function get_transfer_category_id($pdo, string $direction): int
{
    $direction = strtoupper($direction);

    try {
        $stmt = $pdo->prepare("
            SELECT id
            FROM cash_categories
            WHERE is_active = 1
              AND (
                    LOWER(COALESCE(name, '')) LIKE '%transfer%'
                 OR LOWER(COALESCE(name_bn, '')) LIKE '%ট্রান্সফার%'
                 OR LOWER(COALESCE(category_name, '')) LIKE '%transfer%'
                 OR LOWER(COALESCE(title, '')) LIKE '%transfer%'
              )
            ORDER BY id ASC
            LIMIT 1
        ");
        $stmt->execute();

        $id = $stmt->fetchColumn();
        if ($id) {
            return (int)$id;
        }

    } catch (Throwable $e) {
        // column না থাকলে fallback
    }

    try {
        $stmt = $pdo->prepare("
            SELECT id
            FROM cash_categories
            WHERE is_active = 1
              AND direction = ?
            ORDER BY id ASC
            LIMIT 1
        ");
        $stmt->execute([$direction]);

        $id = $stmt->fetchColumn();
        if ($id) {
            return (int)$id;
        }

    } catch (Throwable $e) {
        // direction column না থাকলে fallback
    }

    return get_any_active_category_id($pdo);
}

/**
 * Transfer voucher তৈরি।
 */
function make_transfer_voucher($pdo): string
{
    $prefix = 'TR-' . date('Ym') . '-';

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM cash_transactions
        WHERE voucher_no LIKE ?
    ");
    $stmt->execute([$prefix . '%']);

    $cnt = (int)$stmt->fetchColumn();

    return $prefix . str_pad($cnt + 1, 4, '0', STR_PAD_LEFT);
}

/**
 * Auth check
 */
if (
    (!isset($_SESSION['user_id']) || empty($_SESSION['user_id'])) &&
    (!function_exists('is_logged_in') || !is_logged_in())
) {
    send_json(false, 'লগইন প্রয়োজন', [], 401);
}

if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
    send_json(false, 'শুধু POST অনুরোধ গ্রহণযোগ্য', [], 405);
}

$data = json_decode(file_get_contents('php://input'), true);

if (!is_array($data)) {
    send_json(false, 'Invalid JSON', [], 400);
}

$userId = (int)($_SESSION['user_id'] ?? 0);
$ip     = $_SERVER['REMOTE_ADDR'] ?? '';

$from   = (int)($data['from_account_id'] ?? 0);
$to     = (int)($data['to_account_id'] ?? 0);
$amount = round((float)($data['amount'] ?? 0), 2);
$note   = trim((string)($data['note'] ?? ''));
$date   = trim((string)($data['date'] ?? date('Y-m-d')));

if ($from <= 0 || $to <= 0) {
    send_json(false, 'অ্যাকাউন্ট বাছাই করুন', [], 422);
}

if ($from === $to) {
    send_json(false, 'একই অ্যাকাউন্টে ট্রান্সফার হবে না', [], 422);
}

if ($amount <= 0) {
    send_json(false, 'সঠিক পরিমাণ দিন', [], 422);
}

if ($date === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
    send_json(false, 'সঠিক তারিখ দিন', [], 422);
}

try {
    $pdo->beginTransaction();

    $accStmt = $pdo->prepare("
        SELECT id, account_name, current_balance
        FROM cash_accounts
        WHERE id = ?
        LIMIT 1
    ");

    $accStmt->execute([$from]);
    $fromAcc = $accStmt->fetch(PDO::FETCH_ASSOC);

    $accStmt->execute([$to]);
    $toAcc = $accStmt->fetch(PDO::FETCH_ASSOC);

    if (!$fromAcc) {
        $pdo->rollBack();
        send_json(false, 'উৎস অ্যাকাউন্ট পাওয়া যায়নি', [], 404);
    }

    if (!$toAcc) {
        $pdo->rollBack();
        send_json(false, 'গন্তব্য অ্যাকাউন্ট পাওয়া যায়নি', [], 404);
    }

    $fromBalance = (float)($fromAcc['current_balance'] ?? 0);

    if ($amount > $fromBalance + 0.001) {
        $pdo->rollBack();

        send_json(false, $fromAcc['account_name'] . '-এ পর্যাপ্ত টাকা নেই', [
            'current_balance'  => round($fromBalance, 2),
            'requested_amount' => $amount,
            'short_by'         => round($amount - $fromBalance, 2)
        ], 422);
    }

    $outCategoryId = get_transfer_category_id($pdo, 'OUT');
    $inCategoryId  = get_transfer_category_id($pdo, 'IN');

    if ($outCategoryId <= 0 || $inCategoryId <= 0) {
        $pdo->rollBack();
        send_json(false, 'কোনো active cash category পাওয়া যায়নি', [], 500);
    }

    $voucherBase = make_transfer_voucher($pdo);
    $voucherOut  = $voucherBase . '-OUT';
    $voucherIn   = $voucherBase . '-IN';

    $txNote = $note !== ''
        ? $note
        : ('ট্রান্সফার: ' . $fromAcc['account_name'] . ' → ' . $toAcc['account_name']);

    /**
     * OUT transaction
     */
    $outStmt = $pdo->prepare("
        INSERT INTO cash_transactions
            (
                voucher_no,
                transaction_date,
                cash_account_id,
                category_id,
                direction,
                amount,
                note,
                reference_type,
                reference_id,
                created_by,
                created_ip,
                created_at
            )
        VALUES
            (?, ?, ?, ?, 'OUT', ?, ?, 'ACCOUNT_TRANSFER', ?, ?, ?, NOW())
    ");

    $outStmt->execute([
        $voucherOut,
        $date,
        $from,
        $outCategoryId,
        $amount,
        $txNote,
        $to,
        $userId,
        $ip
    ]);

    $outTxnId = (int)$pdo->lastInsertId();

    /**
     * IN transaction
     */
    $inStmt = $pdo->prepare("
        INSERT INTO cash_transactions
            (
                voucher_no,
                transaction_date,
                cash_account_id,
                category_id,
                direction,
                amount,
                note,
                reference_type,
                reference_id,
                created_by,
                created_ip,
                created_at
            )
        VALUES
            (?, ?, ?, ?, 'IN', ?, ?, 'ACCOUNT_TRANSFER', ?, ?, ?, NOW())
    ");

    $inStmt->execute([
        $voucherIn,
        $date,
        $to,
        $inCategoryId,
        $amount,
        $txNote,
        $from,
        $userId,
        $ip
    ]);

    $inTxnId = (int)$pdo->lastInsertId();

    /**
     * Balance update
     */
    $pdo->prepare("
        UPDATE cash_accounts
        SET current_balance = current_balance - ?
        WHERE id = ?
    ")->execute([$amount, $from]);

    $pdo->prepare("
        UPDATE cash_accounts
        SET current_balance = current_balance + ?
        WHERE id = ?
    ")->execute([$amount, $to]);

    /**
     * Audit note
     */
    $auditNote = "Cash account transfer"
        . " | From: " . ($fromAcc['account_name'] ?? '') . " (#" . $from . ")"
        . " | To: " . ($toAcc['account_name'] ?? '') . " (#" . $to . ")"
        . " | Amount: " . number_format($amount, 2)
        . " | OUT Txn ID: " . $outTxnId
        . " | IN Txn ID: " . $inTxnId
        . " | Note: " . $txNote;

    /**
     * Audit action INSERT রাখা হয়েছে,
     * কারণ audit_log.action column-এ INSERT already working.
     */
    transfer_safe_audit_log(
        $pdo,
        'cash_transactions',
        $outTxnId,
        'INSERT',
        $userId,
        $auditNote
    );

    transfer_safe_audit_log(
        $pdo,
        'cash_transactions',
        $inTxnId,
        'INSERT',
        $userId,
        $auditNote
    );

    $pdo->commit();

    send_json(true, 'ট্রান্সফার সম্পন্ন হয়েছে', [
        'amount'      => $amount,
        'from_id'     => $from,
        'to_id'       => $to,
        'out_txn_id'  => $outTxnId,
        'in_txn_id'   => $inTxnId
    ]);

} catch (Throwable $e) {
    if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) {
        $pdo->rollBack();
    }

    send_json(false, 'সমস্যা হয়েছে: ' . $e->getMessage(), [], 500);
}