import os
from dotenv import load_dotenv
load_dotenv()
from flask import Flask, render_template_string
import pymysql

app = Flask(__name__)

HTML_TEMPLATE = """
<!DOCTYPE html>
<html lang="vi">
<head>
    <meta charset="UTF-8">
    <title>Flask + MySQL - cPanel Deploy Test</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { font-family: 'Segoe UI', sans-serif; background: #0f172a; color: #e2e8f0; min-height: 100vh; display: flex; justify-content: center; align-items: center; }
        .container { max-width: 700px; width: 90%; }
        h1 { text-align: center; margin-bottom: 2rem; color: #38bdf8; font-size: 1.8rem; }
        .card { background: #1e293b; border-radius: 12px; padding: 1.5rem; margin-bottom: 1.5rem; border-left: 4px solid #334155; }
        .card h2 { font-size: 1.1rem; margin-bottom: 0.8rem; color: #94a3b8; }
        .status { display: flex; align-items: center; gap: 0.5rem; margin-bottom: 0.5rem; font-size: 0.95rem; }
        .dot { width: 10px; height: 10px; border-radius: 50%; flex-shrink: 0; }
        .dot.green { background: #22c55e; box-shadow: 0 0 6px #22c55e; }
        .dot.red { background: #ef4444; box-shadow: 0 0 6px #ef4444; }
        .dot.yellow { background: #eab308; box-shadow: 0 0 6px #eab308; }
        table { width: 100%; border-collapse: collapse; margin-top: 0.5rem; }
        th, td { text-align: left; padding: 0.5rem 0.8rem; border-bottom: 1px solid #334155; font-size: 0.9rem; }
        th { color: #94a3b8; font-weight: 600; }
        .badge { display: inline-block; padding: 2px 10px; border-radius: 20px; font-size: 0.8rem; font-weight: 600; }
        .badge-ok { background: #166534; color: #86efac; }
        .badge-err { background: #7f1d1d; color: #fca5a5; }
        footer { text-align: center; color: #475569; font-size: 0.8rem; margin-top: 1rem; }
    </style>
</head>
<body>
    <div class="container">
        <h1>Flask + MySQL cPanel Test</h1>

        <div class="card">
            <h2>System Info</h2>
            <div class="status"><span class="dot green"></span> Python: {{ python_version }}</div>
            <div class="status"><span class="dot green"></span> Flask: {{ flask_version }}</div>
            <div class="status"><span class="dot green"></span> Server: {{ server_name }}</div>
            <div class="status"><span class="dot green"></span> Environment: {{ env }}</div>
        </div>

        <div class="card">
            <h2>MySQL Connection Test</h2>
            <div class="status">
                {% if db_ok %}
                    <span class="dot green"></span>
                    <span>Connected <span class="badge badge-ok">OK</span></span>
                {% else %}
                    <span class="dot red"></span>
                    <span>Failed <span class="badge badge-err">ERROR</span></span>
                {% endif %}
            </div>
            {% if db_error %}
                <p style="color:#f87171; font-size:0.85rem; margin-top:0.5rem;">{{ db_error }}</p>
            {% endif %}
            {% if db_ok %}
                <p style="color:#94a3b8; font-size:0.85rem; margin-top:0.5rem;">Host: {{ db_host }} | Database: {{ db_name }}</p>
            {% endif %}
        </div>

        {% if db_ok %}
        <div class="card">
            <h2>Test Table (flask_test)</h2>
            <div class="status"><span class="dot {{ 'green' if table_ok else 'yellow' }}"></span> {{ table_msg }}</div>
            {% if rows %}
            <table>
                <thead><tr><th>ID</th><th>Name</th><th>Created</th></tr></thead>
                <tbody>
                {% for r in rows %}
                <tr><td>{{ r[0] }}</td><td>{{ r[1] }}</td><td>{{ r[2] }}</td></tr>
                {% endfor %}
                </tbody>
            </table>
            {% endif %}
        </div>
        {% endif %}

        <footer>Deployed on cPanel &bull; {{ now }}</footer>
    </div>
</body>
</html>
"""


def get_db_connection():
    return pymysql.connect(
        host=os.environ.get("DB_HOST", "localhost"),
        user=os.environ.get("DB_USER", "root"),
        password=os.environ.get("DB_PASS", ""),
        database=os.environ.get("DB_NAME", ""),
        charset="utf8mb4",
        cursorclass=pymysql.cursors.Cursor,
    )


@app.route("/")
def index():
    import sys, flask, datetime

    context = {
        "python_version": sys.version.split()[0],
        "flask_version": flask.__version__,
        "server_name": os.environ.get("SERVER_NAME", "unknown"),
        "env": os.environ.get("FLASK_ENV", "production"),
        "db_ok": False,
        "db_error": "",
        "db_host": os.environ.get("DB_HOST", "localhost"),
        "db_name": os.environ.get("DB_NAME", ""),
        "table_ok": False,
        "table_msg": "",
        "rows": [],
        "now": datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
    }

    db_name = os.environ.get("DB_NAME", "")
    if not db_name:
        context["db_error"] = "DB_NAME env var not set. Configure it in cPanel or .env file."
        return render_template_string(HTML_TEMPLATE, **context)

    try:
        conn = get_db_connection()
        context["db_ok"] = True
        cur = conn.cursor()

        cur.execute("""
            CREATE TABLE IF NOT EXISTS flask_test (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()
        context["table_ok"] = True
        context["table_msg"] = "Table ready"

        cur.execute("INSERT INTO flask_test (name) VALUES (%s)", (f"test_{datetime.datetime.now().strftime('%H%M%S')}",))
        conn.commit()

        cur.execute("SELECT id, name, created_at FROM flask_test ORDER BY id DESC LIMIT 10")
        context["rows"] = cur.fetchall()
        cur.close()
        conn.close()
    except Exception as e:
        context["db_error"] = str(e)

    return render_template_string(HTML_TEMPLATE, **context)
