from flask import Flask, render_template, request, g
import sqlite3
from urllib.parse import quote as url_quote
import time
import logging

app = Flask(__name__)

logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

@app.before_request
def before_request():
    g.start_time = time.time()

@app.after_request
def after_request(response):
    if hasattr(g, 'start_time'):
        duration_ms = (time.time() - g.start_time) * 1000
        logger.info(f"{request.method} {request.path} - {response.status_code} - {duration_ms:.2f}ms")
    return response

def get_db():
    db = sqlite3.connect('3cb.db')
    db.row_factory = sqlite3.Row
    db.set_trace_callback(lambda stmt: logger.debug(f"SQLite: {stmt}"))
    return db

def execute_query(db, query, params=()):
    """Execute a query with logging of query text and execution time."""
    start_time = time.time()
    result = db.execute(query, params)
    duration_ms = (time.time() - start_time) * 1000

    # Format query for logging (remove extra whitespace/newlines)
    formatted_query = ' '.join(query.split())
    logger.info(f"SQL Query ({duration_ms:.2f}ms): {formatted_query} | Params: {params}")

    return result

@app.route('/')
def index():
    db = get_db()
    rounds = db.execute("select r.id, r.fileid, r.date, count(distinct d.player) as player_count from round r left join deck d on r.id = d.round group by r.id, r.fileid, r.date order by r.id desc").fetchall()
    db.close()
    subtitle = "rounds"
    return render_template('index.html', rounds=rounds, subtitle=subtitle)

@app.route('/round/<int:id>')
def round_detail(id):
    db = get_db()
    round_data = db.execute("select * from round where id = ?", (id,)).fetchone()
    matches = db.execute("""
        select *
        from round_score
        where round = ?
        order by final_score desc, prelim_score desc
    """, (id,)).fetchall()
    banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()]
    db.close()
    subtitle = "round " + str(id)
    return render_template('matches.html', round=round_data, matches=matches, banned_cards=banned_cards, subtitle=subtitle)

@app.route('/card')
def card():
    name = request.args.get('name')
    db = get_db()
    matches = db.execute("""
        select *
        from round_score
        where card1 = ?1 or card2 = ?1 or card3 = ?1
        order by round desc
    """, (name,)).fetchall()
    banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()]
    db.close()
    subtitle = name
    return render_template('matches.html', matches=matches, banned_cards=banned_cards, subtitle=subtitle)

@app.route('/player')
def player():
    name = request.args.get('name')
    db = get_db()
    matches = db.execute("""
        select *
        from round_score
        where player = ?
        order by round desc
    """, (name,)).fetchall()
    banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()]
    db.close()
    subtitle = "player " + name
    return render_template('matches.html', matches=matches, banned_cards=banned_cards, subtitle=subtitle)

@app.route('/deck')
def deck():
    cards = sorted([request.args.get('c1'), request.args.get('c2'), request.args.get('c3')])
    db = get_db()
    matches = db.execute("""
        select *
        from round_score
        where
        card1 = ?1 and card2 = ?2 and card3 = ?3 or
        card1 = ?1 and card2 = ?3 and card3 = ?2 or
        card1 = ?2 and card2 = ?1 and card3 = ?3 or
        card1 = ?2 and card2 = ?3 and card3 = ?1 or
        card1 = ?3 and card2 = ?1 and card3 = ?2 or
        card1 = ?3 and card2 = ?2 and card3 = ?1
        order by round desc
    """, (cards[0], cards[1], cards[2])).fetchall()
    banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()]
    db.close()
    subtitle = "; ".join(cards)
    return render_template('matches.html', matches=matches, banned_cards=banned_cards, subtitle=subtitle)

@app.route('/players')
def players():
    db = get_db()
    players = db.execute("""
      select 
        d.player,
        count(distinct d.round) as rounds_played,
        sum(case when r.rank = 1 then 1 else 0 end) as rounds_won,
        min(ro.date) as first_played
      from deck d
      left join rank r on d.round = r.round and d.player = r.player
      left join round ro on d.round = ro.id
      group by d.player
      order by rounds_played desc
    """).fetchall()
    db.close()
    subtitle = "players"
    return render_template('players.html', players=players, subtitle=subtitle)

@app.route('/banned')
def banned():
    db = get_db()
    banned_cards = db.execute("select name from ban order by name").fetchall()
    db.close()
    subtitle = "banned cards"
    return render_template('banned.html', banned_cards=banned_cards, subtitle=subtitle)

@app.route('/cards')
def cards():
    db = get_db()
    cards = db.execute("""
        SELECT card, COUNT(*) as play_count 
        FROM card 
        WHERE card IS NOT NULL 
        GROUP BY card 
        ORDER BY play_count DESC, card ASC
    """).fetchall()
    banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()]
    db.close()
    subtitle = "all cards"
    return render_template('cards.html', cards=cards, banned_cards=banned_cards, subtitle=subtitle)

# Template filter for url encoding
@app.template_filter('url_encode')
def url_encode_filter(s):
    return url_quote(str(s))

# Template filter for mana symbols using Mana font
@app.template_filter('mana_symbols')
def mana_symbols_filter(s):
    if not s or s == 'N/A':
        return s

    import re

    # Parse mana symbols like {W}, {U}, {1}, {2/U}, etc.
    def replace_symbol(match):
        symbol = match.group(1).lower()

        # Handle hybrid mana (e.g., {W/U}, {2/W})
        if '/' in symbol:
            parts = symbol.split('/')
            # Check if first part is a number (e.g., 2/W for hybrid)
            if parts[0].isdigit():
                return f'<i class="ms ms-{parts[1]} ms-cost ms-shadow"></i>'
            else:
                return f'<i class="ms ms-{parts[0]}{parts[1]} ms-cost ms-shadow"></i>'
        # Handle Phyrexian mana (e.g., {W/P})
        elif 'p' in symbol:
            color = symbol.replace('/p', '').replace('p', '')
            return f'<i class="ms ms-{color} ms-p ms-cost ms-shadow"></i>'
        # Regular mana
        else:
            return f'<i class="ms ms-{symbol} ms-cost ms-shadow"></i>'

    # Replace all mana symbols
    result = re.sub(r'\{([^}]+)\}', replace_symbol, s)

    return result

create_mview = """CREATE table round_score as
select deck.round, deck.player,
card1,
card2,
card3,
m1.cost as card1_cost,
m1.card_type as card1_type,
m2.cost as card2_cost,
m2.card_type as card2_type,
m3.cost as card3_cost,
m3.card_type as card3_type,
min(match.group_name) as prelim_group,
sum(case when match.group_name != 'final' then match.score else 0 end) as prelim_score,
sum(case when match.group_name = 'final' then match.score else null end) as final_score,
rank.rank,
count(*) as num_players
from deck join match
on deck.round = match.round and deck.player = match.player
join rank
on deck.round = rank.round and deck.player = rank.player
left join mtg m1 on deck.card1 = m1.name
left join mtg m2 on deck.card2 = m2.name
left join mtg m3 on deck.card3 = m3.name
group by 1,2
"""

def init_db():
    """Initialize the materialized view"""
    db = get_db()
    db.execute("drop table if exists round_score")
    db.execute(create_mview)
    db.close()

if __name__ == '__main__':
    # setup "materialized view"
    init_db()
    app.run(debug=False)