import sqlite3

conn = sqlite3.connect("art_catalog.db")  # created if it doesn't exist yet

conn.execute("""
    CREATE TABLE IF NOT EXISTS images (
        commons_filename TEXT PRIMARY KEY,
        resolved_url TEXT,
        license TEXT,
        attribution_text TEXT,
        requires_attribution INTEGER,   -- 0 or 1, computed once, checked forever after
        requires_share_alike INTEGER
    )
""")

# --------------------------------------------------------------------------
# The obligation lives in the license string, but you don't want every
# downstream step re-parsing "CC BY-SA 4.0" to figure out what it owes you.
# Classify it ONCE, at ingestion, and store the answer as data.
# --------------------------------------------------------------------------

# Known license strings, normalized (uppercase, hyphens collapsed) -> (requires_attribution, requires_share_alike)
KNOWN_LICENSES = {
    "CC0":            (0, 0),
    "CC0 1.0":        (0, 0),
    "PUBLIC DOMAIN":  (0, 0),
    "PD":             (0, 0),
    "CC BY":          (1, 0),
    "CC BY 2.0":      (1, 0),
    "CC BY 3.0":      (1, 0),
    "CC BY 4.0":      (1, 0),
    "CC BY-SA":       (1, 1),
    "CC BY-SA 2.0":   (1, 1),
    "CC BY-SA 3.0":   (1, 1),
    "CC BY-SA 4.0":   (1, 1),
}


def classify_license(license_str):
    """
    Turn a raw license string into (requires_attribution, requires_share_alike).

    Unknown strings are NOT quietly assumed safe. They default to requiring
    attribution — the conservative failure mode — and get flagged so a human
    can add the real mapping. A silent wrong guess here is the kind of bug
    that surfaces as a takedown notice months later; a loud one just means
    updating a dict.
    """
    key = license_str.strip().upper().replace("-", " ").replace("  ", " ").strip()
    key = key.replace("CC BY SA", "CC BY-SA")  # restore the one hyphen we care about

    if key in KNOWN_LICENSES:
        return KNOWN_LICENSES[key]

    print(f"  [!] Unrecognized license '{license_str}' — defaulting to "
          f"requires_attribution=1, requires_share_alike=1 pending review")
    return (1, 1)


def ingest_image(commons_filename, resolved_url, license_str, attribution_text):
    """Compute the obligation once, at the door, and store it alongside the file."""
    requires_attribution, requires_share_alike = classify_license(license_str)

    conn.execute("""
        INSERT OR IGNORE INTO images (
            commons_filename, resolved_url, license, attribution_text,
            requires_attribution, requires_share_alike
        ) VALUES (?, ?, ?, ?, ?, ?)
    """, (commons_filename, resolved_url, license_str, attribution_text,
          requires_attribution, requires_share_alike))


# --------------------------------------------------------------------------
# Example ingestion — stand-in for what your harvester scripts feed in
# with real Commons/IIIF metadata.
# --------------------------------------------------------------------------
sample_images = [
    ("Whistler_Nocturne.jpg",
     "https://example.org/iiif/whistler_nocturne/full",
     "CC0",
     ""),
    ("Sargent_Portrait.jpg",
     "https://example.org/iiif/sargent_portrait/full",
     "CC BY 4.0",
     "Photo: Metropolitan Museum of Art"),
    ("Local_Museum_Piece.jpg",
     "https://example.org/iiif/local_museum_piece/full",
     "CC BY-SA 4.0",
     "Photo: Regional Historical Society"),
    ("Mystery_Scan.jpg",
     "https://example.org/iiif/mystery_scan/full",
     "All Rights Reserved (contact institution)",
     ""),
]

for filename, url, license_str, attribution in sample_images:
    ingest_image(filename, url, license_str, attribution)

conn.commit()

# --------------------------------------------------------------------------
# The payoff: a downstream book-assembly step doesn't re-research anything.
# It just asks the mechanical question against stored data.
# --------------------------------------------------------------------------
print("\nImages that need a printed credit line:\n")

for filename, license_str, attribution_text, share_alike in conn.execute("""
    SELECT commons_filename, license, attribution_text, requires_share_alike
    FROM images
    WHERE requires_attribution = 1
    ORDER BY commons_filename
"""):
    credit = attribution_text if attribution_text else f"(license: {license_str} — attribution text needed)"
    tag = "  [also requires share-alike]" if share_alike else ""
    print(f"  - {filename}: {credit}{tag}")

conn.close()