-- Function to get store scores (visibility and current availability) for stores
-- Admin users see ALL stores, Manager users see only their linked stores
-- Returns stores with their visibility score (previous week) and current availability score
--
-- Parameters:
--   p_user_id: UUID of the logged-in user
--   p_customer_id: Customer ID to filter stores (optional)
--
-- Returns: TABLE with store score information

CREATE OR REPLACE FUNCTION get_store_scores(
    p_user_id UUID,
    p_customer_id INTEGER DEFAULT NULL
)
RETURNS TABLE (
    store_code TEXT,
    store_name TEXT,
    customer_id INTEGER,
    visibility_score BIGINT,
    current_availability_score BIGINT,
    current_week_start_date TEXT,
    previous_week_start_date TEXT,
    store_region TEXT,
    store_channel TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_current_week_start DATE;
    v_previous_week_start DATE;
    v_user_role TEXT;
BEGIN
    -- Get user role from user table (web portal users)
    SELECT role INTO v_user_role
    FROM "user"
    WHERE id = p_user_id;
    
    -- Calculate current week start (Monday)
    v_current_week_start := DATE_TRUNC('week', CURRENT_DATE)::DATE;
    -- If today is Monday, use today; otherwise use last Monday
    IF EXTRACT(DOW FROM CURRENT_DATE) = 1 THEN
        v_current_week_start := CURRENT_DATE;
    ELSE
        v_current_week_start := CURRENT_DATE - (EXTRACT(DOW FROM CURRENT_DATE)::INTEGER - 1);
    END IF;
    
    -- Calculate previous week start (7 days before current week start)
    v_previous_week_start := v_current_week_start - INTERVAL '7 days';
    
    RETURN QUERY
    WITH user_stores AS (
        -- Admin users: Get ALL stores (from store_user_link, filtered by customer_id if provided)
        -- Manager users: Get only stores linked to the user
        SELECT DISTINCT
            sul.store_code,
            sul.name AS store_name,
            sul.customer_id,
            sul.region AS store_region,
            sul.channel AS store_channel
        FROM store_user_link sul
        WHERE (
            -- Admin sees all stores
            (v_user_role = 'Admin' AND (p_customer_id IS NULL OR sul.customer_id = p_customer_id))
            OR
            -- Manager sees only their linked stores
            (v_user_role != 'Admin' AND sul.user_id = p_user_id AND sul.active = 1 AND (p_customer_id IS NULL OR sul.customer_id = p_customer_id))
        )
    ),
    current_scores AS (
        -- Get current week availability scores
        SELECT
            sv.store_code,
            sv.customer_id,
            sv.availability_score,
            sv.week_start_date
        FROM store_visibility sv
        WHERE sv.week_start_date::DATE = v_current_week_start
    ),
    previous_scores AS (
        -- Get previous week availability scores (visibility score)
        SELECT DISTINCT ON (sv.store_code, sv.customer_id)
            sv.store_code,
            sv.customer_id,
            sv.availability_score AS visibility_score,
            sv.week_start_date
        FROM store_visibility sv
        WHERE sv.week_start_date::DATE = v_previous_week_start
        ORDER BY sv.store_code, sv.customer_id, sv.updated_at DESC
    )
    SELECT
        us.store_code,
        us.store_name,
        us.customer_id,
        COALESCE(ps.visibility_score, 0)::BIGINT AS visibility_score,
        COALESCE(cs.availability_score, 0)::BIGINT AS current_availability_score,
        v_current_week_start::TEXT AS current_week_start_date,
        v_previous_week_start::TEXT AS previous_week_start_date,
        us.store_region,
        us.store_channel
    FROM user_stores us
    LEFT JOIN current_scores cs 
        ON us.store_code = cs.store_code 
        AND us.customer_id = cs.customer_id
    LEFT JOIN previous_scores ps 
        ON us.store_code = ps.store_code 
        AND us.customer_id = ps.customer_id
    ORDER BY 
        COALESCE(cs.availability_score, 0) DESC,  -- Sort by current availability score
        us.store_name ASC;  -- Then by store name
END;
$$;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION get_store_scores(UUID, INTEGER) TO authenticated;

-- Comment
COMMENT ON FUNCTION get_store_scores IS 'Returns store scores (visibility and current availability). Admin users see ALL stores, Manager users see only their linked stores. Visibility score is from previous week, current availability is from current week';

