import os
import json
import pandas as pd

# === CONFIGURATION ===
# Base directory on server
base_dir = "/var/www/html/naseni-innovation/backend/data/proposal"

# Base public URL (replace this if your domain changes)
base_url = "https://nrcgp-media.naseni.xyz/proposal"

# Fixed file keys expected in the "files" section
file_keys = [
    "phdEvidence",
    "endorsementLetter",
    "journalPublication",
    "productMedia",
    "budget",
    "proposal",
    "pitchDeck"
]

# Collect proposal records
proposals = []

for folder in os.listdir(base_dir):
    folder_path = os.path.join(base_dir, folder)
    details_path = os.path.join(folder_path, "details.json")

    if os.path.isfile(details_path):
        try:
            with open(details_path, "r") as f:
                data = json.load(f)

            # Basic details
            flat = {
                "applicationId": data.get("applicationId"),
                "submittedAt": data.get("submittedAt"),
            }

            # Add formData fields
            formData = data.get("formData", {})
            for key, value in formData.items():
                flat[key] = value

            # Add file links (converted to HTTP URLs)
            files = data.get("files", {})
            for file_key in file_keys:
                file_data = files.get(file_key)
                if file_data and file_data.get("savedName"):
                    # Construct public HTTP URL
                    url = f"{base_url}/{folder}/documents/{file_data['savedName']}"
                    flat[file_key] = url
                else:
                    flat[file_key] = "N/A"

            # Add metadata
            metadata = data.get("metadata", {})
            flat["totalFiles"] = metadata.get("totalFiles", 0)
            flat["version"] = metadata.get("version", "")

            proposals.append(flat)

        except Exception as e:
            print(f"❌ Error reading {details_path}: {e}")

# Convert to DataFrame
df = pd.DataFrame(proposals)

# Save Excel in your home directory to avoid permission issues
output_path = os.path.expanduser("/tmp/naseni_proposals_with_public_links.xlsx")
df.to_excel(output_path, index=False)

print(f"✅ Excel file created successfully at: {output_path}")
print(f"📊 Total proposals exported: {len(df)}")
print(f"🌐 Links formatted using base URL: {base_url}")