import os
import sqlite3
import random
import string
import datetime
from flask import Flask, render_template, request, jsonify, redirect, url_for

app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False

DATA_DIR = os.path.join(os.path.dirname(__file__), 'data')
os.makedirs(DATA_DIR, exist_ok=True)
DB_PATH = os.path.join(DATA_DIR, 'loyalty.db')

def get_db():
    conn = sqlite3.connect(DB_PATH, timeout=10.0)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA busy_timeout = 10000")
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

def init_db():
    conn = get_db()
    with conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS settings (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                storeName TEXT NOT NULL,
                storeSlogan TEXT,
                programType TEXT NOT NULL DEFAULT 'STAMPS',
                stampsGoal INTEGER NOT NULL DEFAULT 10,
                rewardDescription TEXT NOT NULL,
                pointsPerEuro REAL NOT NULL DEFAULT 1.0,
                pointsGoal INTEGER NOT NULL DEFAULT 100,
                primaryColor TEXT NOT NULL DEFAULT '#6366f1',
                secondaryColor TEXT DEFAULT '#8b5cf6',
                cardBgColor TEXT DEFAULT '#0f172a',
                textColor TEXT DEFAULT '#ffffff',
                themePreset TEXT DEFAULT 'indigo',
                cardTheme TEXT DEFAULT 'dark',
                logoUrl TEXT,
                iconType TEXT NOT NULL DEFAULT 'coffee',
                operatorPin TEXT NOT NULL DEFAULT '1234',
                updatedAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS customers (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                phone TEXT UNIQUE NOT NULL,
                email TEXT,
                cardToken TEXT UNIQUE NOT NULL,
                stamps INTEGER NOT NULL DEFAULT 0,
                totalStampsEarned INTEGER NOT NULL DEFAULT 0,
                points INTEGER NOT NULL DEFAULT 0,
                totalPointsEarned INTEGER NOT NULL DEFAULT 0,
                rewardsRedeemed INTEGER NOT NULL DEFAULT 0,
                totalVisits INTEGER NOT NULL DEFAULT 0,
                notes TEXT,
                createdAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                lastVisitAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS transactions (
                id TEXT PRIMARY KEY,
                customerId TEXT NOT NULL,
                type TEXT NOT NULL,
                stampsDelta INTEGER NOT NULL DEFAULT 0,
                pointsDelta INTEGER NOT NULL DEFAULT 0,
                amountEuros REAL,
                description TEXT,
                operatorNote TEXT,
                createdAt TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (customerId) REFERENCES customers(id) ON DELETE CASCADE
            )
        """)

        # Migration columns if table already existed
        cursor = conn.execute("PRAGMA table_info(settings)")
        existing_cols = {row['name'] for row in cursor.fetchall()}
        columns_to_add = [
            ("logoUrl", "TEXT"),
            ("themePreset", "TEXT DEFAULT 'indigo'"),
            ("cardTheme", "TEXT DEFAULT 'dark'"),
            ("secondaryColor", "TEXT DEFAULT '#8b5cf6'"),
            ("cardBgColor", "TEXT DEFAULT '#0f172a'"),
            ("textColor", "TEXT DEFAULT '#ffffff'")
        ]
        for col_name, col_type in columns_to_add:
            if col_name not in existing_cols:
                try:
                    conn.execute(f"ALTER TABLE settings ADD COLUMN {col_name} {col_type}")
                except Exception:
                    pass

        # Seed initial data if empty
        settings_count = conn.execute("SELECT COUNT(*) as count FROM settings").fetchone()['count']
        if settings_count == 0:
            conn.execute("""
                INSERT INTO settings (id, storeName, storeSlogan, programType, stampsGoal, rewardDescription, pointsPerEuro, pointsGoal, primaryColor, secondaryColor, cardBgColor, textColor, themePreset, cardTheme, logoUrl, iconType, operatorPin)
                VALUES (1, 'Café & Bistrô Central', 'O seu ponto de encontro com sabor', 'STAMPS', 10, '1 Café Especial ou Fatia de Bolo à escolha', 1.0, 100, '#6366f1', '#8b5cf6', '#0f172a', '#ffffff', 'indigo', 'dark', NULL, 'coffee', '1234')
            """)

        cust_count = conn.execute("SELECT COUNT(*) as count FROM customers").fetchone()['count']
        if cust_count == 0:
            samples = [
                ('cust_1', 'Ana Silva', '912345678', 'ana.silva@exemplo.pt', 'ana-silva-7821', 8, 18, 80, 180, 1, 18, 'Cliente habitual'),
                ('cust_2', 'João Pedro Santos', '934567890', 'joao.santos@exemplo.pt', 'joao-santos-4392', 10, 20, 100, 200, 1, 20, 'Pronto a resgatar!'),
                ('cust_3', 'Mariana Costa', '965432109', 'mariana.costa@exemplo.pt', 'mariana-costa-1084', 3, 3, 30, 30, 0, 3, 'Novo cliente')
            ]
            for s in samples:
                conn.execute("""
                    INSERT INTO customers (id, name, phone, email, cardToken, stamps, totalStampsEarned, points, totalPointsEarned, rewardsRedeemed, totalVisits, notes, createdAt, lastVisitAt)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                """, s)
                conn.execute("""
                    INSERT INTO transactions (id, customerId, type, stampsDelta, pointsDelta, description, createdAt)
                    VALUES (?, ?, 'ADD_STAMP', ?, ?, 'Registo de selos iniciais', CURRENT_TIMESTAMP)
                """, (f"tx_{s[0]}_{random.randint(100,999)}", s[0], s[5], s[7]))

    conn.close()

# Initialize DB on start
init_db()

# --- WEB PAGE ROUTES ---

@app.route('/')
def home():
    conn = get_db()
    settings = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())
    conn.close()
    return render_template('index.html', settings=settings)

@app.route('/operador')
def operador():
    conn = get_db()
    settings = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())
    conn.close()
    return render_template('operador.html', settings=settings)

@app.route('/cartao/<token>')
def cartao(token):
    conn = get_db()
    settings = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())
    cust_row = conn.execute("SELECT * FROM customers WHERE cardToken = ? OR id = ? OR phone = ?", (token, token, token)).fetchone()
    if not cust_row:
        conn.close()
        return render_template('cartao_not_found.html', token=token, settings=settings), 404
    
    customer = dict(cust_row)
    tx_rows = conn.execute("SELECT * FROM transactions WHERE customerId = ? ORDER BY createdAt DESC LIMIT 20", (customer['id'],)).fetchall()
    transactions = [dict(r) for r in tx_rows]
    conn.close()
    return render_template('cartao.html', customer=customer, settings=settings, transactions=transactions)

@app.route('/admin')
def admin():
    conn = get_db()
    settings = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())
    conn.close()
    return render_template('admin.html', settings=settings)

# --- REST API ENDPOINTS ---

@app.route('/api/settings', methods=['GET', 'POST', 'PUT'])
def api_settings():
    conn = get_db()
    if request.method in ['POST', 'PUT']:
        data = request.get_json() or {}
        current = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())
        with conn:
            conn.execute("""
                UPDATE settings SET
                    storeName = ?,
                    storeSlogan = ?,
                    programType = ?,
                    stampsGoal = ?,
                    rewardDescription = ?,
                    pointsPerEuro = ?,
                    pointsGoal = ?,
                    primaryColor = ?,
                    secondaryColor = ?,
                    cardBgColor = ?,
                    textColor = ?,
                    themePreset = ?,
                    cardTheme = ?,
                    logoUrl = ?,
                    iconType = ?,
                    operatorPin = ?,
                    updatedAt = CURRENT_TIMESTAMP
                WHERE id = 1
            """, (
                data.get('storeName', current['storeName']),
                data.get('storeSlogan', current['storeSlogan']),
                data.get('programType', current['programType']),
                int(data.get('stampsGoal', current['stampsGoal'])),
                data.get('rewardDescription', current['rewardDescription']),
                float(data.get('pointsPerEuro', current['pointsPerEuro'])),
                int(data.get('pointsGoal', current['pointsGoal'])),
                data.get('primaryColor', current['primaryColor']),
                data.get('secondaryColor', current['secondaryColor']),
                data.get('cardBgColor', current['cardBgColor']),
                data.get('textColor', current['textColor']),
                data.get('themePreset', current['themePreset']),
                data.get('cardTheme', current['cardTheme']),
                data.get('logoUrl', current['logoUrl']),
                data.get('iconType', current['iconType']),
                data.get('operatorPin', current['operatorPin'])
            ))
    
    updated = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())
    conn.close()
    return jsonify({"success": True, "settings": updated})

@app.route('/api/customers', methods=['GET', 'POST'])
def api_customers():
    conn = get_db()
    if request.method == 'POST':
        data = request.get_json() or {}
        name = (data.get('name') or '').strip()
        phone = (data.get('phone') or '').strip().replace(' ', '').replace('-', '')
        email = (data.get('email') or '').strip() or None
        notes = (data.get('notes') or '').strip() or None

        if not name or not phone:
            conn.close()
            return jsonify({"success": False, "error": "Nome e Telefone são obrigatórios"}), 400

        existing = conn.execute("SELECT * FROM customers WHERE phone = ?", (phone,)).fetchone()
        if existing:
            conn.close()
            return jsonify({"success": False, "error": "Já existe um cliente com este telefone", "customer": dict(existing)}), 409

        cust_id = f"cust_{int(datetime.datetime.now().timestamp())}_{random.randint(100, 999)}"
        slug = ''.join(c for c in name.lower() if c.isalnum() or c == ' ').strip().replace(' ', '-')[:15]
        token = f"{slug}-{random.randint(1000, 9999)}"

        with conn:
            conn.execute("""
                INSERT INTO customers (id, name, phone, email, cardToken, stamps, totalStampsEarned, points, totalPointsEarned, rewardsRedeemed, totalVisits, notes, createdAt, lastVisitAt)
                VALUES (?, ?, ?, ?, ?, 0, 0, 0, 0, 0, 0, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
            """, (cust_id, name, phone, email, token, notes))

        created = dict(conn.execute("SELECT * FROM customers WHERE id = ?", (cust_id,)).fetchone())
        conn.close()
        return jsonify({"success": True, "customer": created})

    # GET: List or Search
    q = (request.args.get('q') or '').strip()
    if q:
        search = f"%{q}%"
        rows = conn.execute("""
            SELECT * FROM customers 
            WHERE name LIKE ? OR phone LIKE ? OR cardToken LIKE ?
            ORDER BY lastVisitAt DESC
        """, (search, search, search)).fetchall()
    else:
        rows = conn.execute("SELECT * FROM customers ORDER BY lastVisitAt DESC").fetchall()

    customers = [dict(r) for r in rows]
    conn.close()
    return jsonify({"success": True, "customers": customers})

@app.route('/api/customers/<idOrToken>', methods=['GET', 'PUT', 'DELETE'])
def api_customer_detail(idOrToken):
    conn = get_db()
    cust_row = conn.execute("SELECT * FROM customers WHERE id = ? OR cardToken = ? OR phone = ?", (idOrToken, idOrToken, idOrToken)).fetchone()
    if not cust_row:
        conn.close()
        return jsonify({"success": False, "error": "Cliente não encontrado"}), 404

    customer = dict(cust_row)

    if request.method == 'DELETE':
        with conn:
            conn.execute("DELETE FROM customers WHERE id = ?", (customer['id'],))
        conn.close()
        return jsonify({"success": True, "message": "Cliente eliminado com sucesso"})

    if request.method == 'PUT':
        data = request.get_json() or {}
        with conn:
            conn.execute("""
                UPDATE customers SET
                    name = COALESCE(?, name),
                    phone = COALESCE(?, phone),
                    email = ?,
                    notes = ?
                WHERE id = ?
            """, (data.get('name'), data.get('phone'), data.get('email'), data.get('notes'), customer['id']))
        customer = dict(conn.execute("SELECT * FROM customers WHERE id = ?", (customer['id'],)).fetchone())

    tx_rows = conn.execute("SELECT * FROM transactions WHERE customerId = ? ORDER BY createdAt DESC LIMIT 20", (customer['id'],)).fetchall()
    transactions = [dict(r) for r in tx_rows]
    conn.close()
    return jsonify({"success": True, "customer": customer, "transactions": transactions})

@app.route('/api/stamps/add', methods=['POST'])
def api_stamps_add():
    data = request.get_json() or {}
    cust_id = data.get('customerId')
    count = max(1, int(data.get('count', 1)))
    amount_euros = float(data.get('amountEuros')) if data.get('amountEuros') else None
    note = data.get('note')

    if not cust_id:
        return jsonify({"success": False, "error": "ID do cliente é obrigatório"}), 400

    conn = get_db()
    cust_row = conn.execute("SELECT * FROM customers WHERE id = ? OR cardToken = ? OR phone = ?", (cust_id, cust_id, cust_id)).fetchone()
    if not cust_row:
        conn.close()
        return jsonify({"success": False, "error": "Cliente não encontrado"}), 404

    customer = dict(cust_row)
    settings = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())

    points_delta = int(amount_euros * settings['pointsPerEuro']) if amount_euros else (count * 10)
    new_stamps = customer['stamps'] + count
    new_total_stamps = customer['totalStampsEarned'] + count
    new_points = customer['points'] + points_delta
    new_total_points = customer['totalPointsEarned'] + points_delta
    new_visits = customer['totalVisits'] + 1

    tx_id = f"tx_{int(datetime.datetime.now().timestamp())}_{random.randint(100, 999)}"
    desc = f"+{count} carimbo(s) atribuído(s)" + (f" (Compra: €{amount_euros:.2f})" if amount_euros else "")

    with conn:
        conn.execute("""
            UPDATE customers SET
                stamps = ?,
                totalStampsEarned = ?,
                points = ?,
                totalPointsEarned = ?,
                totalVisits = ?,
                lastVisitAt = CURRENT_TIMESTAMP
            WHERE id = ?
        """, (new_stamps, new_total_stamps, new_points, new_total_points, new_visits, customer['id']))

        conn.execute("""
            INSERT INTO transactions (id, customerId, type, stampsDelta, pointsDelta, amountEuros, description, operatorNote, createdAt)
            VALUES (?, ?, 'ADD_STAMP', ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
        """, (tx_id, customer['id'], count, points_delta, amount_euros, desc, note))

    updated_customer = dict(conn.execute("SELECT * FROM customers WHERE id = ?", (customer['id'],)).fetchone())
    conn.close()
    return jsonify({
        "success": True,
        "customer": updated_customer,
        "stampsAdded": count,
        "canRedeem": updated_customer['stamps'] >= settings['stampsGoal'],
        "message": f"{count} carimbo(s) atribuído(s) com sucesso!"
    })

@app.route('/api/stamps/redeem', methods=['POST'])
def api_stamps_redeem():
    data = request.get_json() or {}
    cust_id = data.get('customerId')
    note = data.get('note')

    if not cust_id:
        return jsonify({"success": False, "error": "ID do cliente é obrigatório"}), 400

    conn = get_db()
    cust_row = conn.execute("SELECT * FROM customers WHERE id = ? OR cardToken = ? OR phone = ?", (cust_id, cust_id, cust_id)).fetchone()
    if not cust_row:
        conn.close()
        return jsonify({"success": False, "error": "Cliente não encontrado"}), 404

    customer = dict(cust_row)
    settings = dict(conn.execute("SELECT * FROM settings WHERE id = 1").fetchone())

    if customer['stamps'] < settings['stampsGoal']:
        conn.close()
        return jsonify({"success": False, "error": f"O cliente tem {customer['stamps']} de {settings['stampsGoal']} carimbos necessários."}), 400

    new_stamps = customer['stamps'] - settings['stampsGoal']
    new_redeemed = customer['rewardsRedeemed'] + 1
    tx_id = f"tx_{int(datetime.datetime.now().timestamp())}_{random.randint(100, 999)}"

    with conn:
        conn.execute("""
            UPDATE customers SET
                stamps = ?,
                rewardsRedeemed = ?,
                lastVisitAt = CURRENT_TIMESTAMP
            WHERE id = ?
        """, (new_stamps, new_redeemed, customer['id']))

        conn.execute("""
            INSERT INTO transactions (id, customerId, type, stampsDelta, pointsDelta, description, operatorNote, createdAt)
            VALUES (?, ?, 'REDEEM_REWARD', ?, 0, ?, ?, CURRENT_TIMESTAMP)
        """, (tx_id, customer['id'], -settings['stampsGoal'], f"🎁 Recompensa Resgatada: {settings['rewardDescription']}", note))

    updated_customer = dict(conn.execute("SELECT * FROM customers WHERE id = ?", (customer['id'],)).fetchone())
    conn.close()
    return jsonify({
        "success": True,
        "customer": updated_customer,
        "rewardDescription": settings['rewardDescription'],
        "message": "Prémio resgatado com sucesso!"
    })

@app.route('/api/stamps/adjust', methods=['POST'])
def api_stamps_adjust():
    data = request.get_json() or {}
    cust_id = data.get('customerId')
    target_stamps = data.get('targetStamps')
    reason = data.get('reason')

    if not cust_id:
        return jsonify({"success": False, "error": "ID do cliente é obrigatório"}), 400

    conn = get_db()
    cust_row = conn.execute("SELECT * FROM customers WHERE id = ?", (cust_id,)).fetchone()
    if not cust_row:
        conn.close()
        return jsonify({"success": False, "error": "Cliente não encontrado"}), 404

    customer = dict(cust_row)
    new_stamps = max(0, int(target_stamps)) if target_stamps is not None else customer['stamps']
    stamps_delta = new_stamps - customer['stamps']
    tx_id = f"tx_{int(datetime.datetime.now().timestamp())}_{random.randint(100, 999)}"

    with conn:
        conn.execute("UPDATE customers SET stamps = ? WHERE id = ?", (new_stamps, customer['id']))
        conn.execute("""
            INSERT INTO transactions (id, customerId, type, stampsDelta, pointsDelta, description, operatorNote, createdAt)
            VALUES (?, ?, 'ADJUST_STAMPS', ?, 0, ?, ?, CURRENT_TIMESTAMP)
        """, (tx_id, customer['id'], stamps_delta, f"Ajuste manual pelo gestor: {stamps_delta:+d} carimbos", reason))

    updated_customer = dict(conn.execute("SELECT * FROM customers WHERE id = ?", (customer['id'],)).fetchone())
    conn.close()
    return jsonify({"success": True, "customer": updated_customer, "message": "Saldo ajustado com sucesso"})

@app.route('/api/stats')
def api_stats():
    conn = get_db()
    total_customers = conn.execute("SELECT COUNT(*) as count FROM customers").fetchone()['count']
    stamps_today = conn.execute("""
        SELECT COALESCE(SUM(stampsDelta), 0) as total FROM transactions 
        WHERE type = 'ADD_STAMP' AND date(createdAt) = date('now')
    """).fetchone()['total']
    stamps_month = conn.execute("""
        SELECT COALESCE(SUM(stampsDelta), 0) as total FROM transactions 
        WHERE type = 'ADD_STAMP' AND strftime('%Y-%m', createdAt) = strftime('%Y-%m', 'now')
    """).fetchone()['total']
    rewards_redeemed = conn.execute("SELECT COUNT(*) as count FROM transactions WHERE type = 'REDEEM_REWARD'").fetchone()['count']
    repeat_customers = conn.execute("SELECT COUNT(*) as count FROM customers WHERE totalVisits > 1").fetchone()['count']
    loyalty_rate = round((repeat_customers / total_customers * 100)) if total_customers > 0 else 0

    recent_txs = [dict(r) for r in conn.execute("""
        SELECT t.*, c.name as customerName, c.phone as customerPhone
        FROM transactions t
        JOIN customers c ON t.customerId = c.id
        ORDER BY t.createdAt DESC LIMIT 10
    """).fetchall()]

    top_customers = [dict(r) for r in conn.execute("""
        SELECT * FROM customers ORDER BY totalVisits DESC, totalStampsEarned DESC LIMIT 5
    """).fetchall()]

    conn.close()
    return jsonify({
        "success": True,
        "stats": {
            "totalCustomers": total_customers,
            "stampsGivenToday": stamps_today,
            "stampsGivenThisMonth": stamps_month,
            "rewardsRedeemedTotal": rewards_redeemed,
            "activeLoyaltyRate": loyalty_rate,
            "recentTransactions": recent_txs,
            "topCustomers": top_customers
        }
    })

@app.route('/api/transactions')
def api_transactions():
    conn = get_db()
    limit = int(request.args.get('limit', 50))
    cust_id = request.args.get('customerId')
    if cust_id:
        rows = conn.execute("""
            SELECT t.*, c.name as customerName, c.phone as customerPhone
            FROM transactions t
            JOIN customers c ON t.customerId = c.id
            WHERE t.customerId = ?
            ORDER BY t.createdAt DESC LIMIT ?
        """, (cust_id, limit)).fetchall()
    else:
        rows = conn.execute("""
            SELECT t.*, c.name as customerName, c.phone as customerPhone
            FROM transactions t
            JOIN customers c ON t.customerId = c.id
            ORDER BY t.createdAt DESC LIMIT ?
        """, (limit,)).fetchall()

    txs = [dict(r) for r in rows]
    conn.close()
    return jsonify({"success": True, "transactions": txs})

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)