# edited by glg
import csv
import os
import sqlite3
from datetime import datetime, timedelta

from pypos.core.utils.db_helper import connect_sqlite
from pypos.core.utils.path_utils import get_app_data_resource_dir, get_db_path
from pypos.core.utils.sql_query_builder import build_sql_with_identifier_in_clause


class HistoryCleanupService:
    def __init__(self):
        self.db_path = get_db_path()

    def cleanup_if_due(self) -> int:
        """Jalankan cleanup maksimal sekali per hari sesuai setting."""
        days = self._get_retention_days()
        if days <= 0:
            return 0
        today = datetime.now().strftime("%Y-%m-%d")
        last_run = self._get_setting("history_cleanup_last_run", "")
        if last_run == today:
            return 0
        deleted = self.cleanup_history(days)
        self._set_setting("history_cleanup_last_run", today)
        return deleted

    def cleanup_history(self, retention_days: int) -> int:
        """Hapus transaksi settled yang lebih lama dari retention_days."""
        cutoff = (datetime.now() - timedelta(days=int(retention_days))).strftime("%Y-%m-%d")
        conn = connect_sqlite(self.db_path)
        cur = conn.cursor()
        deleted = 0
        try:
            cur.execute(
                "SELECT id FROM transaksi WHERE DATE(dtime) < ? AND settlement_id != 1",
                (cutoff,),
            )
            rows = cur.fetchall()
            if not rows:
                conn.close()
                return 0
            ids = [r[0] for r in rows]

            delete_transaksi_data_sql, delete_transaksi_data_params = build_sql_with_identifier_in_clause(
                sql_prefix="DELETE FROM transaksi_data WHERE",
                sql_suffix="",
                identifier="transaksi_id",
                values=ids,
                cast_int=True,
                positive_only=True,
                unique=True,
            )
            cur.execute(delete_transaksi_data_sql, delete_transaksi_data_params)

            delete_transaksi_sql, delete_transaksi_params = build_sql_with_identifier_in_clause(
                sql_prefix="DELETE FROM transaksi WHERE",
                sql_suffix="",
                identifier="id",
                values=ids,
                cast_int=True,
                positive_only=True,
                unique=True,
            )
            cur.execute(delete_transaksi_sql, delete_transaksi_params)

            deleted = len(ids)
            conn.commit()
        except (sqlite3.Error, TypeError, ValueError):
            conn.rollback()
            raise
        finally:
            conn.close()
        return deleted

    def _get_retention_days(self) -> int:
        raw = self._get_setting("history_retention_days", "")
        try:
            return int(raw)
        except (TypeError, ValueError):
            return 30

    def _get_setting(self, key: str, default: str) -> str:
        csv_path = os.path.join(get_app_data_resource_dir(), "setting_struk.csv")
        csv_path = os.path.abspath(csv_path)
        if not os.path.exists(csv_path):
            return default
        try:
            with open(csv_path, "r", encoding="utf-8", newline="") as f:
                reader = csv.reader(f)
                for row in reader:
                    if len(row) >= 2 and row[0].strip() == key:
                        return row[1].strip()
        except (OSError, UnicodeError, csv.Error):
            return default
        return default

    def _set_setting(self, key: str, value: str) -> None:
        csv_path = os.path.join(get_app_data_resource_dir(), "setting_struk.csv")
        csv_path = os.path.abspath(csv_path)
        rows = []
        updated = False
        if os.path.exists(csv_path):
            try:
                with open(csv_path, "r", encoding="utf-8", newline="") as f:
                    reader = csv.reader(f)
                    for row in reader:
                        if len(row) >= 1 and row[0].strip() == key:
                            row = [key, value]
                            updated = True
                        rows.append(row)
            except (OSError, UnicodeError, csv.Error):
                rows = []
        if not updated:
            rows.append([key, value])
        with open(csv_path, "w", encoding="utf-8", newline="") as f:
            writer = csv.writer(f)
            writer.writerows(rows)