-- ass1.sql
-- Your Name: [Your Name]
-- Your Student ID: [zID]

----------------------------------------------------------------
-- Q1: SQL function goals_in_match (define before Q5/Q7 if you use it there)
-- up_to_minute: default 90 = full-time; pass 85 for score at minute 85
DROP FUNCTION IF EXISTS goals_in_match(INTEGER, BOOLEAN, INTEGER);
CREATE OR REPLACE FUNCTION goals_in_match(match_id INTEGER, _is_home BOOLEAN, up_to_minute INTEGER DEFAULT 90)
RETURNS INTEGER AS $$
    SELECT 0;  -- replace: count goals where involves.is_home = _is_home and time_scored <= up_to_minute
$$ LANGUAGE SQL;

----------------------------------------------------------------
CREATE OR REPLACE VIEW Q2(team, country, total_goals) AS (
    SELECT
    -- your code here
);

----------------------------------------------------------------
CREATE OR REPLACE VIEW Q3(match_id, player_id, player, first_half_goals, second_half_goals) AS (
    SELECT
    -- your code here
);

----------------------------------------------------------------
CREATE OR REPLACE VIEW Q4(team, country, yellow_cards, red_cards, discipline_score) AS (
    SELECT
    -- your code here
);

----------------------------------------------------------------
CREATE OR REPLACE VIEW Q5(match_id, city, winner, loser, score) AS (
    SELECT
    -- your code here
);

----------------------------------------------------------------
CREATE OR REPLACE VIEW Q6(team, country, matches_played, goal_diff) AS (
    SELECT
    -- your code here
);

----------------------------------------------------------------
CREATE OR REPLACE VIEW Q7(match_id, city, winning_team, losing_team, score_85, fulltime_score) AS (
    SELECT
    -- your code here
);

----------------------------------------------------------------
CREATE OR REPLACE VIEW Q8(match_id, red_team, red_minute, score, goals_after_red) AS (
    SELECT
    -- your code here
);

----------------------------------------------------------------
-- Q9: SQL function (LANGUAGE sql; body = single SELECT returning set of TEXT)
DROP FUNCTION IF EXISTS Q9(pattern TEXT);
CREATE OR REPLACE FUNCTION Q9(pattern TEXT)
RETURNS SETOF TEXT AS $$
    SELECT NULL::TEXT WHERE false;  -- replace with your query
$$ LANGUAGE SQL;

----------------------------------------------------------------
-- Q10: PLpgSQL function Q10(t) — team summary
DROP FUNCTION IF EXISTS Q10(t INTEGER);
CREATE OR REPLACE FUNCTION Q10(t INTEGER)
RETURNS TEXT AS $$
    DECLARE
        result_text TEXT := '';
    BEGIN
        -- your code here
        RETURN result_text;
    END;
$$ LANGUAGE PLpgSQL;

----------------------------------------------------------------
-- End of template
----------------------------------------------------------------
