CREATE TABLE raw_matches (
    id SERIAL PRIMARY KEY,
    season INTEGER,
    data JSONB
);

import json
import os
import psycopg2
import sys
from typing import Any, List

# Database connection
try:
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        dbname="football",
        user="postgres",
        password="postgres"
    )
    cur = conn.cursor()
except psycopg2.Error as e:
    print(f"Database connection error: {e}")
    sys.exit(1)

# Directory containing JSON files
json_dir = '/mnt/c/Disk/dimension/footballstats/JSON'

def flatten_matches(data: Any) -> List[Any]:
    """Flatten nested match structures"""
    matches = []
    
    if isinstance(data, list):
        for item in data:
            if isinstance(item, dict) and 'home_team' in item and 'away_team' in item:
                # This is a match object
                matches.append(item)
            elif isinstance(item, list):
                # This is a nested list, recursively flatten
                matches.extend(flatten_matches(item))
            else:
                print(f"Unexpected item type: {type(item)}")
    elif isinstance(data, dict):
        # Check if this is a match object
        if 'home_team' in data and 'away_team' in data:
            matches.append(data)
        else:
            # This might be a container with matches inside
            for key, value in data.items():
                if isinstance(value, (list, dict)):
                    matches.extend(flatten_matches(value))
    
    return matches

for filename in os.listdir(json_dir):
    if filename.startswith('match_data') and filename.endswith('.json'):
        filepath = os.path.join(json_dir, filename)
        
        try:
            # Extract season from filename
            season = int(filename[10:14])
        except ValueError as e:
            print(f"Error extracting season from {filename}: {e}")
            continue

        try:
            with open(filepath, 'r', encoding='utf-8') as f:
                raw_data = json.load(f)
                
            # Debug: Print the type and structure of the loaded data
            print(f"DEBUG: {filename} - Type of loaded data: {type(raw_data)}")
            if isinstance(raw_data, list) and len(raw_data) > 0:
                print(f"DEBUG: {filename} - First element type: {type(raw_data[0])}")
            
            # Flatten the data structure to extract all matches
            matches = flatten_matches(raw_data)
            print(f"DEBUG: {filename} - Found {len(matches)} matches after flattening")
            
            if len(matches) > 0:
                print(f"DEBUG: {filename} - First match: {json.dumps(matches[0], indent=2)}")
            
        except Exception as e:
            print(f"Error reading/parsing {filename}: {e}")
            continue

        match_count = 0
        error_count = 0
        
        for match in matches:
            try:
                cur.execute(
                    "INSERT INTO raw_matches (season, data) VALUES (%s, %s)",
                    (season, json.dumps(match))
                )
                match_count += 1
            except psycopg2.Error as e:
                error_count += 1
                print(f"Database error inserting match from {filename}: {e}")
                print(f"Problematic match data: {json.dumps(match)}")
            except Exception as e:
                error_count += 1
                print(f"Unexpected error with match from {filename}: {e}")
                print(f"Problematic match data: {json.dumps(match)}")

        print(f"Loaded {match_count} matches from {filename} with {error_count} errors")
        
        try:
            conn.commit()
        except psycopg2.Error as e:
            print(f"Commit error after {filename}: {e}")
            try:
                conn.rollback()
            except:
                pass

cur.close()
conn.close()

CREATE TABLE matches (
    year VARCHAR,
    home_team VARCHAR,
    away_team VARCHAR,
    score VARCHAR,
    goals_minutes_1 TIMESTAMP,
    goals_minutes_2 TIMESTAMP,
    goals_minutes_3 TIMESTAMP,
    goals_minutes_4 TIMESTAMP,
    goals_minutes_5 TIMESTAMP,
    goals_minutes_6 TIMESTAMP,
    goals_minutes_7 TIMESTAMP,
    goals_minutes_8 TIMESTAMP,
    goals_minutes_9 TIMESTAMP,
    goals_minutes_10 TIMESTAMP,
    home_goals INTEGER,
    away_goals INTEGER,
    goals INTEGER,
    home_result VARCHAR,
    away_result VARCHAR
);

import psycopg2
import json
import logging
from datetime import datetime, timedelta
from typing import Optional, Dict, Any, List

# Set up logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('data_load.log'),
        logging.StreamHandler()
    ]
)

def parse_goal_minute(minute_str: str) -> Optional[int]:
    """
    Parse goal minute string, handling additional time notation
    Examples: 
    - "45+1'" becomes 46
    - "90+2'" becomes 92
    - "30'" becomes 30
    """
    try:
        if not minute_str:
            return None
            
        # Remove any trailing apostrophes/quotes
        minute_str = minute_str.rstrip("'’\"")
        
        if '+' in minute_str:
            parts = minute_str.split('+')
            base_minute = int(parts[0])
            added_minutes = int(parts[1])
            return base_minute + added_minutes
        else:
            return int(minute_str)
    except (ValueError, IndexError, AttributeError) as e:
        logging.error(f"Error parsing goal minute '{minute_str}': {e}")
        return None

def clean_score(score_str: str) -> str:
    """
    Clean score string by removing asterisks and other non-numeric characters
    """
    if not score_str:
        return score_str
    
    # Remove asterisks and other non-standard characters
    cleaned = score_str.replace('*', '').replace('--', '0')
    
    # Handle cases like "0 - 3*" -> "0 - 3"
    if ' - ' in cleaned:
        parts = cleaned.split(' - ')
        # Ensure both parts are numeric or can be converted to 0
        try:
            int(parts[0])
        except ValueError:
            parts[0] = '0'
        try:
            int(parts[1])
        except ValueError:
            parts[1] = '0'
        cleaned = ' - '.join(parts)
    
    return cleaned

def process_matches():
    conn = None
    cursor = None
    processed_count = 0
    error_count = 0
    skipped_count = 0
    
    try:
        # Database connection - update with your credentials
        conn = psycopg2.connect(
            host="localhost",
            database="football",
            user="postgres",
            password="postgres",
            port=5432	
        )

        cursor = conn.cursor()
        logging.info("Connected to database successfully")

        # Fetch raw matches
        cursor.execute("SELECT id, season, data FROM raw_matches ORDER BY id")
        raw_matches = cursor.fetchall()
        logging.info(f"Fetched {len(raw_matches)} raw matches")

        # Process each match
        for match_id, season, data in raw_matches:
            try:
                # data is already a dict from psycopg2 for JSONB fields
                match_data = data
                
                # Log full data for problematic matches (for debugging)
                if match_id in [3177, 4941, 5002, 5640, 9710, 14831, 15279, 17657, 20733, 
                              27177, 28565, 28633, 32717, 36030, 36052, 39788, 39794, 39862, 40158]:
                    logging.warning(f"DEBUG - Problematic match {match_id} full data: {match_data}")
                
                # Validate required fields
                required_fields = ['score', 'home_team', 'away_team']
                missing_fields = [field for field in required_fields if field not in match_data]
                if missing_fields:
                    logging.warning(f"Match {match_id} missing required fields: {missing_fields}. Full data: {match_data}")
                    error_count += 1
                    continue
                
                # Parse score safely with cleaning
                try:
                    original_score = match_data['score']
                    cleaned_score = clean_score(original_score)
                    
                    score_parts = cleaned_score.split(' - ')
                    if len(score_parts) != 2:
                        raise ValueError(f"Invalid score format after cleaning: {cleaned_score} (original: {original_score})")
                    
                    home_goals = int(score_parts[0])
                    away_goals = int(score_parts[1])
                    total_goals = home_goals + away_goals
                    
                    # Skip matches with more than 10 goals
                    if total_goals > 10:
                        logging.info(f"Skipping match {match_id} with {total_goals} goals (more than 10)")
                        skipped_count += 1
                        continue
                    
                    # Determine results
                    home_result = 'Wins' if home_goals > away_goals else 'Draws' if home_goals == away_goals else 'Losses'
                    away_result = 'Wins' if away_goals > home_goals else 'Draws' if away_goals == home_goals else 'Losses'
                    
                except (ValueError, IndexError) as e:
                    logging.error(f"Error parsing score for match {match_id}: {e}. Original score: '{match_data['score']}'. Full data: {match_data}")
                    error_count += 1
                    continue
                
                # Collect all goal times from both teams
                all_goal_times = []
                
                # Process home goals
                home_goals_minutes = match_data.get('home_goals_minutes', [])
                for minute_str in home_goals_minutes:
                    minute = parse_goal_minute(minute_str)
                    if minute is not None:
                        goal_time = datetime(2000, 1, 1) + timedelta(minutes=minute)
                        all_goal_times.append(goal_time)
                
                # Process away goals
                away_goals_minutes = match_data.get('away_goals_minutes', [])
                for minute_str in away_goals_minutes:
                    minute = parse_goal_minute(minute_str)
                    if minute is not None:
                        goal_time = datetime(2000, 1, 1) + timedelta(minutes=minute)
                        all_goal_times.append(goal_time)
                
                # Sort goal times chronologically
                all_goal_times.sort()
                
                # Prepare goal columns
                goal_columns = [None] * 10
                for i, goal_time in enumerate(all_goal_times):
                    if i < 10:  # Only store up to 10 goals
                        goal_columns[i] = goal_time
                
                # Insert the match record
                cursor.execute("""
                    INSERT INTO matches (
                        year, home_team, away_team, score, 
                        goals_minutes_1, goals_minutes_2, goals_minutes_3, 
                        goals_minutes_4, goals_minutes_5, goals_minutes_6, 
                        goals_minutes_7, goals_minutes_8, goals_minutes_9, 
                        goals_minutes_10,
                        home_goals, away_goals, goals,
                        home_result, away_result
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (
                    str(season), 
                    match_data['home_team'], 
                    match_data['away_team'], 
                    match_data['score'],
                    goal_columns[0], goal_columns[1], goal_columns[2], 
                    goal_columns[3], goal_columns[4], goal_columns[5], 
                    goal_columns[6], goal_columns[7], goal_columns[8], 
                    goal_columns[9],
                    home_goals, 
                    away_goals, 
                    total_goals,
                    home_result, 
                    away_result
                ))
                
                processed_count += 1
                if processed_count % 1000 == 0:
                    logging.info(f"Progress: {processed_count} matches processed")
                
            except Exception as e:
                logging.error(f"Error processing match {match_id}: {e}. Full data: {match_data}")
                error_count += 1
                continue
        
        # Commit the transaction
        conn.commit()
        logging.info(f"Data load completed: {processed_count} matches processed, {skipped_count} skipped, {error_count} errors")
        
    except Exception as e:
        logging.error(f"Database connection error: {e}")
        if conn:
            conn.rollback()
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        logging.info("Database connection closed")

def test_parse_goal_minute():
    """Test function for goal minute parsing"""
    test_cases = [
        ("45+1'", 46),
        ("90+2'", 92),
        ("30'", 30),
        ("26'", 26),
        ("7'", 7),
        ("", None),
        (None, None),
        ("invalid", None)
    ]
    
    logging.info("Testing goal minute parsing...")
    for input_str, expected in test_cases:
        result = parse_goal_minute(input_str)
        status = "✓" if result == expected else "✗"
        logging.info(f"{status} '{input_str}' -> {result} (expected: {expected})")

def test_clean_score():
    """Test function for score cleaning"""
    test_cases = [
        ("0 - 2*", "0 - 2"),
        ("3* - 0", "3 - 0"),
        ("0 - 3*", "0 - 3"),
        ("-- - --*", "0 - 0"),
        ("1 - 0", "1 - 0"),
        ("2 - 2", "2 - 2"),
    ]
    
    logging.info("Testing score cleaning...")
    for input_str, expected in test_cases:
        result = clean_score(input_str)
        status = "✓" if result == expected else "✗"
        logging.info(f"{status} '{input_str}' -> '{result}' (expected: '{expected}')")

if __name__ == "__main__":
    # Run tests first
    test_parse_goal_minute()
    test_clean_score()
    
    # Process matches
    process_matches()

-- Basic indexes for common filtering
CREATE INDEX idx_matches_year ON matches ("year");
CREATE INDEX idx_matches_home_team ON matches (home_team);
CREATE INDEX idx_matches_away_team ON matches (away_team);
CREATE INDEX idx_matches_home_result ON matches (home_result);
CREATE INDEX idx_matches_away_result ON matches (away_result);

-- Composite indexes for common query patterns
CREATE INDEX idx_matches_team_year ON matches (home_team, "year");
CREATE INDEX idx_matches_away_year ON matches (away_team, "year");
CREATE INDEX idx_matches_year_result ON matches ("year", home_result);

-- Indexes for goal-related queries
CREATE INDEX idx_matches_home_goals ON matches (home_goals);
CREATE INDEX idx_matches_away_goals ON matches (away_goals);
CREATE INDEX idx_matches_total_goals ON matches (goals);

-- Indexes for score column
CREATE INDEX idx_matches_score ON matches (score);

-- Indexes for goals_minutes columns
CREATE INDEX idx_matches_goals_minutes_1 ON matches (goals_minutes_1);
CREATE INDEX idx_matches_goals_minutes_2 ON matches (goals_minutes_2);
CREATE INDEX idx_matches_goals_minutes_3 ON matches (goals_minutes_3);
CREATE INDEX idx_matches_goals_minutes_4 ON matches (goals_minutes_4);
CREATE INDEX idx_matches_goals_minutes_5 ON matches (goals_minutes_5);
CREATE INDEX idx_matches_goals_minutes_6 ON matches (goals_minutes_6);
CREATE INDEX idx_matches_goals_minutes_7 ON matches (goals_minutes_7);
CREATE INDEX idx_matches_goals_minutes_8 ON matches (goals_minutes_8);
CREATE INDEX idx_matches_goals_minutes_9 ON matches (goals_minutes_9);
CREATE INDEX idx_matches_goals_minutes_10 ON matches (goals_minutes_10);

Важно! После загрузки данных не забываем создать индексы.