import pandas as pd
from rdflib import Graph, Namespace, URIRef, Literal
from rdflib.namespace import RDF, RDFS, XSD, SKOS, DCTERMS, FOAF
from datetime import datetime, timedelta, date
import os

# --- Configuration ---
CSV_FILE = "FDIC_Insured_Banks.csv"
ONTOLOGY_FILE = "fdic_ontology_fixed.ttl"
BASE_NAME = "fdic_data_transformed"

# --- Namespaces ---
FDIC = Namespace("https://example.org/fdic#")
EX = Namespace("https://example.org/data#")
DCAT = Namespace("http://www.w3.org/ns/dcat#")
VOID = Namespace("http://rdfs.org/ns/void#")

# --- Load Ontology ---
ontology = Graph()
ontology.parse(ONTOLOGY_FILE, format="turtle")

# Build altLabel mapping
column_to_predicate = {}
for s, p, o in ontology.triples((None, SKOS.altLabel, None)):
    column_to_predicate[str(o)] = s

print(f"✅ columns mapped: {len(column_to_predicate)}")

# --- Load CSV ---
df = pd.read_csv(CSV_FILE, dtype=str).fillna("")
print(f"✅ Loaded CSV with {len(df)} records")

# Identify unmapped columns
unmapped_columns = [col for col in df.columns if col not in column_to_predicate]
if unmapped_columns:
    print("⚠️ Unmapped CSV columns (no skos:altLabel in ontology):")
    for col in unmapped_columns:
        print(f"  - {col}")
    if "OBJECTID" in unmapped_columns:
        print("ℹ️ OBJECTID intentionally unmapped (used as internal index)")
    print(f"❌ columns unmapped: {len(unmapped_columns)}")

# --- Helper: Convert date strings and Excel serials ---
def parse_date(value):
    value = value.strip()
    if not value:
        return None
    # Handle Excel serials
    if value.isdigit():
        try:
            serial = int(value)
            dt = datetime(1899, 12, 30) + timedelta(days=serial)
            return dt.date().isoformat()
        except:
            return None
    # Try known formats
    for fmt in ("%d/%m/%Y", "%m/%d/%Y", "%Y-%m-%d", "%d-%m-%Y", "%m-%d-%Y", "%d.%m.%Y", "%Y/%m/%d"):
        try:
            dt = datetime.strptime(value, fmt)
            return dt.date().isoformat()
        except:
            continue
    return None

# --- Create RDF Graph ---
g = Graph()
g.bind("fdic", FDIC)
g.bind("skos", SKOS)
g.bind("rdfs", RDFS)
g.bind("xsd", XSD)
g.bind("dct", DCTERMS)
g.bind("dcat", DCAT)
g.bind("foaf", FOAF)
g.bind("void", VOID)

# --- Generate RDF Triples ---
for idx, row in df.iterrows():
    subject_uri = EX[f"bankbranch-{idx}"]
    g.set((subject_uri, RDF.type, FDIC.BankBranch))

    for col, val in row.items():
        if col == "OBJECTID" or col not in column_to_predicate:
            continue

        predicate = column_to_predicate[col]
        range_type = ontology.value(subject=predicate, predicate=RDFS.range)

        if range_type == SKOS.Concept:
            g.add((subject_uri, predicate, FDIC[val]))
        elif range_type == XSD.date:
            parsed = parse_date(val)
            if parsed:
                g.add((subject_uri, predicate, Literal(parsed, datatype=XSD.date)))
        else:
            g.add((subject_uri, predicate, Literal(val)))

# --- Add DCAT Metadata ---
dataset_uri = EX["fdic-dataset"]
distribution_uri = EX["fdic-distribution"]

g.add((dataset_uri, RDF.type, DCAT.Dataset))
g.add((dataset_uri, DCTERMS.title, Literal("FDIC Insured Banks Data", lang="en")))
g.add((dataset_uri, DCTERMS.description, Literal("Dataset containing details of FDIC-insured banks and their branches.", lang="en")))
g.add((dataset_uri, DCTERMS.issued, Literal(date.today().isoformat(), datatype=XSD.date)))
g.add((dataset_uri, DCTERMS.language, Literal("en")))
g.add((dataset_uri, DCTERMS.creator, Literal("Semantic Augmentation Team")))
g.add((dataset_uri, DCAT.distribution, distribution_uri))

g.add((distribution_uri, RDF.type, DCAT.Distribution))
g.add((distribution_uri, DCTERMS.format, Literal("text/turtle")))
g.add((distribution_uri, DCAT.accessURL, URIRef("https://example.org/data/fdic_data_transformed.ttl")))

# --- Add VoID Metadata ---
g.add((dataset_uri, RDF.type, VOID.Dataset))
g.add((dataset_uri, VOID.triples, Literal(len(g), datatype=XSD.integer)))

# --- Validate RDF Syntax ---
try:
    g.parse(data=g.serialize(format="turtle"), format="turtle")
    print("✅ RDF graph is valid Turtle syntax")
except Exception as e:
    print("❌ RDF validation error:", e)

# --- Serialize in Multiple Formats ---
formats = {
    "ttl": "turtle"
    # "rdf": "xml",
    # "jsonld": "json-ld",
    # "nt": "nt"
}

for ext, fmt in formats.items():
    file_path = f"{BASE_NAME}.{ext}"
    g.serialize(destination=file_path, format=fmt, encoding="utf-8")
    print(f"✅ RDF written: {file_path}")

# --- Final Summary ---
print(f"\n✅ Summary:")
print(f"   Mapped columns   : {len(column_to_predicate)}")
print(f"   Unmapped columns : {len(unmapped_columns)}")
print(f"   Records processed: {len(df)}")
print(f"   RDF triples      : {len(g)}")