-- Migration: Create ABN cache table for storing ABR lookup results
-- Created: 2026-02-10
-- Description: Caches ABN lookup results from ABR to reduce API calls and improve performance
-- 
-- Features:
-- - Stores validated ABN data with company details
-- - Automatic expiry (30 days for valid ABNs, 7 days for invalid/not found)
-- - Unique constraint on ABN
-- - Index for fast lookups
-- - Created/updated timestamps for monitoring

-- ============================================
-- ABN CACHE TABLE
-- ============================================

CREATE TABLE abn_cache (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  abn text NOT NULL UNIQUE,
  company_name text NOT NULL,
  status text NOT NULL,
  status_date text,
  state text,
  postcode text,
  entity_type text,
  entity_type_code text,
  gst_registered boolean,
  acn text,
  
  -- Cache metadata
  cached_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  expires_at timestamptz NOT NULL,
  
  -- ABR response data (stored as JSON for flexibility)
  abr_response jsonb,
  
  -- Cache statistics
  lookup_count integer DEFAULT 1,
  last_lookup_at timestamptz DEFAULT now()
);

-- ============================================
-- INDEXES
-- ============================================

-- Primary lookup index (ABN)
CREATE INDEX idx_abn_cache_abn ON abn_cache (abn);

-- Expiry index for cleanup jobs
CREATE INDEX idx_abn_cache_expires_at ON abn_cache (expires_at);

-- Last lookup index for cache hit statistics
CREATE INDEX idx_abn_cache_last_lookup ON abn_cache (last_lookup_at);

-- ============================================
-- FUNCTIONS
-- ============================================

-- Function to auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION update_abn_cache_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for updated_at
CREATE TRIGGER abn_cache_updated_at
  BEFORE UPDATE ON abn_cache
  FOR EACH ROW EXECUTE FUNCTION update_abn_cache_updated_at();

-- Function to calculate expiry based on ABN status
CREATE OR REPLACE FUNCTION calculate_abn_expiry(abn_status text, base_date timestamptz DEFAULT now())
RETURNS timestamptz AS $$
BEGIN
  -- Valid ABNs: 30 days expiry
  IF lower(abn_status) = 'active' THEN
    RETURN base_date + INTERVAL '30 days';
  
  -- Invalid/not found ABNs: 7 days expiry (shorter to allow retry)
  ELSE
    RETURN base_date + INTERVAL '7 days';
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- ============================================
-- RLS POLICIES (Read-only for authenticated users)
-- ============================================

-- Enable RLS
ALTER TABLE abn_cache ENABLE ROW LEVEL SECURITY;

-- Allow all authenticated users to read cached ABN data
CREATE POLICY "Allow read access to all authenticated users" ON abn_cache
  FOR SELECT USING (auth.role() = 'authenticated');

-- Only service role can insert/update/delete (via API or admin operations)
CREATE POLICY "Service role can manage cache" ON abn_cache
  FOR ALL USING (auth.jwt() ->> 'role' = 'service_role');

-- ============================================
-- HELPER FUNCTIONS FOR API INTEGRATION
-- ============================================

-- Function to get cached ABN (returns null if expired or not found)
CREATE OR REPLACE FUNCTION get_cached_abn(p_abn text)
RETURNS TABLE (
  id uuid,
  abn text,
  company_name text,
  status text,
  status_date text,
  state text,
  postcode text,
  entity_type text,
  entity_type_code text,
  gst_registered boolean,
  acn text,
  cached_at timestamptz,
  expires_at timestamptz,
  lookup_count integer,
  last_lookup_at timestamptz,
  abr_response jsonb
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    ac.id,
    ac.abn,
    ac.company_name,
    ac.status,
    ac.status_date,
    ac.state,
    ac.postcode,
    ac.entity_type,
    ac.entity_type_code,
    ac.gst_registered,
    ac.acn,
    ac.cached_at,
    ac.expires_at,
    ac.lookup_count,
    ac.last_lookup_at,
    ac.abr_response
  FROM abn_cache ac
  WHERE ac.abn = p_abn 
    AND ac.expires_at > now()  -- Not expired
  LIMIT 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get cache statistics
CREATE OR REPLACE FUNCTION get_abn_cache_stats()
RETURNS TABLE (
  total_cached bigint,
  active_entries bigint,
  expired_entries bigint,
  avg_lookups numeric,
  max_lookups integer
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COUNT(*) as total_cached,
    COUNT(*) FILTER (WHERE expires_at > now()) as active_entries,
    COUNT(*) FILTER (WHERE expires_at <= now()) as expired_entries,
    AVG(lookup_count) as avg_lookups,
    MAX(lookup_count) as max_lookups
  FROM abn_cache;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to increment lookup count and update last_lookup_at
CREATE OR REPLACE FUNCTION increment_abn_lookup(p_abn text)
RETURNS void AS $$
BEGIN
  UPDATE abn_cache 
  SET 
    lookup_count = lookup_count + 1,
    last_lookup_at = now()
  WHERE abn = p_abn;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================
-- CLEANUP FUNCTION (can be run periodically)
-- ============================================

CREATE OR REPLACE FUNCTION cleanup_expired_abn_cache()
RETURNS TABLE (deleted_count integer) AS $$
DECLARE
  deleted integer;
BEGIN
  DELETE FROM abn_cache 
  WHERE expires_at <= now();
  
  GET DIAGNOSTICS deleted = ROW_COUNT;
  RETURN QUERY SELECT deleted;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================
-- INITIAL DATA (if needed)
-- ============================================

-- Insert some common test ABNs for development (optional)
-- These will be overwritten by real data when looked up
/*
INSERT INTO abn_cache (
  abn, 
  company_name, 
  status, 
  status_date, 
  state, 
  postcode, 
  entity_type, 
  entity_type_code, 
  gst_registered, 
  acn,
  expires_at
) VALUES 
  ('51824753556', 'TEST COMPANY PTY LTD', 'Active', '2020-01-01', 'NSW', '2000', 'Australian Private Company', 'PRV', true, '123456789', now() + INTERVAL '30 days'),
  ('34241177887', 'EXAMPLE BUILDERS PTY LTD', 'Active', '2019-06-15', 'VIC', '3000', 'Australian Private Company', 'PRV', true, '987654321', now() + INTERVAL '30 days')
ON CONFLICT (abn) DO NOTHING;
*/

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Check cache statistics
/*
SELECT 
  COUNT(*) as total_cached,
  COUNT(*) FILTER (WHERE expires_at > now()) as active_entries,
  COUNT(*) FILTER (WHERE expires_at <= now()) as expired_entries,
  AVG(lookup_count) as avg_lookups,
  MAX(lookup_count) as max_lookups
FROM abn_cache;
*/

-- Check cache hit rate (run this after some usage)
/*
SELECT 
  status,
  COUNT(*) as count,
  AVG(lookup_count) as avg_lookups,
  MIN(cached_at) as first_cached,
  MAX(last_lookup_at) as last_lookup
FROM abn_cache 
WHERE expires_at > now()
GROUP BY status
ORDER BY count DESC;
*/