-- Migration: Unified Beacon/Post RPC Functions
-- Updates RPC functions to return Post-compatible JSON structures

-- ============================================================================
-- get_beacon_details: Fetch a single beacon with full Post data structure
-- ============================================================================
CREATE OR REPLACE FUNCTION get_beacon_details(p_beacon_id UUID)
RETURNS TABLE (
    id UUID,
    body TEXT,
    author_id UUID,
    category_id UUID,
    tone_label TEXT,
    cis_score NUMERIC,
    status TEXT,
    created_at TIMESTAMPTZ,
    edited_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,
    is_edited BOOLEAN,
    allow_chain BOOLEAN,
    chain_parent_id UUID,
    image_url TEXT,
    body_format TEXT,
    background_id TEXT,
    tags TEXT[],
    is_beacon BOOLEAN,
    beacon_type TEXT,
    confidence_score NUMERIC,
    is_active_beacon BOOLEAN,
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    distance_meters DOUBLE PRECISION,
    author_handle TEXT,
    author_display_name TEXT,
    author_avatar_url TEXT,
    vouch_count INTEGER,
    report_count INTEGER,
    user_vote TEXT,
    status_color TEXT
) LANGUAGE plpgsql STABLE SECURITY DEFINER AS $$
DECLARE
    current_user_id UUID;
BEGIN
    current_user_id := auth.uid();

    RETURN QUERY
    SELECT
        p.id,
        p.body,
        p.author_id,
        p.category_id,
        p.tone_label::TEXT,
        p.cis_score,
        p.status::TEXT,
        p.created_at,
        p.edited_at,
        p.deleted_at,
        (p.edited_at IS NOT NULL) AS is_edited,
        p.allow_chain,
        p.chain_parent_id,
        p.image_url,
        p.body_format,
        p.background_id,
        p.tags,
        p.is_beacon,
        p.beacon_type::TEXT,
        p.confidence_score,
        p.is_active_beacon,
        ST_Y(p.location::geometry) AS latitude,
        ST_X(p.location::geometry) AS longitude,
        0.0 AS distance_meters, -- No distance calculation for single beacon fetch
        prof.handle AS author_handle,
        prof.display_name AS author_display_name,
        prof.avatar_url AS author_avatar_url,
        COALESCE(vouch.cnt, 0)::INT AS vouch_count,
        COALESCE(report.cnt, 0)::INT AS report_count,
        user_vote.vote_type AS user_vote,
        get_beacon_status_color(p.confidence_score) AS status_color
    FROM posts p
    LEFT JOIN profiles prof ON p.author_id = prof.id
    LEFT JOIN (
        SELECT beacon_id, COUNT(*)::INT AS cnt
        FROM beacon_votes
        WHERE vote_type = 'vouch'
        GROUP BY beacon_id
    ) vouch ON p.id = vouch.beacon_id
    LEFT JOIN (
        SELECT beacon_id, COUNT(*)::INT AS cnt
        FROM beacon_votes
        WHERE vote_type = 'report'
        GROUP BY beacon_id
    ) report ON p.id = report.beacon_id
    LEFT JOIN (
        SELECT beacon_id, vote_type
        FROM beacon_votes
        WHERE user_id = current_user_id
    ) user_vote ON p.id = user_vote.beacon_id
    WHERE p.id = p_beacon_id
      AND p.is_beacon = TRUE
      AND p.deleted_at IS NULL;
END;
$$;

-- ============================================================================
-- vouch_beacon: Add a vouch vote to a beacon
-- ============================================================================
CREATE OR REPLACE FUNCTION vouch_beacon(p_beacon_id UUID)
RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    current_user_id UUID;
BEGIN
    current_user_id := auth.uid();

    IF current_user_id IS NULL THEN
        RAISE EXCEPTION 'Not authenticated';
    END IF;

    -- Insert or update the vote
    INSERT INTO beacon_votes (user_id, beacon_id, vote_type)
    VALUES (current_user_id, p_beacon_id, 'vouch')
    ON CONFLICT (user_id, beacon_id)
    DO UPDATE SET vote_type = 'vouch', created_at = NOW();
END;
$$;

-- ============================================================================
-- report_beacon: Add a report vote to a beacon
-- ============================================================================
CREATE OR REPLACE FUNCTION report_beacon(p_beacon_id UUID)
RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    current_user_id UUID;
BEGIN
    current_user_id := auth.uid();

    IF current_user_id IS NULL THEN
        RAISE EXCEPTION 'Not authenticated';
    END IF;

    -- Insert or update the vote
    INSERT INTO beacon_votes (user_id, beacon_id, vote_type)
    VALUES (current_user_id, p_beacon_id, 'report')
    ON CONFLICT (user_id, beacon_id)
    DO UPDATE SET vote_type = 'report', created_at = NOW();

    -- Decrease confidence score
    UPDATE posts
    SET confidence_score = GREATEST(0.0, confidence_score - 0.1)
    WHERE id = p_beacon_id AND is_beacon = TRUE;
END;
$$;

-- ============================================================================
-- remove_beacon_vote: Remove user's vote from a beacon
-- ============================================================================
CREATE OR REPLACE FUNCTION remove_beacon_vote(p_beacon_id UUID)
RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    current_user_id UUID;
BEGIN
    current_user_id := auth.uid();

    IF current_user_id IS NULL THEN
        RAISE EXCEPTION 'Not authenticated';
    END IF;

    DELETE FROM beacon_votes
    WHERE user_id = current_user_id
      AND beacon_id = p_beacon_id;
END;
$$;

-- ============================================================================
-- garbage_collect_beacons: Admin function to disable stale beacons
-- ============================================================================
CREATE OR REPLACE FUNCTION garbage_collect_beacons()
RETURNS INTEGER LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    disabled_count INTEGER;
BEGIN
    -- Disable beacons that are:
    -- 1. Older than 6 hours
    -- 2. Have low confidence score (< 0.3)
    -- 3. Are still marked as active
    UPDATE posts
    SET is_active_beacon = FALSE
    WHERE is_beacon = TRUE
      AND is_active_beacon = TRUE
      AND confidence_score < 0.3
      AND created_at < NOW() - INTERVAL '6 hours'
      AND deleted_at IS NULL;

    GET DIAGNOSTICS disabled_count = ROW_COUNT;
    RETURN disabled_count;
END;
$$;

-- ============================================================================
-- Update fetch_nearby_beacons to return Post-compatible structure
-- ============================================================================
CREATE OR REPLACE FUNCTION fetch_nearby_beacons(
    p_lat DOUBLE PRECISION,
    p_long DOUBLE PRECISION,
    p_radius INTEGER DEFAULT 16000
)
RETURNS TABLE (
    id UUID,
    body TEXT,
    author_id UUID,
    category_id UUID,
    tone_label TEXT,
    cis_score NUMERIC,
    status TEXT,
    created_at TIMESTAMPTZ,
    edited_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,
    is_edited BOOLEAN,
    allow_chain BOOLEAN,
    chain_parent_id UUID,
    image_url TEXT,
    body_format TEXT,
    background_id TEXT,
    tags TEXT[],
    is_beacon BOOLEAN,
    beacon_type TEXT,
    confidence_score NUMERIC,
    is_active_beacon BOOLEAN,
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    distance_meters DOUBLE PRECISION,
    author_handle TEXT,
    author_display_name TEXT,
    author_avatar_url TEXT,
    vouch_count INTEGER,
    report_count INTEGER,
    status_color TEXT
) LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.id,
        p.body,
        p.author_id,
        p.category_id,
        p.tone_label::TEXT,
        p.cis_score,
        p.status::TEXT,
        p.created_at,
        p.edited_at,
        p.deleted_at,
        (p.edited_at IS NOT NULL) AS is_edited,
        p.allow_chain,
        p.chain_parent_id,
        p.image_url,
        p.body_format,
        p.background_id,
        p.tags,
        p.is_beacon,
        p.beacon_type::TEXT,
        p.confidence_score,
        p.is_active_beacon,
        ST_Y(p.location::geometry) AS latitude,
        ST_X(p.location::geometry) AS longitude,
        ST_Distance(p.location, ST_SetSRID(ST_MakePoint(p_long, p_lat), 4326)::geography) AS distance_meters,
        prof.handle AS author_handle,
        prof.display_name AS author_display_name,
        prof.avatar_url AS author_avatar_url,
        COALESCE(vouch.cnt, 0)::INT AS vouch_count,
        COALESCE(report.cnt, 0)::INT AS report_count,
        get_beacon_status_color(p.confidence_score) AS status_color
    FROM posts p
    LEFT JOIN profiles prof ON p.author_id = prof.id
    LEFT JOIN (
        SELECT beacon_id, COUNT(*)::INT AS cnt
        FROM beacon_votes
        WHERE vote_type = 'vouch'
        GROUP BY beacon_id
    ) vouch ON p.id = vouch.beacon_id
    LEFT JOIN (
        SELECT beacon_id, COUNT(*)::INT AS cnt
        FROM beacon_votes
        WHERE vote_type = 'report'
        GROUP BY beacon_id
    ) report ON p.id = report.beacon_id
    WHERE p.is_beacon = TRUE
      AND p.is_active_beacon = TRUE
      AND p.deleted_at IS NULL
      AND ST_DWithin(
          p.location,
          ST_SetSRID(ST_MakePoint(p_long, p_lat), 4326)::geography,
          p_radius::DOUBLE PRECISION
      )
    ORDER BY p.confidence_score DESC, p.created_at DESC
    LIMIT 100;
END;
$$;