-- ============================================================================
-- Absurdities NG  |  Phase 1 schema  |  01 Core: extensions, helpers, locations
-- Owner: FlamyTech Computers Limited
-- Target: PostgreSQL 16+
-- ============================================================================

CREATE EXTENSION IF NOT EXISTS citext;     -- case-insensitive usernames and emails
CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- typo-tolerant search, prefix matching
CREATE EXTENSION IF NOT EXISTS unaccent;   -- accent-insensitive search
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- gen_random_uuid(), digest()

-- Keeps updated_at current on every UPDATE. Attached to tables below.
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ----------------------------------------------------------------------------
-- Nigerian administrative hierarchy: State -> LGA -> Town
-- ----------------------------------------------------------------------------
CREATE TABLE states (
    id          smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        varchar(60)  NOT NULL UNIQUE,
    slug        varchar(80)  NOT NULL UNIQUE,
    code        varchar(4)   NOT NULL UNIQUE,
    geopolitical_zone varchar(20) NOT NULL
        CHECK (geopolitical_zone IN ('North Central','North East','North West','South East','South South','South West')),
    is_active   boolean      NOT NULL DEFAULT true,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now()
);

CREATE TABLE lgas (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id    smallint     NOT NULL REFERENCES states(id) ON DELETE RESTRICT,
    name        varchar(100) NOT NULL,
    slug        varchar(120) NOT NULL,
    is_active   boolean      NOT NULL DEFAULT true,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (state_id, slug)
);
CREATE INDEX lgas_state_idx ON lgas (state_id);
CREATE INDEX lgas_name_trgm ON lgas USING gin (name gin_trgm_ops);

CREATE TABLE towns (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    lga_id      integer      NOT NULL REFERENCES lgas(id) ON DELETE RESTRICT,
    name        varchar(120) NOT NULL,
    slug        varchar(140) NOT NULL,
    latitude    numeric(9,6),
    longitude   numeric(9,6),
    is_active   boolean      NOT NULL DEFAULT true,
    created_by_user_id bigint,             -- users may suggest a missing town
    approved_at timestamptz,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (lga_id, slug)
);
CREATE INDEX towns_lga_idx ON towns (lga_id);
CREATE INDEX towns_name_trgm ON towns USING gin (name gin_trgm_ops);

CREATE TRIGGER states_updated BEFORE UPDATE ON states FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER lgas_updated   BEFORE UPDATE ON lgas   FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER towns_updated  BEFORE UPDATE ON towns  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ----------------------------------------------------------------------------
-- Site settings (feature switches live here)
-- ----------------------------------------------------------------------------
CREATE TABLE site_settings (
    key         varchar(100) PRIMARY KEY,
    value       jsonb        NOT NULL,
    group_name  varchar(50)  NOT NULL DEFAULT 'general',
    description text,
    is_public   boolean      NOT NULL DEFAULT false,   -- safe to expose via public API
    updated_by_staff_id bigint,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now()
);
CREATE TRIGGER site_settings_updated BEFORE UPDATE ON site_settings FOR EACH ROW EXECUTE FUNCTION set_updated_at();