boxscore_ask_match_events

class StasProc3 < ActiveRecord::Migration

  def up
    connection.execute(%q{
      CREATE OR REPLACE FUNCTION boxscore_ask_match_events (_p_match_id bigint, _p_half int DEFAULT 0::int) RETURNS TABLE(
        f_match BIGINT
        ,id BIGINT
        ,half SMALLINT
        ,second real
        ,second_clear real
        ,c_action int
        ,f_team int
        ,f_player int
        ,opponent_f_team int
        ,opponent_f_player int
        ,pos_x real
        ,pos_y real
        ,opponent_pos_x real
        ,opponent_pos_y real
        --,c_zone SMALLINT
        --,opponent_c_zone SMALLINT
        ,data1_int int
        ,data2_int int
        ,link int
        ,poss_second_start int
        ,poss_second_end int
      )
        LANGUAGE plpgsql SECURITY DEFINER
      AS $$
      BEGIN

        _p_half := COALESCE(_p_half, 0);

        RETURN QUERY
        WITH poss_data AS (
          SELECT
            med.possession_num
            ,MIN(me.half) as half
            ,ROUND(MIN(me.second)::decimal,0)::int as poss_second_start
            ,ROUND(MAX(me.second)::decimal,0)::int as poss_second_end
          FROM
            f_match_event me
            JOIN f_match_event_data med
              ON med.f_match_event = me.id
          WHERE
            me.f_match = _p_match_id
          AND me.dl = 0
          AND med.possession_num IS NOT NULL
          AND (_p_half = 0 OR me.half = _p_half)
          GROUP BY med.possession_num
        )
        SELECT
          me.f_match
          ,me.id
          ,me.half
          ,me.second::real as "second"
          ,med.second_clear
          ,me.c_action
          ,me.f_team
          ,me.f_player
          ,COALESCE(opp_t.value::int, me.opponent_f_team) as opponent_f_team
          ,coalesce(opp_p.value::int, me.opponent_f_player) as opponent_f_player
          ,coalesce(ROUND(pos_x.value::decimal,3), me.pos_x) as pos_x
          ,coalesce(ROUND(pos_y.value::decimal,3), me.pos_y) as pos_y
          ,coalesce(ROUND(pos_dest_x.value::decimal,3), me.opponent_pos_x) as opponent_pos_x
          ,coalesce(ROUND(pos_dest_y.value::decimal,3), me.opponent_pos_y) as opponent_pos_y
          --,me.c_zone
          --,me.opponent_c_zone
          ,me.data1_int
          ,me.data2_int
          ,me.link
          ,pd.poss_second_start
          ,pd.poss_second_end
        FROM
          f_match_event me
          LEFT JOIN f_match_event_data med
            ON med.f_match_event = me.id
          LEFT JOIN poss_data pd
            ON pd.possession_num = med.possession_num
          LEFT JOIN f_match_event_prop opp_t
            ON opp_t.f_match_event = me.id
            AND opp_t.c_match_event_prop = 19 -- команда-оппонент
          LEFT JOIN f_match_event_prop opp_p
            ON opp_p.f_match_event = me.id
            AND opp_p.c_match_event_prop = 20 -- игрок-оппонент
          LEFT JOIN f_match_event_prop pos_x
            ON pos_x.f_match_event = me.id
            AND pos_x.c_match_event_prop = 21 -- pos_x
          LEFT JOIN f_match_event_prop pos_y
            ON pos_y.f_match_event = me.id
            AND pos_y.c_match_event_prop = 22 -- pos_y
          LEFT JOIN f_match_event_prop pos_dest_x
            ON pos_dest_x.f_match_event = me.id
            AND pos_dest_x.c_match_event_prop = 23 -- pos_dest_x
          LEFT JOIN f_match_event_prop pos_dest_y
            ON pos_dest_y.f_match_event = me.id
            AND pos_dest_y.c_match_event_prop = 24 -- pos_dest_y
        WHERE
          me.f_match = _p_match_id
        AND me.dl = 0
        AND (_p_half = 0 OR me.half = _p_half)
        AND me.c_action IN (400500, 600200, 800100, 1800300, 1800300, 800100, 300100, 100100, 600100, 400100, 400200, 400300, 400600, 400100, 400200, 400300, 400400, 400500, 800100)
        ORDER BY me.half, me.second, me.id
        ;

      END;
      $$
    })
  end

  def down
    connection.execute(%q{
      DROP FUNCTION boxscore_ask_match_events(BIGINT);
    })
  end
  
end