#!/usr/bin/env python3
"""
Purge policy for index_quotes:
- Keep all rows from the last KEEP_DAYS (default 10).
- For rows older than KEEP_DAYS, keep only the last row per (index_name, day).
"""

import os
import sys
import logging
from contextlib import closing

import pymysql  # pip install PyMySQL

# ---- Config via env (recommended) ----
DB_HOST   = os.getenv("DB_HOST", "192.168.0.41")
DB_USER   = os.getenv("DB_USER", "mawdz09")
DB_PASS   = os.getenv("DB_PASS", "asaxPI1324")
DB_NAME   = os.getenv("DB_NAME", "marketdb")
TABLE     = os.getenv("TABLE", "index_quotes")
PK_COL    = os.getenv("PK_COL", "id")
TS_COL    = os.getenv("TS_COL", "ts")
NAME_COL  = os.getenv("NAME_COL", "index_name")
KEEP_DAYS = int(os.getenv("KEEP_DAYS", "10"))

LOG_LEVEL = os.getenv("LOG_LEVEL", "INFO").upper()

logging.basicConfig(
    level=getattr(logging, LOG_LEVEL, logging.INFO),
    format="%(asctime)s [%(levelname)s] %(message)s",
)
log = logging.getLogger("purge_index_quotes")

SQL_CREATE_INDEXES = [
    # Speed up GROUP BY index_name, DATE(ts) and searches by ts
    f"CREATE INDEX IF NOT EXISTS idx_{TABLE}_{NAME_COL}_{TS_COL} ON {TABLE}({NAME_COL}, {TS_COL});",
    f"CREATE INDEX IF NOT EXISTS idx_{TABLE}_{TS_COL} ON {TABLE}({TS_COL});",
]

from datetime import date, timedelta

# ...

SQL_PURGE = f"""
DELETE iq
FROM {TABLE} iq
LEFT JOIN (
    SELECT MAX({PK_COL}) AS keep_id
    FROM {TABLE}
    WHERE {TS_COL} < %s
    GROUP BY {NAME_COL}, DATE({TS_COL})
) keepers
ON iq.{PK_COL} = keepers.keep_id
WHERE iq.{TS_COL} < %s
  AND keepers.keep_id IS NULL;
"""

def main():
    cutoff = date.today() - timedelta(days=KEEP_DAYS)
    log.info("Starting purge: keeping all rows for last %s days; cutoff=%s", KEEP_DAYS, cutoff)

    with closing(pymysql.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        autocommit=False,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.Cursor,
    )) as conn, conn.cursor() as cur:

        # Optional indexes (ignore if they already exist)
        for stmt in SQL_CREATE_INDEXES:
            try:
                cur.execute(stmt)
            except Exception as e:
                log.debug("Index creation skipped: %s", e)

        cur.execute("START TRANSACTION;")
        cur.execute(SQL_PURGE, (cutoff, cutoff))  # pass cutoff twice for the two %s
        deleted = cur.rowcount
        conn.commit()

    log.info("Purge complete. Rows deleted: %s", deleted)



if __name__ == "__main__":
    try:
        main()
    except Exception:
        log.exception("Purge failed")
        sys.exit(1)