#!/usr/bin/env python3
"""
Standalone script to update instance sizes in PACS database.
Processes studies from specified date to today, calculates file sizes,
and updates database with size information.
"""

import pymysql
import hashlib
import os
import sys
from datetime import datetime, timedelta
import argparse
from tqdm import tqdm

# Global configuration variables
DB_CONFIG = {
    'host': 'localhost',
    'user': 'pacs_user',
    'password': 'pacs_password',
    'database': 'pacs',
    'charset': 'utf8mb4'
}

BASE_PATH = '/path/to/pacs/storage'

def get_connection():
    """Create and return database connection"""
    return pymysql.connect(**DB_CONFIG)

def get_file_size(file_path):
    """Get file size in bytes, return 0 if file doesn't exist"""
    try:
        return os.path.getsize(file_path)
    except (OSError, FileNotFoundError):
        return 0

def get_studies_for_date(connection, study_date):
    """Get all studies for specified date"""
    with connection.cursor(pymysql.cursors.DictCursor) as cursor:
        sql = "SELECT uid, date FROM study WHERE date = %s"
        cursor.execute(sql, (study_date,))
        return cursor.fetchall()

def get_series_for_study(connection, study_uid):
    """Get all series for a study"""
    with connection.cursor(pymysql.cursors.DictCursor) as cursor:
        sql = "SELECT uid FROM series WHERE study_id = %s"
        cursor.execute(sql, (study_uid,))
        return cursor.fetchall()

def get_instances_for_series(connection, series_uid):
    """Get all instances for a series"""
    with connection.cursor(pymysql.cursors.DictCursor) as cursor:
        sql = "SELECT uid, study_date FROM instance WHERE series_id = %s"
        cursor.execute(sql, (series_uid,))
        return cursor.fetchall()

def update_instance_size(connection, instance_uid, size_bytes):
    """Update instance size in database"""
    with connection.cursor() as cursor:
        sql = "UPDATE instance SET size = %s WHERE uid = %s"
        cursor.execute(sql, (size_bytes, instance_uid))

def update_series_size(connection, series_uid, size_bytes):
    """Update series size in database"""
    with connection.cursor() as cursor:
        sql = "UPDATE series SET size = %s WHERE uid = %s"
        cursor.execute(sql, (size_bytes, series_uid))

def update_study_size(connection, study_uid, size_bytes):
    """Update study size in database"""
    with connection.cursor() as cursor:
        sql = "UPDATE study SET size = %s WHERE uid = %s"
        cursor.execute(sql, (size_bytes, study_uid))

def process_study(connection, study):
    """Process a single study - calculate sizes for all instances, series, and study"""
    study_uid = study['uid']
    study_date = study['date']

    print(f"  Processing study: {study_uid}")

    # Get all series for this study
    series_list = get_series_for_study(connection, study_uid)

    study_total_size = 0

    for series in series_list:
        series_uid = series['uid']
        series_total_size = 0

        # Get all instances for this series
        instances = get_instances_for_series(connection, series_uid)

        for instance in instances:
            instance_uid = instance['uid']
            instance_study_date = instance['study_date']

            # Generate file path
            uid_hash = hashlib.md5(instance_uid.encode('utf-8')).hexdigest()[:2]
            file_path = f"{BASE_PATH}/{instance_study_date.strftime('%Y%m')}/{uid_hash}/{instance_uid}.dcm"

            # Get file size
            file_size = get_file_size(file_path)

            if file_size > 0:
                # Update instance size
                update_instance_size(connection, instance_uid, file_size)
                series_total_size += file_size

        # Update series size
        if series_total_size > 0:
            update_series_size(connection, series_uid, series_total_size)
            study_total_size += series_total_size

    # Update study size
    if study_total_size > 0:
        update_study_size(connection, study_uid, study_total_size)

    return len(series_list), study_total_size

def process_date(connection, target_date):
    """Process all studies for a specific date"""
    print(f"Processing date: {target_date}")

    # Get studies for the date
    studies = get_studies_for_date(connection, target_date)

    if not studies:
        print(f"No studies found for {target_date}")
        return

    print(f"Found {len(studies)} studies")

    # Process studies with progress bar
    for study in tqdm(studies, desc=f"Processing studies for {target_date}"):
        series_count, study_size = process_study(connection, study)

        # Commit after each study to ensure data consistency
        connection.commit()

def main():
    parser = argparse.ArgumentParser(description='Update instance sizes in PACS database')
    parser.add_argument('start_date', help='Start date (YYYY-MM-DD)')
    args = parser.parse_args()

    try:
        start_date = datetime.strptime(args.start_date, '%Y-%m-%d').date()
    except ValueError:
        print("Invalid date format. Please use YYYY-MM-DD")
        sys.exit(1)

    today = datetime.now().date()

    if start_date > today:
        print("Start date cannot be in the future")
        sys.exit(1)

    print(f"Processing dates from {start_date} to {today}")

    connection = get_connection()

    try:
        # Process each date from start_date to today
        current_date = start_date
        while current_date <= today:
            process_date(connection, current_date)
            current_date += timedelta(days=1)

    finally:
        connection.close()

    print("Script completed successfully")

if __name__ == "__main__":
    main()
