CREATE OR REPLACE VIEW view_riksdagen_party_longitudinal_performance AS
WITH election_cycles AS (
    SELECT 
        party,
        CASE 
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2002 AND 2005 THEN 2002
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2006 AND 2009 THEN 2006
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2010 AND 2013 THEN 2010
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2014 AND 2017 THEN 2014
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2018 AND 2021 THEN 2018
            WHEN EXTRACT(YEAR FROM ballot_date) BETWEEN 2022 AND 2025 THEN 2022
            ELSE 2026
        END AS election_year,
        COUNT(DISTINCT ballot_id) AS total_ballots,
        COUNT(DISTINCT person_id) AS active_members,
        AVG(CASE WHEN vote = 'Ja' AND won THEN 1 ELSE 0 END) * 100 AS win_rate,
        AVG(CASE WHEN vote = party_vote THEN 1 ELSE 0 END) * 100 AS party_discipline,
        COUNT(DISTINCT document_id) AS documents_authored,
        AVG(rebel_rate) AS avg_rebel_rate,
        SUM(leadership_positions) AS leadership_count
    FROM vote_data vd
    JOIN person_data pd ON vd.person_id = pd.person_id
    LEFT JOIN document_data dd ON pd.person_id = dd.person_id
    GROUP BY party, election_year
),
trend_analysis AS (
    SELECT 
        party, election_year,
        total_ballots, active_members, win_rate, party_discipline,
        LAG(win_rate) OVER (PARTITION BY party ORDER BY election_year) AS prev_win_rate,
        LAG(active_members) OVER (PARTITION BY party ORDER BY election_year) AS prev_members,
        LEAD(win_rate) OVER (PARTITION BY party ORDER BY election_year) AS next_win_rate
    FROM election_cycles
)
SELECT 
    party, election_year,
    total_ballots, active_members, win_rate, party_discipline,
    win_rate - prev_win_rate AS win_rate_change,
    active_members - prev_members AS membership_change,
    CASE 
        WHEN win_rate > prev_win_rate AND next_win_rate > win_rate THEN 'ASCENDING'
        WHEN win_rate < prev_win_rate AND next_win_rate < win_rate THEN 'DESCENDING'
        ELSE 'STABLE'
    END AS trajectory
FROM trend_analysis
ORDER BY party, election_year;

CREATE OR REPLACE VIEW view_riksdagen_party_coalition_evolution AS
SELECT 
    p1.party AS party_1,
    p2.party AS party_2,
    election_year,
    AVG(alignment_rate) AS avg_alignment,
    COUNT(*) AS joint_votes,
    CASE 
        WHEN AVG(alignment_rate) > 70 THEN 'STRONG_COALITION'
        WHEN AVG(alignment_rate) > 50 THEN 'MODERATE_COALITION'
        ELSE 'OPPOSITION'
    END AS coalition_strength
FROM view_riksdagen_vote_data_ballot_party_summary_annual p1
JOIN view_riksdagen_vote_data_ballot_party_summary_annual p2 
  ON p1.ballot_id = p2.ballot_id AND p1.party < p2.party
GROUP BY p1.party, p2.party, election_year
ORDER BY party_1, party_2, election_year;