ssh -p 22222 sotola@m3u \
  "PYTHONHASHSEED=0 PYTHONPATH=/Users/sotola/PycharmProjects/remote_to_m3_ultra_from_m3:\$PYTHONPATH \
   /Users/sotola/anaconda3/bin/python -u \
   /Users/sotola/PycharmProjects/remote_to_m3_ultra_from_m3/gen_data/update_clickhouse_since_date.py \
   --start-date 2025_12_01"

LOCAL (mac_local_m4)
│
├─► run_remote_on_tmux_v2.py
│   └─► Defines command: gen_clickhouse_data.py
│       └─► Creates tmux pane, calls lib/run_remote_with_report.py
│
├─► lib/run_remote_with_report.py
│   └─► RemoteCommandRunner class
│       └─► SSH to sotola@m3u:22222
│           └─► Executes Python command on remote
│
▼
REMOTE (m3u: ~/PycharmProjects/remote_to_m3_ultra_from_m3)
│
├─► gen_data/gen_clickhouse_data.py
│   └─► Imports DBWorkFlows from workflows/db_workflows.py
│   └─► Reads df_trans pickle file
│   └─► Calls workflows.update_days()
│
├─► workflows/db_workflows.py
│   └─► DbOperations class
│       └─► clickhouse_driver.Client(host='localhost', port=9000)
│
▼
DOCKER on m3u
│
└─► Container: clickhouse-server
    └─► Ports: 0.0.0.0:9000 → 9000 (native protocol)
              0.0.0.0:8123 → 8123 (HTTP)
    └─► Volume: clickhouse-data
    └─► Database: default
    └─► Table: transactions

"""[Created by Opus: abc0185a-ae8d-4ec7-b35f-db0859dd58fe]"""
from pathlib import Path
try:
    project_root = Path(__file__).resolve().parent.parent
except NameError:
    print("Running in an interactive session. "
          "Using current working directory as project root.")
    project_root = Path.cwd()

import sys
sys.path.insert(0, str(project_root))

from workflows.db_workflows import DBWorkFlows
from resources.adapters import Adapters
from resources.resources import DB_RESOURCES
from lib.utilities import U
import pandas as pd
import argparse


# =============================================================================
""" CONFIGURATION """
# =============================================================================

DEFAULT_START_DATE = '2025_12_01'


# =============================================================================
""" TERMINAL COLORS """
# =============================================================================

class Tc:
    PURP = '\x1b[95m'
    CYAN = '\x1b[96m'
    GRN = '\x1b[92m'
    YEL = '\x1b[93m'
    RED = '\x1b[91m'
    BOLD = '\x1b[1m'
    END = '\x1b[0m'
    GRAY = '\x1b[90m'


# =============================================================================
""" MAIN LOGIC """
# =============================================================================

def init():
    U.set_pd_display_options()
    pd.options.display.float_format = '{:.1f}'.format
    adapters = Adapters()
    df_trans = adapters.read_pickle(
        DB_RESOURCES.DF_TRANS_NUMERICAL,
        label='update_clickhouse_since_date.init')
    return adapters, df_trans


def main(start_date: str):
    label = f"{Tc.GRAY}update_clickhouse_since_date{Tc.END}.{Tc.CYAN}main{Tc.END}:"

    # Convert start_date to int format (YYYYMMDD)
    start_date_int = int(start_date.replace('_', ''))

    print(f"{label} {Tc.BOLD}Updating ClickHouse for days >= {start_date}{Tc.END}")
    print("=" * 80)

    # Initialize
    adapters, df_trans = init()

    # Get all days from df_trans
    all_days = sorted(df_trans['day'].unique().tolist())

    # Filter days >= start_date
    days_to_update = [d for d in all_days if d >= start_date_int]

    if not days_to_update:
        print(f"{label} {Tc.YEL}⚠{Tc.END} No days found >= {start_date}")
        return

    print(f"{label} Found {Tc.BOLD}{len(days_to_update)}{Tc.END} days to update")
    print(f"{label} Date range: {Tc.CYAN}{days_to_update[0]} to {days_to_update[-1]}{Tc.END}")

    # Format days for display
    formatted_days = []
    for d in days_to_update:
        d_str = str(d)
        formatted_days.append(f"{d_str[:4]}_{d_str[4:6]}_{d_str[6:8]}")

    print(f"{label} Days: {Tc.CYAN}{', '.join(formatted_days)}{Tc.END}")
    print()

    # Initialize workflows
    workflows = DBWorkFlows(adapters=adapters)

    # Update all days at once (upsert mode)
    workflows.update_days(days=days_to_update, df_trans=df_trans)

    # Generate report
    workflows.generate_report()

    print(f"\n{label} {Tc.GRN}✓ Update complete!{Tc.END}")


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        description='Update ClickHouse database for days since a specific date'
    )
    parser.add_argument(
        '--start-date', '-s',
        type=str,
        default=DEFAULT_START_DATE,
        help=f'Start date in YYYY_MM_DD format (default: {DEFAULT_START_DATE})'
    )

    args = parser.parse_args()
    main(args.start_date)

ssh -p 22222 sotola@m3u \
  "PYTHONHASHSEED=0 PYTHONPATH=/Users/sotola/PycharmProjects/remote_to_m3_ultra_from_m3:\$PYTHONPATH \
   /Users/sotola/anaconda3/bin/python -u \
   /Users/sotola/PycharmProjects/remote_to_m3_ultra_from_m3/gen_data/gen_clickhouse_data.py"