<?php
declare(strict_types=1);

require_once __DIR__ . '/db.php';

function ml_table_columns(PDO $pdo, string $schema, string $table): array
{
    $st = $pdo->prepare("SELECT column_name FROM information_schema.columns WHERE table_schema = :s AND table_name = :t");
    $st->execute([':s' => $schema, ':t' => $table]);
    return array_map('strval', $st->fetchAll(PDO::FETCH_COLUMN) ?: []);
}

function ml_table_exists(PDO $pdo, string $schema, string $table): bool
{
    $st = $pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema = :s AND table_name = :t LIMIT 1");
    $st->execute([':s' => $schema, ':t' => $table]);
    return (bool) $st->fetchColumn();
}

function ml_value(array $row, array $keys): ?string
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && trim((string)$row[$k]) !== '') {
            return trim((string)$row[$k]);
        }
    }
    return null;
}

function ml_numeric_tail(?string $value): ?string
{
    $value = trim((string)$value);
    if ($value === '') return null;
    if (preg_match('/(?:^|\.)(\d+)$/', $value, $m)) return $m[1];
    return ctype_digit($value) ? $value : null;
}

try {
    cadastro_require_write_access();

    $pdo = cadastro_pdo();
    $schema = cadastro_schema();
    $qSchema = cadastro_quote_ident($schema);
    $json = cadastro_read_json();

    $lotes = [];
    if (isset($json['lotes']) && is_array($json['lotes'])) $lotes = $json['lotes'];
    if (!$lotes && isset($json['items']) && is_array($json['items'])) $lotes = $json['items'];

    if (!$lotes) {
        cadastro_out(['ok' => false, 'message' => 'Nenhum lote recebido para salvar.'], 400);
    }

    $candidateTables = [
        'sisprommap_ctm_lotes',
        'ctm_lotes',
        'sisprommap_ctm_lotes',
        'jequie_2025_postgis',
        'Jequie_2025_postgis',
        'jequie_2025',
    ];

    $targets = [];
    foreach ($candidateTables as $table) {
        if (!ml_table_exists($pdo, $schema, $table)) continue;
        $cols = ml_table_columns($pdo, $schema, $table);
        if (!in_array('geom', $cols, true)) continue;
        $targets[$table] = $cols;
    }

    if (!$targets) {
        cadastro_out([
            'ok' => false,
            'message' => 'Nenhuma tabela de lote com coluna geom foi encontrada no schema ' . $schema,
            'schema' => $schema,
        ], 500);
    }

    $geomSql = cadastro_geom_expr(':wkt');
    $user = cadastro_user();
    $items = [];
    $updatedTotal = 0;
    $warnings = [];

    $pdo->beginTransaction();

    foreach ($lotes as $idx => $row) {
        if (!is_array($row)) {
            $items[] = ['index' => $idx, 'ok' => false, 'message' => 'Item inválido'];
            continue;
        }

        $wkt = trim((string)($row['wkt'] ?? ''));
        if ($wkt === '') {
            $items[] = ['index' => $idx, 'ok' => false, 'message' => 'WKT vazio'];
            continue;
        }

        $idLote = ml_value($row, ['id_lote', 'id_lote_2026', 'id_lot']);
        $id = ml_value($row, ['id']);
        $fid = ml_value($row, ['fid', 'fid_1']);
        $featureId = ml_value($row, ['feature_id']);
        $featureNum = ml_value($row, ['feature_id_num']) ?: ml_numeric_tail($featureId);
        $seq = ml_value($row, ['sequencial_contribuinte', 'sequencial', 'seq']);
        $inscricao = ml_value($row, ['inscricao', 'inscricao_imobiliaria']);

        $itemUpdates = [];

        foreach ($targets as $table => $cols) {
            $whereCandidates = [];
            if ($idLote !== null && in_array('id_lote', $cols, true)) $whereCandidates[] = ['id_lote', $idLote];
            if ($id !== null && in_array('id', $cols, true)) $whereCandidates[] = ['id', $id];
            if ($featureNum !== null && in_array('id', $cols, true)) $whereCandidates[] = ['id', $featureNum];
            if ($fid !== null && in_array('fid', $cols, true)) $whereCandidates[] = ['fid', $fid];
            if ($fid !== null && in_array('fid_1', $cols, true)) $whereCandidates[] = ['fid_1', $fid];
            if ($featureNum !== null && in_array('fid', $cols, true)) $whereCandidates[] = ['fid', $featureNum];
            if ($featureNum !== null && in_array('fid_1', $cols, true)) $whereCandidates[] = ['fid_1', $featureNum];
            if ($seq !== null && in_array('sequencial_contribuinte', $cols, true)) $whereCandidates[] = ['sequencial_contribuinte', $seq];
            if ($seq !== null && in_array('sequencial', $cols, true)) $whereCandidates[] = ['sequencial', $seq];
            if ($inscricao !== null && in_array('inscricao', $cols, true)) $whereCandidates[] = ['inscricao', $inscricao];

            if (!$whereCandidates) {
                $itemUpdates[] = ['table' => $table, 'updated' => 0, 'message' => 'Sem chave compatível'];
                continue;
            }

            $sets = ["geom = {$geomSql}"];
            if (in_array('updated_at', $cols, true)) $sets[] = 'updated_at = now()';
            if (in_array('updated_by', $cols, true)) $sets[] = 'updated_by = :updated_by';
            if (in_array('area_lote_m2', $cols, true)) $sets[] = "area_lote_m2 = " . cadastro_area_expr($geomSql);
            if (in_array('area', $cols, true)) $sets[] = "area = " . cadastro_area_expr($geomSql);
            if (in_array('latitude', $cols, true)) $sets[] = "latitude = ST_Y(ST_PointOnSurface({$geomSql}))";
            if (in_array('longitude', $cols, true)) $sets[] = "longitude = ST_X(ST_PointOnSurface({$geomSql}))";

            $qTable = cadastro_quote_ident($table);
            $updatedInTable = 0;
            $messages = [];

            foreach ($whereCandidates as [$col, $val]) {
                try {
                    $qCol = cadastro_quote_ident($col);
                    $sql = "UPDATE {$qSchema}.{$qTable} SET " . implode(', ', $sets) . " WHERE {$qCol}::text = :key_value";
                    $params = [':wkt' => $wkt, ':key_value' => (string)$val];
                    if (in_array('updated_by', $cols, true)) $params[':updated_by'] = $user;
                    $st = $pdo->prepare($sql);
                    $st->execute($params);
                    $count = $st->rowCount();
                    if ($count > 0) {
                        $updatedInTable += $count;
                        break;
                    }
                } catch (Throwable $e) {
                    $messages[] = $col . ': ' . $e->getMessage();
                    // Views não atualizáveis ou permissões específicas não devem impedir salvar em outra tabela.
                    continue;
                }
            }

            $updatedTotal += $updatedInTable;
            $itemUpdates[] = [
                'table' => $table,
                'updated' => $updatedInTable,
                'messages' => $messages,
            ];
        }

        $sumItem = array_sum(array_map(fn($x) => (int)($x['updated'] ?? 0), $itemUpdates));
        if ($sumItem <= 0) {
            $warnings[] = 'Lote índice ' . $idx . ' não atualizou nenhuma tabela. Chaves recebidas: id_lote=' . ($idLote ?? '-') . ', id=' . ($id ?? '-') . ', fid=' . ($fid ?? '-') . ', seq=' . ($seq ?? '-') . ', inscricao=' . ($inscricao ?? '-');
        }

        $items[] = [
            'index' => $idx,
            'ok' => $sumItem > 0,
            'updated_total' => $sumItem,
            'keys' => [
                'id_lote' => $idLote,
                'id' => $id,
                'fid' => $fid,
                'feature_id' => $featureId,
                'feature_id_num' => $featureNum,
                'sequencial_contribuinte' => $seq,
                'inscricao' => $inscricao,
            ],
            'updates' => $itemUpdates,
        ];
    }

    if ($updatedTotal <= 0) {
        $pdo->rollBack();
        cadastro_out([
            'ok' => false,
            'message' => 'Nenhum registro foi atualizado. A geometria não foi persistida.',
            'schema' => $schema,
            'targets' => array_keys($targets),
            'warnings' => $warnings,
            'items' => $items,
        ], 409);
    }

    $pdo->commit();

    cadastro_out([
        'ok' => true,
        'message' => 'Movimento de lotes salvo com sucesso.',
        'schema' => $schema,
        'targets' => array_keys($targets),
        'updated_total' => $updatedTotal,
        'warnings' => $warnings,
        'items' => $items,
    ]);
} catch (Throwable $e) {
    if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    cadastro_out([
        'ok' => false,
        'message' => $e->getMessage(),
        'file' => basename($e->getFile()),
        'line' => $e->getLine(),
    ], 500);
}
