app.py

from flask import Flask, render_template, request
import mysql.connector

app = Flask(__name__)

# ---------- CONFIG ----------
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "root@123",   # <- change if needed
    "database": "test_db"
}

db = mysql.connector.connect(**DB_CONFIG)
cursor = db.cursor(dictionary=True)

# ---------- HELPERS ----------
def get_tables():
    cursor.execute("SHOW TABLES")
    return [list(r.values())[0] for r in cursor.fetchall()]

def get_columns(table_name):
    cursor.execute(f"DESCRIBE {table_name}")
    return cursor.fetchall()  # list of dicts: Field, Type, Null, Key, Default, Extra

def get_primary_column(table_name):
    cursor.execute(f"SHOW KEYS FROM {table_name} WHERE Key_name='PRIMARY'")
    r = cursor.fetchone()
    return r['Column_name'] if r else None

def get_fk_map(table_name):
    sql = """
    SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
    FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND REFERENCED_TABLE_NAME IS NOT NULL
    """
    cursor.execute(sql, (DB_CONFIG["database"], table_name))
    res = cursor.fetchall()
    fk = {}
    for r in res:
        fk[r['COLUMN_NAME']] = {
            "ref_table": r['REFERENCED_TABLE_NAME'],
            "ref_column": r['REFERENCED_COLUMN_NAME']
        }
    return fk

def get_display_column_for_table(table_name):
    """
    Return a readable column name for building labels for dropdowns.
    Prefer common names, otherwise first VARCHAR column, else primary key.
    """
    cols = get_columns(table_name)
    # Preferred names
    for pref in ("name", "product_name", "designation", "Emergency_id"):
        for c in cols:
            if c['Field'].lower() == pref.lower():
                return c['Field']
    # pick first VARCHAR
    for c in cols:
        if 'varchar' in c['Type'].lower() or 'text' in c['Type'].lower():
            return c['Field']
    # fallback to primary key
    pk = None
    cursor.execute(f"SHOW KEYS FROM {table_name} WHERE Key_name='PRIMARY'")
    r = cursor.fetchone()
    if r:
        return r['Column_name']
    # else first column
    return cols[0]['Field'] if cols else None

def get_fk_options(table_name):
    fk_map = get_fk_map(table_name)
    options = {}

    for col, ref in fk_map.items():
        ref_table = ref['ref_table']
        ref_col = ref['ref_column']

        # Find a readable display column
        display_col = get_display_column_for_table(ref_table) or ref_col

        cursor.execute(f"""
            SELECT {ref_col} AS val, {display_col} AS lab 
            FROM {ref_table}
            LIMIT 1000
        """)

        rows = cursor.fetchall()
        opts = [(r['val'], f"{r['val']} — {r['lab']}") for r in rows]
        options[col] = opts

    return options

def get_fk_options(table_name):
    fk_map = get_fk_map(table_name)
    options = {}

    for col, ref in fk_map.items():
        ref_table = ref['ref_table']
        ref_col = ref['ref_column']

        # Find a readable display column
        display_col = get_display_column_for_table(ref_table) or ref_col

        cursor.execute(f"""
            SELECT {ref_col} AS val, {display_col} AS lab 
            FROM {ref_table}
            LIMIT 1000
        """)

        rows = cursor.fetchall()
        opts = [(r['val'], f"{r['val']} — {r['lab']}") for r in rows]
        options[col] = opts

    return options

def insert_row(table_name, data_dict):
    # remove empty dictionary
    if not data_dict:
        return
    cols = ", ".join(data_dict.keys())
    placeholders = ", ".join(["%s"] * len(data_dict))
    values = list(data_dict.values())
    sql = f"INSERT INTO {table_name} ({cols}) VALUES ({placeholders})"
    cursor.execute(sql, values)
    db.commit()

def delete_row(table_name, pk_value):
    pk_col = get_primary_column(table_name)
    if not pk_col:
        return
    cursor.execute(f"DELETE FROM {table_name} WHERE {pk_col} = %s", (pk_value,))
    db.commit()

# ---------- ROUTE ----------
@app.route("/", methods=["GET", "POST"])
def home():
    tables = get_tables()
    selected_table = None
    columns = None
    data = None
    msg = ""
    fk_options = {}

    # View table
    if request.method == "POST" and request.form.get("action") == "view":
        selected_table = request.form.get("table_name")
        columns = get_columns(selected_table)
        cursor.execute(f"SELECT * FROM {selected_table}")
        data = cursor.fetchall()
        fk_options = get_fk_options(selected_table)

    # Add record
    if request.method == "POST" and request.form.get("action") == "add_record":
        table_name = request.form.get("table_name")
        cols = get_columns(table_name)
        # build insert dict: include every column that is NOT auto_increment
        insert_data = {}
        for c in cols:
            field = c['Field']
            if "auto_increment" in c['Extra']:
                continue
            # read value from form
            v = request.form.get(field)
            # treat empty string as None
            if v is None or v == "":
                v = None
            insert_data[field] = v
        try:
            insert_row(table_name, insert_data)
            msg = "Record added successfully!"
        except mysql.connector.Error as e:
            msg = f"ERROR: {e.msg}"
        selected_table = table_name
        columns = get_columns(selected_table)
        cursor.execute(f"SELECT * FROM {selected_table}")
        data = cursor.fetchall()
        fk_options = get_fk_options(selected_table)

    # Delete record
    if request.method == "POST" and request.form.get("action") == "delete_record":
        table_name = request.form.get("table_name")
        pk_value = request.form.get("pk")
        try:
            delete_row(table_name, pk_value)
            msg = "Record deleted."
        except mysql.connector.Error as e:
            msg = f"ERROR: {e.msg}"
        selected_table = table_name
        columns = get_columns(selected_table)
        cursor.execute(f"SELECT * FROM {selected_table}")
        data = cursor.fetchall()
        fk_options = get_fk_options(selected_table)

    return render_template("home.html",
                           tables=tables,
                           selected_table=selected_table,
                           columns=columns,
                           data=data,
                           fk_options=fk_options,
                           msg=msg)

if __name__ == "__main__":
    app.run(debug=True)