-- Migration V2: Support for New Excel Template & Future Theme Rules

-- 1. Add new metadata columns to region_permit_details
ALTER TABLE public.region_permit_details 
ADD COLUMN IF NOT EXISTS filler_name text,
ADD COLUMN IF NOT EXISTS unit_name text,
ADD COLUMN IF NOT EXISTS source_update_date text;

-- 2. Create base table for theme rules (placeholder for future logic)
CREATE TABLE IF NOT EXISTS public.permit_theme_rules (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    permit_name text NOT NULL,
    theme_id uuid NOT NULL,
    region_id uuid, -- Optional: specific rule for a region
    created_at timestamp with time zone DEFAULT now() NOT NULL
);

-- 3. Comment on columns for clarity
COMMENT ON COLUMN public.region_permit_details.filler_name IS 'Original filler name from Excel (填表人)';
COMMENT ON COLUMN public.region_permit_details.unit_name IS 'Original unit name from Excel (单位名称)';
COMMENT ON COLUMN public.region_permit_details.source_update_date IS 'Original update date from Excel (表格更新日期)';
COMMENT ON TABLE public.permit_theme_rules IS 'Registry for mapping permits to themes manually or via rules';