# -*- coding: utf-8 -*-
"""
サンプルDB(lerisa_ftrecsample)のデータを、直近14日分(2026-08-10?2026-08-16)に削減する。
表示を軽くするため、8/03より前のデータを削除する。
本番(226のrankradar_data)は一切触らない。サンプルDBのみ。
実行(226のTera Term):
    C:\apps\ranking_system\venv\Scripts\python.exe C:\apps\ranking_system\trim_sample.py
"""
import mysql.connector
import sys

CUTOFF = '2026-08-10'  # この日より前を削除

SAMPLE_DB = dict(host='mysql57s-32.kagoya.net', port=3306, user='lerisa',
                 password='stance0209P0531#', database='lerisa_ftrecsample',
                 charset='utf8mb4', connection_timeout=60)

def batch_delete(cur, conn, table, date_col, label):
    total = 0
    while True:
        cur.execute("DELETE FROM `" + table + "` WHERE `" + date_col + "` < %s LIMIT 5000", (CUTOFF,))
        conn.commit()
        n = cur.rowcount
        total += n
        if n > 0:
            print("  " + label + ": " + str(total) + "件 削除...")
            sys.stdout.flush()
        if n == 0:
            break
    print(label + ": 合計 " + str(total) + "件 削除")
    return total

def main():
    conn = mysql.connector.connect(**SAMPLE_DB)
    cur = conn.cursor()
    print("サンプルDBを " + CUTOFF + " 以降(?2026-08-16)に削減します")
    print("=" * 50)
    batch_delete(cur, conn, 'scraping_results', 'fetched_at', 'scraping_results')
    batch_delete(cur, conn, 'analysis_suggestions', 'suggested_at', 'analysis_suggestions')
    batch_delete(cur, conn, 'analysis_results', 'analyzed_date', 'analysis_results')
    batch_delete(cur, conn, 'error_logs', 'occurred_at', 'error_logs')
    batch_delete(cur, conn, 'update_histories', 'executed_at', 'update_histories')
    print("=" * 50)
    cur.execute("SELECT COUNT(*) FROM scraping_results")
    print("scraping_results 残り: " + str(cur.fetchone()[0]) + " 件")
    cur.execute("SELECT MIN(DATE(fetched_at)), MAX(DATE(fetched_at)) FROM scraping_results")
    r = cur.fetchone()
    print("期間: " + str(r[0]) + " ? " + str(r[1]))
    conn.close()
    print("=" * 50)
    print("削減 完了")

if __name__ == '__main__':
    main()