<?php
declare(strict_types=1);

error_reporting(E_ALL);
ini_set('display_errors', '0');
header('Content-Type: application/json; charset=utf-8');

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

if (!function_exists('out_json')) {
    function out_json(bool $success, string $message = '', array $data = [], int $status = 200): void
    {
        http_response_code($status);
        echo json_encode([
            'success' => $success,
            'status'  => $status,
            'message' => $message,
            'data'    => $data
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        exit;
    }
}

try {
    $pdo = $pdo ?? $db ?? $conn ?? null;

    if (!$pdo instanceof PDO) {
        out_json(false, 'PDO database connection পাওয়া যায়নি', [], 500);
    }

    $loggedIn = false;
    if (function_exists('is_logged_in')) {
        $loggedIn = is_logged_in();
    } else {
        $loggedIn = !empty($_SESSION['user_id']) || !empty($_SESSION['uid']) || !empty($_SESSION['admin_id']);
    }

    if (!$loggedIn) {
        out_json(false, 'লগইন সেশন পাওয়া যায়নি', [], 401);
    }

    function table_exists(PDO $pdo, string $table): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*) 
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() 
              AND table_name = ?
        ");
        $stmt->execute([$table]);
        return (int)$stmt->fetchColumn() > 0;
    }

    function get_columns(PDO $pdo, string $table): array
    {
        $stmt = $pdo->prepare("
            SELECT COLUMN_NAME
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = ?
            ORDER BY ORDINAL_POSITION
        ");
        $stmt->execute([$table]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];
    }

    function pick_col(array $columns, array $candidates): ?string
    {
        foreach ($candidates as $col) {
            if (in_array($col, $columns, true)) {
                return $col;
            }
        }
        return null;
    }

    function numf($value, int $d = 3): float
    {
        return round((float)$value, $d);
    }

    if (!table_exists($pdo, 'parties')) {
        out_json(false, 'parties table পাওয়া যায়নি', [], 500);
    }
    if (!table_exists($pdo, 'karigar_issues')) {
        out_json(false, 'karigar_issues table পাওয়া যায়নি', [], 500);
    }

    $search       = trim((string)($_GET['search'] ?? ''));
    $mode         = strtoupper(trim((string)($_GET['mode'] ?? 'ALL'))); // ALL | SIMPLE | JOB
    $overdueDays  = (int)($_GET['overdue_days'] ?? 0);
    $karigarId    = (int)($_GET['karigar_id'] ?? 0);
    $sort         = strtoupper(trim((string)($_GET['sort'] ?? 'AGE_DESC')));

    $issueCols   = get_columns($pdo, 'karigar_issues');
    $recvCols    = table_exists($pdo, 'karigar_receives') ? get_columns($pdo, 'karigar_receives') : [];
    $jobCols     = table_exists($pdo, 'karigar_jobs') ? get_columns($pdo, 'karigar_jobs') : [];
    $partyCols   = get_columns($pdo, 'parties');

    $issueIdCol        = pick_col($issueCols, ['id']);
    $issueDateCol      = pick_col($issueCols, ['issue_date', 'issued_at', 'created_at']);
    $issueKarigarCol   = pick_col($issueCols, ['karigar_id', 'party_id']);
    $issueItemNameCol  = pick_col($issueCols, ['item_name', 'product_name', 'title']);
    $issueItemTypeCol  = pick_col($issueCols, ['item_type', 'category_name', 'category']);
    $issuePiecesCol    = pick_col($issueCols, ['pieces', 'qty', 'quantity']);
    $issueGrossCol     = pick_col($issueCols, ['gross_weight_gram', 'gross_gram', 'issued_gross_gram']);
    $issuePureCol      = pick_col($issueCols, ['pure_weight_gram', 'pure_gram', 'issued_pure_gram']);
    $issueKaratCol     = pick_col($issueCols, ['karat', 'karat_value']);
    $issueSourceCol    = pick_col($issueCols, ['source_type']);
    $issueJobIdCol     = pick_col($issueCols, ['job_id', 'karigar_job_id']);
    $issueStatusCol    = pick_col($issueCols, ['status']);

    $recvRelatedIssueCol = pick_col($recvCols, ['related_issue_id', 'issue_id']);
    $recvPureCol         = pick_col($recvCols, ['pure_received_gram', 'pure_weight_gram', 'received_pure_gram']);
    $recvWastageCol      = pick_col($recvCols, ['wastage_gram']);
    $recvShortageCol     = pick_col($recvCols, ['shortage_gram']);
    $recvLaborCol        = pick_col($recvCols, ['labor_amount']);
    $recvDateCol         = pick_col($recvCols, ['receive_date', 'created_at']);

    $jobIdCol          = pick_col($jobCols, ['id']);
    $jobNoCol          = pick_col($jobCols, ['job_no', 'job_code']);
    $jobDateCol        = pick_col($jobCols, ['job_date', 'created_at']);
    $jobDueDateCol     = pick_col($jobCols, ['due_date']);
    $jobKarigarCol     = pick_col($jobCols, ['karigar_id', 'party_id']);
    $jobItemNameCol    = pick_col($jobCols, ['item_name', 'product_name', 'title']);
    $jobItemTypeCol    = pick_col($jobCols, ['item_type', 'category_name', 'category']);
    $jobPiecesCol      = pick_col($jobCols, ['pieces', 'qty', 'quantity']);
    $jobIssuedPureCol  = pick_col($jobCols, ['issued_pure_gram', 'issue_pure_gram', 'pure_issued_gram']);
    $jobReceivedPureCol= pick_col($jobCols, ['received_pure_gram', 'pure_received_gram']);
    $jobWastageCol     = pick_col($jobCols, ['wastage_gram']);
    $jobShortageCol    = pick_col($jobCols, ['shortage_gram']);
    $jobPendingPureCol = pick_col($jobCols, ['pending_pure_gram']);
    $jobLaborDueCol    = pick_col($jobCols, ['labor_due_amount']);
    $jobStatusCol      = pick_col($jobCols, ['job_status', 'status']);

    $simpleRows = [];
    $jobRows    = [];

    if ($mode === 'ALL' || $mode === 'SIMPLE') {
        $recvSubquery = "SELECT 0 AS related_issue_id, 0 AS received_pure, 0 AS wastage, 0 AS shortage, 0 AS labor_amount, NULL AS last_receive_date";
        if ($recvRelatedIssueCol && !empty($recvCols)) {
            $recvPureExpr     = $recvPureCol     ? "COALESCE(`{$recvPureCol}`, 0)" : "0";
            $recvWastageExpr  = $recvWastageCol  ? "COALESCE(`{$recvWastageCol}`, 0)" : "0";
            $recvShortageExpr = $recvShortageCol ? "COALESCE(`{$recvShortageCol}`, 0)" : "0";
            $recvLaborExpr    = $recvLaborCol    ? "COALESCE(`{$recvLaborCol}`, 0)" : "0";
            $recvDateExpr     = $recvDateCol     ? "`{$recvDateCol}`" : "NULL";

            $recvSubquery = "
                SELECT
                    `{$recvRelatedIssueCol}` AS related_issue_id,
                    SUM({$recvPureExpr})     AS received_pure,
                    SUM({$recvWastageExpr})  AS wastage,
                    SUM({$recvShortageExpr}) AS shortage,
                    SUM({$recvLaborExpr})    AS labor_amount,
                    MAX({$recvDateExpr})     AS last_receive_date
                FROM `karigar_receives`
                GROUP BY `{$recvRelatedIssueCol}`
            ";
        }

        $issueDateExpr    = $issueDateCol     ? "ki.`{$issueDateCol}`" : "ki.created_at";
        $issueItemNameExpr= $issueItemNameCol ? "ki.`{$issueItemNameCol}`" : "''";
        $issueItemTypeExpr= $issueItemTypeCol ? "ki.`{$issueItemTypeCol}`" : "''";
        $issuePiecesExpr  = $issuePiecesCol   ? "ki.`{$issuePiecesCol}`" : "0";
        $issueGrossExpr   = $issueGrossCol    ? "ki.`{$issueGrossCol}`" : "0";
        $issuePureExpr    = $issuePureCol     ? "ki.`{$issuePureCol}`" : "0";
        $issueKaratExpr   = $issueKaratCol    ? "ki.`{$issueKaratCol}`" : "''";
        $issueSourceExpr  = $issueSourceCol   ? "ki.`{$issueSourceCol}`" : "'OTHER'";
        $issueJobExpr     = $issueJobIdCol    ? "ki.`{$issueJobIdCol}`" : "NULL";
        $issueStatusExpr  = $issueStatusCol   ? "ki.`{$issueStatusCol}`" : "'OPEN'";

        $where = ["p.is_karigar = 1"];
        $params = [];

        if ($issueKarigarCol) {
            if ($karigarId > 0) {
                $where[] = "ki.`{$issueKarigarCol}` = ?";
                $params[] = $karigarId;
            }
        }

        if ($search !== '') {
            $where[] = "(p.name LIKE ? OR p.phone LIKE ? OR {$issueItemNameExpr} LIKE ? OR {$issueItemTypeExpr} LIKE ?)";
            $like = '%' . $search . '%';
            array_push($params, $like, $like, $like, $like);
        }

        if ($issueStatusCol) {
            $where[] = "COALESCE(ki.`{$issueStatusCol}`, 'OPEN') <> 'CANCELLED'";
        }

        $whereSql = implode(' AND ', $where);

        $sql = "
            SELECT
                ki.`{$issueIdCol}` AS issue_id,
                {$issueDateExpr}   AS issue_date,
                ki.`{$issueKarigarCol}` AS karigar_id,
                p.name,
                COALESCE(p.phone, '') AS phone,
                {$issueItemNameExpr} AS item_name,
                {$issueItemTypeExpr} AS item_type,
                {$issuePiecesExpr}   AS pieces,
                {$issueGrossExpr}    AS issued_gross,
                {$issuePureExpr}     AS issued_pure,
                {$issueKaratExpr}    AS karat,
                {$issueSourceExpr}   AS source_type,
                {$issueJobExpr}      AS job_id,
                {$issueStatusExpr}   AS issue_status,
                COALESCE(ra.received_pure, 0) AS received_pure,
                COALESCE(ra.wastage, 0)       AS wastage,
                COALESCE(ra.shortage, 0)      AS shortage,
                COALESCE(ra.labor_amount, 0)  AS labor_due,
                ra.last_receive_date,
                DATEDIFF(CURDATE(), DATE({$issueDateExpr})) AS age_days
            FROM `karigar_issues` ki
            INNER JOIN `parties` p ON p.id = ki.`{$issueKarigarCol}`
            LEFT JOIN ({$recvSubquery}) ra ON ra.related_issue_id = ki.`{$issueIdCol}`
            WHERE {$whereSql}
            ORDER BY DATE({$issueDateExpr}) DESC, ki.`{$issueIdCol}` DESC
        ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        foreach ($rows as $r) {
            $issuedPure   = (float)$r['issued_pure'];
            $receivedPure = (float)$r['received_pure'];
            $wastage      = (float)$r['wastage'];
            $shortage     = (float)$r['shortage'];
            $pendingPure  = max(0, $issuedPure - ($receivedPure + $wastage + $shortage));
            $ageDays      = (int)$r['age_days'];

            if ($pendingPure <= 0.0001) {
                continue;
            }
            if ($overdueDays > 0 && $ageDays < $overdueDays) {
                continue;
            }

            $simpleRows[] = [
                'mode'           => 'SIMPLE',
                'mode_label'     => 'Simple Issue',
                'issue_id'       => (int)$r['issue_id'],
                'job_id'         => $r['job_id'] !== null ? (int)$r['job_id'] : null,
                'job_no'         => null,
                'karigar_id'     => (int)$r['karigar_id'],
                'karigar_name'   => (string)$r['name'],
                'phone'          => (string)$r['phone'],
                'issue_date'     => (string)$r['issue_date'],
                'due_date'       => null,
                'item_name'      => (string)$r['item_name'],
                'item_type'      => (string)$r['item_type'],
                'pieces'         => (int)$r['pieces'],
                'source_type'    => (string)$r['source_type'],
                'karat'          => (string)$r['karat'],
                'issued_gross'   => numf($r['issued_gross']),
                'issued_pure'    => numf($issuedPure),
                'received_pure'  => numf($receivedPure),
                'wastage'        => numf($wastage),
                'shortage'       => numf($shortage),
                'pending_pure'   => numf($pendingPure),
                'labor_due'      => round((float)$r['labor_due'], 2),
                'status'         => 'PENDING',
                'age_days'       => $ageDays,
                'last_receive_date' => $r['last_receive_date'],
                'is_overdue'     => $ageDays >= 7
            ];
        }
    }

    if (($mode === 'ALL' || $mode === 'JOB') && table_exists($pdo, 'karigar_jobs') && !empty($jobCols)) {
        $jobDateExpr      = $jobDateCol        ? "kj.`{$jobDateCol}`" : "kj.created_at";
        $jobDueDateExpr   = $jobDueDateCol     ? "kj.`{$jobDueDateCol}`" : "NULL";
        $jobNoExpr        = $jobNoCol          ? "kj.`{$jobNoCol}`" : "CONCAT('JOB-', LPAD(kj.`{$jobIdCol}`, 4, '0'))";
        $jobItemNameExpr  = $jobItemNameCol    ? "kj.`{$jobItemNameCol}`" : "''";
        $jobItemTypeExpr  = $jobItemTypeCol    ? "kj.`{$jobItemTypeCol}`" : "''";
        $jobPiecesExpr    = $jobPiecesCol      ? "kj.`{$jobPiecesCol}`" : "0";
        $jobIssuedPureExpr= $jobIssuedPureCol  ? "COALESCE(kj.`{$jobIssuedPureCol}`, 0)" : "0";
        $jobRecvPureExpr  = $jobReceivedPureCol? "COALESCE(kj.`{$jobReceivedPureCol}`, 0)" : "0";
        $jobWastageExpr   = $jobWastageCol     ? "COALESCE(kj.`{$jobWastageCol}`, 0)" : "0";
        $jobShortExpr     = $jobShortageCol    ? "COALESCE(kj.`{$jobShortageCol}`, 0)" : "0";
        $jobPendingExpr   = $jobPendingPureCol ? "COALESCE(kj.`{$jobPendingPureCol}`, 0)" : "GREATEST({$jobIssuedPureExpr} - ({$jobRecvPureExpr} + {$jobWastageExpr} + {$jobShortExpr}), 0)";
        $jobLaborDueExpr  = $jobLaborDueCol    ? "COALESCE(kj.`{$jobLaborDueCol}`, 0)" : "0";
        $jobStatusExpr    = $jobStatusCol      ? "COALESCE(kj.`{$jobStatusCol}`, 'OPEN')" : "'OPEN'";

        $where = ["p.is_karigar = 1"];
        $params = [];

        if ($jobKarigarCol && $karigarId > 0) {
            $where[] = "kj.`{$jobKarigarCol}` = ?";
            $params[] = $karigarId;
        }

        if ($search !== '') {
            $where[] = "(p.name LIKE ? OR p.phone LIKE ? OR {$jobNoExpr} LIKE ? OR {$jobItemNameExpr} LIKE ? OR {$jobItemTypeExpr} LIKE ?)";
            $like = '%' . $search . '%';
            array_push($params, $like, $like, $like, $like, $like);
        }

        $whereSql = implode(' AND ', $where);

        $sql = "
            SELECT
                kj.`{$jobIdCol}` AS job_id,
                {$jobNoExpr}      AS job_no,
                {$jobDateExpr}    AS job_date,
                {$jobDueDateExpr} AS due_date,
                kj.`{$jobKarigarCol}` AS karigar_id,
                p.name,
                COALESCE(p.phone, '') AS phone,
                {$jobItemNameExpr}    AS item_name,
                {$jobItemTypeExpr}    AS item_type,
                {$jobPiecesExpr}      AS pieces,
                {$jobIssuedPureExpr}  AS issued_pure,
                {$jobRecvPureExpr}    AS received_pure,
                {$jobWastageExpr}     AS wastage,
                {$jobShortExpr}       AS shortage,
                {$jobPendingExpr}     AS pending_pure,
                {$jobLaborDueExpr}    AS labor_due,
                {$jobStatusExpr}      AS job_status,
                DATEDIFF(CURDATE(), DATE({$jobDateExpr})) AS age_days
            FROM `karigar_jobs` kj
            INNER JOIN `parties` p ON p.id = kj.`{$jobKarigarCol}`
            WHERE {$whereSql}
            ORDER BY DATE({$jobDateExpr}) DESC, kj.`{$jobIdCol}` DESC
        ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        foreach ($rows as $r) {
            $pendingPure = (float)$r['pending_pure'];
            $ageDays     = (int)$r['age_days'];

            if ($pendingPure <= 0.0001 && !in_array(strtoupper((string)$r['job_status']), ['OPEN', 'IN_PROGRESS', 'PENDING'], true)) {
                continue;
            }
            if ($overdueDays > 0 && $ageDays < $overdueDays) {
                continue;
            }

            $jobRows[] = [
                'mode'           => 'JOB',
                'mode_label'     => 'Job Based',
                'issue_id'       => null,
                'job_id'         => (int)$r['job_id'],
                'job_no'         => (string)$r['job_no'],
                'karigar_id'     => (int)$r['karigar_id'],
                'karigar_name'   => (string)$r['name'],
                'phone'          => (string)$r['phone'],
                'issue_date'     => (string)$r['job_date'],
                'due_date'       => $r['due_date'],
                'item_name'      => (string)$r['item_name'],
                'item_type'      => (string)$r['item_type'],
                'pieces'         => (int)$r['pieces'],
                'source_type'    => 'JOB',
                'karat'          => '',
                'issued_gross'   => 0,
                'issued_pure'    => numf($r['issued_pure']),
                'received_pure'  => numf($r['received_pure']),
                'wastage'        => numf($r['wastage']),
                'shortage'       => numf($r['shortage']),
                'pending_pure'   => numf($pendingPure),
                'labor_due'      => round((float)$r['labor_due'], 2),
                'status'         => strtoupper((string)$r['job_status']),
                'age_days'       => $ageDays,
                'last_receive_date' => null,
                'is_overdue'     => $ageDays >= 7
            ];
        }
    }

    $allRows = array_merge($jobRows, $simpleRows);

    usort($allRows, function ($a, $b) use ($sort) {
        switch ($sort) {
            case 'NAME_ASC':
                return strcmp($a['karigar_name'], $b['karigar_name']);
            case 'PENDING_DESC':
                return $b['pending_pure'] <=> $a['pending_pure'];
            case 'LABOR_DESC':
                return $b['labor_due'] <=> $a['labor_due'];
            case 'AGE_ASC':
                return $a['age_days'] <=> $b['age_days'];
            case 'AGE_DESC':
            default:
                return $b['age_days'] <=> $a['age_days'];
        }
    });

    $summary = [
        'total_rows'          => count($allRows),
        'job_rows'            => count($jobRows),
        'simple_rows'         => count($simpleRows),
        'total_pending_pure'  => 0,
        'total_labor_due'     => 0,
        'overdue_rows'        => 0,
        'over_15_days'        => 0,
        'over_30_days'        => 0
    ];

    foreach ($allRows as $row) {
        $summary['total_pending_pure'] += (float)$row['pending_pure'];
        $summary['total_labor_due']    += (float)$row['labor_due'];
        if (!empty($row['is_overdue'])) {
            $summary['overdue_rows']++;
        }
        if ((int)$row['age_days'] >= 15) {
            $summary['over_15_days']++;
        }
        if ((int)$row['age_days'] >= 30) {
            $summary['over_30_days']++;
        }
    }

    $summary['total_pending_pure'] = numf($summary['total_pending_pure']);
    $summary['total_labor_due']    = round($summary['total_labor_due'], 2);

    out_json(true, 'Pending jobs fetched successfully', [
        'filters' => [
            'search'       => $search,
            'mode'         => $mode,
            'overdue_days' => $overdueDays,
            'karigar_id'   => $karigarId,
            'sort'         => $sort
        ],
        'summary' => $summary,
        'rows'    => $allRows
    ]);
} catch (Throwable $e) {
    out_json(false, 'Pending jobs load failed: ' . $e->getMessage(), [], 500);
}
