import os
import mysql.connector
from mysql.connector import errorcode
from supabase import create_client, Client
from dotenv import load_dotenv

# Load environment variables from .env file using absolute path relative to this script
current_dir = os.path.dirname(os.path.abspath(__file__))
dotenv_path = os.path.join(current_dir, ".env")
load_dotenv(dotenv_path=dotenv_path)


# ==============================================================================
# Schema (unified single table):
#
# CREATE TABLE IF NOT EXISTS data_fcgntion (
#     id_tabel  INT NOT NULL AUTO_INCREMENT,
#     create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
#     person    VARCHAR(128) NULL,
#     rfid_uid  VARCHAR(64)  NULL DEFAULT NULL,
#     fc        JSON         NULL DEFAULT NULL,
#     PRIMARY KEY (id_tabel),
#     UNIQUE KEY uniq_rfid (rfid_uid),
#     KEY idx_person (person)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
#
# CREATE TABLE IF NOT EXISTS attendance_log (
#     id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
#     uid             VARCHAR(32) NOT NULL,
#     recognized_name VARCHAR(128) NULL,
#     face_ok         TINYINT(1) NOT NULL,
#     created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
#     PRIMARY KEY (id),
#     KEY idx_uid_time (uid, created_at)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# ==============================================================================


def get_connection_from_env():
    """Connect to MySQL from .env config. Auto-creates the database if missing."""
    cfg = {
        "user": os.environ.get("DB_USER", "root"),
        "password": os.environ.get("DB_PASSWORD", ""),
        "host": os.environ.get("DB_HOST", "127.0.0.1"),
        "port": int(os.environ.get("DB_PORT", 3306)),
        "database": os.environ.get("DB_NAME", "face_db"),
    }
    try:
        conn = mysql.connector.connect(**cfg)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_BAD_DB_ERROR:
            tmp_cfg = cfg.copy()
            tmp_cfg.pop("database")
            conn = mysql.connector.connect(**tmp_cfg)
            cursor = conn.cursor()
            cursor.execute(f"CREATE DATABASE {cfg['database']}")
            conn.database = cfg["database"]
        else:
            raise
    return conn


def get_supabase_client() -> "Client | None":
    """Get Supabase client from environment variables."""
    url = os.environ.get("SUPABASE_URL")
    key = os.environ.get("SUPABASE_ANON_KEY")
    if not url or not key:
        return None
    try:
        return create_client(url, key)
    except Exception as e:
        print(f"[SUPABASE] Error creating client: {e}")
        return None


# ==============================================================================
# TABLE SETUP
# ==============================================================================

def ensure_data_table(conn):
    """Ensure unified data_fcgntion table and attendance_log table exist."""
    cursor = conn.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS data_fcgntion (
            id_tabel  INT NOT NULL AUTO_INCREMENT,
            create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            person    VARCHAR(128) NULL,
            rfid_uid  VARCHAR(64)  NULL DEFAULT NULL,
            fc        JSON         NULL DEFAULT NULL,
            PRIMARY KEY (id_tabel),
            UNIQUE KEY uniq_rfid (rfid_uid),
            KEY idx_person (person)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """
    )
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS attendance_log (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            uid             VARCHAR(32) NOT NULL,
            recognized_name VARCHAR(128) NULL,
            face_ok         TINYINT(1) NOT NULL,
            created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_uid_time (uid, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """
    )
    conn.commit()


# ==============================================================================
# MODE DAFTAR - Enrollment Functions
# ==============================================================================

def get_or_create_data_row(conn, name: str) -> int:
    """Get or create a row in data_fcgntion by person name. Returns id_tabel."""
    name = (name or "").strip()
    if not name:
        raise ValueError("name is empty")
    cursor = conn.cursor()
    cursor.execute("SELECT id_tabel FROM data_fcgntion WHERE person=%s LIMIT 1", (name,))
    row = cursor.fetchone()
    if row:
        return int(row[0])
    cursor.execute("INSERT INTO data_fcgntion (person) VALUES (%s)", (name,))
    conn.commit()
    return int(cursor.lastrowid)


def save_or_update_person(conn, name: str, embedding_json_str: str,
                           rfid_uid=None) -> int:
    """Save face embedding for a person. Updates existing row or inserts new.
    Returns id_tabel.
    """
    name = (name or "").strip()
    cursor = conn.cursor()
    cursor.execute("SELECT id_tabel FROM data_fcgntion WHERE person=%s LIMIT 1", (name,))
    row = cursor.fetchone()
    if row:
        row_id = int(row[0])
        if rfid_uid:
            cursor.execute(
                "UPDATE data_fcgntion SET fc=%s, rfid_uid=COALESCE(%s, rfid_uid) WHERE id_tabel=%s",
                (embedding_json_str, rfid_uid, row_id),
            )
        else:
            cursor.execute(
                "UPDATE data_fcgntion SET fc=%s WHERE id_tabel=%s",
                (embedding_json_str, row_id),
            )
        conn.commit()
        return row_id
    else:
        cursor.execute(
            "INSERT INTO data_fcgntion (person, rfid_uid, fc) VALUES (%s, %s, %s)",
            (name, rfid_uid, embedding_json_str),
        )
        conn.commit()
        return int(cursor.lastrowid)


def upsert_person_rfid(conn, rfid_uid: str, name=None):
    """Insert or update rfid_uid in data_fcgntion.

    Priority:
      1. rfid_uid already in table -> update name if given, done.
      2. row with matching name exists and no rfid -> assign rfid_uid.
      3. Otherwise insert new row.
    """
    rfid_uid = (rfid_uid or "").strip().upper()
    if not rfid_uid:
        return
    cursor = conn.cursor()
    cursor.execute("SELECT id_tabel FROM data_fcgntion WHERE rfid_uid=%s LIMIT 1", (rfid_uid,))
    existing = cursor.fetchone()
    if existing:
        if name:
            cursor.execute(
                "UPDATE data_fcgntion SET person=COALESCE(%s, person) WHERE id_tabel=%s",
                (name, int(existing[0])),
            )
        conn.commit()
        return
    if name:
        cursor.execute(
            "SELECT id_tabel FROM data_fcgntion WHERE person=%s AND rfid_uid IS NULL LIMIT 1",
            (name,),
        )
        row = cursor.fetchone()
        if row:
            cursor.execute(
                "UPDATE data_fcgntion SET rfid_uid=%s WHERE id_tabel=%s",
                (rfid_uid, int(row[0])),
            )
            conn.commit()
            return
    cursor.execute(
        "INSERT INTO data_fcgntion (person, rfid_uid) VALUES (%s, %s)",
        (name or rfid_uid, rfid_uid),
    )
    conn.commit()


def claim_rfid_by_name(conn, name: str, rfid_uid: str) -> bool:
    """Assign rfid_uid to a row where person=name and rfid_uid IS NULL.
    Returns True if a row was updated.
    """
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id_tabel FROM data_fcgntion WHERE person=%s AND rfid_uid IS NULL LIMIT 1",
        (name,),
    )
    row = cursor.fetchone()
    if not row:
        return False
    cursor.execute(
        "UPDATE data_fcgntion SET rfid_uid=%s WHERE id_tabel=%s",
        (rfid_uid.strip().upper(), int(row[0])),
    )
    conn.commit()
    return True


# ==============================================================================
# MODE ABSEN - Recognition / Attendance Functions
# ==============================================================================

def load_all_faces(conn) -> list:
    """Load all face embeddings from data_fcgntion where fc IS NOT NULL.
    Returns list of dicts: {id, name, rfid_uid, embedding}.
    """
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id_tabel, person, rfid_uid, fc FROM data_fcgntion WHERE fc IS NOT NULL"
    )
    rows = cursor.fetchall()
    result = []
    for r in rows:
        result.append({"id": r[0], "name": r[1], "rfid_uid": r[2], "embedding": r[3]})
    return result


def get_person_by_rfid(conn, rfid_uid):
    """Get data_fcgntion row by rfid_uid. Returns dict or None."""
    if rfid_uid is None:
        return None
    rfid_uid = rfid_uid.strip().upper()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id_tabel, person, rfid_uid, fc, create_at FROM data_fcgntion WHERE rfid_uid=%s LIMIT 1",
        (rfid_uid,),
    )
    row = cursor.fetchone()
    if not row:
        return None
    return {
        "id": row[0],
        "person": row[1],
        "rfid_uid": row[2],
        "embedding": row[3],          # <-- fc field (JSON embedding)
        "has_face": row[3] is not None,
        "create_at": row[4],
    }


def get_person_by_name(conn, name: str):
    """Get data_fcgntion row by person name. Returns dict or None."""
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id_tabel, person, rfid_uid, fc, create_at FROM data_fcgntion WHERE person=%s LIMIT 1",
        (name.strip(),),
    )
    row = cursor.fetchone()
    if not row:
        return None
    return {
        "id": row[0],
        "person": row[1],
        "rfid_uid": row[2],
        "has_face": row[3] is not None,
        "create_at": row[4],
    }


def log_attendance(conn, uid: str, recognized_name, face_ok: bool):
    """Log an attendance event and update create_at on matching data_fcgntion row."""
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO attendance_log (uid, recognized_name, face_ok) VALUES (%s, %s, %s)",
        (uid, recognized_name, 1 if face_ok else 0),
    )
    cursor.execute(
        "UPDATE data_fcgntion SET create_at=CURRENT_TIMESTAMP WHERE UPPER(TRIM(rfid_uid))=UPPER(TRIM(%s))",
        (uid,),
    )
    rows_affected = cursor.rowcount
    conn.commit()
    print(f"[ATTENDANCE] uid={uid} name={recognized_name} face_ok={face_ok} rows={rows_affected}")
    if rows_affected == 0:
        print(f"[ATTENDANCE] WARNING: no data_fcgntion row matched rfid_uid={uid}")
    if recognized_name and recognized_name != "Unknown":
        send_attendance_to_supabase(recognized_name)


def send_attendance_to_supabase(recognized_name: str):
    """Send attendance record to Supabase output_alat table (non-blocking background thread)."""
    import threading
    import requests

    def _push():
        try:
            url = os.environ.get("SUPABASE_URL", "").rstrip("/")
            key = os.environ.get("SUPABASE_ANON_KEY", "")
            if not url or not key:
                return
            headers = {
                "apikey": key,
                "Authorization": f"Bearer {key}",
                "Content-Type": "application/json",
                "Prefer": "return=minimal",
            }
            resp = requests.post(f"{url}/rest/v1/output_alat", headers=headers,
                                 json={"nama_user": recognized_name}, timeout=10)
            if resp.status_code in (200, 201):
                print(f"[SUPABASE] sent nama_user='{recognized_name}' status={resp.status_code}")
            else:
                print(f"[SUPABASE] failed status={resp.status_code} body={resp.text[:200]}")
        except Exception as e:
            print(f"[SUPABASE] exception: {e}")

    threading.Thread(target=_push, daemon=True).start()


# ==============================================================================
# LIST / READ
# ==============================================================================

def list_all_persons(conn, limit: int = 200) -> list:
    """List all rows from data_fcgntion ordered newest first."""
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id_tabel, person, rfid_uid, create_at, "
        "CASE WHEN fc IS NOT NULL THEN 1 ELSE 0 END AS has_face "
        "FROM data_fcgntion ORDER BY create_at DESC LIMIT %s",
        (int(limit),),
    )
    rows = cursor.fetchall()
    result = []
    for r in rows:
        result.append({
            "id": r[0],
            "person": r[1],
            "rfid_uid": r[2],
            "create_at": r[3],
            "has_face": bool(r[4]),
        })
    return result


def list_attendance_log(conn, limit: int = 200) -> list:
    """List attendance log entries ordered newest first."""
    cursor = conn.cursor()
    cursor.execute(
        "SELECT uid, recognized_name, face_ok, created_at "
        "FROM attendance_log ORDER BY created_at DESC LIMIT %s",
        (int(limit),),
    )
    rows = cursor.fetchall()
    result = []
    for r in rows:
        result.append({
            "uid": r[0],
            "recognized_name": r[1],
            "face_ok": bool(r[2]),
            "created_at": r[3],
        })
    return result


# ==============================================================================
# MODE HAPUS - Delete / Cleanup
# ==============================================================================

def delete_person_by_rfid(conn, rfid_uid: str):
    """Delete data_fcgntion row by rfid_uid."""
    cursor = conn.cursor()
    cursor.execute("DELETE FROM data_fcgntion WHERE rfid_uid=%s", (rfid_uid.strip().upper(),))
    conn.commit()


def delete_person_by_name(conn, name: str):
    """Delete data_fcgntion row(s) by person name."""
    cursor = conn.cursor()
    cursor.execute("DELETE FROM data_fcgntion WHERE person=%s", (name.strip(),))
    conn.commit()


def delete_unused_persons(conn) -> int:
    """Delete rows where BOTH fc IS NULL AND rfid_uid IS NULL.
    Returns number of rows deleted.
    """
    cursor = conn.cursor()
    cursor.execute("DELETE FROM data_fcgntion WHERE fc IS NULL AND rfid_uid IS NULL")
    count = cursor.rowcount
    conn.commit()
    print(f"[CLEANUP] Deleted {count} unused rows (fc=NULL, rfid=NULL).")
    return count


def delete_old_attendance_logs(conn, days: int = 30) -> int:
    """Delete attendance_log rows older than `days` days.
    Returns number of rows deleted.
    """
    cursor = conn.cursor()
    cursor.execute(
        "DELETE FROM attendance_log WHERE created_at < NOW() - INTERVAL %s DAY",
        (int(days),),
    )
    count = cursor.rowcount
    conn.commit()
    print(f"[CLEANUP] Deleted {count} attendance log rows older than {days} days.")
    return count