--
--


SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;


--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION postgis IS 'PostGIS geometry and geography spatial types and functions';


--
-- Name: calculate_count_discrepancy(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.calculate_count_discrepancy() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Calculate discrepancy when either count changes
    IF NEW.local_subscriber_count IS NOT NULL AND NEW.mailchimp_subscriber_count IS NOT NULL THEN
        NEW.count_discrepancy := NEW.mailchimp_subscriber_count - NEW.local_subscriber_count;
        
        -- Update sync status based on discrepancy
        IF ABS(NEW.count_discrepancy) = 0 THEN
            NEW.count_sync_status := 'synced';
        ELSIF ABS(NEW.count_discrepancy) <= 10 THEN
            NEW.count_sync_status := 'minor_diff';
        ELSIF ABS(NEW.count_discrepancy) <= 100 THEN
            NEW.count_sync_status := 'moderate_diff';
        ELSE
            NEW.count_sync_status := 'major_diff';
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$;


--
-- Name: calculate_daily_analytics(character varying, date); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.calculate_daily_analytics(p_workflow_instance character varying DEFAULT 'prod1'::character varying, p_sync_date date DEFAULT CURRENT_DATE) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_total_subscribers INTEGER;
    v_active_subscribers INTEGER;
    v_vip_subscribers INTEGER;
    v_overall_open_rate NUMERIC(5,2);
    v_overall_click_rate NUMERIC(5,2);
    v_countries_count INTEGER;
    v_uk_subscribers INTEGER;
    v_synced_to_flodesk INTEGER;
    v_ultra_engaged INTEGER;
    v_high_engaged INTEGER;
    v_standard_engaged INTEGER;
    v_low_engaged INTEGER;
    v_new_subscribers_7d INTEGER;
    v_new_subscribers_30d INTEGER;
    v_rating_5 INTEGER;
    v_rating_4 INTEGER;
    v_rating_3 INTEGER;
    v_rating_2 INTEGER;
    v_rating_1 INTEGER;
    v_estimated_cost NUMERIC(10,2);
    v_monthly_savings NUMERIC(10,2);
BEGIN
    -- Calculate main metrics
    SELECT 
        COUNT(*),
        COUNT(*) FILTER (WHERE status = 'subscribed'),
        COUNT(*) FILTER (WHERE vip = true),
        COALESCE(ROUND(AVG(avg_open_rate), 2), 0),
        COALESCE(ROUND(AVG(avg_click_rate), 2), 0),
        COUNT(DISTINCT country_code),
        COUNT(*) FILTER (WHERE country_code = 'GB'),
        COUNT(*) FILTER (WHERE flodesk_id IS NOT NULL)
    INTO 
        v_total_subscribers,
        v_active_subscribers,
        v_vip_subscribers,
        v_overall_open_rate,
        v_overall_click_rate,
        v_countries_count,
        v_uk_subscribers,
        v_synced_to_flodesk
    FROM subscribers 
    WHERE status = 'subscribed';

    -- Calculate engagement tiers
    SELECT 
        COUNT(*) FILTER (WHERE avg_open_rate >= 40),
        COUNT(*) FILTER (WHERE avg_open_rate >= 25 AND avg_open_rate < 40),
        COUNT(*) FILTER (WHERE avg_open_rate >= 10 AND avg_open_rate < 25),
        COUNT(*) FILTER (WHERE avg_open_rate < 10)
    INTO 
        v_ultra_engaged,
        v_high_engaged,
        v_standard_engaged,
        v_low_engaged
    FROM subscribers 
    WHERE status = 'subscribed';

    -- Calculate new subscribers
    SELECT 
        COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'),
        COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '30 days')
    INTO 
        v_new_subscribers_7d,
        v_new_subscribers_30d
    FROM subscribers;

    -- Calculate member rating distribution
    SELECT 
        COUNT(*) FILTER (WHERE member_rating = 5),
        COUNT(*) FILTER (WHERE member_rating = 4),
        COUNT(*) FILTER (WHERE member_rating = 3),
        COUNT(*) FILTER (WHERE member_rating = 2),
        COUNT(*) FILTER (WHERE member_rating = 1)
    INTO 
        v_rating_5,
        v_rating_4,
        v_rating_3,
        v_rating_2,
        v_rating_1
    FROM subscribers 
    WHERE status = 'subscribed';

    -- Calculate cost savings (based on subscriber count)
    v_estimated_cost := CASE 
        WHEN v_total_subscribers <= 2000 THEN 29.99
        WHEN v_total_subscribers <= 3000 THEN 39.99
        WHEN v_total_subscribers <= 5000 THEN 59.99
        WHEN v_total_subscribers <= 10000 THEN 99.99
        WHEN v_total_subscribers <= 15000 THEN 199.99
        ELSE 299.99
    END;
    
    v_monthly_savings := v_estimated_cost - 35.00;

    -- Insert or update analytics
    INSERT INTO sync_analytics (
        sync_date,
        workflow_instance,
        total_subscribers,
        active_subscribers,
        vip_subscribers,
        overall_open_rate,
        overall_click_rate,
        countries_count,
        uk_subscribers,
        uk_percentage,
        international_subscribers,
        synced_to_flodesk,
        sync_success_rate,
        ultra_engaged,
        high_engaged,
        standard_engaged,
        low_engaged,
        new_subscribers_7d,
        new_subscribers_30d,
        rating_5_stars,
        rating_4_stars,
        rating_3_stars,
        rating_2_stars,
        rating_1_star,
        estimated_mailchimp_cost,
        actual_flodesk_cost,
        monthly_savings,
        last_sync_time
    ) VALUES (
        p_sync_date,
        p_workflow_instance,
        v_total_subscribers,
        v_active_subscribers,
        v_vip_subscribers,
        v_overall_open_rate,
        v_overall_click_rate,
        v_countries_count,
        v_uk_subscribers,
        CASE WHEN v_total_subscribers > 0 THEN 
            ROUND(v_uk_subscribers::numeric / v_total_subscribers * 100, 2) 
        ELSE 0 END,
        v_total_subscribers - v_uk_subscribers,
        v_synced_to_flodesk,
        CASE WHEN v_total_subscribers > 0 THEN 
            ROUND(v_synced_to_flodesk::numeric / v_total_subscribers * 100, 2) 
        ELSE 0 END,
        v_ultra_engaged,
        v_high_engaged,
        v_standard_engaged,
        v_low_engaged,
        v_new_subscribers_7d,
        v_new_subscribers_30d,
        v_rating_5,
        v_rating_4,
        v_rating_3,
        v_rating_2,
        v_rating_1,
        v_estimated_cost,
        35.00,
        v_monthly_savings,
        NOW()
    ) ON CONFLICT (sync_date, workflow_instance) DO UPDATE SET
        total_subscribers = EXCLUDED.total_subscribers,
        active_subscribers = EXCLUDED.active_subscribers,
        vip_subscribers = EXCLUDED.vip_subscribers,
        overall_open_rate = EXCLUDED.overall_open_rate,
        overall_click_rate = EXCLUDED.overall_click_rate,
        countries_count = EXCLUDED.countries_count,
        uk_subscribers = EXCLUDED.uk_subscribers,
        uk_percentage = EXCLUDED.uk_percentage,
        international_subscribers = EXCLUDED.international_subscribers,
        synced_to_flodesk = EXCLUDED.synced_to_flodesk,
        sync_success_rate = EXCLUDED.sync_success_rate,
        ultra_engaged = EXCLUDED.ultra_engaged,
        high_engaged = EXCLUDED.high_engaged,
        standard_engaged = EXCLUDED.standard_engaged,
        low_engaged = EXCLUDED.low_engaged,
        new_subscribers_7d = EXCLUDED.new_subscribers_7d,
        new_subscribers_30d = EXCLUDED.new_subscribers_30d,
        rating_5_stars = EXCLUDED.rating_5_stars,
        rating_4_stars = EXCLUDED.rating_4_stars,
        rating_3_stars = EXCLUDED.rating_3_stars,
        rating_2_stars = EXCLUDED.rating_2_stars,
        rating_1_star = EXCLUDED.rating_1_star,
        estimated_mailchimp_cost = EXCLUDED.estimated_mailchimp_cost,
        monthly_savings = EXCLUDED.monthly_savings,
        last_sync_time = EXCLUDED.last_sync_time,
        updated_at = NOW();

    RAISE NOTICE 'Analytics updated for %: % subscribers, %.2f%% open rate, £%.2f monthly savings', 
        p_sync_date, v_total_subscribers, v_overall_open_rate, v_monthly_savings;

END;
$$;


--
-- Name: cleanup_old_sync_records(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.cleanup_old_sync_records(days_to_keep integer DEFAULT 90) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM synced_subscribers 
    WHERE processed_at < CURRENT_DATE - INTERVAL '1 day' * days_to_keep
    AND flodesk_success = true;
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$;


--
-- Name: create_default_account_settings(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.create_default_account_settings() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO account_settings (mailchimp_account_id, created_by)
    VALUES (NEW.id, 'system');
    
    INSERT INTO account_limits (mailchimp_account_id)
    VALUES (NEW.id);
    
    RETURN NEW;
END;
$$;


--
-- Name: get_account_id_by_nickname(character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_account_id_by_nickname(nickname character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    account_id INTEGER;
BEGIN
    SELECT id INTO account_id
    FROM mailchimp_accounts
    WHERE account_nickname = nickname
    AND is_active = true;
    
    IF account_id IS NULL THEN
        RAISE EXCEPTION 'Account with nickname "%" not found or inactive', nickname;
    END IF;
    
    RETURN account_id;
END;
$$;


--
-- Name: get_account_nickname_by_id(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_account_nickname_by_id(account_id integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
    nickname VARCHAR(100);
BEGIN
    SELECT account_nickname INTO nickname
    FROM mailchimp_accounts
    WHERE id = account_id;
    
    RETURN COALESCE(nickname, 'unknown');
END;
$$;


--
-- Name: get_best_geo_for_subscriber(integer, numeric, numeric, text, text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_best_geo_for_subscriber(subscriber_id_param integer, lat_param numeric DEFAULT NULL::numeric, lng_param numeric DEFAULT NULL::numeric, ip_opt_param text DEFAULT NULL::text, ip_signup_param text DEFAULT NULL::text) RETURNS TABLE(geo_id integer, geo_location character varying, data_source character varying, confidence_score integer)
    LANGUAGE plpgsql
    AS $_$
DECLARE
    coord_result RECORD;
    ip_result RECORD;
BEGIN
    -- Priority 1: Coordinate-based lookup (highest confidence)
    IF lat_param IS NOT NULL AND lng_param IS NOT NULL 
       AND lat_param BETWEEN -90 AND 90 AND lng_param BETWEEN -180 AND 180 THEN
        
        SELECT * INTO coord_result FROM get_geo_id_from_coordinates(lat_param, lng_param);
        
        IF coord_result.geo_id > 0 THEN
            RETURN QUERY SELECT 
                coord_result.geo_id, 
                coord_result.geo_location, 
                'coordinates'::VARCHAR(50), 
                coord_result.confidence;
            RETURN;
        END IF;
    END IF;
    
    -- Priority 2: IP_opt lookup (more recent IP address)
    IF ip_opt_param IS NOT NULL AND ip_opt_param ~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN
        SELECT * INTO ip_result FROM get_geo_id_from_ip(ip_opt_param);
        
        IF ip_result.geo_id > 0 AND ip_result.source != 'maxmind_pending' THEN
            RETURN QUERY SELECT 
                ip_result.geo_id, 
                ip_result.geo_location, 
                'ip_opt'::VARCHAR(50), 
                80;
            RETURN;
        ELSIF ip_result.source = 'maxmind_pending' THEN
            RETURN QUERY SELECT 
                0, 
                'needs_maxmind_processing'::VARCHAR(255), 
                'maxmind_pending'::VARCHAR(50), 
                80;
            RETURN;
        END IF;
    END IF;
    
    -- Priority 3: IP_signup lookup (fallback IP address)
    IF ip_signup_param IS NOT NULL AND ip_signup_param ~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN
        SELECT * INTO ip_result FROM get_geo_id_from_ip(ip_signup_param);
        
        IF ip_result.geo_id > 0 AND ip_result.source != 'maxmind_pending' THEN
            RETURN QUERY SELECT 
                ip_result.geo_id, 
                ip_result.geo_location, 
                'ip_signup'::VARCHAR(50), 
                75;
            RETURN;
        ELSIF ip_result.source = 'maxmind_pending' THEN
            RETURN QUERY SELECT 
                0, 
                'needs_maxmind_processing'::VARCHAR(255), 
                'maxmind_pending'::VARCHAR(50), 
                75;
            RETURN;
        END IF;
    END IF;
    
    -- Priority 4: No geo data available
    RETURN QUERY SELECT 
        0, 
        'no_geo_data_available'::VARCHAR(255), 
        'none'::VARCHAR(50), 
        0;
END;
$_$;


--
-- Name: get_best_geo_for_subscriber(numeric, numeric, character varying, character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_best_geo_for_subscriber(p_latitude numeric DEFAULT NULL::numeric, p_longitude numeric DEFAULT NULL::numeric, p_country_code character varying DEFAULT NULL::character varying, p_ip_opt character varying DEFAULT NULL::character varying, p_ip_signup character varying DEFAULT NULL::character varying, p_city character varying DEFAULT NULL::character varying, p_country character varying DEFAULT NULL::character varying) RETURNS TABLE(geo_id integer, geo_location character varying, data_source character varying, confidence_score integer)
    LANGUAGE plpgsql
    AS $_$
DECLARE
    result_geo_id INTEGER := 0;
    result_location VARCHAR := '';
    result_source VARCHAR := 'none';
    result_confidence INTEGER := 0;
    coord_result RECORD;
    ip_result RECORD;
BEGIN
    -- Priority 1: Coordinate-based lookup (highest confidence: 95%)
    IF p_latitude IS NOT NULL AND p_longitude IS NOT NULL THEN
        -- Use the working global coordinate function with explicit column aliases
        SELECT 
            coord.geo_id, 
            coord.geo_location 
        INTO 
            result_geo_id, 
            result_location
        FROM get_global_geo_id_from_coordinates(p_latitude, p_longitude) AS coord;
        
        IF result_geo_id > 0 THEN
            RETURN QUERY SELECT result_geo_id, result_location, 'coordinates'::VARCHAR, 95;
            RETURN;
        END IF;
    END IF;
    
    -- Priority 2: Country code direct mapping (confidence: 80%)
    IF p_country_code IS NOT NULL AND length(p_country_code) = 2 THEN
        SELECT mgm.mailchimp_geo_id, mgm.geo_location_name
        INTO result_geo_id, result_location
        FROM mailchimp_geo_mapping mgm
        WHERE mgm.country_code = p_country_code
        AND mgm.city_name IS NULL  -- Get country-level mapping
        ORDER BY mgm.is_primary_market DESC, mgm.confidence_level DESC
        LIMIT 1;
        
        IF result_geo_id > 0 THEN
            RETURN QUERY SELECT result_geo_id, result_location, 'country_code'::VARCHAR, 80;
            RETURN;
        END IF;
    END IF;
    
    -- Priority 3: IP opt-in lookup (more recent IP: confidence 70%)
    IF p_ip_opt IS NOT NULL AND p_ip_opt ~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN
        SELECT * INTO ip_result FROM get_geo_id_from_ip(p_ip_opt);
        IF ip_result.geo_id > 0 AND ip_result.source != 'maxmind_pending' THEN
            RETURN QUERY SELECT ip_result.geo_id, ip_result.geo_location::VARCHAR, 'ip_opt'::VARCHAR, 70;
            RETURN;
        END IF;
    END IF;
    
    -- Priority 4: IP signup lookup (fallback IP: confidence 60%)
    IF p_ip_signup IS NOT NULL AND p_ip_signup ~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN
        SELECT * INTO ip_result FROM get_geo_id_from_ip(p_ip_signup);
        IF ip_result.geo_id > 0 AND ip_result.source != 'maxmind_pending' THEN
            RETURN QUERY SELECT ip_result.geo_id, ip_result.geo_location::VARCHAR, 'ip_signup'::VARCHAR, 60;
            RETURN;
        END IF;
    END IF;
    
    -- Priority 5: Manual text field mapping (lowest confidence: 50%)
    IF p_city IS NOT NULL THEN
        -- Amsterdam variations
        IF lower(p_city) LIKE '%amsterdam%' THEN
            RETURN QUERY SELECT 528001, 'Amsterdam, Netherlands'::VARCHAR, 'manual_city'::VARCHAR, 50;
            RETURN;
        END IF;
        
        -- Manchester variations
        IF lower(p_city) LIKE '%manchester%' THEN
            RETURN QUERY SELECT 25, 'Manchester, UK'::VARCHAR, 'manual_city'::VARCHAR, 50;
            RETURN;
        END IF;
        
        -- London variations
        IF lower(p_city) LIKE '%london%' THEN
            RETURN QUERY SELECT 26, 'London, UK'::VARCHAR, 'manual_city'::VARCHAR, 50;
            RETURN;
        END IF;
        
        -- Birmingham variations
        IF lower(p_city) LIKE '%birmingham%' THEN
            RETURN QUERY SELECT 27, 'Birmingham, UK'::VARCHAR, 'manual_city'::VARCHAR, 50;
            RETURN;
        END IF;
    END IF;
    
    -- Default: Unknown location
    RETURN QUERY SELECT 0, 'Unknown Location'::VARCHAR, 'none'::VARCHAR, 0;
END;
$_$;


--
-- Name: FUNCTION get_best_geo_for_subscriber(p_latitude numeric, p_longitude numeric, p_country_code character varying, p_ip_opt character varying, p_ip_signup character varying, p_city character varying, p_country character varying); Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON FUNCTION public.get_best_geo_for_subscriber(p_latitude numeric, p_longitude numeric, p_country_code character varying, p_ip_opt character varying, p_ip_signup character varying, p_city character varying, p_country character varying) IS 'Fixed multi-source geo lookup with column alias resolution';


--
-- Name: get_geo_id_from_coordinates(numeric, numeric); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_geo_id_from_coordinates(lat_param numeric, lng_param numeric) RETURNS TABLE(geo_id integer, geo_location character varying, confidence integer)
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Manchester area check
    IF lat_param BETWEEN 53.4 AND 53.5 AND lng_param BETWEEN -2.3 AND -2.2 THEN
        RETURN QUERY SELECT 25, 'Manchester, UK'::VARCHAR(255), 95;
        RETURN;
    END IF;
    
    -- London area check
    IF lat_param BETWEEN 51.4 AND 51.6 AND lng_param BETWEEN -0.2 AND 0.1 THEN
        RETURN QUERY SELECT 26, 'London, UK'::VARCHAR(255), 95;
        RETURN;
    END IF;
    
    -- Birmingham area check
    IF lat_param BETWEEN 52.4 AND 52.6 AND lng_param BETWEEN -2.0 AND -1.8 THEN
        RETURN QUERY SELECT 27, 'Birmingham, UK'::VARCHAR(255), 90;
        RETURN;
    END IF;
    
    -- No coordinate match found
    RETURN QUERY SELECT 0, 'coordinates_no_match'::VARCHAR(255), 0;
END;
$$;


--
-- Name: get_geo_id_from_ip(text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_geo_id_from_ip(ip_addr text) RETURNS TABLE(geo_id integer, geo_location character varying, source character varying, confidence integer)
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Check cache first (if you have ip_geo_cache table)
    -- For now, using simple IP range matching
    
    -- Manchester IP ranges
    IF ip_addr LIKE '2.24.%' OR ip_addr LIKE '2.120.%' THEN
        RETURN QUERY SELECT 25, 'Manchester, UK'::VARCHAR(255), 'ip_range'::VARCHAR(50), 80;
        RETURN;
    END IF;
    
    -- London IP ranges
    IF ip_addr LIKE '31.222.%' OR ip_addr LIKE '31.13.%' THEN
        RETURN QUERY SELECT 26, 'London, UK'::VARCHAR(255), 'ip_range'::VARCHAR(50), 80;
        RETURN;
    END IF;
    
    -- General UK IP ranges
    IF ip_addr LIKE '86.%' OR ip_addr LIKE '87.%' OR ip_addr LIKE '90.%' THEN
        RETURN QUERY SELECT 24, 'United Kingdom'::VARCHAR(255), 'ip_range'::VARCHAR(50), 70;
        RETURN;
    END IF;
    
    -- US IP ranges
    IF ip_addr LIKE '8.%' OR ip_addr LIKE '192.%' THEN
        RETURN QUERY SELECT 840, 'United States'::VARCHAR(255), 'ip_range'::VARCHAR(50), 65;
        RETURN;
    END IF;
    
    -- Unknown IP - needs MaxMind processing
    RETURN QUERY SELECT 0, 'needs_maxmind_processing'::VARCHAR(255), 'maxmind_pending'::VARCHAR(50), 0;
END;
$$;


--
-- Name: get_global_geo_id_from_coordinates(numeric, numeric); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_global_geo_id_from_coordinates(lat numeric, lng numeric) RETURNS TABLE(geo_id integer, geo_location text, city_name text, country_code text, confidence integer, source text, importance_rank integer, population integer)
    LANGUAGE plpgsql
    AS $$
DECLARE
    result_record RECORD;
BEGIN
    -- Input validation
    IF lat < -90 OR lat > 90 OR lng < -180 OR lng > 180 THEN
        RETURN QUERY SELECT 0, 'invalid_coordinates'::TEXT, ''::TEXT, ''::TEXT, 0, 'error'::TEXT, 999, 0;
        RETURN;
    END IF;
    
    -- Primary lookup: Find city containing the point
    SELECT 
        cgm.mailchimp_geo_id, 
        cgm.geo_location_name,
        cgm.city_name,
        cgm.country_code,
        cgm.confidence_level,
        cgm.importance_rank,
        cgm.population
    INTO result_record
    FROM coordinate_geo_mapping cgm
    WHERE lat BETWEEN cgm.geo_bounds_south AND cgm.geo_bounds_north
    AND lng BETWEEN cgm.geo_bounds_west AND cgm.geo_bounds_east
    ORDER BY 
        cgm.importance_rank ASC,      -- Prefer major cities (1 = highest importance)
        cgm.confidence_level DESC,    -- Then confidence
        cgm.population DESC,          -- Then population
        (cgm.geo_bounds_north - cgm.geo_bounds_south) * (cgm.geo_bounds_east - cgm.geo_bounds_west) ASC  -- Prefer smaller, more specific areas
    LIMIT 1;
    
    -- If found, return result
    IF result_record.mailchimp_geo_id > 0 THEN
        RETURN QUERY SELECT 
            result_record.mailchimp_geo_id, 
            result_record.geo_location_name::TEXT,
            COALESCE(result_record.city_name, '')::TEXT,
            COALESCE(result_record.country_code, '')::TEXT,
            result_record.confidence_level,
            'coordinates_exact'::TEXT,
            result_record.importance_rank,
            COALESCE(result_record.population, 0);
        RETURN;
    END IF;
    
    -- Fallback: Find nearest major city within 100km using simplified approach
    -- Use a CTE to calculate distances properly
    WITH city_distances AS (
        SELECT 
            cgm.mailchimp_geo_id, 
            cgm.geo_location_name,
            cgm.city_name,
            cgm.country_code,
            cgm.confidence_level,
            cgm.importance_rank,
            cgm.population,
            -- Calculate center of bounding box
            (cgm.geo_bounds_north + cgm.geo_bounds_south) / 2 as center_lat,
            (cgm.geo_bounds_east + cgm.geo_bounds_west) / 2 as center_lng,
            -- Simple distance calculation (Haversine approximation)
            6371 * acos(
                LEAST(1.0, GREATEST(-1.0,
                    cos(radians(lat)) * cos(radians((cgm.geo_bounds_north + cgm.geo_bounds_south) / 2)) * 
                    cos(radians((cgm.geo_bounds_east + cgm.geo_bounds_west) / 2) - radians(lng)) + 
                    sin(radians(lat)) * sin(radians((cgm.geo_bounds_north + cgm.geo_bounds_south) / 2))
                ))
            ) as distance_km
        FROM coordinate_geo_mapping cgm
        WHERE cgm.importance_rank <= 50  -- Only consider reasonably important cities
    )
    SELECT 
        cd.mailchimp_geo_id, 
        cd.geo_location_name,
        cd.city_name,
        cd.country_code,
        cd.confidence_level,
        cd.importance_rank,
        cd.population,
        cd.distance_km
    INTO result_record
    FROM city_distances cd
    WHERE cd.distance_km <= 100  -- Within 100km
    ORDER BY cd.distance_km ASC, cd.importance_rank ASC
    LIMIT 1;
    
    -- If found a nearby city
    IF result_record.mailchimp_geo_id > 0 THEN
        RETURN QUERY SELECT 
            result_record.mailchimp_geo_id, 
            (result_record.geo_location_name || ' (nearest)')::TEXT,
            COALESCE(result_record.city_name, '')::TEXT,
            COALESCE(result_record.country_code, '')::TEXT,
            GREATEST(result_record.confidence_level - 15, 30), -- Reduce confidence for nearest match
            'coordinates_nearest'::TEXT,
            result_record.importance_rank,
            COALESCE(result_record.population, 0);
        RETURN;
    END IF;
    
    -- If no nearby city found
    RETURN QUERY SELECT 0, 'coordinates_not_found'::TEXT, ''::TEXT, ''::TEXT, 0, 'none'::TEXT, 999, 0;
END;
$$;


--
-- Name: get_iso_geo_from_coordinates(numeric, numeric); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_iso_geo_from_coordinates(lat numeric, lng numeric) RETURNS TABLE(iso_geo_id integer, iso_geo_location character varying, iso_country_code integer, confidence integer, source character varying)
    LANGUAGE plpgsql
    AS $$
DECLARE
    result_iso_geo_id INTEGER := 0;
    result_location VARCHAR := '';
    result_country_code INTEGER := 0;
    result_confidence INTEGER := 0;
BEGIN
    -- Check coordinate boundaries for ISO cities
    SELECT 
        igm.iso_geo_id,
        igm.geo_location_name,
        igm.iso_country_code,
        igm.confidence_level
    INTO result_iso_geo_id, result_location, result_country_code, result_confidence
    FROM iso_geo_mapping igm
    WHERE igm.is_country_level = FALSE
    AND igm.city_name IS NOT NULL
    ORDER BY igm.confidence_level DESC
    LIMIT 1;
    
    -- If no specific city found, try country-level detection
    IF result_iso_geo_id = 0 THEN
        -- Check if coordinates are in UK (approximate bounds)
        IF lat BETWEEN 49.5 AND 60.9 AND lng BETWEEN -8.2 AND 1.8 THEN
            SELECT 826, 'United Kingdom', 826, 80 
            INTO result_iso_geo_id, result_location, result_country_code, result_confidence;
        END IF;
    END IF;
    
    IF result_iso_geo_id > 0 THEN
        RETURN QUERY SELECT result_iso_geo_id, result_location, result_country_code, result_confidence, 'iso_coordinates';
    ELSE
        RETURN QUERY SELECT 0, 'coordinates_not_found', 0, 0, 'none';
    END IF;
END;
$$;


--
-- Name: get_iso_geo_from_location(character varying, character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_iso_geo_from_location(city_name character varying, country_code character varying) RETURNS TABLE(iso_geo_id integer, iso_geo_location character varying, iso_country_code integer, confidence integer, source character varying)
    LANGUAGE plpgsql
    AS $$
DECLARE
    result_iso_geo_id INTEGER := 0;
    result_location VARCHAR := '';
    result_country_code INTEGER := 0;
    result_confidence INTEGER := 0;
BEGIN
    -- Try exact city match first
    SELECT 
        igm.iso_geo_id,
        igm.geo_location_name,
        igm.iso_country_code,
        igm.confidence_level
    INTO result_iso_geo_id, result_location, result_country_code, result_confidence
    FROM iso_geo_mapping igm
    WHERE LOWER(igm.city_name) = LOWER(city_name)
    AND igm.country_code = country_code
    AND igm.is_country_level = FALSE
    ORDER BY igm.confidence_level DESC
    LIMIT 1;
    
    -- If no city match, try country-level
    IF result_iso_geo_id = 0 THEN
        SELECT 
            igm.iso_geo_id,
            igm.geo_location_name,
            igm.iso_country_code,
            igm.confidence_level
        INTO result_iso_geo_id, result_location, result_country_code, result_confidence
        FROM iso_geo_mapping igm
        WHERE igm.country_code = country_code
        AND igm.is_country_level = TRUE
        LIMIT 1;
        
        -- Reduce confidence for country-level match
        result_confidence := COALESCE(result_confidence, 0) - 20;
    END IF;
    
    IF result_iso_geo_id > 0 THEN
        RETURN QUERY SELECT result_iso_geo_id, result_location, result_country_code, result_confidence, 'iso_location_text';
    ELSE
        RETURN QUERY SELECT 0, 'location_not_found', 0, 0, 'none';
    END IF;
END;
$$;


--
-- Name: get_next_iso_city_id(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_next_iso_city_id(p_iso_country_code integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    next_id INTEGER;
    base_id INTEGER;
BEGIN
    -- Calculate base ID (country code * 1000)
    base_id := p_iso_country_code * 1000;
    
    -- Find next available city ID for this country
    SELECT COALESCE(MAX(iso_geo_id), base_id) + 1 
    INTO next_id
    FROM iso_geo_mapping 
    WHERE iso_country_code = p_iso_country_code 
    AND is_country_level = FALSE;
    
    -- Ensure we don't exceed country boundaries (max 999 cities per country)
    IF next_id > base_id + 999 THEN
        RAISE EXCEPTION 'Country % has reached maximum city capacity (999)', p_iso_country_code;
    END IF;
    
    RETURN next_id;
END;
$$;


--
-- Name: get_or_create_city_geo_id(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_or_create_city_geo_id(p_city_name character varying, p_country_code character varying, p_state_region character varying DEFAULT NULL::character varying) RETURNS TABLE(geo_id integer, geo_location character varying, confidence integer)
    LANGUAGE plpgsql
    AS $$
DECLARE
    result_geo_id INTEGER;
    result_location VARCHAR;
    country_base_id INTEGER;
    city_offset INTEGER;
BEGIN
    -- First, check if city already exists in our mapping
    SELECT mailchimp_geo_id, geo_location_name, confidence_level
    INTO result_geo_id, result_location
    FROM city_geo_mapping 
    WHERE LOWER(city_name) = LOWER(p_city_name) 
      AND country_code = p_country_code
    LIMIT 1;
    
    IF result_geo_id IS NOT NULL THEN
        -- Found existing city mapping
        RETURN QUERY SELECT result_geo_id, result_location, 90;
        RETURN;
    END IF;
    
    -- Get country base geo ID for auto-generation
    SELECT default_mailchimp_geo_id INTO country_base_id
    FROM country_geo_mapping 
    WHERE country_code = p_country_code;
    
    IF country_base_id IS NULL THEN
        -- Country not found, fall back to generic
        RETURN QUERY SELECT 0, p_city_name || ', ' || p_country_code, 50;
        RETURN;
    END IF;
    
    -- Auto-generate city geo ID using country base + city hash
    -- This ensures consistent geo IDs for same city names
    city_offset := (hashtext(LOWER(p_city_name)) % 10000)::INTEGER;
    result_geo_id := country_base_id * 1000 + city_offset;
    result_location := COALESCE(p_city_name, 'Unknown City') || ', ' || 
                      COALESCE(
                          (SELECT geo_location_name FROM country_geo_mapping WHERE country_code = p_country_code),
                          p_country_code
                      );
    
    -- Save the auto-generated mapping for future use
    INSERT INTO city_geo_mapping (
        city_name, country_code, state_region, mailchimp_geo_id, 
        geo_location_name, confidence_level, is_major_city
    ) VALUES (
        p_city_name, p_country_code, p_state_region, result_geo_id,
        result_location, 75, FALSE
    ) ON CONFLICT (city_name, country_code) DO NOTHING;
    
    RETURN QUERY SELECT result_geo_id, result_location, 75;
END;
$$;


--
-- Name: get_simple_geo_id_from_coordinates(numeric, numeric); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.get_simple_geo_id_from_coordinates(lat numeric, lng numeric) RETURNS TABLE(geo_id integer, geo_location text, city_name text, country_code text, confidence integer, source text)
    LANGUAGE plpgsql
    AS $$
DECLARE
    result_record RECORD;
BEGIN
    -- Input validation
    IF lat < -90 OR lat > 90 OR lng < -180 OR lng > 180 THEN
        RETURN QUERY SELECT 0, 'invalid_coordinates'::TEXT, ''::TEXT, ''::TEXT, 0, 'error'::TEXT;
        RETURN;
    END IF;
    
    -- Primary lookup: Find city containing the point
    SELECT 
        cgm.mailchimp_geo_id, 
        cgm.geo_location_name,
        cgm.city_name,
        cgm.country_code,
        cgm.confidence_level
    INTO result_record
    FROM coordinate_geo_mapping cgm
    WHERE lat BETWEEN cgm.geo_bounds_south AND cgm.geo_bounds_north
    AND lng BETWEEN cgm.geo_bounds_west AND cgm.geo_bounds_east
    ORDER BY 
        cgm.importance_rank ASC,
        cgm.confidence_level DESC,
        cgm.population DESC
    LIMIT 1;
    
    -- If found, return result
    IF result_record.mailchimp_geo_id > 0 THEN
        RETURN QUERY SELECT 
            result_record.mailchimp_geo_id, 
            result_record.geo_location_name::TEXT,
            COALESCE(result_record.city_name, '')::TEXT,
            COALESCE(result_record.country_code, '')::TEXT,
            result_record.confidence_level,
            'coordinates_exact'::TEXT;
        RETURN;
    END IF;
    
    -- If not found
    RETURN QUERY SELECT 0, 'coordinates_not_found'::TEXT, ''::TEXT, ''::TEXT, 0, 'none'::TEXT;
END;
$$;


--
-- Name: increment_sync_retry(character varying, character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.increment_sync_retry(p_email character varying, p_workflow_instance character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    UPDATE synced_subscribers 
    SET retry_count = retry_count + 1,
        last_retry_at = CURRENT_TIMESTAMP
    WHERE email = p_email 
    AND workflow_instance = p_workflow_instance;
END;
$$;


--
-- Name: process_coordinate_geo_batch(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.process_coordinate_geo_batch(batch_size integer DEFAULT 2000) RETURNS TABLE(processed_count integer, coordinate_hits integer, maxmind_hits integer, international_count integer, unknown_count integer)
    LANGUAGE plpgsql
    AS $$
DECLARE
    result_processed INTEGER := 0;
    result_coordinate INTEGER := 0;
    result_maxmind INTEGER := 0;
    result_international INTEGER := 0;
    result_unknown INTEGER := 0;
    subscriber_rec RECORD;
    geo_result RECORD;
BEGIN
    -- Process subscribers in batches with coordinate priority
    FOR subscriber_rec IN 
        SELECT id, latitude, longitude, ip_opt, ip_signup
        FROM subscribers 
        WHERE computed_geo_id = 0 
           OR geo_updated_at IS NULL 
           OR geo_updated_at < NOW() - INTERVAL '30 days'  -- Reprocess old data
        ORDER BY 
            -- Prioritize subscribers with coordinates first
            CASE WHEN latitude IS NOT NULL AND longitude IS NOT NULL THEN 1 ELSE 2 END,
            id
        LIMIT batch_size
    LOOP
        -- Get best geo data using priority system
        SELECT * INTO geo_result 
        FROM get_best_geo_for_subscriber(
            subscriber_rec.id,
            subscriber_rec.latitude,
            subscriber_rec.longitude,
            subscriber_rec.ip_opt,
            subscriber_rec.ip_signup
        );
        
        -- Update subscriber if we got a result
        IF geo_result.geo_id > 0 THEN
            UPDATE subscribers 
            SET 
                computed_geo_id = geo_result.geo_id,
                computed_geo_location = geo_result.geo_location,
                geo_data_source = geo_result.data_source,
                geo_confidence_score = geo_result.confidence_score,
                geo_updated_at = NOW(),
                geo_processing_notes = CASE 
                    WHEN geo_result.data_source = 'coordinates' THEN 'Coordinate-based mapping'
                    WHEN geo_result.data_source LIKE 'ip_%' THEN 'IP-based mapping (' || geo_result.data_source || ')'
                    ELSE 'Other mapping source'
                END
            WHERE id = subscriber_rec.id;
            
            result_processed := result_processed + 1;
            
            -- Count by source type
            CASE geo_result.data_source
                WHEN 'coordinates' THEN result_coordinate := result_coordinate + 1;
                WHEN 'ip_opt', 'ip_signup' THEN 
                    -- Check if it's a UK geo_id or international
                    IF geo_result.geo_id BETWEEN 24 AND 29 THEN
                        result_coordinate := result_coordinate + 1;  -- Count UK as success
                    ELSE
                        result_international := result_international + 1;
                    END IF;
            END CASE;
            
        ELSIF geo_result.data_source = 'maxmind_pending' THEN
            -- Count as needing MaxMind processing
            result_maxmind := result_maxmind + 1;
            
        ELSE
            -- Count as unknown/no data
            result_unknown := result_unknown + 1;
            
            -- Still mark as processed to avoid reprocessing
            UPDATE subscribers 
            SET 
                geo_updated_at = NOW(),
                geo_processing_notes = 'No geographic data available'
            WHERE id = subscriber_rec.id;
        END IF;
    END LOOP;
    
    RETURN QUERY SELECT result_processed, result_coordinate, result_maxmind, result_international, result_unknown;
END;
$$;


--
-- Name: FUNCTION process_coordinate_geo_batch(batch_size integer); Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON FUNCTION public.process_coordinate_geo_batch(batch_size integer) IS 'Batch processes subscribers using coordinate-priority system';


--
-- Name: refresh_unified_geo_locations(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.refresh_unified_geo_locations() RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    REFRESH MATERIALIZED VIEW mv_unified_geo_locations;
END;
$$;


--
-- Name: search_unified_geo_locations(text, text, character varying, integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.search_unified_geo_locations(search_term text DEFAULT NULL::text, geo_type text DEFAULT NULL::text, country_filter character varying DEFAULT NULL::character varying, min_subscribers integer DEFAULT 0, result_limit integer DEFAULT 50) RETURNS TABLE(id text, display_name character varying, type text, country_code character varying, country_name text, subscriber_count bigint, flag_emoji text, mailchimp_geo_id integer, confidence_level integer)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    SELECT 
        v.id,
        v.display_name,
        v.type,
        v.country_code,
        v.country_name,
        v.subscriber_count,
        v.flag_emoji,
        v.mailchimp_geo_id,
        v.confidence_level
    FROM v_unified_geo_locations v
    WHERE 
        (search_term IS NULL OR 
         v.display_name ILIKE '%' || search_term || '%' OR
         v.city_name ILIKE '%' || search_term || '%' OR
         v.country_name ILIKE '%' || search_term || '%' OR
         search_term = ANY(v.search_terms))
    AND (geo_type IS NULL OR v.type = geo_type)
    AND (country_filter IS NULL OR v.country_code = country_filter)
    AND (v.subscriber_count >= min_subscribers)
    ORDER BY 
        v.subscriber_count DESC NULLS LAST,
        v.importance_rank ASC,
        v.display_name ASC
    LIMIT result_limit;
END;
$$;


--
-- Name: test_coordinate_detection(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.test_coordinate_detection() RETURNS TABLE(test_location character varying, detected_location character varying, geo_id integer, confidence integer)
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Test Manchester coordinates
    RETURN QUERY
    SELECT 
        'Manchester Test' as test_location,
        r.geo_location as detected_location,
        r.geo_id,
        r.confidence
    FROM get_geo_id_from_coordinates(53.4808, -2.2426) r;
    
    -- Test London coordinates  
    RETURN QUERY
    SELECT 
        'London Test' as test_location,
        r.geo_location as detected_location,
        r.geo_id,
        r.confidence
    FROM get_geo_id_from_coordinates(51.5074, -0.1278) r;
    
    -- Test Birmingham coordinates
    RETURN QUERY
    SELECT 
        'Birmingham Test' as test_location,
        r.geo_location as detected_location,
        r.geo_id,
        r.confidence
    FROM get_geo_id_from_coordinates(52.4862, -1.8904) r;
END;
$$;


--
-- Name: FUNCTION test_coordinate_detection(); Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON FUNCTION public.test_coordinate_detection() IS 'Tests coordinate detection for major UK cities';


--
-- Name: update_subscriber_geo_batch(integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.update_subscriber_geo_batch(batch_size integer DEFAULT 500) RETURNS TABLE(processed_count integer, cache_hits integer, manual_hits integer, maxmind_needed integer)
    LANGUAGE plpgsql
    AS $$
DECLARE
    result_processed INTEGER := 0;
    result_cache INTEGER := 0;
    result_manual INTEGER := 0;
    result_maxmind INTEGER := 0;
    geo_result RECORD;
    subscriber_rec RECORD;
BEGIN
    FOR subscriber_rec IN 
        SELECT id, ip_signup, ip_opt 
        FROM subscribers 
        WHERE computed_geo_id = 0 
        AND (ip_signup IS NOT NULL OR ip_opt IS NOT NULL)
        LIMIT batch_size
    LOOP
        -- Try ip_opt first (more recent), then ip_signup
        SELECT * INTO geo_result 
        FROM get_geo_id_from_ip(COALESCE(subscriber_rec.ip_opt, subscriber_rec.ip_signup));
        
        IF geo_result.source != 'maxmind_pending' THEN
            UPDATE subscribers 
            SET 
                computed_geo_id = geo_result.geo_id,
                computed_geo_location = geo_result.geo_location,
                geo_data_source = geo_result.source,
                geo_confidence_score = geo_result.confidence,
                geo_updated_at = NOW()
            WHERE id = subscriber_rec.id;
            
            result_processed := result_processed + 1;
            
            CASE geo_result.source
                WHEN 'cache' THEN result_cache := result_cache + 1;
                WHEN 'manual' THEN result_manual := result_manual + 1;
            END CASE;
        ELSE
            result_maxmind := result_maxmind + 1;
        END IF;
    END LOOP;
    
    RETURN QUERY SELECT result_processed, result_cache, result_manual, result_maxmind;
END;
$$;


--
-- Name: update_updated_at_column(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.update_updated_at_column() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;


--
-- Name: upsert_subscriber(text, jsonb); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.upsert_subscriber(p_email text, p_mailchimp_data jsonb) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    merge_fields jsonb;
    stats_data jsonb;
    location_data jsonb;
BEGIN
    -- Extract nested JSON data
    merge_fields := p_mailchimp_data->'merge_fields';
    stats_data := p_mailchimp_data->'stats';
    location_data := p_mailchimp_data->'location';
    
    INSERT INTO subscribers (
        email,
        first_name,
        last_name,
        status,
        email_type,
        mailchimp_member_id,
        unique_email_id,
        subscriber_hash,
        vip,
        member_rating,
        timestamp_signup,
        timestamp_opt,
        ip_signup,
        ip_opt,
        language,
        total_opens,
        total_clicks,
        avg_open_rate,
        avg_click_rate,
        country,
        city,
        postal_code,
        country_code,
        timezone,
        latitude,
        longitude,
        gmtoff,
        dstoff,
        custom_fields,
        updated_at
    ) VALUES (
        p_email,
        COALESCE(merge_fields->>'FNAME', ''),
        COALESCE(merge_fields->>'LNAME', ''),
        p_mailchimp_data->>'status',
        COALESCE(p_mailchimp_data->>'email_type', 'html'),
        p_mailchimp_data->>'id',
        p_mailchimp_data->>'unique_email_id',
        p_mailchimp_data->>'web_id',
        COALESCE((p_mailchimp_data->>'vip')::boolean, false),
        COALESCE((p_mailchimp_data->>'member_rating')::integer, 2),
        CASE 
            WHEN p_mailchimp_data->>'timestamp_signup' != '' 
            THEN (p_mailchimp_data->>'timestamp_signup')::timestamp
            ELSE NULL 
        END,
        CASE 
            WHEN p_mailchimp_data->>'timestamp_opt' != '' 
            THEN (p_mailchimp_data->>'timestamp_opt')::timestamp
            ELSE NULL 
        END,
        p_mailchimp_data->>'ip_signup',
        p_mailchimp_data->>'ip_opt',
        p_mailchimp_data->>'language',
        COALESCE((stats_data->>'avg_open_rate')::numeric * 100, 0),
        COALESCE((stats_data->>'avg_click_rate')::numeric * 100, 0),
        COALESCE((stats_data->>'avg_open_rate')::numeric(5,2), 0.00),
        COALESCE((stats_data->>'avg_click_rate')::numeric(5,2), 0.00),
        COALESCE(merge_fields->>'ADDRESS'->>'country', location_data->>'country_code'),
        COALESCE(merge_fields->>'ADDRESS'->>'city', ''),
        COALESCE(merge_fields->>'ADDRESS'->>'zip', ''),
        location_data->>'country_code',
        location_data->>'timezone',
        CASE 
            WHEN location_data->>'latitude' != '0' 
            THEN (location_data->>'latitude')::numeric 
            ELSE NULL 
        END,
        CASE 
            WHEN location_data->>'longitude' != '0' 
            THEN (location_data->>'longitude')::numeric 
            ELSE NULL 
        END,
        COALESCE((location_data->>'gmtoff')::integer, 0),
        COALESCE((location_data->>'dstoff')::integer, 0),
        merge_fields,
        NOW()
    ) ON CONFLICT (email) DO UPDATE SET
        first_name = EXCLUDED.first_name,
        last_name = EXCLUDED.last_name,
        status = EXCLUDED.status,
        email_type = EXCLUDED.email_type,
        mailchimp_member_id = EXCLUDED.mailchimp_member_id,
        unique_email_id = EXCLUDED.unique_email_id,
        subscriber_hash = EXCLUDED.subscriber_hash,
        vip = EXCLUDED.vip,
        member_rating = EXCLUDED.member_rating,
        timestamp_signup = COALESCE(EXCLUDED.timestamp_signup, subscribers.timestamp_signup),
        timestamp_opt = COALESCE(EXCLUDED.timestamp_opt, subscribers.timestamp_opt),
        ip_signup = COALESCE(EXCLUDED.ip_signup, subscribers.ip_signup),
        ip_opt = COALESCE(EXCLUDED.ip_opt, subscribers.ip_opt),
        language = EXCLUDED.language,
        total_opens = EXCLUDED.total_opens,
        total_clicks = EXCLUDED.total_clicks,
        avg_open_rate = EXCLUDED.avg_open_rate,
        avg_click_rate = EXCLUDED.avg_click_rate,
        country = EXCLUDED.country,
        city = EXCLUDED.city,
        postal_code = EXCLUDED.postal_code,
        country_code = EXCLUDED.country_code,
        timezone = EXCLUDED.timezone,
        latitude = EXCLUDED.latitude,
        longitude = EXCLUDED.longitude,
        gmtoff = EXCLUDED.gmtoff,
        dstoff = EXCLUDED.dstoff,
        custom_fields = EXCLUDED.custom_fields,
        updated_at = NOW();
END;
$$;


--
-- Name: upsert_sync_state(character varying, integer, integer, integer, integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.upsert_sync_state(p_workflow_instance character varying, p_current_offset integer, p_last_successful_batch integer, p_total_processed integer, p_total_added integer, p_total_updated integer, p_total_errors integer, p_status character varying, p_recovery_point character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO sync_state (
        workflow_instance, current_offset, last_successful_batch,
        total_processed, total_added, total_updated, total_errors,
        status, recovery_point, last_update_time, updated_at
    ) VALUES (
        p_workflow_instance, p_current_offset, p_last_successful_batch,
        p_total_processed, p_total_added, p_total_updated, p_total_errors,
        p_status, p_recovery_point, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
    )
    ON CONFLICT (workflow_instance) DO UPDATE SET
        current_offset = EXCLUDED.current_offset,
        last_successful_batch = EXCLUDED.last_successful_batch,
        total_processed = EXCLUDED.total_processed,
        total_added = EXCLUDED.total_added,
        total_updated = EXCLUDED.total_updated,
        total_errors = EXCLUDED.total_errors,
        status = EXCLUDED.status,
        recovery_point = EXCLUDED.recovery_point,
        last_update_time = EXCLUDED.last_update_time,
        updated_at = EXCLUDED.updated_at;
END;
$$;




--
-- Name: account_limits; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.account_limits (
    id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    monthly_send_limit integer,
    current_monthly_sends integer DEFAULT 0,
    total_subscribers integer DEFAULT 0,
    subscriber_limit integer,
    api_calls_remaining integer,
    api_calls_reset_time timestamp with time zone,
    daily_api_limit integer DEFAULT 10000,
    plan_type text,
    plan_name text,
    billing_cycle text,
    last_fetched_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


--
-- Name: TABLE account_limits; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.account_limits IS 'Current account limits and usage from Mailchimp API';


--
-- Name: account_limits_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.account_limits_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_limits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.account_limits_id_seq OWNED BY public.account_limits.id;


--
-- Name: account_settings; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.account_settings (
    id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    api_rate_limit integer DEFAULT 10000,
    api_timeout integer DEFAULT 30000,
    max_retries integer DEFAULT 3,
    retry_delay integer DEFAULT 1000,
    auto_sync_enabled boolean DEFAULT false,
    sync_frequency integer DEFAULT 3600,
    sync_lists text[],
    sync_segments boolean DEFAULT true,
    sync_subscribers boolean DEFAULT true,
    sync_tags boolean DEFAULT true,
    sync_custom_fields boolean DEFAULT false,
    keep_unsubscribed boolean DEFAULT true,
    keep_cleaned boolean DEFAULT false,
    data_retention_days integer DEFAULT 365,
    notify_sync_complete boolean DEFAULT true,
    notify_sync_errors boolean DEFAULT true,
    notify_api_limits boolean DEFAULT true,
    notification_email text,
    notification_webhook text,
    use_double_optin boolean DEFAULT true,
    merge_duplicate_contacts boolean DEFAULT false,
    update_existing_contacts boolean DEFAULT true,
    preserve_existing_tags boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    created_by text,
    updated_by text
);


--
-- Name: TABLE account_settings; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.account_settings IS 'Advanced configuration settings for each Mailchimp account';


--
-- Name: account_settings_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.account_settings_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_settings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.account_settings_id_seq OWNED BY public.account_settings.id;


--
-- Name: mailchimp_accounts; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.mailchimp_accounts (
    id integer NOT NULL,
    account_name character varying(255) NOT NULL,
    account_nickname character varying(100),
    api_key character varying(255) NOT NULL,
    api_server character varying(200) NOT NULL,
    datacenter character varying(10) NOT NULL,
    mailchimp_account_id character varying(50),
    account_email character varying(255),
    company_name character varying(255),
    total_subscribers integer DEFAULT 0,
    is_active boolean DEFAULT true,
    sync_enabled boolean DEFAULT true,
    last_sync_date timestamp without time zone,
    api_calls_today integer DEFAULT 0,
    api_limit_per_day integer DEFAULT 10000,
    last_api_reset date DEFAULT CURRENT_DATE,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    connection_status character varying(20) DEFAULT 'pending'::character varying,
    last_test_at timestamp with time zone,
    description text,
    CONSTRAINT check_connection_status CHECK (((connection_status)::text = ANY ((ARRAY['pending'::character varying, 'connected'::character varying, 'error'::character varying, 'disconnected'::character varying])::text[])))
);


--
-- Name: subscriber_mailchimp_accounts; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.subscriber_mailchimp_accounts (
    id integer NOT NULL,
    subscriber_id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    mailchimp_list_id integer NOT NULL,
    mailchimp_member_id character varying(50) NOT NULL,
    status character varying(20) NOT NULL,
    member_rating integer DEFAULT 0,
    last_changed timestamp without time zone,
    opt_in_time timestamp without time zone,
    merge_fields jsonb,
    interests jsonb,
    last_synced_at timestamp without time zone,
    sync_errors jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now()
);


--
-- Name: active_subscribers_by_account; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.active_subscribers_by_account AS
 SELECT ma.id AS account_id,
    ma.account_name,
    ma.account_nickname,
    count(sma.subscriber_id) AS total_subscribers,
    count(
        CASE
            WHEN ((sma.status)::text = 'subscribed'::text) THEN 1
            ELSE NULL::integer
        END) AS subscribed_count,
    count(
        CASE
            WHEN ((sma.status)::text = 'unsubscribed'::text) THEN 1
            ELSE NULL::integer
        END) AS unsubscribed_count,
    count(
        CASE
            WHEN ((sma.status)::text = 'cleaned'::text) THEN 1
            ELSE NULL::integer
        END) AS cleaned_count
   FROM (public.mailchimp_accounts ma
     LEFT JOIN public.subscriber_mailchimp_accounts sma ON ((ma.id = sma.mailchimp_account_id)))
  WHERE (ma.is_active = true)
  GROUP BY ma.id, ma.account_name, ma.account_nickname;


--
-- Name: api_usage_tracking; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.api_usage_tracking (
    id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    date_hour timestamp with time zone NOT NULL,
    requests_count integer DEFAULT 0,
    successful_requests integer DEFAULT 0,
    failed_requests integer DEFAULT 0,
    rate_limited_requests integer DEFAULT 0,
    avg_response_time numeric(8,2),
    max_response_time numeric(8,2),
    min_response_time numeric(8,2),
    error_codes jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now()
);


--
-- Name: TABLE api_usage_tracking; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.api_usage_tracking IS 'Hourly tracking of API usage and performance metrics';


--
-- Name: api_usage_tracking_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.api_usage_tracking_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: api_usage_tracking_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.api_usage_tracking_id_seq OWNED BY public.api_usage_tracking.id;


--
-- Name: batch_progress; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.batch_progress (
    batch_number integer NOT NULL,
    segment_id integer,
    segment_name character varying(255),
    page_number integer,
    total_pages integer,
    member_count integer,
    records_processed integer,
    processed_at timestamp without time zone DEFAULT now(),
    workflow_run_id character varying(100),
    status character varying(50) DEFAULT 'completed'::character varying
);


--
-- Name: campaigns; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.campaigns (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    campaign_type character varying(50),
    platform character varying(20),
    external_campaign_id character varying(100),
    sent_date timestamp without time zone,
    subject_line text,
    total_sent integer DEFAULT 0,
    total_opens integer DEFAULT 0,
    total_clicks integer DEFAULT 0,
    created_at timestamp without time zone DEFAULT now(),
    mailchimp_account_id integer,
    mailchimp_list_id integer
);


--
-- Name: campaigns_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.campaigns_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: campaigns_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.campaigns_id_seq OWNED BY public.campaigns.id;


--
-- Name: city_geo_mapping; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.city_geo_mapping (
    id integer NOT NULL,
    city_name character varying(255),
    country_code character varying(2),
    state_region character varying(255),
    mailchimp_geo_id integer,
    geo_location_name character varying(255),
    confidence_level integer DEFAULT 85,
    is_major_city boolean DEFAULT false,
    subscriber_count integer DEFAULT 0,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now()
);


--
-- Name: city_geo_mapping_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.city_geo_mapping_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: city_geo_mapping_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.city_geo_mapping_id_seq OWNED BY public.city_geo_mapping.id;


--
-- Name: coordinate_geo_boundaries; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.coordinate_geo_boundaries (
    id integer NOT NULL,
    mailchimp_geo_id integer,
    geo_location_name character varying(255),
    country_code character varying(2),
    region_name character varying(100),
    city_name character varying(100),
    geo_bounds public.geometry(Polygon,4326),
    confidence_level integer DEFAULT 95,
    is_primary_market boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: coordinate_geo_boundaries_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.coordinate_geo_boundaries_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: coordinate_geo_boundaries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.coordinate_geo_boundaries_id_seq OWNED BY public.coordinate_geo_boundaries.id;


--
-- Name: coordinate_geo_mapping; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.coordinate_geo_mapping (
    id integer NOT NULL,
    geo_bounds_north numeric(10,7),
    geo_bounds_south numeric(10,7),
    geo_bounds_east numeric(10,7),
    geo_bounds_west numeric(10,7),
    mailchimp_geo_id integer,
    geo_location_name character varying(255),
    country_code character varying(2),
    region_name character varying(100),
    confidence_level integer DEFAULT 95,
    created_at timestamp without time zone DEFAULT now(),
    city_name character varying(100),
    population integer,
    metro_area_population integer,
    importance_rank integer DEFAULT 100,
    venue_category character varying(50),
    time_zone character varying(50),
    country_name character varying(100)
);


--
-- Name: TABLE coordinate_geo_mapping; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.coordinate_geo_mapping IS 'Geographic coordinate mapping using bounding boxes for Mailchimp-compatible geo IDs';


--
-- Name: coordinate_geo_mapping_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.coordinate_geo_mapping_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: coordinate_geo_mapping_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.coordinate_geo_mapping_id_seq OWNED BY public.coordinate_geo_mapping.id;


--
-- Name: country_geo_fallback; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.country_geo_fallback (
    country_code character varying(2) NOT NULL,
    mailchimp_geo_id integer,
    geo_location_name character varying(255),
    confidence_level integer DEFAULT 40,
    importance_rank integer DEFAULT 100
);


--
-- Name: country_geo_mapping; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.country_geo_mapping (
    id integer NOT NULL,
    country_code character varying(2),
    default_mailchimp_geo_id integer,
    geo_location_name character varying(255),
    is_primary_market boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: country_geo_mapping_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.country_geo_mapping_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: country_geo_mapping_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.country_geo_mapping_id_seq OWNED BY public.country_geo_mapping.id;


--
-- Name: subscribers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.subscribers (
    id integer NOT NULL,
    email character varying(255) NOT NULL,
    first_name character varying(100),
    last_name character varying(100),
    primary_mailchimp_account_id integer,
    account_statuses jsonb,
    country character varying(100),
    city character varying(100),
    postal_code character varying(20),
    timezone character varying(50),
    language character varying(10),
    signup_date timestamp without time zone,
    last_open_date timestamp without time zone,
    last_click_date timestamp without time zone,
    total_opens integer DEFAULT 0,
    total_clicks integer DEFAULT 0,
    avg_open_rate numeric(5,2),
    avg_click_rate numeric(5,2),
    custom_fields jsonb,
    flodesk_id character varying(50),
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    mailchimp_member_id character varying(50),
    unique_email_id character varying(50),
    subscriber_hash character varying(32),
    status character varying(20) DEFAULT 'subscribed'::character varying NOT NULL,
    email_type character varying(10) DEFAULT 'html'::character varying,
    vip boolean DEFAULT false,
    member_rating integer,
    timestamp_signup timestamp without time zone,
    timestamp_opt timestamp without time zone,
    ip_signup character varying(45),
    ip_opt character varying(45),
    source character varying(100),
    latitude numeric(10,8),
    longitude numeric(11,8),
    country_code character varying(2),
    dstoff integer,
    gmtoff integer,
    tags text,
    computed_geo_id integer DEFAULT 0,
    computed_geo_location character varying(255),
    geo_data_source character varying(20),
    geo_confidence_score integer DEFAULT 0,
    geo_updated_at timestamp without time zone,
    geo_processing_notes text,
    iso_geo_id integer,
    iso_geo_location character varying(255),
    iso_country_code integer,
    iso_confidence_level integer,
    iso_data_source character varying(50),
    iso_updated_at timestamp without time zone,
    migration_status character varying(20) DEFAULT 'pending'::character varying,
    geo_status character varying(20) DEFAULT NULL::character varying,
    CONSTRAINT check_geo_status CHECK (((geo_status)::text = ANY ((ARRAY['pending'::character varying, 'processing'::character varying, 'processed'::character varying, 'no_valid_data'::character varying, 'failed'::character varying, NULL::character varying])::text[]))),
    CONSTRAINT subscribers_member_rating_check CHECK (((member_rating >= 1) AND (member_rating <= 5)))
);


--
-- Name: COLUMN subscribers.computed_geo_id; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.subscribers.computed_geo_id IS 'Mailchimp-compatible geo_id (25=Manchester, 26=London, etc.)';


--
-- Name: COLUMN subscribers.iso_geo_id; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.subscribers.iso_geo_id IS 'ISO 3166-1 based geo_id (826001=Manchester, 826002=London, etc.)';


--
-- Name: COLUMN subscribers.iso_country_code; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.subscribers.iso_country_code IS 'ISO 3166-1 numeric country code (826=UK, 840=US, 036=AU, etc.)';


--
-- Name: cross_account_subscriber_summary; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.cross_account_subscriber_summary AS
 SELECT s.id,
    s.email,
    s.first_name,
    s.last_name,
    count(sma.mailchimp_account_id) AS account_count,
    string_agg((ma.account_nickname)::text, ', '::text) AS account_names,
    s.avg_open_rate,
    s.avg_click_rate,
    s.last_open_date
   FROM ((public.subscribers s
     LEFT JOIN public.subscriber_mailchimp_accounts sma ON ((s.id = sma.subscriber_id)))
     LEFT JOIN public.mailchimp_accounts ma ON ((sma.mailchimp_account_id = ma.id)))
  WHERE (s.primary_mailchimp_account_id IS NOT NULL)
  GROUP BY s.id, s.email, s.first_name, s.last_name, s.avg_open_rate, s.avg_click_rate, s.last_open_date;


--
-- Name: flodesk_integrations; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.flodesk_integrations (
    id integer NOT NULL,
    mailchimp_account_id integer,
    integration_name character varying(255) NOT NULL,
    flodesk_api_key text NOT NULL,
    flodesk_workspace_id character varying(50),
    default_segment_id character varying(50),
    is_active boolean DEFAULT true,
    last_sync_date timestamp without time zone,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now()
);


--
-- Name: flodesk_integrations_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.flodesk_integrations_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: flodesk_integrations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.flodesk_integrations_id_seq OWNED BY public.flodesk_integrations.id;


--
-- Name: geo_id_migration_map; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.geo_id_migration_map (
    id integer NOT NULL,
    old_geo_id integer NOT NULL,
    new_iso_geo_id integer NOT NULL,
    migration_status character varying(20) DEFAULT 'pending'::character varying,
    migration_date timestamp without time zone,
    notes text,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: geo_id_migration_map_backup_008; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.geo_id_migration_map_backup_008 (
    id integer NOT NULL,
    mailchimp_geo_id integer NOT NULL,
    iso_geo_id integer NOT NULL,
    mapping_type character varying(20) DEFAULT 'direct'::character varying,
    confidence_level integer DEFAULT 95,
    notes text,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: geo_id_migration_map_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.geo_id_migration_map_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: geo_id_migration_map_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.geo_id_migration_map_id_seq OWNED BY public.geo_id_migration_map_backup_008.id;


--
-- Name: geo_id_migration_map_id_seq1; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.geo_id_migration_map_id_seq1
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: geo_id_migration_map_id_seq1; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.geo_id_migration_map_id_seq1 OWNED BY public.geo_id_migration_map.id;


--
-- Name: hierarchical_geo_view; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.hierarchical_geo_view AS
 SELECT country_code,
    country_name,
    region_name,
    city_name,
    geo_location_name,
    mailchimp_geo_id,
    confidence_level,
    count(*) OVER (PARTITION BY country_code) AS country_location_count,
    count(*) OVER (PARTITION BY country_code, region_name) AS region_location_count,
    count(*) OVER (PARTITION BY country_code, region_name, city_name) AS city_location_count
   FROM public.coordinate_geo_mapping
  WHERE (country_code IS NOT NULL)
  ORDER BY country_name, region_name, city_name, geo_location_name;


--
-- Name: ip_geo_cache; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.ip_geo_cache (
    ip_address inet NOT NULL,
    mailchimp_geo_id integer,
    geo_location_name character varying(255),
    country_code character varying(2),
    city_name character varying(100),
    lookup_source character varying(20),
    confidence_score integer,
    lookup_count integer DEFAULT 1,
    last_used timestamp without time zone DEFAULT now(),
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: iso_coordinate_mapping; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.iso_coordinate_mapping (
    id integer NOT NULL,
    iso_geo_id integer NOT NULL,
    iso_geo_location character varying(255) NOT NULL,
    iso_country_code integer NOT NULL,
    confidence_level integer DEFAULT 85,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: iso_coordinate_mapping_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.iso_coordinate_mapping_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: iso_coordinate_mapping_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.iso_coordinate_mapping_id_seq OWNED BY public.iso_coordinate_mapping.id;


--
-- Name: iso_geo_mapping; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.iso_geo_mapping (
    iso_geo_id integer NOT NULL,
    geo_location_name character varying(255) NOT NULL,
    country_code character varying(2) NOT NULL,
    iso_country_code integer NOT NULL,
    city_name character varying(100),
    region_name character varying(100),
    is_country_level boolean DEFAULT false,
    confidence_level integer DEFAULT 90,
    population integer,
    timezone character varying(50),
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now()
);


--
-- Name: mailchimp_accounts_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.mailchimp_accounts_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: mailchimp_accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.mailchimp_accounts_id_seq OWNED BY public.mailchimp_accounts.id;


--
-- Name: mailchimp_geo_mapping; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.mailchimp_geo_mapping (
    id integer NOT NULL,
    mailchimp_geo_id integer,
    geo_location_name character varying(255),
    country_code character varying(2),
    region_name character varying(100),
    city_name character varying(100),
    confidence_level integer DEFAULT 80,
    is_primary_market boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: mailchimp_geo_mapping_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.mailchimp_geo_mapping_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: mailchimp_geo_mapping_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.mailchimp_geo_mapping_id_seq OWNED BY public.mailchimp_geo_mapping.id;


--
-- Name: mailchimp_lists; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.mailchimp_lists (
    id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    list_id character varying(50) NOT NULL,
    list_name character varying(255) NOT NULL,
    permission_reminder text,
    use_archive_bar boolean DEFAULT true,
    campaign_defaults jsonb,
    notify_on_subscribe character varying(255),
    notify_on_unsubscribe character varying(255),
    member_count integer DEFAULT 0,
    unsubscribe_count integer DEFAULT 0,
    cleaned_count integer DEFAULT 0,
    member_count_since_send integer DEFAULT 0,
    unsubscribe_count_since_send integer DEFAULT 0,
    cleaned_count_since_send integer DEFAULT 0,
    is_primary boolean DEFAULT false,
    sync_enabled boolean DEFAULT true,
    last_sync_date timestamp without time zone,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now()
);


--
-- Name: mailchimp_lists_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.mailchimp_lists_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: mailchimp_lists_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.mailchimp_lists_id_seq OWNED BY public.mailchimp_lists.id;


--
-- Name: manual_ip_ranges; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.manual_ip_ranges (
    id integer NOT NULL,
    ip_start inet,
    ip_end inet,
    mailchimp_geo_id integer,
    geo_location_name character varying(255),
    notes text,
    confidence_level integer DEFAULT 90,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: manual_ip_ranges_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.manual_ip_ranges_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: manual_ip_ranges_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.manual_ip_ranges_id_seq OWNED BY public.manual_ip_ranges.id;


--
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.schema_migrations (
    id integer NOT NULL,
    migration_name character varying(255) NOT NULL,
    applied_at timestamp with time zone DEFAULT now()
);


--
-- Name: schema_migrations_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.schema_migrations_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: schema_migrations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.schema_migrations_id_seq OWNED BY public.schema_migrations.id;


--
-- Name: segment_mappings; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.segment_mappings (
    id integer NOT NULL,
    mailchimp_segment_id character varying(50) NOT NULL,
    mailchimp_segment_name character varying(255) NOT NULL,
    flodesk_segment_id character varying(100) NOT NULL,
    flodesk_segment_name character varying(255) NOT NULL,
    active boolean DEFAULT true,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


--
-- Name: segment_mappings_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.segment_mappings_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: segment_mappings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.segment_mappings_id_seq OWNED BY public.segment_mappings.id;


--
-- Name: segment_rule_templates; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.segment_rule_templates (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    description text,
    rule_definition json,
    category character varying(50),
    is_system_template boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: segment_rule_templates_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.segment_rule_templates_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: segment_rule_templates_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.segment_rule_templates_id_seq OWNED BY public.segment_rule_templates.id;


--
-- Name: segment_sync_status; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.segment_sync_status (
    id integer NOT NULL,
    segment_id integer NOT NULL,
    provider character varying(50) NOT NULL,
    status character varying(50) NOT NULL,
    sync_type character varying(50) DEFAULT 'full'::character varying,
    synced_count integer DEFAULT 0,
    failed_count integer DEFAULT 0,
    total_count integer,
    error_message text,
    error_details jsonb,
    execution_id character varying(255),
    started_at timestamp without time zone,
    completed_at timestamp without time zone,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    duration_seconds integer
);


--
-- Name: TABLE segment_sync_status; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.segment_sync_status IS 'Tracks synchronization operations between segments and external platforms';


--
-- Name: COLUMN segment_sync_status.provider; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segment_sync_status.provider IS 'External platform name (flodesk, mailchimp, etc.)';


--
-- Name: COLUMN segment_sync_status.status; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segment_sync_status.status IS 'Current status of the sync operation';


--
-- Name: COLUMN segment_sync_status.execution_id; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segment_sync_status.execution_id IS 'Reference to external execution (e.g., n8n workflow execution ID)';


--
-- Name: segment_sync_status_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.segment_sync_status_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: segment_sync_status_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.segment_sync_status_id_seq OWNED BY public.segment_sync_status.id;


--
-- Name: segments; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.segments (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    description text,
    mailchimp_account_id integer,
    scope character varying(20) DEFAULT 'account'::character varying,
    mailchimp_segment_id character varying(50),
    flodesk_segment_id character varying(50),
    segment_type character varying(20) NOT NULL,
    platform character varying(20) DEFAULT 'both'::character varying,
    segment_rules jsonb,
    is_active boolean DEFAULT true,
    auto_update boolean DEFAULT false,
    last_updated timestamp without time zone,
    subscriber_count integer DEFAULT 0,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    last_sync_date timestamp without time zone,
    mailchimp_updated_at timestamp without time zone,
    sync_status character varying(20) DEFAULT 'pending'::character varying,
    error_details jsonb,
    performance_metrics jsonb,
    open_rate numeric(5,2),
    click_rate numeric(5,2),
    growth_rate numeric(5,2),
    last_campaign_sent timestamp without time zone,
    migration_status character varying(20) DEFAULT 'pending'::character varying,
    old_segment_rules jsonb,
    iso_ready boolean DEFAULT false,
    segment_source character varying(20) DEFAULT 'mailchimp'::character varying,
    created_in_system character varying(20) DEFAULT 'mailchimp'::character varying,
    local_subscriber_count integer DEFAULT 0,
    mailchimp_subscriber_count integer,
    count_discrepancy integer,
    last_count_sync_at timestamp without time zone,
    count_sync_status character varying(20) DEFAULT 'pending'::character varying,
    count_notes text,
    flodesk_segment_name character varying(255),
    sync_subscribe_status jsonb DEFAULT '["subscribed"]'::jsonb,
    sync_frequency character varying(50) DEFAULT 'manual'::character varying,
    batch_size integer DEFAULT 100,
    auto_create_segment boolean DEFAULT true,
    sync_custom_fields jsonb DEFAULT '[]'::jsonb,
    sync_tags boolean DEFAULT true,
    conflict_resolution character varying(50) DEFAULT 'update_existing'::character varying,
    error_handling character varying(50) DEFAULT 'continue'::character varying,
    notification_email character varying(255),
    mailchimp_list_id character varying(255)
);


--
-- Name: COLUMN segments.last_sync_date; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.last_sync_date IS 'Last time this segment was synced from Mailchimp';


--
-- Name: COLUMN segments.mailchimp_updated_at; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.mailchimp_updated_at IS 'When this segment was last updated in Mailchimp';


--
-- Name: COLUMN segments.sync_status; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.sync_status IS 'Current sync status: pending, syncing, completed, error';


--
-- Name: COLUMN segments.error_details; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.error_details IS 'JSON object containing sync error details';


--
-- Name: COLUMN segments.performance_metrics; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.performance_metrics IS 'JSON object with performance data like growth trends';


--
-- Name: COLUMN segments.open_rate; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.open_rate IS 'Average open rate for this segment';


--
-- Name: COLUMN segments.click_rate; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.click_rate IS 'Average click rate for this segment';


--
-- Name: COLUMN segments.growth_rate; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.growth_rate IS 'Monthly growth rate percentage';


--
-- Name: COLUMN segments.last_campaign_sent; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.last_campaign_sent IS 'When the last campaign was sent to this segment';


--
-- Name: COLUMN segments.segment_source; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.segment_source IS 'Where to get subscriber count from: mailchimp (subscriber_segments table), dashboard (rule evaluation), or both (compare both sources)';


--
-- Name: COLUMN segments.created_in_system; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.segments.created_in_system IS 'System where segment was originally created: mailchimp or dashboard';


--
-- Name: segments_backup_geo_fix_20251003; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.segments_backup_geo_fix_20251003 (
    id integer,
    name character varying(255),
    description text,
    mailchimp_account_id integer,
    scope character varying(20),
    mailchimp_segment_id character varying(50),
    flodesk_segment_id character varying(50),
    segment_type character varying(20),
    platform character varying(20),
    segment_rules jsonb,
    is_active boolean,
    auto_update boolean,
    last_updated timestamp without time zone,
    subscriber_count integer,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    last_sync_date timestamp without time zone,
    mailchimp_updated_at timestamp without time zone,
    sync_status character varying(20),
    error_details jsonb,
    performance_metrics jsonb,
    open_rate numeric(5,2),
    click_rate numeric(5,2),
    growth_rate numeric(5,2),
    last_campaign_sent timestamp without time zone,
    migration_status character varying(20),
    old_segment_rules jsonb,
    iso_ready boolean,
    segment_source character varying(20),
    created_in_system character varying(20)
);


--
-- Name: segments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.segments_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: segments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.segments_id_seq OWNED BY public.segments.id;


--
-- Name: settings_audit_log; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.settings_audit_log (
    id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    table_name text NOT NULL,
    record_id integer NOT NULL,
    action text NOT NULL,
    old_values jsonb,
    new_values jsonb,
    changed_fields text[],
    changed_by text,
    change_reason text,
    ip_address inet,
    user_agent text,
    created_at timestamp with time zone DEFAULT now(),
    CONSTRAINT settings_audit_log_action_check CHECK ((action = ANY (ARRAY['CREATE'::text, 'UPDATE'::text, 'DELETE'::text])))
);


--
-- Name: TABLE settings_audit_log; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.settings_audit_log IS 'Audit trail for all settings changes';


--
-- Name: settings_audit_log_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.settings_audit_log_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: settings_audit_log_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.settings_audit_log_id_seq OWNED BY public.settings_audit_log.id;


--
-- Name: subscriber_mailchimp_accounts_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.subscriber_mailchimp_accounts_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: subscriber_mailchimp_accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.subscriber_mailchimp_accounts_id_seq OWNED BY public.subscriber_mailchimp_accounts.id;


--
-- Name: subscriber_segments; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.subscriber_segments (
    id integer NOT NULL,
    email character varying(255) NOT NULL,
    mailchimp_segment_id character varying(50) NOT NULL,
    mailchimp_segment_name character varying(255) NOT NULL,
    subscriber_hash character varying(32) NOT NULL,
    sync_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    flodesk_segment_id character varying(255),
    subscriber_id integer,
    segment_id integer,
    source_mailchimp_account_id integer,
    join_method character varying(20) DEFAULT 'manual'::character varying,
    join_date timestamp without time zone DEFAULT now(),
    synced_to_mailchimp boolean DEFAULT false,
    synced_to_flodesk boolean DEFAULT false,
    mailchimp_synced_at timestamp without time zone,
    flodesk_synced_at timestamp without time zone,
    sync_errors jsonb,
    is_active boolean DEFAULT true,
    created_at timestamp without time zone DEFAULT now(),
    last_synced_to_flodesk timestamp without time zone
);


--
-- Name: subscriber_segments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.subscriber_segments_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: subscriber_segments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.subscriber_segments_id_seq OWNED BY public.subscriber_segments.id;


--
-- Name: subscribers_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.subscribers_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: subscribers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.subscribers_id_seq OWNED BY public.subscribers.id;


--
-- Name: sync_activity; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.sync_activity (
    id integer NOT NULL,
    email character varying(255) NOT NULL,
    action character varying(100) NOT NULL,
    details jsonb,
    success boolean DEFAULT true,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


--
-- Name: sync_activity_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.sync_activity_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sync_activity_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.sync_activity_id_seq OWNED BY public.sync_activity.id;


--
-- Name: sync_analytics; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.sync_analytics (
    id integer NOT NULL,
    sync_date date DEFAULT CURRENT_DATE NOT NULL,
    workflow_instance character varying(50) DEFAULT 'prod1'::character varying,
    total_subscribers integer DEFAULT 0,
    active_subscribers integer DEFAULT 0,
    vip_subscribers integer DEFAULT 0,
    new_subscribers_today integer DEFAULT 0,
    new_subscribers_7d integer DEFAULT 0,
    new_subscribers_30d integer DEFAULT 0,
    overall_open_rate numeric(5,2) DEFAULT 0.00,
    overall_click_rate numeric(5,2) DEFAULT 0.00,
    total_opens_today integer DEFAULT 0,
    total_clicks_today integer DEFAULT 0,
    ultra_engaged integer DEFAULT 0,
    high_engaged integer DEFAULT 0,
    standard_engaged integer DEFAULT 0,
    low_engaged integer DEFAULT 0,
    countries_count integer DEFAULT 0,
    uk_subscribers integer DEFAULT 0,
    uk_percentage numeric(5,2) DEFAULT 0.00,
    international_subscribers integer DEFAULT 0,
    synced_to_flodesk integer DEFAULT 0,
    sync_success_rate numeric(5,2) DEFAULT 0.00,
    total_sync_errors integer DEFAULT 0,
    rating_5_stars integer DEFAULT 0,
    rating_4_stars integer DEFAULT 0,
    rating_3_stars integer DEFAULT 0,
    rating_2_stars integer DEFAULT 0,
    rating_1_star integer DEFAULT 0,
    estimated_mailchimp_cost numeric(10,2) DEFAULT 0.00,
    actual_flodesk_cost numeric(10,2) DEFAULT 35.00,
    monthly_savings numeric(10,2) DEFAULT 0.00,
    last_sync_time timestamp without time zone DEFAULT now(),
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    CONSTRAINT check_click_rate_valid CHECK (((overall_click_rate >= (0)::numeric) AND (overall_click_rate <= (100)::numeric))),
    CONSTRAINT check_open_rate_valid CHECK (((overall_open_rate >= (0)::numeric) AND (overall_open_rate <= (100)::numeric))),
    CONSTRAINT check_subscribers_positive CHECK (((total_subscribers >= 0) AND (active_subscribers >= 0)))
);


--
-- Name: sync_analytics_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.sync_analytics_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sync_analytics_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.sync_analytics_id_seq OWNED BY public.sync_analytics.id;


--
-- Name: sync_jobs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.sync_jobs (
    id integer NOT NULL,
    job_type character varying(50) NOT NULL,
    mailchimp_account_id integer,
    segment_id integer,
    status character varying(20) DEFAULT 'pending'::character varying,
    started_at timestamp without time zone,
    completed_at timestamp without time zone,
    records_processed integer DEFAULT 0,
    records_successful integer DEFAULT 0,
    records_failed integer DEFAULT 0,
    error_details jsonb,
    duration_seconds integer,
    api_calls_made integer DEFAULT 0,
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: sync_jobs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.sync_jobs_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sync_jobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.sync_jobs_id_seq OWNED BY public.sync_jobs.id;


--
-- Name: sync_schedules; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.sync_schedules (
    id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    schedule_name text NOT NULL,
    schedule_type text NOT NULL,
    interval_minutes integer,
    cron_expression text,
    timezone text DEFAULT 'UTC'::text,
    sync_type text DEFAULT 'incremental'::text,
    target_lists text[],
    target_segments text[],
    is_active boolean DEFAULT true,
    last_run_at timestamp with time zone,
    next_run_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    created_by text,
    CONSTRAINT sync_schedules_schedule_type_check CHECK ((schedule_type = ANY (ARRAY['interval'::text, 'cron'::text, 'manual'::text]))),
    CONSTRAINT sync_schedules_sync_type_check CHECK ((sync_type = ANY (ARRAY['full'::text, 'incremental'::text, 'lists_only'::text, 'segments_only'::text]))),
    CONSTRAINT valid_interval_or_cron CHECK ((((schedule_type = 'interval'::text) AND (interval_minutes IS NOT NULL)) OR ((schedule_type = 'cron'::text) AND (cron_expression IS NOT NULL)) OR (schedule_type = 'manual'::text)))
);


--
-- Name: TABLE sync_schedules; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.sync_schedules IS 'Automated sync schedules for each account';


--
-- Name: sync_schedules_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.sync_schedules_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sync_schedules_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.sync_schedules_id_seq OWNED BY public.sync_schedules.id;


--
-- Name: sync_state; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.sync_state (
    workflow_instance character varying(10) NOT NULL,
    current_offset integer,
    last_successful_batch integer,
    total_processed integer,
    total_added integer,
    total_updated integer,
    total_errors integer,
    last_update_time timestamp without time zone,
    status character varying(20),
    recovery_point character varying(50),
    batch_size integer DEFAULT 50,
    offset_start integer DEFAULT 0,
    offset_end integer DEFAULT 1000,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


--
-- Name: synced_subscribers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.synced_subscribers (
    email character varying(255) NOT NULL,
    first_name character varying(255),
    last_name character varying(255),
    status character varying(50),
    mailchimp_id character varying(255),
    tags text,
    segments text,
    segment_ids_json text,
    workflow_instance character varying(10) NOT NULL,
    current_offset integer,
    subscriber_index integer,
    import_date timestamp without time zone,
    id integer NOT NULL,
    flodesk_action character varying(50) DEFAULT 'processed'::character varying,
    flodesk_success boolean DEFAULT true,
    flodesk_error text,
    processed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    type character varying(50) DEFAULT 'manual_sync'::character varying,
    list_id character varying(100),
    sync_direction character varying(20) DEFAULT 'mc_to_flodesk'::character varying,
    sync_source character varying(100),
    retry_count integer DEFAULT 0,
    last_retry_at timestamp without time zone,
    mailchimp_account_id integer,
    CONSTRAINT chk_retry_count CHECK (((retry_count >= 0) AND (retry_count <= 10))),
    CONSTRAINT chk_sync_direction CHECK (((sync_direction)::text = ANY ((ARRAY['mc_to_flodesk'::character varying, 'flodesk_to_mc'::character varying, 'bidirectional'::character varying])::text[]))),
    CONSTRAINT chk_sync_type CHECK (((type)::text = ANY ((ARRAY['manual_sync'::character varying, 'webhook_new_subscriber'::character varying, 'webhook_update'::character varying, 'webhook_unsubscribe'::character varying, 'bulk_import'::character varying])::text[])))
);


--
-- Name: COLUMN synced_subscribers.type; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.synced_subscribers.type IS 'Type of sync: manual_sync, webhook_new_subscriber, webhook_update, webhook_unsubscribe';


--
-- Name: COLUMN synced_subscribers.list_id; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.synced_subscribers.list_id IS 'Specific Mailchimp list/audience ID within the account';


--
-- Name: COLUMN synced_subscribers.sync_direction; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.synced_subscribers.sync_direction IS 'Direction: mc_to_flodesk, flodesk_to_mc, bidirectional';


--
-- Name: COLUMN synced_subscribers.sync_source; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.synced_subscribers.sync_source IS 'What triggered the sync (workflow name, webhook endpoint, etc.)';


--
-- Name: COLUMN synced_subscribers.retry_count; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.synced_subscribers.retry_count IS 'Number of retry attempts for failed syncs';


--
-- Name: COLUMN synced_subscribers.last_retry_at; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.synced_subscribers.last_retry_at IS 'Timestamp of last retry attempt';


--
-- Name: synced_subscribers_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.synced_subscribers_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: synced_subscribers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.synced_subscribers_id_seq OWNED BY public.synced_subscribers.id;


--
-- Name: tags; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.tags (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    description text,
    color character varying(7),
    category character varying(50),
    mailchimp_account_id integer,
    mailchimp_tag_id character varying(50),
    created_at timestamp without time zone DEFAULT now()
);


--
-- Name: tags_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.tags_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: tags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.tags_id_seq OWNED BY public.tags.id;


--
-- Name: v_engagement_trends; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_engagement_trends AS
 SELECT sync_date,
    ultra_engaged,
    high_engaged,
    standard_engaged,
    low_engaged,
    round((((ultra_engaged)::numeric / (NULLIF(total_subscribers, 0))::numeric) * (100)::numeric), 1) AS ultra_engaged_pct,
    round((((high_engaged)::numeric / (NULLIF(total_subscribers, 0))::numeric) * (100)::numeric), 1) AS high_engaged_pct,
    round((((standard_engaged)::numeric / (NULLIF(total_subscribers, 0))::numeric) * (100)::numeric), 1) AS standard_engaged_pct,
    round((((low_engaged)::numeric / (NULLIF(total_subscribers, 0))::numeric) * (100)::numeric), 1) AS low_engaged_pct,
    overall_open_rate,
    overall_click_rate
   FROM public.sync_analytics
  ORDER BY sync_date DESC;


--
-- Name: v_exec_summary; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_exec_summary AS
 SELECT sync_date,
    total_subscribers,
    active_subscribers,
    vip_subscribers,
    (overall_open_rate || '%'::text) AS open_rate,
    (overall_click_rate || '%'::text) AS click_rate,
    (countries_count || ' countries'::text) AS global_reach,
    (((uk_subscribers || ' ('::text) || uk_percentage) || '%)'::text) AS uk_market,
    (ultra_engaged || ' ultra-engaged'::text) AS premium_audience,
    (('£'::text || monthly_savings) || ' monthly savings'::text) AS cost_benefit,
    last_sync_time
   FROM public.sync_analytics
  ORDER BY sync_date DESC;


--
-- Name: v_unified_geo_locations; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_unified_geo_locations AS
 SELECT 3 AS sort_order,
    ('city-'::text || cgm.mailchimp_geo_id) AS id,
    cgm.geo_location_name AS display_name,
    'city'::text AS type,
    cgm.mailchimp_geo_id AS legacy_id,
    cgm.mailchimp_geo_id AS computed_geo_id,
    cgm.mailchimp_geo_id,
    NULL::integer AS iso_geo_id,
    cgm.country_code AS iso_alpha2,
        CASE
            WHEN ((cgm.country_code)::text = 'GB'::text) THEN 826
            WHEN ((cgm.country_code)::text = 'US'::text) THEN 840
            WHEN ((cgm.country_code)::text = 'CA'::text) THEN 124
            WHEN ((cgm.country_code)::text = 'AU'::text) THEN 36
            WHEN ((cgm.country_code)::text = 'DE'::text) THEN 276
            WHEN ((cgm.country_code)::text = 'FR'::text) THEN 250
            ELSE NULL::integer
        END AS iso_country_code,
    cgm.country_code,
    (
        CASE cgm.country_code
            WHEN 'GB'::text THEN 'United Kingdom'::character varying
            WHEN 'US'::text THEN 'United States'::character varying
            WHEN 'CA'::text THEN 'Canada'::character varying
            WHEN 'AU'::text THEN 'Australia'::character varying
            WHEN 'DE'::text THEN 'Germany'::character varying
            WHEN 'FR'::text THEN 'France'::character varying
            ELSE cgm.country_code
        END)::text AS country_name,
    cgm.region_name,
    cgm.city_name,
    cgm.population,
    cgm.confidence_level,
    cgm.importance_rank,
    cgm.venue_category,
    ( SELECT count(*) AS count
           FROM public.subscribers
          WHERE (subscribers.computed_geo_id = cgm.mailchimp_geo_id)) AS subscriber_count,
    cgm.geo_bounds_north AS bounds_north,
    cgm.geo_bounds_south AS bounds_south,
    cgm.geo_bounds_east AS bounds_east,
    cgm.geo_bounds_west AS bounds_west,
    ARRAY[lower((cgm.city_name)::text), lower((cgm.geo_location_name)::text), lower((cgm.country_code)::text), lower((cgm.region_name)::text)] AS search_terms,
        CASE cgm.country_code
            WHEN 'GB'::text THEN '🇬🇧'::text
            WHEN 'US'::text THEN '🇺🇸'::text
            WHEN 'CA'::text THEN '🇨🇦'::text
            WHEN 'AU'::text THEN '🇦🇺'::text
            WHEN 'DE'::text THEN '🇩🇪'::text
            WHEN 'FR'::text THEN '🇫🇷'::text
            ELSE '🌍'::text
        END AS flag_emoji,
    cgm.time_zone,
    'coordinate_mapping'::text AS data_source,
    now() AS last_updated
   FROM public.coordinate_geo_mapping cgm
UNION ALL
 SELECT 1 AS sort_order,
    ('country-'::text || (cgm3.country_code)::text) AS id,
    (cgm3.country_name || ' (Country)'::text) AS display_name,
    'country'::text AS type,
    NULL::integer AS legacy_id,
    NULL::integer AS computed_geo_id,
    NULL::integer AS mailchimp_geo_id,
    NULL::integer AS iso_geo_id,
    cgm3.country_code AS iso_alpha2,
    cgm3.iso_country_code,
    cgm3.country_code,
    cgm3.country_name,
    NULL::character varying AS region_name,
    NULL::character varying AS city_name,
    NULL::integer AS population,
    90 AS confidence_level,
    1 AS importance_rank,
    'country'::character varying AS venue_category,
    ( SELECT count(*) AS count
           FROM (public.subscribers s
             JOIN public.coordinate_geo_mapping cgm2 ON ((s.computed_geo_id = cgm2.mailchimp_geo_id)))
          WHERE ((cgm2.country_code)::text = (cgm3.country_code)::text)) AS subscriber_count,
    NULL::numeric AS bounds_north,
    NULL::numeric AS bounds_south,
    NULL::numeric AS bounds_east,
    NULL::numeric AS bounds_west,
    ARRAY[lower(cgm3.country_name), lower((cgm3.country_code)::text)] AS search_terms,
        CASE cgm3.country_code
            WHEN 'GB'::text THEN '🇬🇧'::text
            WHEN 'US'::text THEN '🇺🇸'::text
            WHEN 'CA'::text THEN '🇨🇦'::text
            WHEN 'AU'::text THEN '🇦🇺'::text
            WHEN 'DE'::text THEN '🇩🇪'::text
            WHEN 'FR'::text THEN '🇫🇷'::text
            ELSE '🌍'::text
        END AS flag_emoji,
    'Europe/London'::character varying AS time_zone,
    'country_aggregate'::text AS data_source,
    now() AS last_updated
   FROM ( SELECT DISTINCT coordinate_geo_mapping.country_code,
            (
                CASE coordinate_geo_mapping.country_code
                    WHEN 'GB'::text THEN 'United Kingdom'::character varying
                    WHEN 'US'::text THEN 'United States'::character varying
                    WHEN 'CA'::text THEN 'Canada'::character varying
                    WHEN 'AU'::text THEN 'Australia'::character varying
                    WHEN 'DE'::text THEN 'Germany'::character varying
                    WHEN 'FR'::text THEN 'France'::character varying
                    ELSE coordinate_geo_mapping.country_code
                END)::text AS country_name,
                CASE
                    WHEN ((coordinate_geo_mapping.country_code)::text = 'GB'::text) THEN 826
                    WHEN ((coordinate_geo_mapping.country_code)::text = 'US'::text) THEN 840
                    WHEN ((coordinate_geo_mapping.country_code)::text = 'CA'::text) THEN 124
                    WHEN ((coordinate_geo_mapping.country_code)::text = 'AU'::text) THEN 36
                    WHEN ((coordinate_geo_mapping.country_code)::text = 'DE'::text) THEN 276
                    WHEN ((coordinate_geo_mapping.country_code)::text = 'FR'::text) THEN 250
                    ELSE NULL::integer
                END AS iso_country_code
           FROM public.coordinate_geo_mapping
          WHERE (coordinate_geo_mapping.country_code IS NOT NULL)) cgm3
UNION ALL
 SELECT 2 AS sort_order,
    ((('region-'::text || lower(replace((cgm4.region_name)::text, ' '::text, '-'::text))) || '-'::text) || (cgm4.country_code)::text) AS id,
    ((((cgm4.region_name)::text || ', '::text) || (cgm4.country_code)::text) || ' (Region)'::text) AS display_name,
    'region'::text AS type,
    NULL::integer AS legacy_id,
    NULL::integer AS computed_geo_id,
    NULL::integer AS mailchimp_geo_id,
    NULL::integer AS iso_geo_id,
    cgm4.country_code AS iso_alpha2,
        CASE
            WHEN ((cgm4.country_code)::text = 'GB'::text) THEN 826
            ELSE NULL::integer
        END AS iso_country_code,
    cgm4.country_code,
    (
        CASE cgm4.country_code
            WHEN 'GB'::text THEN 'United Kingdom'::character varying
            ELSE cgm4.country_code
        END)::text AS country_name,
    cgm4.region_name,
    NULL::character varying AS city_name,
    NULL::integer AS population,
    85 AS confidence_level,
    2 AS importance_rank,
    'region'::character varying AS venue_category,
    ( SELECT count(*) AS count
           FROM (public.subscribers s
             JOIN public.coordinate_geo_mapping cgm2 ON ((s.computed_geo_id = cgm2.mailchimp_geo_id)))
          WHERE (((cgm2.region_name)::text = (cgm4.region_name)::text) AND ((cgm2.country_code)::text = (cgm4.country_code)::text))) AS subscriber_count,
    NULL::numeric AS bounds_north,
    NULL::numeric AS bounds_south,
    NULL::numeric AS bounds_east,
    NULL::numeric AS bounds_west,
    ARRAY[lower((cgm4.region_name)::text), lower((cgm4.country_code)::text)] AS search_terms,
        CASE cgm4.country_code
            WHEN 'GB'::text THEN '🇬🇧'::text
            ELSE '🌍'::text
        END AS flag_emoji,
    'Europe/London'::character varying AS time_zone,
    'region_aggregate'::text AS data_source,
    now() AS last_updated
   FROM ( SELECT DISTINCT coordinate_geo_mapping.region_name,
            coordinate_geo_mapping.country_code
           FROM public.coordinate_geo_mapping
          WHERE ((coordinate_geo_mapping.region_name IS NOT NULL) AND (coordinate_geo_mapping.country_code IS NOT NULL))) cgm4
  ORDER BY 1, 19 DESC NULLS LAST, 17, 3;


--
-- Name: v_geo_country_distribution; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_geo_country_distribution AS
 SELECT country_name,
    country_code,
    flag_emoji,
    count(*) AS city_count,
    sum(subscriber_count) AS total_subscribers,
    round(avg(subscriber_count), 0) AS avg_subscribers_per_city
   FROM public.v_unified_geo_locations
  WHERE (type = 'city'::text)
  GROUP BY country_name, country_code, flag_emoji
  ORDER BY (sum(subscriber_count)) DESC;


--
-- Name: v_growth_tracking; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_growth_tracking AS
 SELECT sync_date,
    total_subscribers,
    active_subscribers,
    new_subscribers_7d,
    new_subscribers_30d,
    round(((new_subscribers_30d)::numeric / 30.0), 1) AS avg_daily_signups,
    lag(total_subscribers) OVER (ORDER BY sync_date) AS prev_total,
    (total_subscribers - lag(total_subscribers) OVER (ORDER BY sync_date)) AS net_growth,
    vip_subscribers,
    synced_to_flodesk,
    (sync_success_rate || '%'::text) AS sync_rate
   FROM public.sync_analytics
  ORDER BY sync_date DESC;


--
-- Name: v_segment_count_health; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_segment_count_health AS
 SELECT s.id,
    s.name,
    s.local_subscriber_count,
    s.mailchimp_subscriber_count,
    s.count_discrepancy,
    s.count_sync_status,
    s.last_count_sync_at,
    s.last_updated,
    s.count_notes,
    ma.account_name,
        CASE
            WHEN (s.count_discrepancy IS NULL) THEN 'Not Synced'::text
            WHEN (abs(s.count_discrepancy) = 0) THEN '✅ Perfect Match'::text
            WHEN (abs(s.count_discrepancy) <= 10) THEN '⚠️ Minor Difference'::text
            WHEN (abs(s.count_discrepancy) <= 100) THEN '⚠️ Moderate Difference'::text
            ELSE '🚨 Major Difference'::text
        END AS health_status,
        CASE
            WHEN (s.last_count_sync_at IS NULL) THEN 'Never'::text
            WHEN (s.last_count_sync_at < (now() - '1 day'::interval)) THEN 'Stale (>1 day)'::text
            WHEN (s.last_count_sync_at < (now() - '01:00:00'::interval)) THEN 'Recent (<1 day)'::text
            ELSE 'Fresh (<1 hour)'::text
        END AS sync_freshness
   FROM (public.segments s
     LEFT JOIN public.mailchimp_accounts ma ON ((s.mailchimp_account_id = ma.id)))
  WHERE (s.is_active = true)
  ORDER BY (abs(s.count_discrepancy)) DESC NULLS LAST;


--
-- Name: v_sync_summary; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_sync_summary AS
 SELECT ss.mailchimp_account_id,
    ma.account_nickname,
    ma.account_name,
    ss.list_id,
    ss.type,
    ss.sync_direction,
    count(*) AS total_syncs,
    count(*) FILTER (WHERE (ss.flodesk_success = true)) AS successful_syncs,
    count(*) FILTER (WHERE (ss.flodesk_success = false)) AS failed_syncs,
    round(avg(ss.retry_count), 2) AS avg_retry_count,
    max(ss.processed_at) AS last_sync_time,
    min(ss.processed_at) AS first_sync_time
   FROM (public.synced_subscribers ss
     LEFT JOIN public.mailchimp_accounts ma ON ((ss.mailchimp_account_id = ma.id)))
  GROUP BY ss.mailchimp_account_id, ma.account_nickname, ma.account_name, ss.list_id, ss.type, ss.sync_direction
  ORDER BY ma.account_nickname, (max(ss.processed_at)) DESC;


--
-- Name: v_top_geo_locations; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_top_geo_locations AS
 SELECT display_name,
    type,
    country_name,
    subscriber_count,
    flag_emoji,
    mailchimp_geo_id,
    round((((subscriber_count)::numeric / NULLIF(( SELECT sum(v_unified_geo_locations_1.subscriber_count) AS sum
           FROM public.v_unified_geo_locations v_unified_geo_locations_1
          WHERE (v_unified_geo_locations_1.type = 'city'::text)), (0)::numeric)) * (100)::numeric), 2) AS percentage
   FROM public.v_unified_geo_locations
  WHERE ((type = 'city'::text) AND (subscriber_count > 0))
  ORDER BY subscriber_count DESC
 LIMIT 20;


--
-- Name: webhook_configurations; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.webhook_configurations (
    id integer NOT NULL,
    mailchimp_account_id integer NOT NULL,
    webhook_url text NOT NULL,
    webhook_secret text,
    webhook_id text,
    events text[] DEFAULT ARRAY['subscribe'::text, 'unsubscribe'::text, 'profile'::text, 'cleaned'::text, 'upemail'::text, 'campaign'::text],
    sources text[] DEFAULT ARRAY['user'::text, 'admin'::text, 'api'::text],
    is_active boolean DEFAULT true,
    ssl_verification boolean DEFAULT true,
    status text DEFAULT 'pending'::text,
    last_success_at timestamp with time zone,
    last_error_at timestamp with time zone,
    last_error_message text,
    success_count integer DEFAULT 0,
    error_count integer DEFAULT 0,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT valid_webhook_url CHECK ((webhook_url ~ '^https?://.*'::text)),
    CONSTRAINT webhook_configurations_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'active'::text, 'failed'::text, 'disabled'::text])))
);


--
-- Name: TABLE webhook_configurations; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.webhook_configurations IS 'Webhook endpoints configured for real-time updates from Mailchimp';


--
-- Name: webhook_configurations_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.webhook_configurations_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: webhook_configurations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.webhook_configurations_id_seq OWNED BY public.webhook_configurations.id;


--
-- Name: workflow_state; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.workflow_state (
    id integer NOT NULL,
    workflow_name character varying(255) DEFAULT 'mailchimp_segment_sync'::character varying,
    last_completed_batch integer DEFAULT 0,
    total_batches integer,
    started_at timestamp without time zone,
    last_updated timestamp without time zone DEFAULT now(),
    status character varying(50) DEFAULT 'running'::character varying
);


--
-- Name: workflow_state_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.workflow_state_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: workflow_state_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.workflow_state_id_seq OWNED BY public.workflow_state.id;


--
-- Name: workflow_sync_state; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.workflow_sync_state (
    workflow_name character varying(50) NOT NULL,
    next_offset integer DEFAULT 0,
    last_updated timestamp without time zone DEFAULT now()
);


--
-- Name: account_limits id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_limits ALTER COLUMN id SET DEFAULT nextval('public.account_limits_id_seq'::regclass);


--
-- Name: account_settings id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_settings ALTER COLUMN id SET DEFAULT nextval('public.account_settings_id_seq'::regclass);


--
-- Name: api_usage_tracking id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.api_usage_tracking ALTER COLUMN id SET DEFAULT nextval('public.api_usage_tracking_id_seq'::regclass);


--
-- Name: campaigns id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaigns ALTER COLUMN id SET DEFAULT nextval('public.campaigns_id_seq'::regclass);


--
-- Name: city_geo_mapping id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.city_geo_mapping ALTER COLUMN id SET DEFAULT nextval('public.city_geo_mapping_id_seq'::regclass);


--
-- Name: coordinate_geo_boundaries id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.coordinate_geo_boundaries ALTER COLUMN id SET DEFAULT nextval('public.coordinate_geo_boundaries_id_seq'::regclass);


--
-- Name: coordinate_geo_mapping id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.coordinate_geo_mapping ALTER COLUMN id SET DEFAULT nextval('public.coordinate_geo_mapping_id_seq'::regclass);


--
-- Name: country_geo_mapping id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.country_geo_mapping ALTER COLUMN id SET DEFAULT nextval('public.country_geo_mapping_id_seq'::regclass);


--
-- Name: flodesk_integrations id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.flodesk_integrations ALTER COLUMN id SET DEFAULT nextval('public.flodesk_integrations_id_seq'::regclass);


--
-- Name: geo_id_migration_map id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.geo_id_migration_map ALTER COLUMN id SET DEFAULT nextval('public.geo_id_migration_map_id_seq1'::regclass);


--
-- Name: geo_id_migration_map_backup_008 id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.geo_id_migration_map_backup_008 ALTER COLUMN id SET DEFAULT nextval('public.geo_id_migration_map_id_seq'::regclass);


--
-- Name: iso_coordinate_mapping id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.iso_coordinate_mapping ALTER COLUMN id SET DEFAULT nextval('public.iso_coordinate_mapping_id_seq'::regclass);


--
-- Name: mailchimp_accounts id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_accounts ALTER COLUMN id SET DEFAULT nextval('public.mailchimp_accounts_id_seq'::regclass);


--
-- Name: mailchimp_geo_mapping id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_geo_mapping ALTER COLUMN id SET DEFAULT nextval('public.mailchimp_geo_mapping_id_seq'::regclass);


--
-- Name: mailchimp_lists id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_lists ALTER COLUMN id SET DEFAULT nextval('public.mailchimp_lists_id_seq'::regclass);


--
-- Name: manual_ip_ranges id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.manual_ip_ranges ALTER COLUMN id SET DEFAULT nextval('public.manual_ip_ranges_id_seq'::regclass);


--
-- Name: schema_migrations id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.schema_migrations ALTER COLUMN id SET DEFAULT nextval('public.schema_migrations_id_seq'::regclass);


--
-- Name: segment_mappings id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_mappings ALTER COLUMN id SET DEFAULT nextval('public.segment_mappings_id_seq'::regclass);


--
-- Name: segment_rule_templates id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_rule_templates ALTER COLUMN id SET DEFAULT nextval('public.segment_rule_templates_id_seq'::regclass);


--
-- Name: segment_sync_status id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_sync_status ALTER COLUMN id SET DEFAULT nextval('public.segment_sync_status_id_seq'::regclass);


--
-- Name: segments id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segments ALTER COLUMN id SET DEFAULT nextval('public.segments_id_seq'::regclass);


--
-- Name: settings_audit_log id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.settings_audit_log ALTER COLUMN id SET DEFAULT nextval('public.settings_audit_log_id_seq'::regclass);


--
-- Name: subscriber_mailchimp_accounts id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_mailchimp_accounts ALTER COLUMN id SET DEFAULT nextval('public.subscriber_mailchimp_accounts_id_seq'::regclass);


--
-- Name: subscriber_segments id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_segments ALTER COLUMN id SET DEFAULT nextval('public.subscriber_segments_id_seq'::regclass);


--
-- Name: subscribers id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscribers ALTER COLUMN id SET DEFAULT nextval('public.subscribers_id_seq'::regclass);


--
-- Name: sync_activity id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_activity ALTER COLUMN id SET DEFAULT nextval('public.sync_activity_id_seq'::regclass);


--
-- Name: sync_analytics id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_analytics ALTER COLUMN id SET DEFAULT nextval('public.sync_analytics_id_seq'::regclass);


--
-- Name: sync_jobs id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_jobs ALTER COLUMN id SET DEFAULT nextval('public.sync_jobs_id_seq'::regclass);


--
-- Name: sync_schedules id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_schedules ALTER COLUMN id SET DEFAULT nextval('public.sync_schedules_id_seq'::regclass);


--
-- Name: synced_subscribers id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.synced_subscribers ALTER COLUMN id SET DEFAULT nextval('public.synced_subscribers_id_seq'::regclass);


--
-- Name: tags id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tags ALTER COLUMN id SET DEFAULT nextval('public.tags_id_seq'::regclass);


--
-- Name: webhook_configurations id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.webhook_configurations ALTER COLUMN id SET DEFAULT nextval('public.webhook_configurations_id_seq'::regclass);


--
-- Name: workflow_state id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.workflow_state ALTER COLUMN id SET DEFAULT nextval('public.workflow_state_id_seq'::regclass);


--
-- Name: account_limits account_limits_mailchimp_account_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_limits
    ADD CONSTRAINT account_limits_mailchimp_account_id_key UNIQUE (mailchimp_account_id);


--
-- Name: account_limits account_limits_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_limits
    ADD CONSTRAINT account_limits_pkey PRIMARY KEY (id);


--
-- Name: account_settings account_settings_mailchimp_account_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_settings
    ADD CONSTRAINT account_settings_mailchimp_account_id_key UNIQUE (mailchimp_account_id);


--
-- Name: account_settings account_settings_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_settings
    ADD CONSTRAINT account_settings_pkey PRIMARY KEY (id);


--
-- Name: api_usage_tracking api_usage_tracking_mailchimp_account_id_date_hour_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.api_usage_tracking
    ADD CONSTRAINT api_usage_tracking_mailchimp_account_id_date_hour_key UNIQUE (mailchimp_account_id, date_hour);


--
-- Name: api_usage_tracking api_usage_tracking_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.api_usage_tracking
    ADD CONSTRAINT api_usage_tracking_pkey PRIMARY KEY (id);


--
-- Name: batch_progress batch_progress_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.batch_progress
    ADD CONSTRAINT batch_progress_pkey PRIMARY KEY (batch_number);


--
-- Name: campaigns campaigns_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaigns
    ADD CONSTRAINT campaigns_pkey PRIMARY KEY (id);


--
-- Name: city_geo_mapping city_geo_mapping_city_name_country_code_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.city_geo_mapping
    ADD CONSTRAINT city_geo_mapping_city_name_country_code_key UNIQUE (city_name, country_code);


--
-- Name: city_geo_mapping city_geo_mapping_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.city_geo_mapping
    ADD CONSTRAINT city_geo_mapping_pkey PRIMARY KEY (id);


--
-- Name: coordinate_geo_boundaries coordinate_geo_boundaries_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.coordinate_geo_boundaries
    ADD CONSTRAINT coordinate_geo_boundaries_pkey PRIMARY KEY (id);


--
-- Name: coordinate_geo_mapping coordinate_geo_mapping_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.coordinate_geo_mapping
    ADD CONSTRAINT coordinate_geo_mapping_pkey PRIMARY KEY (id);


--
-- Name: country_geo_fallback country_geo_fallback_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.country_geo_fallback
    ADD CONSTRAINT country_geo_fallback_pkey PRIMARY KEY (country_code);


--
-- Name: country_geo_mapping country_geo_mapping_country_code_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.country_geo_mapping
    ADD CONSTRAINT country_geo_mapping_country_code_key UNIQUE (country_code);


--
-- Name: country_geo_mapping country_geo_mapping_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.country_geo_mapping
    ADD CONSTRAINT country_geo_mapping_pkey PRIMARY KEY (id);


--
-- Name: flodesk_integrations flodesk_integrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.flodesk_integrations
    ADD CONSTRAINT flodesk_integrations_pkey PRIMARY KEY (id);


--
-- Name: geo_id_migration_map_backup_008 geo_id_migration_map_mailchimp_geo_id_iso_geo_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.geo_id_migration_map_backup_008
    ADD CONSTRAINT geo_id_migration_map_mailchimp_geo_id_iso_geo_id_key UNIQUE (mailchimp_geo_id, iso_geo_id);


--
-- Name: geo_id_migration_map_backup_008 geo_id_migration_map_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.geo_id_migration_map_backup_008
    ADD CONSTRAINT geo_id_migration_map_pkey PRIMARY KEY (id);


--
-- Name: geo_id_migration_map geo_id_migration_map_pkey1; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.geo_id_migration_map
    ADD CONSTRAINT geo_id_migration_map_pkey1 PRIMARY KEY (id);


--
-- Name: ip_geo_cache ip_geo_cache_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.ip_geo_cache
    ADD CONSTRAINT ip_geo_cache_pkey PRIMARY KEY (ip_address);


--
-- Name: iso_coordinate_mapping iso_coordinate_mapping_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.iso_coordinate_mapping
    ADD CONSTRAINT iso_coordinate_mapping_pkey PRIMARY KEY (id);


--
-- Name: iso_geo_mapping iso_geo_mapping_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.iso_geo_mapping
    ADD CONSTRAINT iso_geo_mapping_pkey PRIMARY KEY (iso_geo_id);


--
-- Name: mailchimp_accounts mailchimp_accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_accounts
    ADD CONSTRAINT mailchimp_accounts_pkey PRIMARY KEY (id);


--
-- Name: mailchimp_geo_mapping mailchimp_geo_mapping_mailchimp_geo_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_geo_mapping
    ADD CONSTRAINT mailchimp_geo_mapping_mailchimp_geo_id_key UNIQUE (mailchimp_geo_id);


--
-- Name: mailchimp_geo_mapping mailchimp_geo_mapping_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_geo_mapping
    ADD CONSTRAINT mailchimp_geo_mapping_pkey PRIMARY KEY (id);


--
-- Name: mailchimp_lists mailchimp_lists_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_lists
    ADD CONSTRAINT mailchimp_lists_pkey PRIMARY KEY (id);


--
-- Name: manual_ip_ranges manual_ip_ranges_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.manual_ip_ranges
    ADD CONSTRAINT manual_ip_ranges_pkey PRIMARY KEY (id);


--
-- Name: schema_migrations schema_migrations_migration_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.schema_migrations
    ADD CONSTRAINT schema_migrations_migration_name_key UNIQUE (migration_name);


--
-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.schema_migrations
    ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (id);


--
-- Name: segment_mappings segment_mappings_mailchimp_segment_id_flodesk_segment_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_mappings
    ADD CONSTRAINT segment_mappings_mailchimp_segment_id_flodesk_segment_id_key UNIQUE (mailchimp_segment_id, flodesk_segment_id);


--
-- Name: segment_mappings segment_mappings_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_mappings
    ADD CONSTRAINT segment_mappings_pkey PRIMARY KEY (id);


--
-- Name: segment_rule_templates segment_rule_templates_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_rule_templates
    ADD CONSTRAINT segment_rule_templates_pkey PRIMARY KEY (id);


--
-- Name: segment_sync_status segment_sync_status_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_sync_status
    ADD CONSTRAINT segment_sync_status_pkey PRIMARY KEY (id);


--
-- Name: segments segments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segments
    ADD CONSTRAINT segments_pkey PRIMARY KEY (id);


--
-- Name: settings_audit_log settings_audit_log_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.settings_audit_log
    ADD CONSTRAINT settings_audit_log_pkey PRIMARY KEY (id);


--
-- Name: subscriber_mailchimp_accounts subscriber_mailchimp_accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_mailchimp_accounts
    ADD CONSTRAINT subscriber_mailchimp_accounts_pkey PRIMARY KEY (id);


--
-- Name: subscriber_segments subscriber_segments_email_mailchimp_segment_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_segments
    ADD CONSTRAINT subscriber_segments_email_mailchimp_segment_id_key UNIQUE (email, mailchimp_segment_id);


--
-- Name: subscriber_segments subscriber_segments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_segments
    ADD CONSTRAINT subscriber_segments_pkey PRIMARY KEY (id);


--
-- Name: subscribers subscribers_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscribers
    ADD CONSTRAINT subscribers_pkey PRIMARY KEY (id);


--
-- Name: sync_activity sync_activity_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_activity
    ADD CONSTRAINT sync_activity_pkey PRIMARY KEY (id);


--
-- Name: sync_analytics sync_analytics_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_analytics
    ADD CONSTRAINT sync_analytics_pkey PRIMARY KEY (id);


--
-- Name: sync_jobs sync_jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_jobs
    ADD CONSTRAINT sync_jobs_pkey PRIMARY KEY (id);


--
-- Name: sync_schedules sync_schedules_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_schedules
    ADD CONSTRAINT sync_schedules_pkey PRIMARY KEY (id);


--
-- Name: sync_state sync_state_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_state
    ADD CONSTRAINT sync_state_pkey PRIMARY KEY (workflow_instance);


--
-- Name: synced_subscribers synced_subscribers_email_workflow_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.synced_subscribers
    ADD CONSTRAINT synced_subscribers_email_workflow_key UNIQUE (email, workflow_instance);


--
-- Name: synced_subscribers synced_subscribers_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.synced_subscribers
    ADD CONSTRAINT synced_subscribers_pkey PRIMARY KEY (email, workflow_instance);


--
-- Name: tags tags_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tags
    ADD CONSTRAINT tags_pkey PRIMARY KEY (id);


--
-- Name: workflow_state uk_workflow_state_name; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.workflow_state
    ADD CONSTRAINT uk_workflow_state_name UNIQUE (workflow_name);


--
-- Name: flodesk_integrations unique_account_flodesk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.flodesk_integrations
    ADD CONSTRAINT unique_account_flodesk UNIQUE (mailchimp_account_id);


--
-- Name: mailchimp_lists unique_account_list; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_lists
    ADD CONSTRAINT unique_account_list UNIQUE (mailchimp_account_id, list_id);


--
-- Name: mailchimp_accounts unique_account_nickname; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_accounts
    ADD CONSTRAINT unique_account_nickname UNIQUE (account_nickname);


--
-- Name: tags unique_account_tag; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tags
    ADD CONSTRAINT unique_account_tag UNIQUE (mailchimp_account_id, name);


--
-- Name: mailchimp_accounts unique_api_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_accounts
    ADD CONSTRAINT unique_api_key UNIQUE (api_key);


--
-- Name: subscribers unique_email; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscribers
    ADD CONSTRAINT unique_email UNIQUE (email);


--
-- Name: segments unique_mailchimp_segment_per_account; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segments
    ADD CONSTRAINT unique_mailchimp_segment_per_account UNIQUE (mailchimp_segment_id, mailchimp_account_id);


--
-- Name: subscriber_mailchimp_accounts unique_subscriber_account_list; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_mailchimp_accounts
    ADD CONSTRAINT unique_subscriber_account_list UNIQUE (subscriber_id, mailchimp_account_id, mailchimp_list_id);


--
-- Name: sync_analytics unique_sync_date_workflow; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_analytics
    ADD CONSTRAINT unique_sync_date_workflow UNIQUE (sync_date, workflow_instance);


--
-- Name: webhook_configurations webhook_configurations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.webhook_configurations
    ADD CONSTRAINT webhook_configurations_pkey PRIMARY KEY (id);


--
-- Name: workflow_state workflow_state_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.workflow_state
    ADD CONSTRAINT workflow_state_pkey PRIMARY KEY (id);


--
-- Name: workflow_sync_state workflow_sync_state_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.workflow_sync_state
    ADD CONSTRAINT workflow_sync_state_pkey PRIMARY KEY (workflow_name);


--
-- Name: idx_account_limits_mailchimp_account_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_account_limits_mailchimp_account_id ON public.account_limits USING btree (mailchimp_account_id);


--
-- Name: idx_account_settings_mailchimp_account_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_account_settings_mailchimp_account_id ON public.account_settings USING btree (mailchimp_account_id);


--
-- Name: idx_api_usage_tracking_account_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_api_usage_tracking_account_date ON public.api_usage_tracking USING btree (mailchimp_account_id, date_hour);


--
-- Name: idx_batch_progress_batch_number; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_batch_progress_batch_number ON public.batch_progress USING btree (batch_number);


--
-- Name: idx_batch_progress_segment; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_batch_progress_segment ON public.batch_progress USING btree (segment_id, segment_name);


--
-- Name: idx_city_geo_mapping_city; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_city_geo_mapping_city ON public.city_geo_mapping USING btree (city_name);


--
-- Name: idx_city_geo_mapping_country; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_city_geo_mapping_country ON public.city_geo_mapping USING btree (country_code);


--
-- Name: idx_city_geo_mapping_major; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_city_geo_mapping_major ON public.city_geo_mapping USING btree (is_major_city) WHERE (is_major_city = true);


--
-- Name: idx_coord_geo_bounds; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coord_geo_bounds ON public.coordinate_geo_mapping USING btree (geo_bounds_north, geo_bounds_south, geo_bounds_east, geo_bounds_west);


--
-- Name: idx_coord_geo_country; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coord_geo_country ON public.coordinate_geo_mapping USING btree (country_code, importance_rank);


--
-- Name: idx_coord_geo_importance; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coord_geo_importance ON public.coordinate_geo_mapping USING btree (importance_rank, confidence_level DESC);


--
-- Name: idx_coord_geo_mailchimp_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coord_geo_mailchimp_id ON public.coordinate_geo_mapping USING btree (mailchimp_geo_id);


--
-- Name: idx_coord_geo_population; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coord_geo_population ON public.coordinate_geo_mapping USING btree (metro_area_population DESC, population DESC);


--
-- Name: idx_coord_geo_venue; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coord_geo_venue ON public.coordinate_geo_mapping USING btree (venue_category, importance_rank);


--
-- Name: idx_coordinate_bounds_lookup; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coordinate_bounds_lookup ON public.coordinate_geo_mapping USING btree (geo_bounds_north, geo_bounds_south, geo_bounds_east, geo_bounds_west);


--
-- Name: idx_coordinate_geo_bounds; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coordinate_geo_bounds ON public.coordinate_geo_boundaries USING gist (geo_bounds);


--
-- Name: idx_coordinate_geo_mapping_bounds; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coordinate_geo_mapping_bounds ON public.coordinate_geo_mapping USING btree (geo_bounds_south, geo_bounds_north, geo_bounds_west, geo_bounds_east);


--
-- Name: idx_coordinate_geo_mapping_country_code; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coordinate_geo_mapping_country_code ON public.coordinate_geo_mapping USING btree (country_code);


--
-- Name: idx_coordinate_geo_mapping_mailchimp_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coordinate_geo_mapping_mailchimp_id ON public.coordinate_geo_mapping USING btree (mailchimp_geo_id);


--
-- Name: idx_coordinate_geo_mapping_priority; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_coordinate_geo_mapping_priority ON public.coordinate_geo_mapping USING btree (confidence_level DESC, importance_rank);


--
-- Name: idx_flodesk_integrations_active; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_flodesk_integrations_active ON public.flodesk_integrations USING btree (is_active);


--
-- Name: idx_flodesk_segment_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_flodesk_segment_id ON public.subscriber_segments USING btree (flodesk_segment_id);


--
-- Name: idx_ip_geo_cache_lookup; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ip_geo_cache_lookup ON public.ip_geo_cache USING btree (ip_address, lookup_count DESC);


--
-- Name: idx_iso_coord_country; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_iso_coord_country ON public.iso_coordinate_mapping USING btree (iso_country_code);


--
-- Name: idx_iso_coord_geo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_iso_coord_geo_id ON public.iso_coordinate_mapping USING btree (iso_geo_id);


--
-- Name: idx_iso_geo_mapping_city; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_iso_geo_mapping_city ON public.iso_geo_mapping USING btree (city_name, country_code);


--
-- Name: idx_iso_geo_mapping_country; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_iso_geo_mapping_country ON public.iso_geo_mapping USING btree (iso_country_code);


--
-- Name: idx_iso_geo_mapping_level; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_iso_geo_mapping_level ON public.iso_geo_mapping USING btree (is_country_level);


--
-- Name: idx_iso_geo_mapping_location; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_iso_geo_mapping_location ON public.iso_geo_mapping USING btree (geo_location_name);


--
-- Name: idx_mailchimp_accounts_connection_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_mailchimp_accounts_connection_status ON public.mailchimp_accounts USING btree (connection_status);


--
-- Name: idx_mailchimp_accounts_is_active; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_mailchimp_accounts_is_active ON public.mailchimp_accounts USING btree (is_active);


--
-- Name: idx_mailchimp_accounts_last_test_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_mailchimp_accounts_last_test_at ON public.mailchimp_accounts USING btree (last_test_at);


--
-- Name: idx_mailchimp_accounts_sync_enabled; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_mailchimp_accounts_sync_enabled ON public.mailchimp_accounts USING btree (sync_enabled);


--
-- Name: idx_mailchimp_lists_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_mailchimp_lists_account ON public.mailchimp_lists USING btree (mailchimp_account_id);


--
-- Name: idx_mailchimp_lists_sync_enabled; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_mailchimp_lists_sync_enabled ON public.mailchimp_lists USING btree (sync_enabled);


--
-- Name: idx_migration_iso_geo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_migration_iso_geo_id ON public.geo_id_migration_map USING btree (new_iso_geo_id);


--
-- Name: idx_migration_old_geo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX idx_migration_old_geo_id ON public.geo_id_migration_map USING btree (old_geo_id);


--
-- Name: idx_migration_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_migration_status ON public.geo_id_migration_map USING btree (migration_status);


--
-- Name: idx_segment_mappings_mc_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segment_mappings_mc_id ON public.segment_mappings USING btree (mailchimp_segment_id);


--
-- Name: idx_segment_sync_created_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segment_sync_created_at ON public.segment_sync_status USING btree (created_at DESC);


--
-- Name: idx_segment_sync_provider; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segment_sync_provider ON public.segment_sync_status USING btree (provider);


--
-- Name: idx_segment_sync_segment_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segment_sync_segment_id ON public.segment_sync_status USING btree (segment_id);


--
-- Name: idx_segment_sync_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segment_sync_status ON public.segment_sync_status USING btree (status);


--
-- Name: idx_segments_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_account ON public.segments USING btree (mailchimp_account_id);


--
-- Name: idx_segments_account_active; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_account_active ON public.segments USING btree (mailchimp_account_id, is_active);


--
-- Name: idx_segments_auto_update; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_auto_update ON public.segments USING btree (auto_update);


--
-- Name: idx_segments_count_discrepancy; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_count_discrepancy ON public.segments USING btree (count_discrepancy) WHERE (count_discrepancy IS NOT NULL);


--
-- Name: idx_segments_count_sync_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_count_sync_status ON public.segments USING btree (count_sync_status);


--
-- Name: idx_segments_created_in; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_created_in ON public.segments USING btree (created_in_system);


--
-- Name: idx_segments_flodesk_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_flodesk_id ON public.segments USING btree (flodesk_segment_id);


--
-- Name: idx_segments_iso_ready; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_iso_ready ON public.segments USING btree (iso_ready);


--
-- Name: idx_segments_last_count_sync; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_last_count_sync ON public.segments USING btree (last_count_sync_at DESC);


--
-- Name: idx_segments_last_sync_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_last_sync_date ON public.segments USING btree (last_sync_date DESC);


--
-- Name: idx_segments_migration_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_migration_status ON public.segments USING btree (migration_status);


--
-- Name: idx_segments_performance; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_performance ON public.segments USING gin (performance_metrics);


--
-- Name: idx_segments_rules; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_rules ON public.segments USING gin (segment_rules);


--
-- Name: idx_segments_scope; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_scope ON public.segments USING btree (scope);


--
-- Name: idx_segments_scope_active; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_scope_active ON public.segments USING btree (scope, is_active);


--
-- Name: idx_segments_source; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_source ON public.segments USING btree (segment_source);


--
-- Name: idx_segments_sync_frequency; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_sync_frequency ON public.segments USING btree (sync_frequency);


--
-- Name: idx_segments_sync_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_sync_status ON public.segments USING btree (sync_status);


--
-- Name: idx_segments_type; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_type ON public.segments USING btree (segment_type);


--
-- Name: idx_segments_updated_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_segments_updated_at ON public.segments USING btree (updated_at DESC);


--
-- Name: idx_settings_audit_log_account_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_settings_audit_log_account_id ON public.settings_audit_log USING btree (mailchimp_account_id);


--
-- Name: idx_settings_audit_log_created_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_settings_audit_log_created_at ON public.settings_audit_log USING btree (created_at);


--
-- Name: idx_subscriber_mailchimp_accounts_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_accounts_account ON public.subscriber_mailchimp_accounts USING btree (mailchimp_account_id);


--
-- Name: idx_subscriber_mailchimp_accounts_list; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_accounts_list ON public.subscriber_mailchimp_accounts USING btree (mailchimp_list_id);


--
-- Name: idx_subscriber_mailchimp_accounts_member_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_accounts_member_id ON public.subscriber_mailchimp_accounts USING btree (mailchimp_member_id);


--
-- Name: idx_subscriber_mailchimp_accounts_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_accounts_status ON public.subscriber_mailchimp_accounts USING btree (status);


--
-- Name: idx_subscriber_mailchimp_accounts_subscriber; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_accounts_subscriber ON public.subscriber_mailchimp_accounts USING btree (subscriber_id);


--
-- Name: idx_subscriber_mailchimp_composite; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_composite ON public.subscriber_mailchimp_accounts USING btree (mailchimp_account_id, status, last_synced_at);


--
-- Name: idx_subscriber_mailchimp_list; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_list ON public.subscriber_mailchimp_accounts USING btree (mailchimp_list_id);


--
-- Name: idx_subscriber_mailchimp_subscriber; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_mailchimp_subscriber ON public.subscriber_mailchimp_accounts USING btree (subscriber_id);


--
-- Name: idx_subscriber_segments_email; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_email ON public.subscriber_segments USING btree (email);


--
-- Name: idx_subscriber_segments_email_segment; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_email_segment ON public.subscriber_segments USING btree (email, mailchimp_segment_id);


--
-- Name: idx_subscriber_segments_flodesk_sync; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_flodesk_sync ON public.subscriber_segments USING btree (segment_id, synced_to_flodesk);


--
-- Name: idx_subscriber_segments_hash; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_hash ON public.subscriber_segments USING btree (subscriber_hash);


--
-- Name: idx_subscriber_segments_segment; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_segment ON public.subscriber_segments USING btree (segment_id);


--
-- Name: idx_subscriber_segments_segment_active; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_segment_active ON public.subscriber_segments USING btree (segment_id, is_active);


--
-- Name: idx_subscriber_segments_segment_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_segment_id ON public.subscriber_segments USING btree (mailchimp_segment_id);


--
-- Name: idx_subscriber_segments_source_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_source_account ON public.subscriber_segments USING btree (source_mailchimp_account_id);


--
-- Name: idx_subscriber_segments_subscriber; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_subscriber ON public.subscriber_segments USING btree (subscriber_id);


--
-- Name: idx_subscriber_segments_sync_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscriber_segments_sync_status ON public.subscriber_segments USING btree (synced_to_mailchimp, synced_to_flodesk);


--
-- Name: idx_subscribers_account_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_account_status ON public.subscribers USING gin (account_statuses);


--
-- Name: idx_subscribers_city_country; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_city_country ON public.subscribers USING btree (city, country) WHERE ((city IS NOT NULL) AND (country IS NOT NULL));


--
-- Name: idx_subscribers_computed_geo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_computed_geo_id ON public.subscribers USING btree (computed_geo_id);


--
-- Name: idx_subscribers_coordinates; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_coordinates ON public.subscribers USING btree (latitude, longitude) WHERE ((latitude IS NOT NULL) AND (longitude IS NOT NULL));


--
-- Name: idx_subscribers_country_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_country_account ON public.subscribers USING btree (country_code, primary_mailchimp_account_id);


--
-- Name: idx_subscribers_country_code; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_country_code ON public.subscribers USING btree (country_code);


--
-- Name: idx_subscribers_dual_geo; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_dual_geo ON public.subscribers USING btree (computed_geo_id, iso_geo_id) WHERE ((computed_geo_id IS NOT NULL) OR (iso_geo_id IS NOT NULL));


--
-- Name: idx_subscribers_email; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_email ON public.subscribers USING btree (email);


--
-- Name: idx_subscribers_engagement; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_engagement ON public.subscribers USING btree (total_opens, total_clicks);


--
-- Name: idx_subscribers_geo_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_geo_account ON public.subscribers USING btree (computed_geo_id, primary_mailchimp_account_id);


--
-- Name: idx_subscribers_geo_location; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_geo_location ON public.subscribers USING btree (computed_geo_location) WHERE (computed_geo_location IS NOT NULL);


--
-- Name: idx_subscribers_geo_lookup; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_geo_lookup ON public.subscribers USING btree (computed_geo_id, geo_confidence_score) WHERE (computed_geo_id > 0);


--
-- Name: idx_subscribers_geo_processing; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_geo_processing ON public.subscribers USING btree (computed_geo_id, geo_updated_at) WHERE (computed_geo_id = 0);


--
-- Name: idx_subscribers_geo_processing_priority; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_geo_processing_priority ON public.subscribers USING btree (computed_geo_id, geo_updated_at NULLS FIRST) WHERE ((computed_geo_id = 0) AND ((ip_opt IS NOT NULL) OR (ip_signup IS NOT NULL)));


--
-- Name: idx_subscribers_geo_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_geo_status ON public.subscribers USING btree (geo_status);


--
-- Name: idx_subscribers_geo_status_processing; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_geo_status_processing ON public.subscribers USING btree (geo_status, computed_geo_id, geo_updated_at);


--
-- Name: idx_subscribers_hash; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_hash ON public.subscribers USING btree (subscriber_hash);


--
-- Name: idx_subscribers_ip_processing; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_ip_processing ON public.subscribers USING btree (ip_signup, ip_opt) WHERE (computed_geo_id = 0);


--
-- Name: idx_subscribers_iso_country; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_iso_country ON public.subscribers USING btree (iso_country_code) WHERE (iso_country_code IS NOT NULL);


--
-- Name: idx_subscribers_iso_geo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_iso_geo_id ON public.subscribers USING btree (iso_geo_id) WHERE (iso_geo_id IS NOT NULL);


--
-- Name: idx_subscribers_iso_updated; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_iso_updated ON public.subscribers USING btree (iso_updated_at) WHERE (iso_updated_at IS NOT NULL);


--
-- Name: idx_subscribers_last_activity; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_last_activity ON public.subscribers USING btree (last_open_date, last_click_date);


--
-- Name: idx_subscribers_location; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_location ON public.subscribers USING btree (country_code, timezone);


--
-- Name: idx_subscribers_mailchimp_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_mailchimp_id ON public.subscribers USING btree (mailchimp_member_id);


--
-- Name: idx_subscribers_member_rating; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_member_rating ON public.subscribers USING btree (member_rating);


--
-- Name: idx_subscribers_migration_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_migration_status ON public.subscribers USING btree (migration_status);


--
-- Name: idx_subscribers_primary_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_primary_account ON public.subscribers USING btree (primary_mailchimp_account_id);


--
-- Name: idx_subscribers_signup_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_signup_date ON public.subscribers USING btree (signup_date);


--
-- Name: idx_subscribers_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_status ON public.subscribers USING btree (status);


--
-- Name: idx_subscribers_status_engagement; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_status_engagement ON public.subscribers USING btree (avg_open_rate, avg_click_rate);


--
-- Name: idx_subscribers_tags; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_tags ON public.subscribers USING btree (tags) WHERE (tags IS NOT NULL);


--
-- Name: idx_subscribers_vip; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_subscribers_vip ON public.subscribers USING btree (vip);


--
-- Name: idx_sync_activity_action; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_activity_action ON public.sync_activity USING btree (action);


--
-- Name: idx_sync_activity_created_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_activity_created_at ON public.sync_activity USING btree (created_at);


--
-- Name: idx_sync_activity_email; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_activity_email ON public.sync_activity USING btree (email);


--
-- Name: idx_sync_analytics_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_analytics_date ON public.sync_analytics USING btree (sync_date DESC);


--
-- Name: idx_sync_analytics_engagement; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_analytics_engagement ON public.sync_analytics USING btree (overall_open_rate DESC, overall_click_rate DESC);


--
-- Name: idx_sync_analytics_workflow; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_analytics_workflow ON public.sync_analytics USING btree (workflow_instance, sync_date DESC);


--
-- Name: idx_sync_jobs_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_jobs_account ON public.sync_jobs USING btree (mailchimp_account_id);


--
-- Name: idx_sync_jobs_created_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_jobs_created_at ON public.sync_jobs USING btree (created_at);


--
-- Name: idx_sync_jobs_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_jobs_status ON public.sync_jobs USING btree (status);


--
-- Name: idx_sync_jobs_type; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_jobs_type ON public.sync_jobs USING btree (job_type);


--
-- Name: idx_sync_schedules_mailchimp_account_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_schedules_mailchimp_account_id ON public.sync_schedules USING btree (mailchimp_account_id);


--
-- Name: idx_sync_schedules_next_run; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sync_schedules_next_run ON public.sync_schedules USING btree (next_run_at) WHERE (is_active = true);


--
-- Name: idx_synced_subscribers_account_list; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_account_list ON public.synced_subscribers USING btree (mailchimp_account_id, list_id);


--
-- Name: idx_synced_subscribers_account_type; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_account_type ON public.synced_subscribers USING btree (mailchimp_account_id, type);


--
-- Name: idx_synced_subscribers_email; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_email ON public.synced_subscribers USING btree (email);


--
-- Name: idx_synced_subscribers_offset; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_offset ON public.synced_subscribers USING btree (current_offset);


--
-- Name: idx_synced_subscribers_processed_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_processed_at ON public.synced_subscribers USING btree (processed_at);


--
-- Name: idx_synced_subscribers_processed_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_processed_date ON public.synced_subscribers USING btree (processed_at DESC);


--
-- Name: idx_synced_subscribers_status_retry; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_status_retry ON public.synced_subscribers USING btree (flodesk_success, retry_count) WHERE (flodesk_success = false);


--
-- Name: idx_synced_subscribers_workflow; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_synced_subscribers_workflow ON public.synced_subscribers USING btree (workflow_instance);


--
-- Name: idx_tags_account; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_tags_account ON public.tags USING btree (mailchimp_account_id);


--
-- Name: idx_tags_name; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_tags_name ON public.tags USING btree (name);


--
-- Name: idx_unique_active_sync; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX idx_unique_active_sync ON public.segment_sync_status USING btree (segment_id, provider) WHERE ((status)::text = ANY ((ARRAY['started'::character varying, 'in_progress'::character varying])::text[]));


--
-- Name: idx_webhook_configurations_mailchimp_account_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_webhook_configurations_mailchimp_account_id ON public.webhook_configurations USING btree (mailchimp_account_id);


--
-- Name: idx_webhook_configurations_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_webhook_configurations_status ON public.webhook_configurations USING btree (status);


--
-- Name: idx_workflow_state_name; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_workflow_state_name ON public.workflow_state USING btree (workflow_name);


--
-- Name: account_limits trigger_account_limits_updated_at; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trigger_account_limits_updated_at BEFORE UPDATE ON public.account_limits FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: account_settings trigger_account_settings_updated_at; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trigger_account_settings_updated_at BEFORE UPDATE ON public.account_settings FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: segments trigger_calculate_count_discrepancy; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trigger_calculate_count_discrepancy BEFORE UPDATE OF local_subscriber_count, mailchimp_subscriber_count ON public.segments FOR EACH ROW EXECUTE FUNCTION public.calculate_count_discrepancy();


--
-- Name: mailchimp_accounts trigger_create_default_settings; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trigger_create_default_settings AFTER INSERT ON public.mailchimp_accounts FOR EACH ROW EXECUTE FUNCTION public.create_default_account_settings();


--
-- Name: sync_schedules trigger_sync_schedules_updated_at; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trigger_sync_schedules_updated_at BEFORE UPDATE ON public.sync_schedules FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: webhook_configurations trigger_webhook_configurations_updated_at; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trigger_webhook_configurations_updated_at BEFORE UPDATE ON public.webhook_configurations FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: account_limits account_limits_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_limits
    ADD CONSTRAINT account_limits_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: account_settings account_settings_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.account_settings
    ADD CONSTRAINT account_settings_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: api_usage_tracking api_usage_tracking_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.api_usage_tracking
    ADD CONSTRAINT api_usage_tracking_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: synced_subscribers fk_synced_subscribers_mailchimp_account; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.synced_subscribers
    ADD CONSTRAINT fk_synced_subscribers_mailchimp_account FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: flodesk_integrations flodesk_integrations_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.flodesk_integrations
    ADD CONSTRAINT flodesk_integrations_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: mailchimp_lists mailchimp_lists_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mailchimp_lists
    ADD CONSTRAINT mailchimp_lists_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: segment_sync_status segment_sync_status_segment_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segment_sync_status
    ADD CONSTRAINT segment_sync_status_segment_id_fkey FOREIGN KEY (segment_id) REFERENCES public.segments(id) ON DELETE CASCADE;


--
-- Name: segments segments_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.segments
    ADD CONSTRAINT segments_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: settings_audit_log settings_audit_log_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.settings_audit_log
    ADD CONSTRAINT settings_audit_log_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: subscriber_mailchimp_accounts subscriber_mailchimp_accounts_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_mailchimp_accounts
    ADD CONSTRAINT subscriber_mailchimp_accounts_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: subscriber_mailchimp_accounts subscriber_mailchimp_accounts_mailchimp_list_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_mailchimp_accounts
    ADD CONSTRAINT subscriber_mailchimp_accounts_mailchimp_list_id_fkey FOREIGN KEY (mailchimp_list_id) REFERENCES public.mailchimp_lists(id) ON DELETE CASCADE;


--
-- Name: subscriber_mailchimp_accounts subscriber_mailchimp_accounts_subscriber_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscriber_mailchimp_accounts
    ADD CONSTRAINT subscriber_mailchimp_accounts_subscriber_id_fkey FOREIGN KEY (subscriber_id) REFERENCES public.subscribers(id) ON DELETE CASCADE;


--
-- Name: subscribers subscribers_primary_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.subscribers
    ADD CONSTRAINT subscribers_primary_mailchimp_account_id_fkey FOREIGN KEY (primary_mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE SET NULL;


--
-- Name: sync_jobs sync_jobs_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_jobs
    ADD CONSTRAINT sync_jobs_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: sync_jobs sync_jobs_segment_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_jobs
    ADD CONSTRAINT sync_jobs_segment_id_fkey FOREIGN KEY (segment_id) REFERENCES public.segments(id) ON DELETE CASCADE;


--
-- Name: sync_schedules sync_schedules_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sync_schedules
    ADD CONSTRAINT sync_schedules_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: tags tags_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tags
    ADD CONSTRAINT tags_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
-- Name: webhook_configurations webhook_configurations_mailchimp_account_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.webhook_configurations
    ADD CONSTRAINT webhook_configurations_mailchimp_account_id_fkey FOREIGN KEY (mailchimp_account_id) REFERENCES public.mailchimp_accounts(id) ON DELETE CASCADE;


--
--

