CREATE SCHEMA IF NOT EXISTS import;

-- Transfere os lotes reais do plano GPL do Kilamba (DWG -> DXF -> import.kilamba_dxf)
-- para territorial.projectos / territorial.lotes.
-- Pré-requisito: import.kilamba_dxf carregado com ogr2ogr em EPSG:32733.
-- Idempotente: apaga e recria apenas o projecto 'Kilamba — Plano GPL V3 (2023)'.
\pset pager off
\set ON_ERROR_STOP on
BEGIN;

SET max_parallel_workers_per_gather = 0;

-- Rótulos de lote do plano (LHU 12, LE 1, ...) em todas as camadas de legendas da zona do Kilamba
DROP TABLE IF EXISTS import.kilamba_rotulos;
CREATE TABLE import.kilamba_rotulos AS
SELECT DISTINCT ON (trim(regexp_replace(t.text, '\s+', ' ', 'g')), round(ST_X(t.geom)), round(ST_Y(t.geom)))
       t.fid, trim(regexp_replace(t.text, '\s+', ' ', 'g')) AS rotulo, t.geom
FROM import.kilamba_dxf t
WHERE t.layer IN ('EGTI_Legendas', 'L_001_AreaVerdeProteccaoEnquadramento')
  AND ST_GeometryType(t.geom) = 'ST_Point' AND trim(t.text) ~ '^L[A-Z]{1,3} ?\d+'
  AND ST_Y(t.geom) BETWEEN 8900000 AND 9100000;
CREATE INDEX ON import.kilamba_rotulos USING gist (geom);

-- 1. Polígonos dos lotes, apenas na zona georreferenciada do Kilamba
--    (o desenho contém uma cópia deslocada para N ~ 18 000 000 que duplica lotes já existentes, e é ignorada):
--    a) polilinhas fechadas da camada EGTI_PL_LOTES;
--    b) polilinhas da mesma camada com folga de fecho inferior a 1 m;
--    c) polígonos da camada "Lotes" (Área Verde de Protecção e Enquadramento, LHU 01 a LHU 80).
DROP TABLE IF EXISTS import.kilamba_lotes_final;
CREATE TABLE import.kilamba_lotes_final AS
SELECT d.fid, 'EGTI_PL_LOTES'::text AS origem,
       (ST_Dump(ST_CollectionExtract(ST_MakeValid(ST_MakePolygon(ST_Force2D(d.geom))), 3))).geom AS geom
FROM import.kilamba_dxf d
WHERE d.layer IN ('EGTI_PL_LOTES', 'Lotes')
  AND ST_GeometryType(d.geom) = 'ST_LineString'
  AND ST_IsClosed(d.geom) AND ST_NPoints(d.geom) >= 4
  AND ST_YMax(d.geom) BETWEEN 8900000 AND 9100000
  AND ST_XMax(d.geom) BETWEEN 250000 AND 400000
UNION ALL
SELECT d.fid, 'EGTI_PL_LOTES (fecho < 1 m)',
       (ST_Dump(ST_CollectionExtract(ST_MakeValid(ST_MakePolygon(ST_AddPoint(ST_Force2D(d.geom), ST_StartPoint(ST_Force2D(d.geom))))), 3))).geom
FROM import.kilamba_dxf d
WHERE d.layer = 'EGTI_PL_LOTES'
  AND ST_GeometryType(d.geom) = 'ST_LineString'
  AND NOT ST_IsClosed(d.geom) AND ST_NPoints(d.geom) >= 4
  AND ST_Distance(ST_StartPoint(d.geom), ST_EndPoint(d.geom)) < 1
  AND ST_YMax(d.geom) BETWEEN 8900000 AND 9100000;
UPDATE import.kilamba_lotes_final f SET origem = 'Lotes'
FROM import.kilamba_dxf d WHERE d.fid = f.fid AND d.layer = 'Lotes';
DELETE FROM import.kilamba_lotes_final WHERE ST_Area(geom) < 50;
CREATE INDEX ON import.kilamba_lotes_final USING gist (geom);

-- 2. Remover polígonos duplicados (sobreposição > 90% com outro de fid inferior)
DELETE FROM import.kilamba_lotes_final b
USING import.kilamba_lotes_final a
WHERE a.fid < b.fid AND ST_Intersects(a.geom, b.geom)
  AND ST_Area(ST_Intersection(a.geom, b.geom)) > 0.9 * least(ST_Area(a.geom), ST_Area(b.geom));

-- 2b. Lotes do plano que só têm rótulo: usa-se o polígono da "Linha Base" que contém o rótulo,
--     desde que não se sobreponha a lotes já delimitados
INSERT INTO import.kilamba_lotes_final (fid, origem, geom)
SELECT DISTINCT ON (p.fid) p.fid, 'Linha Base (rótulo ' || r.rotulo || ')', p.g
FROM import.kilamba_rotulos r
JOIN LATERAL (
    SELECT d.fid, ST_MakeValid(ST_MakePolygon(ST_Force2D(d.geom))) AS g
    FROM import.kilamba_dxf d
    WHERE d.layer IN ('EGTI_Linha Base', '_Linha Base')
      AND ST_GeometryType(d.geom) = 'ST_LineString' AND ST_IsClosed(d.geom) AND ST_NPoints(d.geom) >= 4
      AND d.geom && r.geom AND ST_Contains(ST_MakePolygon(ST_Force2D(d.geom)), r.geom)
      AND ST_Area(ST_MakePolygon(ST_Force2D(d.geom))) BETWEEN 100 AND 20000
    ORDER BY ST_Area(ST_MakePolygon(ST_Force2D(d.geom))) LIMIT 1
) p ON true
WHERE NOT EXISTS (SELECT 1 FROM import.kilamba_lotes_final f WHERE ST_Intersects(f.geom, r.geom))
  AND ST_GeometryType(p.g) = 'ST_Polygon'
  AND NOT EXISTS (SELECT 1 FROM import.kilamba_lotes_final f
                  WHERE ST_Intersects(f.geom, p.g) AND ST_Area(ST_Intersection(f.geom, p.g)) > 0.1 * ST_Area(p.g));

-- 3. Rótulo (LHU 12, LE 1, ...), uso (pelo rótulo ou pela trama de uso do solo), dimensões e quarteirão
ALTER TABLE import.kilamba_lotes_final
    ADD COLUMN rotulo text, ADD COLUMN uso text, ADD COLUMN quadra text, ADD COLUMN numero int,
    ADD COLUMN largura numeric, ADD COLUMN comprimento numeric;

UPDATE import.kilamba_lotes_final c SET rotulo = (
    SELECT r.rotulo FROM import.kilamba_rotulos r WHERE ST_Intersects(c.geom, r.geom)
    ORDER BY ST_Distance(r.geom, ST_PointOnSurface(c.geom)) LIMIT 1);

UPDATE import.kilamba_lotes_final c SET uso = CASE substring(c.rotulo FROM '^(L[A-Z]+)')
        WHEN 'LHU' THEN 'Habitação unifamiliar'
        WHEN 'LHM' THEN 'Habitação multifamiliar'
        WHEN 'LMF' THEN 'Habitação multifamiliar'
        WHEN 'LC'  THEN 'Comércio'
        WHEN 'LUM' THEN 'Uso misto'
        WHEN 'LSV' THEN 'Serviços'
        WHEN 'LE'  THEN 'Equipamento'
    END;

UPDATE import.kilamba_lotes_final c SET uso = (
    SELECT CASE
             WHEN t.layer LIKE '%UNIFAMILIAR'   THEN 'Habitação unifamiliar'
             WHEN t.layer LIKE '%MULTIFAMILIAR' THEN 'Habitação multifamiliar'
             WHEN t.layer LIKE '%USO MISTO'     THEN 'Uso misto'
             WHEN t.layer LIKE '%SERVI%'        THEN 'Serviços'
             WHEN t.layer LIKE '%RCIO'          THEN 'Comércio'
             WHEN t.layer LIKE '%VERDE'         THEN 'Espaço verde'
           END
    FROM import.kilamba_dxf t
    WHERE (t.layer LIKE 'EGTI_HATCH %' OR t.layer LIKE 'EGTI_ESPA%O VERDE')
      AND ST_GeometryType(t.geom) IN ('ST_Polygon', 'ST_MultiPolygon')
      AND ST_Intersects(t.geom, ST_PointOnSurface(c.geom))
    ORDER BY ST_Area(t.geom)
    LIMIT 1
)
WHERE c.uso IS NULL;

UPDATE import.kilamba_lotes_final SET uso = 'Por classificar' WHERE uso IS NULL;

-- Lados do rectângulo orientado mínimo (frente = lado menor)
UPDATE import.kilamba_lotes_final c SET
    largura = round(least(s.a, s.b)::numeric, 2),
    comprimento = round(greatest(s.a, s.b)::numeric, 2)
FROM (SELECT fid,
             ST_Distance(ST_PointN(ST_ExteriorRing(ST_OrientedEnvelope(geom)), 1), ST_PointN(ST_ExteriorRing(ST_OrientedEnvelope(geom)), 2)) AS a,
             ST_Distance(ST_PointN(ST_ExteriorRing(ST_OrientedEnvelope(geom)), 2), ST_PointN(ST_ExteriorRing(ST_OrientedEnvelope(geom)), 3)) AS b
      FROM import.kilamba_lotes_final) s
WHERE s.fid = c.fid;

-- Quarteirões: lotes a menos de 15 m uns dos outros; numerados de Norte para Sul, Oeste para Este
WITH grupos AS (
    SELECT fid, ST_ClusterDBSCAN(geom, eps := 15, minpoints := 1) OVER () AS g FROM import.kilamba_lotes_final
), ordem AS (
    SELECT g, row_number() OVER (ORDER BY round(max(ST_YMax(k.geom)) / 100) DESC, min(ST_XMin(k.geom))) AS n
    FROM grupos JOIN import.kilamba_lotes_final k USING (fid) GROUP BY g
)
UPDATE import.kilamba_lotes_final c SET quadra = 'Q' || lpad(o.n::text, 2, '0')
FROM grupos gr JOIN ordem o USING (g) WHERE gr.fid = c.fid;

WITH num AS (
    SELECT fid, row_number() OVER (ORDER BY quadra, round(ST_Y(ST_Centroid(geom)) / 5) DESC, ST_X(ST_Centroid(geom))) AS n
    FROM import.kilamba_lotes_final
)
UPDATE import.kilamba_lotes_final c SET numero = num.n FROM num WHERE num.fid = c.fid;

-- 4. Projecto e lotes no schema territorial
DELETE FROM territorial.projectos WHERE nome = 'Kilamba — Plano GPL V3 (2023)';

WITH limite AS (
    SELECT ST_ConvexHull(ST_Collect(geom)) AS g FROM import.kilamba_lotes_final
)
INSERT INTO territorial.projectos (nome, localizacao, municipio, area_ha, estado, data_inicio,
                                   base_lat, base_lng, utm_e, utm_n, geom)
SELECT 'Kilamba — Plano GPL V3 (2023)',
       'Distrito Urbano do Kilamba, Município de Belas (GPL Plano Kilamba Alterado, Actualização V3 - 2023)',
       'Belas', round((ST_Area(g) / 10000)::numeric, 3), 'Em planeamento', DATE '2023-01-01',
       ST_Y(ST_Transform(ST_Centroid(g), 4326)), ST_X(ST_Transform(ST_Centroid(g), 4326)),
       round(ST_X(ST_Centroid(g))::numeric, 3), round(ST_Y(ST_Centroid(g))::numeric, 3),
       ST_Transform(g, 4326)
FROM limite;

INSERT INTO territorial.lotes (projecto_id, numero, quadra, lote_numero, uso, largura_m, comprimento_m, area_m2,
                               estado, utm_e, utm_n, lat, lng, geom)
SELECT p.id, k.numero, k.quadra,
       coalesce(k.rotulo, k.quadra || '-' || lpad((row_number() OVER (PARTITION BY k.quadra ORDER BY k.numero))::text, 3, '0')),
       k.uso, k.largura, k.comprimento, round(ST_Area(k.geom)::numeric, 2),
       'free',
       round(ST_X(ST_Centroid(k.geom))::numeric, 3), round(ST_Y(ST_Centroid(k.geom))::numeric, 3),
       ST_Y(ST_Transform(ST_Centroid(k.geom), 4326)), ST_X(ST_Transform(ST_Centroid(k.geom), 4326)),
       ST_Transform(k.geom, 4326)
FROM import.kilamba_lotes_final k
CROSS JOIN (SELECT id FROM territorial.projectos WHERE nome = 'Kilamba — Plano GPL V3 (2023)') p;

-- 4b. Lotes identificados no plano apenas pelo rótulo (sem polígono no desenho): ficam como lotes
--     pontuais "por delimitar", na posição do rótulo e no quarteirão mais próximo
INSERT INTO territorial.lotes (projecto_id, numero, quadra, lote_numero, uso, estado, utm_e, utm_n, lat, lng, observacoes)
SELECT p.id, m.n + row_number() OVER (ORDER BY round(ST_Y(r.geom) / 5) DESC, ST_X(r.geom)),
       coalesce((SELECT k.quadra FROM import.kilamba_lotes_final k
                 WHERE ST_DWithin(k.geom, r.geom, 60) ORDER BY ST_Distance(k.geom, r.geom) LIMIT 1), 'S/Q'),
       r.rotulo,
       CASE substring(r.rotulo FROM '^(L[A-Z]+)')
            WHEN 'LHU' THEN 'Habitação unifamiliar' WHEN 'LHM' THEN 'Habitação multifamiliar'
            WHEN 'LMF' THEN 'Habitação multifamiliar' WHEN 'LC' THEN 'Comércio'
            WHEN 'LUM' THEN 'Uso misto' WHEN 'LSV' THEN 'Serviços' WHEN 'LE' THEN 'Equipamento'
            ELSE 'Por classificar' END,
       'free',
       round(ST_X(r.geom)::numeric, 3), round(ST_Y(r.geom)::numeric, 3),
       ST_Y(ST_Transform(r.geom, 4326)), ST_X(ST_Transform(r.geom, 4326)),
       'Lote por delimitar: identificado no plano apenas pelo rótulo, sem polígono no desenho.'
FROM (SELECT DISTINCT ON (rotulo) * FROM import.kilamba_rotulos ORDER BY rotulo, fid) r
CROSS JOIN (SELECT id FROM territorial.projectos WHERE nome = 'Kilamba — Plano GPL V3 (2023)') p
CROSS JOIN (SELECT max(numero) AS n FROM import.kilamba_lotes_final) m
WHERE NOT EXISTS (SELECT 1 FROM import.kilamba_lotes_final k WHERE ST_DWithin(k.geom, r.geom, 0.5))
  AND NOT EXISTS (SELECT 1 FROM import.kilamba_lotes_final k WHERE k.rotulo = r.rotulo
                  AND ST_DWithin(k.geom, r.geom, 200));

UPDATE territorial.projectos p SET
    largura_lote_m = s.larg, comprimento_lote_m = s.comp, perimetro_m = round(ST_Perimeter(ST_Transform(p.geom, 32733))::numeric, 2)
FROM (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY largura_m) AS larg,
             percentile_cont(0.5) WITHIN GROUP (ORDER BY comprimento_m) AS comp
      FROM territorial.lotes l JOIN territorial.projectos pp ON pp.id = l.projecto_id
      WHERE pp.nome = 'Kilamba — Plano GPL V3 (2023)') s
WHERE p.nome = 'Kilamba — Plano GPL V3 (2023)';

COMMIT;

-- 5. Resumo
\echo '== Projecto criado'
SELECT id, nome, area_ha, perimetro_m, largura_lote_m, comprimento_lote_m, round(base_lat::numeric, 5) AS lat, round(base_lng::numeric, 5) AS lng
FROM territorial.projectos WHERE nome = 'Kilamba — Plano GPL V3 (2023)';
\echo '== Lotes por uso'
SELECT uso, count(*) AS lotes, round(avg(area_m2)) AS area_media_m2, count(*) FILTER (WHERE lote_numero ~ '^L') AS com_codigo_do_plano
FROM territorial.lotes l JOIN territorial.projectos p ON p.id = l.projecto_id
WHERE p.nome = 'Kilamba — Plano GPL V3 (2023)' GROUP BY 1 ORDER BY 2 DESC;
\echo '== Geometrias'
SELECT count(*) AS lotes, count(*) FILTER (WHERE ST_IsValid(l.geom)) AS validas, count(DISTINCT quadra) AS quarteiroes
FROM territorial.lotes l JOIN territorial.projectos p ON p.id = l.projecto_id
WHERE p.nome = 'Kilamba — Plano GPL V3 (2023)';