#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys
import re

# Add repository root and 'ntuh' directory to sys.path
script_dir = os.path.dirname(os.path.abspath(__file__))
base_dir = os.path.dirname(script_dir)
ntuh_dir = os.path.join(base_dir, 'ntuh')

if base_dir not in sys.path:
    sys.path.insert(0, base_dir)
if ntuh_dir not in sys.path:
    sys.path.insert(0, ntuh_dir)

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'ntuh.settings')

import django
django.setup()

import pandas as pd
from registry.models import PathologyReport

illegal_re = re.compile(r'[\x00-\x08\x0b-\x0c\x0e-\x1f]')


def clean_string_for_excel(val):
    if not isinstance(val, str):
        return val
    # Remove illegal XML / openpyxl control characters
    val = illegal_re.sub('', val)
    # Strip unnecessary head, style, script, meta, link tags so report body fits within cell limits
    h = re.sub(r'<(?:head|style|script|meta|link)[^>]*?>.*?</(?:head|style|script|meta|link)>', '', val, flags=re.DOTALL | re.IGNORECASE)
    h = re.sub(r'<(?:meta|link)[^>]*?>', '', h, flags=re.IGNORECASE)
    h = re.sub(r'\n\s*\n', '\n', h)
    return h.strip()


def fetch_pathology_reports(output_file=None):
    if output_file is None:
        output_file = os.path.join(script_dir, 'registry_pathologyreport.xlsx')

    print(f"Fetching PathologyReport records from database...")
    qs = PathologyReport.objects.all().values()
    data = list(qs)

    print(f"Cleaning string fields & HTML body for Excel cell limit...")
    for item in data:
        for k, v in item.items():
            if isinstance(v, str):
                item[k] = clean_string_for_excel(v)

    df = pd.DataFrame(data)
    print(f"Fetched {len(df)} rows. Writing to {output_file}...")
    df.to_excel(output_file, index=False)
    print(f"Successfully exported {len(df)} rows to {output_file}")


if __name__ == '__main__':
    default_output = os.path.join(script_dir, 'registry_pathologyreport.xlsx')
    target_path = sys.argv[1] if len(sys.argv) > 1 else default_output
    fetch_pathology_reports(target_path)