# Copyright (c) 2025, Ora Lassila & So Many Aircraft
# All rights reserved.
#
# See LICENSE for licensing information
#
# This file implements the application of R2RML/RML and SPARQL -based transformations from CSV to
# an RDF graph.
#
import datetime
import csv
import logging
from rdfhelpers import Composable
from rdflib import Namespace
from xmptools import PHOTOSHOP

# Source file's dates are (per our guess) in the format MS Excel uses. This function is used in the
# mapping to produce proper xsd:date literals.
def convert_excel_date(datestr):
    if datestr:
        return datetime.date(1899, 12, 30) + datetime.timedelta(days=float(datestr))
    else:
        return None

# Namespace strings
FDICIB = Namespace("https://somanyaircraft.com/data/experimental/fdicib/schema/core#")
FIBO = "https://spec.edmcouncil.org/fibo/ontology/"

# Files used in the mapping exercise
SOURCE_CSV = "data/FDIC_Insured_Banks.csv"
MAPPING = "schema/fdic-insured-bank-mapping.ttl"
TARGET_RDF = "data/fdic-insured-banks.ttl"

if __name__ == "__main__":
    # Basic logic:
    #   1. Contents of the CSV file are read and fed to the R2RML/RML mapping
    #   2. Some namespace declarations are added to make the result serialize neatly
    #   3. A SPARQL update query removes an anomalous "empty" branch
    #   4. A SPARQL update query transforms the location-related properties into a new node that is
    #      linked to a branch via the fdicib:address predicate (R2RML mapping itself cannot express
    #      this)
    #   5. Resulting graph is serialized as Turtle
    logging.basicConfig(level=logging.INFO)
    with open(SOURCE_CSV, newline='') as source:
        Composable()\
            .mapIterable(global_bindings={"convert_excel_date": convert_excel_date},
                         mapping=MAPPING,
                         iterable=csv.DictReader(source)) \
            .bind("fdicib", FDICIB) \
            .bind("tmp", "https://somanyaircraft.com/data/experimental/fdicib/schema/tmp#")\
            .bind("Iptc4xmpCore", "http://iptc.org/std/Iptc4xmpCore/1.0/xmlns/") \
            .bind("photoshop", PHOTOSHOP) \
            .bind("fibo-be-le-fbo", FIBO + "BE/LegalEntities/FormalBusinessOrganizations/") \
            .bind("fibo-fnd-dt-fd", FIBO + "FND/DatesAndTimes/FinancialDates/") \
            .bind("fibo-fnd-plc-adr", FIBO + "FND/Places/Addresses/") \
            .bind("lcc-cr", "https://www.omg.org/spec/LCC/Countries/CountryRepresentation/") \
            .bind("cmns-dt", "https://www.omg.org/spec/Commons/DatesAndTimes/")\
            .update("""
                DELETE { ?bank ?p ?o }
                WHERE {
                    ?bank a fibo-be-le-fbo:Branch ; ?p ?o
                    FILTER (isBlank(?bank))
                };
                DELETE {
                    ?bank tmp:city ?city ; tmp:address ?address ; tmp:zip ?zip ; tmp:state ?state
                }
                INSERT {
                    ?bank fdicib:address [
                        Iptc4xmpCore:Location ?address ;
                        photoshop:City ?city ;
                        photoshop:State ?state ;
                        fibo-fnd-plc-adr:hasPostalCode ?zip
                    ]
                }
                WHERE {
                    ?bank a fibo-be-le-fbo:Branch ;
                        tmp:city ?city ; tmp:address ?address ; tmp:zip ?zip ; tmp:state ?state
                }""")\
            .serialize(TARGET_RDF)