-- ============================================================================
-- LUPMIS — Land Use Parcels schema
-- ============================================================================
-- Source: "LAND USE INFORMATION FOR LUPMIS" (LUSPA, February 2026, revised)
-- Implements the parcel-attribute table defined by Stephen / LUSPA, with a
-- PostGIS geometry column and the indices needed for typical access patterns
-- (spatial queries, lookup by zone / district / locality, time filtering).
--
-- Conventions:
--   • Identifiers are unquoted (lowercase) — PostgreSQL folds them to lower
--     case anyway, and this avoids the need for double-quotes in queries.
--   • Source column names are PascalCase / Mixed_Case in the spec; their
--     mapping to snake_case is shown in COMMENT ON COLUMN.
--   • Geometry is stored in EPSG:4326 (WGS 84) for portability with the
--     remote API. The MultiPolygon type accommodates parcels with islands
--     or multi-part shapes.
--
-- Run as a database superuser (or a role with CREATEEXTENSION privilege)
-- in the target database.
-- ============================================================================

-- PostGIS is required for the geometry column and spatial index.
CREATE EXTENSION IF NOT EXISTS postgis;

-- Drop existing table for clean re-runs in dev. Comment out for production.
-- DROP TABLE IF EXISTS public.landuse_parcels CASCADE;

-- ---------------------------------------------------------------------------
-- Table: public.landuse_parcels
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.landuse_parcels (
    id              BIGSERIAL    PRIMARY KEY,

    -- Spec field 1: UPN — Unique Parcel Number (Integer, up to 10 digits).
    -- 10-digit integers can exceed INTEGER's max (2,147,483,647), hence BIGINT.
    upn             BIGINT       NOT NULL,

    -- Spec field 2: Style — Colour Assign ID (Integer, 2 digits).
    -- References the colour palette defined in the Revised Zoning Guidelines
    -- and Planning Standards (2025). Optional FK to a lookup table.
    style           SMALLINT,

    -- Spec field 3: Landuse — Broad land use (Text, 50).
    landuse         VARCHAR(50),

    -- Spec field 4: Zone_Code — Zone acronym (Text, 5), e.g. "Re A".
    zone_code       VARCHAR(5),

    -- Spec field 5: Zone_Name — Zone name (Text, 50), e.g. "Residential Zone A".
    zone_name       VARCHAR(50),

    -- Spec field 6: Sector — Sector number of plan area (Text, 5).
    sector          VARCHAR(5),

    -- Spec field 7: Block — Block name within the sector (Text, 3).
    block           VARCHAR(3),

    -- Spec field 8: Parcel_No — Plot number for land registration (Text, 5).
    parcel_no       VARCHAR(5),

    -- Spec field 9: Prop_No — Property number for street addressing (Text, 5).
    prop_no         VARCHAR(5),

    -- Spec field 10: St_Name — Street name (Text, 18). From the Street Naming
    -- and Property Addressing System (SNPAS).
    st_name         VARCHAR(18),

    -- Spec field 11: Prop_Add — Property address (Text, 25).
    prop_add        VARCHAR(25),

    -- Spec field 12: Fac_Name — Facility name (Text, 100).
    fac_name        VARCHAR(100),

    -- Spec field 13: Min_Height — Minimum building height in storeys (Integer, 3).
    min_height      SMALLINT,

    -- Spec field 14: Max_Height — Maximum building height in storeys (Integer, 3).
    max_height      SMALLINT,

    -- Spec field 15: Eff_Date — Effective approval date by the District SPC.
    eff_date        DATE,

    -- Spec field 16: LP_Name — Local plan name (Text, 100).
    lp_name         VARCHAR(100),

    -- Spec field 17: Locality — Community / area name (Text, 50).
    locality        VARCHAR(50),

    -- Spec field 18: MMDA — Metropolitan / Municipal / District Assembly
    --                       abbreviation (Text, 10), e.g. "LADMA".
    mmda            VARCHAR(10),

    -- Spec field 19: Last_Update — Last update on a parcel (e.g. change of
    -- use approved by SPC).
    last_update     DATE,

    -- Spec field 20: Remarks — Additional info (Text, 200).
    remarks         VARCHAR(200),

    -- ------------------------------------------------------------------
    -- Geometry — parcel polygon in WGS 84 (EPSG:4326).
    -- MultiPolygon allows parcels with islands or disjoint parts.
    -- ------------------------------------------------------------------
    geom            geometry(MultiPolygon, 4326),

    -- ------------------------------------------------------------------
    -- Audit columns (not in the spec, added for change tracking)
    -- ------------------------------------------------------------------
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    -- ------------------------------------------------------------------
    -- Constraints
    -- ------------------------------------------------------------------
    CONSTRAINT uq_landuse_parcels_upn        UNIQUE (upn),
    CONSTRAINT ck_landuse_parcels_style      CHECK (style       IS NULL OR style      >= 0),
    CONSTRAINT ck_landuse_parcels_min_height CHECK (min_height  IS NULL OR min_height >= 0),
    CONSTRAINT ck_landuse_parcels_max_height CHECK (max_height  IS NULL OR max_height >= 0),
    CONSTRAINT ck_landuse_parcels_height_order
        CHECK (min_height IS NULL OR max_height IS NULL OR min_height <= max_height)
);

-- ---------------------------------------------------------------------------
-- Column comments — preserve the source-document descriptions
-- ---------------------------------------------------------------------------
COMMENT ON TABLE  public.landuse_parcels IS 'Land use parcels — LUSPA spec, February 2026 (revised).';

COMMENT ON COLUMN public.landuse_parcels.upn         IS 'UPN — Unique Parcel Number (Integer, 10 digits).';
COMMENT ON COLUMN public.landuse_parcels.style       IS 'Style — Colour Assign ID per Revised Zoning Guidelines (2025).';
COMMENT ON COLUMN public.landuse_parcels.landuse     IS 'Broad land use, e.g. Residential, Commercial, Mixed.';
COMMENT ON COLUMN public.landuse_parcels.zone_code   IS 'Zone code (acronym), e.g. Re A.';
COMMENT ON COLUMN public.landuse_parcels.zone_name   IS 'Zone name, e.g. Residential Zone A.';
COMMENT ON COLUMN public.landuse_parcels.sector      IS 'Sector number of the plan area.';
COMMENT ON COLUMN public.landuse_parcels.block       IS 'Block name within the sector.';
COMMENT ON COLUMN public.landuse_parcels.parcel_no   IS 'Plot number for land registration.';
COMMENT ON COLUMN public.landuse_parcels.prop_no     IS 'Property number for street addressing.';
COMMENT ON COLUMN public.landuse_parcels.st_name     IS 'Street name (max 18 characters, per SNPAS).';
COMMENT ON COLUMN public.landuse_parcels.prop_add    IS 'Property address of parcel.';
COMMENT ON COLUMN public.landuse_parcels.fac_name    IS 'Facility name of property.';
COMMENT ON COLUMN public.landuse_parcels.min_height  IS 'Minimum building height (storeys).';
COMMENT ON COLUMN public.landuse_parcels.max_height  IS 'Maximum building height (storeys).';
COMMENT ON COLUMN public.landuse_parcels.eff_date    IS 'Effective approval date by the District Spatial Planning Committee.';
COMMENT ON COLUMN public.landuse_parcels.lp_name     IS 'Local plan name.';
COMMENT ON COLUMN public.landuse_parcels.locality    IS 'Name of community or area.';
COMMENT ON COLUMN public.landuse_parcels.mmda        IS 'Metropolitan/Municipal/District Assembly abbreviation, e.g. LADMA.';
COMMENT ON COLUMN public.landuse_parcels.last_update IS 'Last update on a parcel (e.g. change of use approved by SPC).';
COMMENT ON COLUMN public.landuse_parcels.remarks     IS 'Additional information on the parcel.';
COMMENT ON COLUMN public.landuse_parcels.geom        IS 'Parcel boundary geometry (MultiPolygon, EPSG:4326).';
COMMENT ON COLUMN public.landuse_parcels.created_at  IS 'Row-creation timestamp (audit).';
COMMENT ON COLUMN public.landuse_parcels.updated_at  IS 'Row last-modified timestamp (audit, maintained by trigger).';

-- ---------------------------------------------------------------------------
-- Indices
-- ---------------------------------------------------------------------------

-- Spatial index — required for any ST_Intersects / ST_Within / map-bbox query.
CREATE INDEX IF NOT EXISTS idx_landuse_parcels_geom
    ON public.landuse_parcels
    USING GIST (geom);

-- B-tree indices for common attribute lookups.
-- (uq_landuse_parcels_upn already creates an implicit index on upn.)
CREATE INDEX IF NOT EXISTS idx_landuse_parcels_zone_code
    ON public.landuse_parcels (zone_code);

CREATE INDEX IF NOT EXISTS idx_landuse_parcels_mmda
    ON public.landuse_parcels (mmda);

CREATE INDEX IF NOT EXISTS idx_landuse_parcels_locality
    ON public.landuse_parcels (locality);

CREATE INDEX IF NOT EXISTS idx_landuse_parcels_lp_name
    ON public.landuse_parcels (lp_name);

CREATE INDEX IF NOT EXISTS idx_landuse_parcels_eff_date
    ON public.landuse_parcels (eff_date);

CREATE INDEX IF NOT EXISTS idx_landuse_parcels_last_update
    ON public.landuse_parcels (last_update);

-- Composite index for the very common "find all parcels in MMDA X with zone Y" query.
CREATE INDEX IF NOT EXISTS idx_landuse_parcels_mmda_zone
    ON public.landuse_parcels (mmda, zone_code);

-- ---------------------------------------------------------------------------
-- Trigger — keep updated_at fresh on every UPDATE
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fn_landuse_parcels_set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_landuse_parcels_set_updated_at ON public.landuse_parcels;

CREATE TRIGGER trg_landuse_parcels_set_updated_at
BEFORE UPDATE ON public.landuse_parcels
FOR EACH ROW
EXECUTE FUNCTION public.fn_landuse_parcels_set_updated_at();

-- ============================================================================
-- End of script
-- ============================================================================