#!/usr/bin/env python3

import os
import time
import logging
import mysql.connector
from mysql.connector import Error


# ============================================================
# EC CAPTURE AUTO DELETE
# ============================================================
#
# Fungsi:
#   1. Mencari data ec_capture yang lebih tua dari 2 jam
#   2. Menghapus file JPG berdasarkan name_file
#   3. Menghapus record database
#
# Contoh:
#   Sekarang 19:25
#
#   Batas waktu:
#   17:25
#
#   Yang dihapus:
#       date_at < 17:25
#
#   Yang dipertahankan:
#       date_at >= 17:25
#
# ============================================================


# ============================================================
# DATABASE CONFIGURATION
# ============================================================

DB_CONFIG = {
    "host": "127.0.0.1",
    "port": 3306,
    "database": "horek940_mkits2024",
    "user": "horek940_mkits2024",
    "password": "G@ruda75772024",
}


# ============================================================
# CAPTURE DIRECTORY
# ============================================================

CAPTURE_DIR = "/home/horek940/public_html/eceng/capture"


# ============================================================
# DELETE RULE
# ============================================================

# Hapus data yang lebih tua dari 2 jam
DELETE_AFTER_HOURS = 2


# ============================================================
# CHECK INTERVAL
# ============================================================

# Pemeriksaan dilakukan setiap 30 detik
CHECK_INTERVAL = 1800


# ============================================================
# BATCH SIZE
# ============================================================

# Jumlah record maksimal yang diproses dalam satu batch.
#
# Ini supaya kalau database memiliki ribuan data lama,
# service tidak langsung memproses semuanya sekaligus.
#
BATCH_SIZE = 100


# ============================================================
# LOG FILE
# ============================================================

LOG_FILE = (
    "/home/horek940/public_html/eceng/recognized/"
    "delete_data_capture.log"
)


# ============================================================
# LOGGING
# ============================================================

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    handlers=[
        logging.FileHandler(LOG_FILE),
        logging.StreamHandler()
    ]
)

logger = logging.getLogger("delete_data_capture")


# ============================================================
# DATABASE CONNECTION
# ============================================================

def get_connection():

    try:

        connection = mysql.connector.connect(
            host=DB_CONFIG["host"],
            port=DB_CONFIG["port"],
            database=DB_CONFIG["database"],
            user=DB_CONFIG["user"],
            password=DB_CONFIG["password"],
            autocommit=False
        )

        if connection.is_connected():

            return connection

    except Error as e:

        logger.error(
            f"DATABASE CONNECTION ERROR: {e}"
        )

    return None


# ============================================================
# GET OLD RECORDS
# ============================================================

def get_old_records(connection):

    cursor = None

    try:

        cursor = connection.cursor(dictionary=True)

        query = """
            SELECT
                id,
                name_file,
                date_at
            FROM ec_capture
            WHERE date_at < DATE_SUB(NOW(), INTERVAL %s HOUR)
            ORDER BY date_at ASC
            LIMIT %s
        """

        cursor.execute(
            query,
            (
                DELETE_AFTER_HOURS,
                BATCH_SIZE
            )
        )

        records = cursor.fetchall()

        return records

    except Error as e:

        logger.error(
            f"FAILED TO READ OLD RECORDS: {e}"
        )

        return []

    finally:

        if cursor:
            cursor.close()


# ============================================================
# DELETE ONE RECORD
# ============================================================

def delete_record(connection, record):

    record_id = record["id"]
    name_file = record["name_file"]
    date_at = record["date_at"]

    logger.info(
        f"PROCESS ID={record_id} | "
        f"date_at={date_at} | "
        f"file={name_file}"
    )

    # ========================================================
    # SECURITY CHECK
    # ========================================================
    #
    # name_file harus benar-benar nama file.
    #
    # Contoh valid:
    #   20261008_103835.jpg
    #
    # Contoh tidak valid:
    #   ../../file.jpg
    #   /tmp/file.jpg
    #
    # ========================================================

    if not name_file:

        logger.error(
            f"INVALID EMPTY FILENAME | ID={record_id}"
        )

        return False

    if os.path.basename(name_file) != name_file:

        logger.error(
            f"INVALID FILENAME | "
            f"ID={record_id} | "
            f"name_file={name_file}"
        )

        return False

    # ========================================================
    # FORCE JPG LOCATION
    # ========================================================

    file_path = os.path.join(
        CAPTURE_DIR,
        name_file
    )

    # ========================================================
    # DELETE FILE
    # ========================================================

    if os.path.exists(file_path):

        if not os.path.isfile(file_path):

            logger.error(
                f"NOT A FILE | "
                f"ID={record_id} | "
                f"path={file_path}"
            )

            return False

        try:

            os.remove(file_path)

            logger.info(
                f"FILE DELETED | "
                f"ID={record_id} | "
                f"{file_path}"
            )

        except OSError as e:

            logger.error(
                f"FILE DELETE FAILED | "
                f"ID={record_id} | "
                f"{file_path} | "
                f"{e}"
            )

            # Jangan hapus database jika file gagal dihapus.
            return False

    else:

        # ====================================================
        # FILE SUDAH TIDAK ADA
        # ====================================================
        #
        # Kita tetap hapus database record.
        #
        # Kalau file sudah tidak ada, tidak ada gunanya
        # mempertahankan record lama tersebut.
        #
        logger.warning(
            f"FILE NOT FOUND | "
            f"ID={record_id} | "
            f"{file_path}"
        )

    # ========================================================
    # DELETE DATABASE RECORD
    # ========================================================

    cursor = None

    try:

        cursor = connection.cursor()

        query = """
            DELETE FROM ec_capture
            WHERE id = %s
        """

        cursor.execute(
            query,
            (record_id,)
        )

        # Pastikan memang ada record yang dihapus
        if cursor.rowcount != 1:

            logger.warning(
                f"DB DELETE AFFECTED {cursor.rowcount} ROW(S) | "
                f"ID={record_id}"
            )

        connection.commit()

        logger.info(
            f"DB DELETED | "
            f"ID={record_id} | "
            f"FILE={name_file}"
        )

        return True

    except Error as e:

        connection.rollback()

        logger.error(
            f"DB DELETE FAILED | "
            f"ID={record_id} | "
            f"{e}"
        )

        return False

    finally:

        if cursor:
            cursor.close()


# ============================================================
# CLEANUP
# ============================================================

def cleanup():

    connection = get_connection()

    if not connection:

        return

    try:

        total_deleted = 0
        total_failed = 0

        while True:

            records = get_old_records(connection)

            if not records:

                break

            logger.info(
                f"FOUND {len(records)} OLD RECORD(S)"
            )

            for record in records:

                try:

                    result = delete_record(
                        connection,
                        record
                    )

                    if result:

                        total_deleted += 1

                    else:

                        total_failed += 1

                except Exception as e:

                    total_failed += 1

                    logger.exception(
                        f"UNEXPECTED ERROR ID="
                        f"{record.get('id')}: {e}"
                    )

            # Jika batch tidak penuh, berarti kemungkinan
            # sudah tidak ada data lama lagi.
            if len(records) < BATCH_SIZE:

                break

        if total_deleted > 0 or total_failed > 0:

            logger.info(
                f"CLEANUP FINISHED | "
                f"deleted={total_deleted} | "
                f"failed={total_failed}"
            )

    except Exception as e:

        logger.exception(
            f"CLEANUP ERROR: {e}"
        )

    finally:

        if connection.is_connected():

            connection.close()


# ============================================================
# MAIN
# ============================================================

def main():

    logger.info("=" * 70)

    logger.info(
        "EC CAPTURE DELETE SERVICE STARTED"
    )

    logger.info(
        f"Capture directory : {CAPTURE_DIR}"
    )

    logger.info(
        f"Delete older than : {DELETE_AFTER_HOURS} hours"
    )

    logger.info(
        f"Check interval    : {CHECK_INTERVAL} seconds"
    )

    logger.info(
        f"Batch size        : {BATCH_SIZE}"
    )

    logger.info("=" * 70)

    while True:

        try:

            cleanup()

        except KeyboardInterrupt:

            logger.info(
                "SERVICE STOPPED BY USER"
            )

            break

        except Exception as e:

            logger.exception(
                f"MAIN LOOP ERROR: {e}"
            )

        time.sleep(
            CHECK_INTERVAL
        )


# ============================================================
# START
# ============================================================

if __name__ == "__main__":

    main()