parser_fixed.py

import os
import re
import sqlite3
from pathlib import Path

class FixedBrasileraoParser:
    def __init__(self, db_path="brasileirao_fixed.db"):
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path)
        self.create_tables()
    
    def create_tables(self):
        cursor = self.conn.cursor()
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS matches (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                jogo_number TEXT,
                campeonato TEXT,
                rodada INTEGER,
                mandante TEXT,
                visitante TEXT,
                data TEXT,
                horario TEXT,
                estadio TEXT,
                resultado_1t TEXT,
                resultado_final TEXT
            )
        ''')
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS jogadores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                match_id INTEGER,
                equipe TEXT,
                numero INTEGER,
                apelido TEXT,
                nome_completo TEXT,
                titular_reserva TEXT,
                cbf TEXT,
                FOREIGN KEY (match_id) REFERENCES matches (id)
            )
        ''')
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS gols (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                match_id INTEGER,
                tempo TEXT,
                periodo TEXT,
                numero INTEGER,
                tipo TEXT,
                jogador TEXT,
                equipe TEXT,
                FOREIGN KEY (match_id) REFERENCES matches (id)
            )
        ''')
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS cartoes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                match_id INTEGER,
                tempo TEXT,
                periodo TEXT,
                numero INTEGER,
                jogador TEXT,
                equipe TEXT,
                tipo TEXT,
                motivo TEXT,
                FOREIGN KEY (match_id) REFERENCES matches (id)
            )
        ''')
        
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS substituicoes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                match_id INTEGER,
                tempo TEXT,
                periodo TEXT,
                equipe TEXT,
                entrou TEXT,
                saiu TEXT,
                FOREIGN KEY (match_id) REFERENCES matches (id)
            )
        ''')
        
        self.conn.commit()
    
    def parse_file(self, file_path):
        with open(file_path, 'r', encoding='utf-8') as f:
            content = f.read()
        
        # Parse match info
        match_info = {}
        
        jogo_match = re.search(r'Jogo:\s*(\d+)\s*CBF', content)
        match_info['jogo_number'] = jogo_match.group(1) if jogo_match else None
        
        camp_match = re.search(r'\|\s*Campeonato:\s*\|\s*([^|]+?)\s*\|.*?\|\s*Rodada:\s*\|\s*(\d+)\s*\|', content, re.DOTALL)
        if camp_match:
            match_info['campeonato'] = camp_match.group(1).strip()
            match_info['rodada'] = int(camp_match.group(2))
        
        teams_match = re.search(r'\|\s*Jogo:\s*\|\s*([^|]+?)\s*X\s*([^|]+?)\s*\|', content)
        if teams_match:
            match_info['mandante'] = teams_match.group(1).strip()
            match_info['visitante'] = teams_match.group(2).strip()
        
        # Fixed date/time/stadium parsing
        date_match = re.search(r'\|\s*Data:\s*\|\s*([^|]+?)\s*\|\s*Horário:\s*\|\s*([^|]+?)\s*\|\s*Estádio:\s*\|\s*([^|]+?)\s*\|', content)
        if date_match:
            match_info['data'] = date_match.group(1).strip()
            match_info['horario'] = date_match.group(2).strip()
            match_info['estadio'] = date_match.group(3).strip()
        
        result_1t_match = re.search(r'\|\s*Resultado do 1o Tempo:\s*([^|]+?)\s*\|', content)
        match_info['resultado_1t'] = result_1t_match.group(1).strip() if result_1t_match else None
        
        result_final_match = re.search(r'\|\s*Resultado Final:\s*([^|]+?)\s*\|', content)
        match_info['resultado_final'] = result_final_match.group(1).strip() if result_final_match else None
        
        # Insert match
        cursor = self.conn.cursor()
        cursor.execute('''
            INSERT INTO matches (jogo_number, campeonato, rodada, mandante, visitante, 
                               data, horario, estadio, resultado_1t, resultado_final)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            match_info.get('jogo_number'),
            match_info.get('campeonato'),
            match_info.get('rodada'),
            match_info.get('mandante'),
            match_info.get('visitante'),
            match_info.get('data'),
            match_info.get('horario'),
            match_info.get('estadio'),
            match_info.get('resultado_1t'),
            match_info.get('resultado_final')
        ))
        
        match_id = cursor.lastrowid
        
        # Parse goals
        goals_section = re.search(r'\|\s*Gols\s*\|.*?\|\s*NR\s*=\s*Normal', content, re.DOTALL)
        if goals_section:
            goal_lines = re.findall(r'\|\s*([^|]+?)\s*\|\s*(1T|2T)\s*\|\s*(\d+)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|', goals_section.group(0))
            for line in goal_lines:
                tempo, periodo, numero, tipo, jogador, equipe = line
                if tempo.strip() != 'Tempo' and tempo.strip():
                    cursor.execute('''
                        INSERT INTO gols (match_id, tempo, periodo, numero, tipo, jogador, equipe)
                        VALUES (?, ?, ?, ?, ?, ?, ?)
                    ''', (match_id, tempo.strip(), periodo.strip(), int(numero) if numero.isdigit() else None,
                          tipo.strip(), jogador.strip(), equipe.strip()))
        
        # Fixed players parsing
        mandante = match_info.get('mandante', 'Time A')
        visitante = match_info.get('visitante', 'Time B')
        
        # Find player table section
        player_section = re.search(r'\|\s*Relação de Jogadores\s*\|.*?\|\s*T\s*=\s*Titular', content, re.DOTALL)
        if player_section:
            lines = player_section.group(0).split('\n')
            for line in lines:
                # Match player rows with both teams
                player_match = re.match(r'\|\s*(\d+)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|\s*(T|R)(?:\([^)]*\))?\s*\|\s*P\s*\|\s*(\d+)\s*\|\s*(\d+)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|\s*(T|R)(?:\([^)]*\))?\s*\|\s*P\s*\|\s*(\d+)\s*\|', line)
                if player_match:
                    # First player (mandante)
                    cursor.execute('''
                        INSERT INTO jogadores (match_id, equipe, numero, apelido, nome_completo, titular_reserva, cbf)
                        VALUES (?, ?, ?, ?, ?, ?, ?)
                    ''', (match_id, mandante, int(player_match.group(1)), player_match.group(2).strip(), 
                          player_match.group(3).strip(), player_match.group(4), player_match.group(5)))
                    
                    # Second player (visitante)
                    cursor.execute('''
                        INSERT INTO jogadores (match_id, equipe, numero, apelido, nome_completo, titular_reserva, cbf)
                        VALUES (?, ?, ?, ?, ?, ?, ?)
                    ''', (match_id, visitante, int(player_match.group(6)), player_match.group(7).strip(),
                          player_match.group(8).strip(), player_match.group(9), player_match.group(10)))
        
        # Fixed cards parsing
        cards_section = re.search(r'\|\s*Cartões Amarelos\s*\|.*?\|\s*Cartões Vermelhos\s*\|', content, re.DOTALL)
        if cards_section:
            lines = cards_section.group(0).split('\n')
            current_card = None
            
            for line in lines:
                # Match card entry
                card_match = re.match(r'\|\s*([^|]+?)\s*\|\s*(1T|2T)\s*\|\s*(\d+)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|', line)
                if card_match and card_match.group(1).strip() != 'Tempo':
                    current_card = {
                        'tempo': card_match.group(1).strip(),
                        'periodo': card_match.group(2).strip(),
                        'numero': int(card_match.group(3)) if card_match.group(3).isdigit() else None,
                        'jogador': card_match.group(4).strip(),
                        'equipe': card_match.group(5).strip(),
                        'motivo': None
                    }
                elif current_card and 'Motivo:' in line:
                    motivo_match = re.search(r'Motivo:\s*([^|]+)', line)
                    if motivo_match:
                        current_card['motivo'] = motivo_match.group(1).strip()
                    
                    cursor.execute('''
                        INSERT INTO cartoes (match_id, tempo, periodo, numero, jogador, equipe, tipo, motivo)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    ''', (match_id, current_card['tempo'], current_card['periodo'], current_card['numero'],
                          current_card['jogador'], current_card['equipe'], 'Amarelo', current_card['motivo']))
                    current_card = None
        
        # Fixed substitutions parsing
        subs_section = re.search(r'\|\s*Substituições\s*\|.*?(?=\*\*Confederação|$)', content, re.DOTALL)
        if subs_section:
            sub_lines = re.findall(r'\|\s*(\d+:\d+)\s*\|\s*(1T|2T|INT)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|', subs_section.group(0))
            for line in sub_lines:
                tempo, periodo, equipe, entrou, saiu = line
                cursor.execute('''
                    INSERT INTO substituicoes (match_id, tempo, periodo, equipe, entrou, saiu)
                    VALUES (?, ?, ?, ?, ?, ?)
                ''', (match_id, tempo.strip(), periodo.strip(), equipe.strip(), entrou.strip(), saiu.strip()))
        
        self.conn.commit()
        return match_id
    
    def process_all_files(self, md_folder):
        md_path = Path(md_folder)
        processed = 0
        
        for md_file in md_path.glob("*.md"):
            try:
                self.parse_file(md_file)
                processed += 1
                if processed % 100 == 0:
                    print(f"Processed {processed} files...")
            except Exception as e:
                print(f"Error processing {md_file.name}: {e}")
        
        print(f"\nTotal files processed: {processed}")
        return processed
    
    def close(self):
        self.conn.close()

def main():
    parser = FixedBrasileraoParser()
    md_folder = "/Users/brunnomi/Desktop/Python/brasileirao/md"
    parser.process_all_files(md_folder)
    parser.close()
    print("Fixed database created: brasileirao_fixed.db")

if __name__ == "__main__":
    main()