CREATE TABLE programs (
  -- Identity
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT UNIQUE NOT NULL,              -- URL-friendly identifier
  name TEXT NOT NULL,                     -- "Supplemental Nutrition Assistance Program"
  short_name TEXT,                        -- "SNAP"

  -- Classification
  category TEXT NOT NULL,                 -- See categories below
  subcategory TEXT,
  tags TEXT[] DEFAULT '{}',               -- ["food", "nutrition", "ebt"]

  -- Administering agency
  agency_name TEXT NOT NULL,
  agency_level TEXT NOT NULL,             -- federal, state, county, city
  agency_website TEXT,
  agency_phone TEXT,

  -- Geographic coverage
  coverage_type TEXT NOT NULL,            -- federal, state, county, city
  coverage_states TEXT[] DEFAULT '{}',    -- ["TX", "CA"] or ["*"] for all
  coverage_fips TEXT[] DEFAULT '{}',      -- County FIPS codes

  -- Eligibility
  eligibility_rules JSONB DEFAULT '[]',
  eligibility_summary TEXT,               -- Human-readable summary
  income_limit_fpl_percent INTEGER,       -- e.g., 130 for 130% FPL
  income_limit_type TEXT,                 -- gross, net, modified_gross
  asset_limit_cents INTEGER,
  categorical_eligibility BOOLEAN DEFAULT false,

  -- Benefit details
  benefit_type TEXT NOT NULL,             -- cash, voucher, service, tax_credit
  benefit_value_cents INTEGER,
  benefit_value_max_cents INTEGER,
  benefit_frequency TEXT,                 -- monthly, annual, one_time
  benefit_description TEXT,

  -- Application process
  application_url TEXT,
  application_methods TEXT[] DEFAULT '{}', -- ["online", "in_person", "phone", "mail"]
  processing_time_days INTEGER,
  required_documents JSONB DEFAULT '[]',
  how_to_apply JSONB DEFAULT '[]',        -- Step-by-step instructions

  -- Content
  description TEXT NOT NULL,
  short_description TEXT,
  what_it_covers TEXT,
  who_qualifies TEXT,
  tips TEXT,                              -- Insider tips for applicants

  -- Search
  search_vector TSVECTOR,                 -- Full-text search

  -- Metadata
  source_url TEXT,                        -- Where we got this data
  last_verified_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT true,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_programs_category ON programs(category);
CREATE INDEX idx_programs_coverage ON programs(coverage_type, coverage_states);
CREATE INDEX idx_programs_fips ON programs USING GIN(coverage_fips);
CREATE INDEX idx_programs_search ON programs USING GIN(search_vector);
CREATE INDEX idx_programs_active ON programs(is_active) WHERE is_active = true;

const PROGRAM_CATEGORIES = {
  food_assistance: {
    name: "Food Assistance",
    icon: "utensils",
    programs: ["SNAP", "WIC", "School Meals", "Food Banks"]
  },
  healthcare: {
    name: "Healthcare",
    icon: "heart-pulse",
    programs: ["Medicaid", "CHIP", "Medicare", "ACA Marketplace"]
  },
  housing: {
    name: "Housing",
    icon: "house",
    programs: ["Section 8", "Public Housing", "LIHTC", "Rental Assistance"]
  },
  income_support: {
    name: "Income Support",
    icon: "hand-holding-dollar",
    programs: ["TANF", "SSI", "SSDI", "Unemployment"]
  },
  childcare: {
    name: "Childcare",
    icon: "children",
    programs: ["CCDF", "Head Start", "Pre-K"]
  },
  utilities: {
    name: "Utilities",
    icon: "bolt",
    programs: ["LIHEAP", "Weatherization", "Lifeline"]
  },
  education: {
    name: "Education",
    icon: "graduation-cap",
    programs: ["Pell Grant", "FAFSA", "Work-Study"]
  },
  tax_credits: {
    name: "Tax Credits",
    icon: "file-invoice-dollar",
    programs: ["EITC", "CTC", "ACTC", "Savers Credit"]
  },
  veterans: {
    name: "Veterans",
    icon: "shield",
    programs: ["VA Healthcare", "GI Bill", "VA Pension"]
  },
  disability: {
    name: "Disability",
    icon: "wheelchair",
    programs: ["SSDI", "SSI", "Vocational Rehab"]
  }
} as const;

-- Auto-update search vector on insert/update
CREATE OR REPLACE FUNCTION update_program_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.short_name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.agency_name, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(NEW.short_description, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'C') ||
    setweight(to_tsvector('english', COALESCE(array_to_string(NEW.tags, ' '), '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER programs_search_vector_update
  BEFORE INSERT OR UPDATE ON programs
  FOR EACH ROW EXECUTE FUNCTION update_program_search_vector();

// In programs.repository.ts
async function searchPrograms(query: string, options: SearchOptions) {
  const { data } = await supabaseAdmin
    .from('programs')
    .select('*')
    .textSearch('search_vector', query, {
      type: 'websearch',  // Supports "food assistance" AND queries
      config: 'english'
    })
    .eq('is_active', true)
    .limit(options.limit || 20);

  return data;
}

async function getProgramsByLocation(
  state: string,
  fips: string
): Promise<Program[]> {
  const { data } = await supabaseAdmin
    .from('programs')
    .select('*')
    .eq('is_active', true)
    .or(`
      coverage_type.eq.federal,
      and(coverage_type.eq.state,coverage_states.cs.{${state}}),
      and(coverage_type.eq.county,coverage_fips.cs.{${fips}}),
      and(coverage_type.eq.city,coverage_fips.cs.{${fips}})
    `);

  return data;
}

// geocoding.ts
interface GeocodedLocation {
  lat: number;
  lng: number;
  state: string;      // "TX"
  county: string;     // "Travis County"
  fips: string;       // "48453"
  city?: string;      // "Austin"
}

async function geocodeZip(zip: string): Promise<GeocodedLocation> {
  // 1. Geocode ZIP to coordinates via Nominatim
  // 2. Reverse geocode to get county boundary
  // 3. Look up FIPS code from county name
}

interface EligibilityRule {
  // Rule identification
  id: string;
  description?: string;

  // Condition
  field: string;                    // Which screener field to check
  operator: EligibilityOperator;    // How to compare
  value: RuleValue;                 // What to compare against

  // Compound rules
  conjunction?: "AND" | "OR";
  children?: EligibilityRule[];
}

type EligibilityOperator =
  | "equals"
  | "not_equals"
  | "greater_than"
  | "less_than"
  | "greater_than_or_equal"
  | "less_than_or_equal"
  | "in"                          // Value is in array
  | "not_in"
  | "contains"                    // Array contains value
  | "less_than_fpl_percent"       // Income below X% FPL
  | "age_at_least"
  | "age_under"
  | "has_dependent_under"
  | "is_citizen_or_qualified";

type RuleValue = string | number | boolean | string[];

{
  "eligibility_rules": [
    {
      "id": "income",
      "description": "Gross income below 130% FPL",
      "field": "household_income_monthly",
      "operator": "less_than_fpl_percent",
      "value": 130
    },
    {
      "id": "citizenship",
      "description": "Must be citizen or qualified immigrant",
      "field": "citizenship_status",
      "operator": "in",
      "value": ["citizen", "qualified_alien", "refugee", "asylee"]
    },
    {
      "id": "resources",
      "description": "Resources below $2,500 (or $3,500 if elderly/disabled)",
      "conjunction": "OR",
      "children": [
        {
          "field": "countable_resources_cents",
          "operator": "less_than_or_equal",
          "value": 250000
        },
        {
          "conjunction": "AND",
          "children": [
            { "field": "has_elderly_or_disabled", "operator": "equals", "value": true },
            { "field": "countable_resources_cents", "operator": "less_than_or_equal", "value": 350000 }
          ]
        }
      ]
    }
  ]
}

interface DocumentRequirement {
  type: DocumentType;
  description: string;
  required: boolean;
  alternatives?: DocumentType[];  // Acceptable substitutes
  notes?: string;
}

type DocumentType =
  | "photo_id"
  | "ssn_card"
  | "birth_certificate"
  | "proof_of_income"
  | "pay_stubs"
  | "tax_return"
  | "bank_statement"
  | "utility_bill"
  | "lease_agreement"
  | "proof_of_residency"
  | "immigration_documents"
  | "disability_determination"
  | "medical_records"
  | "school_enrollment"
  | "other";

{
  "required_documents": [
    {
      "type": "photo_id",
      "description": "Valid government-issued photo ID",
      "required": true,
      "alternatives": ["drivers_license", "passport", "state_id"],
      "notes": "Must not be expired"
    },
    {
      "type": "proof_of_income",
      "description": "Income verification for all household members",
      "required": true,
      "alternatives": ["pay_stubs", "employer_letter", "tax_return"],
      "notes": "Last 30 days of pay stubs or most recent tax return"
    },
    {
      "type": "proof_of_residency",
      "description": "Proof you live in the service area",
      "required": true,
      "alternatives": ["utility_bill", "lease_agreement", "bank_statement"],
      "notes": "Must show current address, dated within 60 days"
    }
  ]
}

const { data } = await supabaseAdmin
  .from('programs')
  .select('id, name, short_name, short_description, benefit_value_cents')
  .eq('category', 'food_assistance')
  .eq('is_active', true)
  .order('name');

const { data } = await supabaseAdmin
  .from('programs')
  .select('*')
  .eq('is_active', true)
  .not('benefit_value_cents', 'is', null)
  .gte('benefit_value_cents', 50000)  // $500+/month
  .order('benefit_value_cents', { ascending: false })
  .limit(10);

const { data } = await supabaseAdmin
  .from('programs')
  .select('*')
  .eq('is_active', true)
  .not('application_deadline', 'is', null)
  .gte('application_deadline', new Date().toISOString())
  .order('application_deadline');