-- 1. Enable PostGIS if possible (attempt only)
DO $$ BEGIN
    CREATE EXTENSION IF NOT EXISTS postgis SCHEMA extensions;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'PostGIS could not be enabled';
END $$;

-- 2. Helper Functions for Currency
CREATE OR REPLACE FUNCTION fn_get_exchange_rate(target_currency TEXT) 
RETURNS DECIMAL AS $$
DECLARE
    rate DECIMAL;
BEGIN
    SELECT usd_to_ars INTO rate FROM exchange_rates ORDER BY date DESC LIMIT 1;
    IF rate IS NULL THEN RETURN 1.0; END IF;
    IF target_currency = 'ARS' THEN RETURN rate;
    ELSIF target_currency = 'USD' THEN RETURN 1.0 / rate;
    ELSE RETURN 1.0; END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION fn_convert_price(amount DECIMAL, from_curr TEXT, to_curr TEXT) 
RETURNS DECIMAL AS $$
DECLARE
    rate DECIMAL;
BEGIN
    IF amount IS NULL THEN RETURN 0; END IF;
    IF from_curr = to_curr THEN RETURN amount; END IF;
    SELECT usd_to_ars INTO rate FROM exchange_rates ORDER BY date DESC LIMIT 1;
    IF rate IS NULL THEN RETURN amount; END IF;
    IF from_curr = 'USD' AND to_curr = 'ARS' THEN RETURN amount * rate;
    ELSIF from_curr = 'ARS' AND to_curr = 'USD' THEN RETURN amount / rate;
    ELSE RETURN amount; END IF;
END;
$$ LANGUAGE plpgsql;

-- 3. Search Zones Table
CREATE TABLE search_zones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    search_id TEXT NOT NULL REFERENCES searches(id) ON DELETE CASCADE,
    raw_coordinates JSONB NOT NULL, 
    created_at TIMESTAMPTZ DEFAULT NOW(),
    geometry extensions.geometry(Polygon, 4326)
);

CREATE INDEX idx_search_zones_geometry ON search_zones USING GIST (geometry);

-- Remove dynamic alteration block
-- DO $$ ... END $$;

-- 4. Matching Logic Functions

-- DROP FUNCTIONS first to ensure fresh definition
DROP FUNCTION IF EXISTS fn_refresh_matches_for_search(TEXT) CASCADE;
DROP FUNCTION IF EXISTS fn_refresh_matches_for_property(UUID) CASCADE;
DROP FUNCTION IF EXISTS trg_fn_on_search_change() CASCADE;

-- FUNCTION: Refresh Matches for a Search
CREATE OR REPLACE FUNCTION fn_refresh_matches_for_search(p_search_id TEXT)
RETURNS VOID AS $$
DECLARE
    v_search RECORD;
    v_use_postgis BOOLEAN;
BEGIN
    SELECT * INTO v_search FROM searches WHERE id = p_search_id;
    IF v_search IS NULL OR v_search.status != 'ACTIVE' THEN 
        IF v_search IS NOT NULL AND v_search.is_archived THEN
             DELETE FROM matches WHERE search_id = p_search_id;
        END IF;
        RETURN;
    END IF;

    -- Check if PostGIS extension exists AND the geometry column exists in PUBLIC schema
    SELECT EXISTS (
        SELECT 1 
        FROM information_schema.columns 
        WHERE table_schema = 'public'
        AND table_name = 'search_zones' 
        AND column_name = 'geometry'
    ) INTO v_use_postgis;

    -- Upsert Matches
    IF v_use_postgis THEN
        BEGIN
            EXECUTE '
            INSERT INTO matches (search_id, property_uuid, property_id, user_id, created_at, matched_at)
            SELECT 
                $1, p.uuid, p.id, $2, NOW(), NOW()
            FROM properties p
            WHERE 
                p.is_active = true
                AND ($3 IS NULL OR fn_convert_price(p.price, p.currency, $4) <= $3)
                AND ($5 IS NULL OR p.rooms >= $5)
                AND ($6 IS NULL 
                     OR (jsonb_array_length($6) = 0)
                     OR EXISTS (
                        SELECT 1 FROM public.search_zones sz 
                        WHERE sz.search_id = $1 
                        AND sz.geometry IS NOT NULL 
                        AND p.latitude IS NOT NULL AND p.longitude IS NOT NULL
                        AND ST_Intersects(sz.geometry, ST_SetSRID(ST_MakePoint(p.longitude, p.latitude), 4326))
                     )
                )
            ON CONFLICT (search_id, property_uuid) DO UPDATE SET matched_at = NOW()
            ' USING v_search.id, v_search.user_id, v_search.max_budget, v_search.currency, v_search.min_rooms, v_search.zones;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'PostGIS query failed, falling back: %', SQLERRM;
            v_use_postgis := FALSE;
        END;
    END IF;

    IF NOT v_use_postgis THEN
        -- Fallback
        INSERT INTO matches (search_id, property_uuid, property_id, user_id, created_at, matched_at)
        SELECT 
            v_search.id, p.uuid, p.id, v_search.user_id, NOW(), NOW()
        FROM properties p
        WHERE 
            p.is_active = true
            AND (v_search.max_budget IS NULL OR fn_convert_price(p.price, p.currency, v_search.currency) <= v_search.max_budget)
            AND (v_search.min_rooms IS NULL OR p.rooms >= v_search.min_rooms)
            -- If zones exist, we just match (fallback behavior)
        ON CONFLICT (search_id, property_uuid) DO UPDATE SET matched_at = NOW();
    END IF;

    -- Delete matches logic
    IF v_use_postgis THEN
        BEGIN
            EXECUTE '
            DELETE FROM matches m
            WHERE m.search_id = $1
            AND NOT EXISTS (
                SELECT 1 FROM properties p
                WHERE p.uuid = m.property_uuid
                AND p.is_active = true
                AND ($2 IS NULL OR fn_convert_price(p.price, p.currency, $3) <= $2)
                AND ($4 IS NULL OR p.rooms >= $4)
                AND ($5 IS NULL 
                     OR (jsonb_array_length($5) = 0)
                     OR EXISTS (
                        SELECT 1 FROM public.search_zones sz 
                        WHERE sz.search_id = $1
                        AND sz.geometry IS NOT NULL
                        AND p.latitude IS NOT NULL AND p.longitude IS NOT NULL
                        AND ST_Intersects(sz.geometry, ST_SetSRID(ST_MakePoint(p.longitude, p.latitude), 4326))
                     )
                )
            )
            ' USING v_search.id, v_search.max_budget, v_search.currency, v_search.min_rooms, v_search.zones;
        EXCEPTION WHEN OTHERS THEN
            v_use_postgis := FALSE; -- Force fallback for delete too
        END;
    END IF;
    
    IF NOT v_use_postgis THEN
        DELETE FROM matches m
        WHERE m.search_id = p_search_id
        AND NOT EXISTS (
            SELECT 1 FROM properties p
            WHERE p.uuid = m.property_uuid
            AND p.is_active = true
            AND (v_search.max_budget IS NULL OR fn_convert_price(p.price, p.currency, v_search.currency) <= v_search.max_budget)
            AND (v_search.min_rooms IS NULL OR p.rooms >= v_search.min_rooms)
        );
    END IF;
END;
$$ LANGUAGE plpgsql SET search_path = public, extensions;

-- FUNCTION: Refresh Matches for a Property
CREATE OR REPLACE FUNCTION fn_refresh_matches_for_property(p_property_uuid UUID)
RETURNS VOID AS $$
DECLARE
    v_prop RECORD;
    v_use_postgis BOOLEAN;
BEGIN
    SELECT * INTO v_prop FROM properties WHERE uuid = p_property_uuid;
    IF v_prop IS NULL OR v_prop.is_active = false THEN 
        DELETE FROM matches WHERE property_uuid = p_property_uuid;
        RETURN; 
    END IF;

    -- Check if PostGIS extension exists AND the geometry column exists in PUBLIC schema
    SELECT EXISTS (
        SELECT 1 
        FROM information_schema.columns 
        WHERE table_schema = 'public'
        AND table_name = 'search_zones' 
        AND column_name = 'geometry'
    ) INTO v_use_postgis;

    -- Upsert Matches
    IF v_use_postgis THEN
        BEGIN
            EXECUTE '
            INSERT INTO matches (search_id, property_uuid, property_id, user_id, created_at, matched_at)
            SELECT 
                s.id, $1, $2, s.user_id, NOW(), NOW()
            FROM searches s
            WHERE 
                s.status = ''ACTIVE'' AND s.is_archived = false
                AND (s.max_budget IS NULL OR fn_convert_price($3, $4, s.currency) <= s.max_budget)
                AND (s.min_rooms IS NULL OR $5 >= s.min_rooms)
                AND (
                    s.zones IS NULL 
                    OR (jsonb_array_length(s.zones) = 0)
                    OR EXISTS (
                        SELECT 1 FROM public.search_zones sz 
                        WHERE sz.search_id = s.id 
                        AND sz.geometry IS NOT NULL 
                        AND $6 IS NOT NULL AND $7 IS NOT NULL
                        AND ST_Intersects(sz.geometry, ST_SetSRID(ST_MakePoint($7, $6), 4326))
                     )
                )
            ON CONFLICT (search_id, property_uuid) DO UPDATE SET matched_at = NOW()
            ' USING v_prop.uuid, v_prop.id, v_prop.price, v_prop.currency, v_prop.rooms, v_prop.latitude, v_prop.longitude;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'PostGIS query failed (prop), falling back: %', SQLERRM;
            v_use_postgis := FALSE;
        END;
    END IF;

    IF NOT v_use_postgis THEN
        INSERT INTO matches (search_id, property_uuid, property_id, user_id, created_at, matched_at)
        SELECT 
            s.id, v_prop.uuid, v_prop.id, s.user_id, NOW(), NOW()
        FROM searches s
        WHERE 
            s.status = 'ACTIVE' AND s.is_archived = false
            AND (s.max_budget IS NULL OR fn_convert_price(v_prop.price, v_prop.currency, s.currency) <= s.max_budget)
            AND (s.min_rooms IS NULL OR v_prop.rooms >= s.min_rooms)
        ON CONFLICT (search_id, property_uuid) DO UPDATE SET matched_at = NOW();
    END IF;

    -- Cleanup
    IF v_use_postgis THEN
        BEGIN
            EXECUTE '
            DELETE FROM matches m
            WHERE m.property_uuid = $1
            AND NOT EXISTS (
                SELECT 1 FROM searches s
                WHERE s.id = m.search_id
                AND s.status = ''ACTIVE'' AND s.is_archived = false
                AND (s.max_budget IS NULL OR fn_convert_price($2, $3, s.currency) <= s.max_budget)
                AND (s.min_rooms IS NULL OR $4 >= s.min_rooms)
                AND (
                    s.zones IS NULL 
                    OR (jsonb_array_length(s.zones) = 0)
                    OR EXISTS (
                        SELECT 1 FROM public.search_zones sz 
                        WHERE sz.search_id = s.id 
                        AND sz.geometry IS NOT NULL 
                        AND $5 IS NOT NULL AND $6 IS NOT NULL
                        AND ST_Intersects(sz.geometry, ST_SetSRID(ST_MakePoint($6, $5), 4326))
                    )
                )
            )
            ' USING v_prop.uuid, v_prop.price, v_prop.currency, v_prop.rooms, v_prop.latitude, v_prop.longitude;
        EXCEPTION WHEN OTHERS THEN
            v_use_postgis := FALSE;
        END;
    END IF;
    
    IF NOT v_use_postgis THEN
         DELETE FROM matches m
        WHERE m.property_uuid = p_property_uuid
        AND NOT EXISTS (
            SELECT 1 FROM searches s
            WHERE s.id = m.search_id
            AND s.status = 'ACTIVE' AND s.is_archived = false
            AND (s.max_budget IS NULL OR fn_convert_price(v_prop.price, v_prop.currency, s.currency) <= s.max_budget)
            AND (s.min_rooms IS NULL OR v_prop.rooms >= s.min_rooms)
        );
    END IF;
END;
$$ LANGUAGE plpgsql SET search_path = public, extensions;

-- 5. Triggers
-- Search Change Trigger
CREATE OR REPLACE FUNCTION trg_fn_on_search_change()
RETURNS TRIGGER AS $$
DECLARE
    v_has_geometry BOOLEAN;
BEGIN
    IF (TG_OP = 'INSERT') OR (NEW.zones IS DISTINCT FROM OLD.zones) THEN
        -- Standardize Zone Geometry (if PostGIS exists AND column exists)
        IF NEW.zones IS NOT NULL AND jsonb_array_length(NEW.zones) > 0 THEN
             -- Clear old zones for this search
             DELETE FROM search_zones WHERE search_id = NEW.id;
             -- Insert new raw zones
             INSERT INTO search_zones (search_id, raw_coordinates)
             SELECT NEW.id, elem
             FROM jsonb_array_elements(NEW.zones) elem;
             
             -- Check if geometry column exists
             SELECT EXISTS (
                SELECT 1 
                FROM information_schema.columns 
                WHERE table_schema = 'public'
                AND table_name = 'search_zones' 
                AND column_name = 'geometry'
             ) INTO v_has_geometry;

             IF v_has_geometry THEN
                 EXECUTE 'UPDATE search_zones SET geometry = ST_SetSRID(ST_GeomFromGeoJSON(raw_coordinates->>''geometry''), 4326) WHERE search_id = $1' USING NEW.id;
             END IF;
        END IF;

        -- Refresh Matches (calling processing function)
        PERFORM fn_refresh_matches_for_search(NEW.id);
    END IF;
    RETURN NULL; -- AFTER trigger
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_on_search_change ON searches;
CREATE TRIGGER trg_on_search_change
AFTER INSERT OR UPDATE ON searches
FOR EACH ROW EXECUTE FUNCTION trg_fn_on_search_change();

-- Property Change Trigger
CREATE OR REPLACE FUNCTION trg_fn_on_property_change()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM fn_refresh_matches_for_property(NEW.uuid);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_on_property_change ON properties;
CREATE TRIGGER trg_on_property_change
AFTER INSERT OR UPDATE ON properties
FOR EACH ROW EXECUTE FUNCTION trg_fn_on_property_change();

-- 6. Match Event Trigger
CREATE OR REPLACE FUNCTION trg_fn_on_match_created()
RETURNS TRIGGER AS $$
BEGIN
    -- Only capture if fn_capture_event exists
    IF EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'fn_capture_event') THEN
        PERFORM public.fn_capture_event(
            'match.created',
            CASE WHEN NEW.user_id ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' 
                 THEN NEW.user_id::uuid ELSE NULL END,
            'match',
            NEW.uuid,
            'match.created:' || NEW.search_id || ':' || NEW.property_uuid,
            jsonb_build_object(
                'search_id', NEW.search_id,
                'property_uuid', NEW.property_uuid,
                'property_id', NEW.property_id
            )
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS trg_on_match_created ON matches;
CREATE TRIGGER trg_on_match_created
AFTER INSERT ON matches
FOR EACH ROW EXECUTE FUNCTION trg_fn_on_match_created();
