-- =============================================================================
-- Vistas útiles (ejecutar después de 01_schema_campeonato.sql)
-- =============================================================================

USE campeonato_db;

-- Tabla de posiciones por campeonato (usa puntos configurados en campeonatos)
CREATE OR REPLACE VIEW v_tabla_posiciones AS
SELECT
  ce.id                    AS campeonato_equipo_id,
  ce.campeonato_id,
  ce.equipo_id,
  e.nombre                 AS nombre_equipo,
  e.abreviatura,
  ce.grupo_codigo,
  COALESCE(r.partidos_jugados, 0)   AS partidos_jugados,
  COALESCE(r.ganados, 0)            AS ganados,
  COALESCE(r.empatados, 0)          AS empatados,
  COALESCE(r.perdidos, 0)           AS perdidos,
  COALESCE(r.goles_favor, 0)        AS goles_favor,
  COALESCE(r.goles_contra, 0)       AS goles_contra,
  COALESCE(r.goles_favor, 0) - COALESCE(r.goles_contra, 0) AS diferencia_goles,
  COALESCE(r.puntos, 0)             AS puntos
FROM campeonato_equipo ce
JOIN equipos e ON e.id = ce.equipo_id
LEFT JOIN (
  SELECT
    u.campeonato_id,
    u.equipo_id,
    SUM(u.pj)  AS partidos_jugados,
    SUM(u.pg)  AS ganados,
    SUM(u.pe)  AS empatados,
    SUM(u.pp)  AS perdidos,
    SUM(u.gf)  AS goles_favor,
    SUM(u.gc)  AS goles_contra,
    SUM(u.pts) AS puntos
  FROM (
    SELECT
      p.campeonato_id,
      p.equipo_local_id AS equipo_id,
      1 AS pj,
      CASE WHEN p.goles_local > p.goles_visitante THEN 1 ELSE 0 END AS pg,
      CASE WHEN p.goles_local = p.goles_visitante THEN 1 ELSE 0 END AS pe,
      CASE WHEN p.goles_local < p.goles_visitante THEN 1 ELSE 0 END AS pp,
      p.goles_local  AS gf,
      p.goles_visitante AS gc,
      CASE
        WHEN p.goles_local > p.goles_visitante  THEN c.puntos_victoria
        WHEN p.goles_local = p.goles_visitante THEN c.puntos_empate
        ELSE c.puntos_derrota
      END AS pts
    FROM partidos p
    JOIN campeonatos c ON c.id = p.campeonato_id
    WHERE p.estado IN ('finalizado', 'wo', 'en_juego')
       OR (p.estado = 'programado' AND (p.goles_local > 0 OR p.goles_visitante > 0))
    UNION ALL
    SELECT
      p.campeonato_id,
      p.equipo_visitante_id,
      1,
      CASE WHEN p.goles_visitante > p.goles_local THEN 1 ELSE 0 END,
      CASE WHEN p.goles_visitante = p.goles_local THEN 1 ELSE 0 END,
      CASE WHEN p.goles_visitante < p.goles_local THEN 1 ELSE 0 END,
      p.goles_visitante,
      p.goles_local,
      CASE
        WHEN p.goles_visitante > p.goles_local  THEN c.puntos_victoria
        WHEN p.goles_visitante = p.goles_local THEN c.puntos_empate
        ELSE c.puntos_derrota
      END
    FROM partidos p
    JOIN campeonatos c ON c.id = p.campeonato_id
    WHERE p.estado IN ('finalizado', 'wo', 'en_juego')
       OR (p.estado = 'programado' AND (p.goles_local > 0 OR p.goles_visitante > 0))
  ) u
  GROUP BY u.campeonato_id, u.equipo_id
) r ON r.campeonato_id = ce.campeonato_id AND r.equipo_id = ce.equipo_id
WHERE ce.estado IN ('inscrito', 'activo');

-- Resumen de tarjetas por jugador y campeonato (desde actas)
CREATE OR REPLACE VIEW v_tarjetas_jugador_campeonato AS
SELECT
  p.campeonato_id,
  t.jugador_id,
  j.nombre,
  j.apellidos,
  SUM(CASE WHEN t.tipo = 'amarilla' THEN 1 ELSE 0 END) AS amarillas,
  SUM(CASE WHEN t.tipo IN ('roja', 'segunda_amarilla') THEN 1 ELSE 0 END) AS rojas
FROM tarjetas_partido t
JOIN partidos p ON p.id = t.partido_id
JOIN jugadores j ON j.id = t.jugador_id
GROUP BY p.campeonato_id, t.jugador_id, j.nombre, j.apellidos;
