process_data.py

import pandas as pd
import sqlite3
import os
import re
import datetime
import gc
from tqdm import tqdm
import sys

log_file = r"c:\Files\VibeCoding\ReportalRMS\final_processing_log.txt"

def log_progress(message):
    timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    formatted_msg = f"[{timestamp}] {message}"
    print(formatted_msg, flush=True)
    try:
        with open(log_file, "a", encoding="utf-8") as f:
            f.write(formatted_msg + "\n")
    except:
        pass # Prevent script crash if log file is locked by editor

def extract_year(filename):
    match = re.search(r'(\d{4})', filename)
    return int(match.group(1)) if match else 0

data_dir = r"c:\Files\VibeCoding\ReportalRMS\data"
db_path = r"c:\Files\VibeCoding\ReportalRMS\financial_data.db"

# 1. Get all files and sort them by year (ascending)
# This way, newer files' data will naturally "win" if we use a replace strategy 
# or we can do a final deduplication preferring later processed records.
files = [f for f in os.listdir(data_dir) if f.endswith('.xlsx')]
# Sort by year extracted from filename
files.sort(key=lambda x: (extract_year(x), x))

log_progress(f"Found {len(files)} files. Processing in order: {files}")

# 2. Connect to SQLite
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Check already processed files if table exists
processed_files = set()
try:
    cursor.execute("SELECT DISTINCT SourceFile FROM raw_data")
    processed_files = {row[0] for row in cursor.fetchall()}
except:
    pass

# 3. Define the desired columns for the master table
target_columns = [
    'IdCode', 'OrgNameInReport', 'ReportYear', 'FVYear', 
    'CategoryMain', 'FormName', 
    'SheetName', 'LineItemGEO', 'LineItemENG', 'Value', 
    'SourceFile'
]

# We'll create the table on the first successful read
table_created = False

# Mapping for inconsistent headers across years
header_map = {
    'OrgName': 'OrgNameInReport',
    'Category': 'CategoryMain',
    'Form': 'FormName',
    'LineItem': 'LineItemGEO',  # Fallback: map general LineItem to GEO
    'Thousands': 'GEL'
}

# 6. Normalize Category Column
def normalize_category(cat):
    if pd.isna(cat): return None
    cat_str = str(cat).upper().strip()
    if 'სდპ' in cat_str or 'SDP' in cat_str or 'SADP' in cat_str:
        return 'SDP'
    if 'IV' in cat_str or '4' in cat_str:
        return '4'
    if 'III' in cat_str or '3' in cat_str:
        return '3'
    if 'II' in cat_str or '2' in cat_str:
        return '2'
    if 'I' in cat_str or '1' in cat_str:
        return '1'
    return cat_str

# Columns to read from Excel (target columns + legacy names from header_map)
cols_to_read_base = [c for c in target_columns if c != 'SourceFile']
legacy_cols = list(header_map.keys())
potential_cols = cols_to_read_base + legacy_cols + ['GEL'] # GEL is used for normalization

for filename in tqdm(files, desc="Processing Excel Files"):
    if filename in processed_files:
        log_progress(f"Skipping {filename} (already in DB).")
        continue

    file_path = os.path.join(data_dir, filename)
    log_progress(f"Starting {filename}...")
    
    try:
        # Optimization: Only read columns that exist in the file and are needed
        # We first peek at the headers
        full_cols = pd.read_excel(file_path, nrows=0).columns.tolist()
        actual_usecols = [c for c in potential_cols if c in full_cols]
        
        # Load the excel file with filtered columns to save memory
        df = pd.read_excel(file_path, usecols=actual_usecols)
        log_progress(f"  Loaded {len(df)} rows (columns: {len(actual_usecols)}) from {filename}.")
        
        # 1. Rename columns based on map
        df = df.rename(columns=header_map)
        
        # 2. Normalize Value based on GEL/Thousands column
        # GEL might have been renamed to Thousands or vice versa, rename handles it
        norm_col = 'GEL'
        if norm_col in df.columns:
            df['Value'] = pd.to_numeric(df['Value'], errors='coerce')
            is_thousands = df[norm_col].astype(str).str.contains('000')
            df.loc[is_thousands, 'Value'] = df.loc[is_thousands, 'Value'] * 1000
        
        # 3. Add SourceFile info
        df['SourceFile'] = filename
        
        # 4. Handle missing SheetName
        if 'SheetName' not in df.columns:
            df['SheetName'] = 'Unknown'
            
        # 5. Standardize columns
        current_cols = df.columns
        for col in target_columns:
            if col not in current_cols:
                df[col] = None
        
        df = df[target_columns]
        
        # Data cleaning
        df['Value'] = pd.to_numeric(df['Value'], errors='coerce')
        df['CategoryMain'] = df['CategoryMain'].apply(normalize_category)
        
        # Strip whitespace
        string_cols = ['IdCode', 'SheetName', 'LineItemGEO', 'LineItemENG', 'FormName', 'CategoryMain']
        for col in string_cols:
            if col in df.columns:
                # Ensure it's string type before stripping
                df[col] = df[col].astype(str).str.strip()

        # Load into SQLite (append)
        df.to_sql('raw_data', conn, if_exists='append', index=False)
        log_progress(f"  Successfully inserted {len(df)} rows from {filename}.")
        
        # Explicit memory management
        del df
        gc.collect()

    except Exception as e:
        log_progress(f"  CRITICAL ERROR processing {filename}: {str(e)}")
        if 'df' in locals():
            del df
        gc.collect()

# 4. Handle "Latest Data" Logic
# 4. Finalize Deduplication
# Check if all files are in raw_data before final deduplication
cursor.execute("SELECT DISTINCT SourceFile FROM raw_data")
current_files = {row[0] for row in cursor.fetchall()}
missing_files = set(files) - current_files

if missing_files:
    log_progress(f"Warning: The following files are STILL missing from raw_data: {missing_files}")
    log_progress("Deduplication skipped until all files are loaded. Use separate scripts for failed files if memory is tight.")
else:
    log_progress("All files loaded. Starting Final Deduplication (this may take a minute)...")
    
    cursor.execute("DROP TABLE IF EXISTS financial_records")
    cursor.execute(f"""
    CREATE TABLE financial_records AS
    WITH RankedData AS (
        SELECT *,
               ROW_NUMBER() OVER (
                   PARTITION BY IdCode, FVYear, SheetName, LineItemGEO, FormName
                   ORDER BY CAST(SUBSTR(SourceFile, 1, 4) AS INTEGER) DESC, ReportYear DESC, rowid DESC
               ) as rn
        FROM raw_data
    )
    SELECT {', '.join(target_columns)}
    FROM RankedData
    WHERE rn = 1
    """)
    conn.commit()

    # Check counts
    cursor.execute("SELECT COUNT(*) FROM raw_data")
    raw_count = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(*) FROM financial_records")
    final_count = cursor.fetchone()[0]

    log_progress("Processing Complete!")
    log_progress(f"Total rows in raw data: {raw_count}")
    log_progress(f"Total rows after deduplication (keeping latest): {final_count}")

    # 5. DB Optimization (Optional, uncomment once verified)
    # log_progress("Optimizing database size (VACUUM)...")
    # cursor.execute("DROP TABLE raw_data")
    # conn.execute("VACUUM")
    # conn.commit()

conn.close()
log_progress(f"Database process finished.")