-- Permit source tracking table

BEGIN;

CREATE TABLE IF NOT EXISTS public.permit_sources (
    region_id uuid NOT NULL REFERENCES public.regions(id) ON DELETE CASCADE,
    permit_id uuid NOT NULL REFERENCES public.permits(id) ON DELETE CASCADE,
    source_type text NOT NULL,
    source_name text NOT NULL,
    source_detail text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (region_id, permit_id)
);

CREATE INDEX IF NOT EXISTS permit_sources_source_name_idx
    ON public.permit_sources (source_name);

COMMIT;