-- ABP Sync Queries
-- Syncs basedata.abp from adhoc.tbl_abp_import
-- Run in order: 1 (insert), 2 (update), 3 (remove), 4 (resync project premises)

-- ============================================================
-- QUERY 1: Insert new UPRNs from tbl_abp_import not in basedata.abp
-- ============================================================

INSERT INTO basedata.abp (
    uprn,
    classification_code,
    class_description,
    parent_uprn,
    block,
    postcode,
    address,
    business_name,
    borough,
    property_type,
    cross_reference,
    geom,
    usrn,
    postcode_sector,
    street_name,
    street_authority
)
SELECT
    i.uprn,
    i.building_classification_code,
    i.building_classification_code_desc,
    i.parent_uprn,
    NULLIF(TRIM(i.pao_text), ''),
    i.postcode,
    NULLIF(TRIM(CONCAT_WS(', ',
        NULLIF(TRIM(i.sao), ''),
        NULLIF(TRIM(i.pao), ''),
        NULLIF(TRIM(i.street_description), ''),
        NULLIF(TRIM(i.locality_name), ''),
        NULLIF(TRIM(i.town_name), '')
    )), ''),
    NULLIF(TRIM(i.organisation_name), ''),
    i.administrative_area,
    i.building_type,
    NULLIF(TRIM(i.os_topo_toid), ''),
    CASE
        WHEN i.x_coordinate IS NOT NULL AND i.x_coordinate != ''
         AND i.y_coordinate IS NOT NULL AND i.y_coordinate != ''
        THEN ST_SetSRID(ST_MakePoint(i.x_coordinate::numeric, i.y_coordinate::numeric), 27700)
        ELSE NULL
    END,
    i.usrn,
    i.postcode_sector,
    i.street_description,
    i.administrative_area
FROM adhoc.tbl_abp_import i
WHERE i.source_id != 'NI_POINTER'
  AND NOT EXISTS (
    SELECT 1 FROM basedata.abp a WHERE a.uprn = i.uprn
);


-- ============================================================
-- QUERY 2: Update existing UPRNs in basedata.abp from tbl_abp_import
-- ============================================================

UPDATE basedata.abp a
SET
    classification_code = i.building_classification_code,
    class_description   = i.building_classification_code_desc,
    parent_uprn         = i.parent_uprn,
    block               = NULLIF(TRIM(i.pao_text), ''),
    postcode            = i.postcode,
    address             = NULLIF(TRIM(CONCAT_WS(', ',
                              NULLIF(TRIM(i.sao), ''),
                              NULLIF(TRIM(i.pao), ''),
                              NULLIF(TRIM(i.street_description), ''),
                              NULLIF(TRIM(i.locality_name), ''),
                              NULLIF(TRIM(i.town_name), '')
                          )), ''),
    business_name       = NULLIF(TRIM(i.organisation_name), ''),
    borough             = i.administrative_area,
    property_type       = i.building_type,
    cross_reference     = NULLIF(TRIM(i.os_topo_toid), ''),
    geom                = CASE
                              WHEN i.x_coordinate IS NOT NULL AND i.x_coordinate != ''
                               AND i.y_coordinate IS NOT NULL AND i.y_coordinate != ''
                              THEN ST_SetSRID(ST_MakePoint(i.x_coordinate::numeric, i.y_coordinate::numeric), 27700)
                              ELSE NULL
                          END,
    usrn                = i.usrn,
    postcode_sector     = i.postcode_sector,
    street_name         = i.street_description,
    street_authority    = i.administrative_area
FROM adhoc.tbl_abp_import i
WHERE a.uprn = i.uprn
  AND i.source_id != 'NI_POINTER';


-- ============================================================
-- QUERY 3: Remove UPRNs no longer in tbl_abp_import
--          First create the removed table if it doesn't exist,
--          then move the rows, then delete from abp.
-- ============================================================

CREATE TABLE IF NOT EXISTS basedata.abp_import_removed
    (LIKE basedata.abp INCLUDING ALL);

ALTER TABLE basedata.abp_import_removed
    ADD COLUMN IF NOT EXISTS removed_at timestamp NOT NULL DEFAULT now();

-- Move removed rows into abp_import_removed
INSERT INTO basedata.abp_import_removed
SELECT a.*, now()
FROM basedata.abp a
WHERE NOT EXISTS (
    SELECT 1 FROM adhoc.tbl_abp_import i WHERE i.uprn = a.uprn AND i.source_id != 'NI_POINTER'
);

-- Delete from abp
DELETE FROM basedata.abp a
WHERE NOT EXISTS (
    SELECT 1 FROM adhoc.tbl_abp_import i WHERE i.uprn = a.uprn AND i.source_id != 'NI_POINTER'
);


-- ============================================================
-- QUERY 4: Resync project premises
--          New-build UPRNs land inside existing project boundaries with no
--          boundary change, so no trigger fires on projects.projects and
--          nothing else picks them up. Queries 1-3 above are therefore not
--          finished until this has run.
--
--          Run sql/project_premises_resync.sql in psql. It is a dry run by
--          default; change its closing ROLLBACK to COMMIT to write.
--
--          Referenced rather than inlined so this file stays a script about
--          addresses.
-- ============================================================
