-- Polish legal entities (country PL, e.g. KRS-derived company profiles).
-- Holds top-level identity, denormalized BASIC_INFO scalars, and JSONB for nested
-- section payloads (registration numbers, addresses, entities, activities).
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS + ADD COLUMN IF NOT EXISTS.
-- Requires PostgreSQL 11+ (IF NOT EXISTS on ADD COLUMN).

BEGIN;

CREATE TABLE IF NOT EXISTS public.pl_companies (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone,
    name text,
    slug text,
    registration_number text,
    country text NOT NULL DEFAULT 'PL'::text,
    CONSTRAINT pl_companies_pkey PRIMARY KEY (id)
);

-- BASIC_INFO (sections.pl-basic-info)
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS registered_name text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS legal_form text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS legal_status text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS registration_date date;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS registration_numbers jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS share_capital_value numeric(20, 4);
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS share_capital_currency text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS websites jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS basic_info_data_source text;

-- ADDRESSES (sections.pl-addresses)
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS addresses_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS addresses jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS addresses_data_source text;

-- ENTITIES: current bodies
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS authorized_body_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS authorized_body_name text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS authorized_body_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS authorized_body_data_source text;

ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS supervisory_body_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS supervisory_body_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS supervisory_body_data_source text;

ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS fe_representation_body_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS fe_representation_body_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS fe_representation_body_data_source text;

ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS commercial_proxies_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS commercial_proxies_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS commercial_proxies_data_source text;

ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS shareholders_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS shareholders_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS shareholders_data_source text;

-- ACTIVITIES (sections.pl-activities)
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS activities_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS activities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS activities_data_source text;

-- ENTITIES: former bodies
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_authorized_body_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_authorized_body_name text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_authorized_body_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_authorized_body_data_source text;

ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_supervisory_body_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_supervisory_body_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_supervisory_body_data_source text;

ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_fe_representation_body_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_fe_representation_body_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_fe_representation_body_data_source text;

ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_commercial_proxies_heading_key text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_commercial_proxies_entities jsonb;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS former_commercial_proxies_data_source text;

-- Lossless API / ingest snapshot (full payload including sections array)
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS source_document jsonb;

CREATE UNIQUE INDEX IF NOT EXISTS pl_companies_slug_uq
    ON public.pl_companies (slug)
    WHERE slug IS NOT NULL AND btrim(slug) <> '';

CREATE INDEX IF NOT EXISTS pl_companies_registration_number_idx
    ON public.pl_companies (registration_number)
    WHERE registration_number IS NOT NULL AND btrim(registration_number) <> '';

CREATE INDEX IF NOT EXISTS pl_companies_name_idx ON public.pl_companies (name);

CREATE INDEX IF NOT EXISTS pl_companies_registration_numbers_gin
    ON public.pl_companies USING gin (registration_numbers);

CREATE INDEX IF NOT EXISTS pl_companies_addresses_gin
    ON public.pl_companies USING gin (addresses);

-- Align with provision.sql / dexter-sentinel inserts (bigint `id` deployments).
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS registry_uuid text;
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS share_capital_value numeric(20, 4);
ALTER TABLE public.pl_companies ADD COLUMN IF NOT EXISTS share_capital_currency text;

COMMIT;
