#!/usr/bin/env python3
"""Route AI-written column descriptions to the data owner and publish only the approved ones.

    python3 owner_approval.py                 # clean demo run
    python3 owner_approval.py --plant-error   # same run with four faults planted in the review log
    python3 owner_approval.py --fail-on-blocked   # exit 1 when any row is Blocked (for a pipeline)

Needs: pip install duckdb. Plain SQL does the checking; no AI call and no key is involved.
The drafts, glossary, owners and review log below are INVENTED stand-ins (example.com addresses,
invented people). Only the column names come from a public source: the FEC "Contributions by
individuals" file description, https://www.fec.gov/campaign-finance-data/contributions-individuals-file-description/
"""
import argparse
import sys

import duckdb

# ---- YOUR DATA ---------------------------------------------------------------------------
# DB_FILE = None builds the invented demo tables in memory. To use your own data, set DB_FILE to
# a DuckDB file that holds your four tables, then change the table and column names inside the
# four CREATE VIEW statements in SOURCES so each view returns exactly the columns named after AS.
# The views are named src_... so they cannot clash with your own tables (glossary, drafts and so on).
DB_FILE = None

SOURCES = """
CREATE VIEW src_glossary AS
  SELECT term AS term, definition AS definition, owner_email AS owner_email FROM demo_glossary;
CREATE VIEW src_owners AS
  SELECT email AS email, full_name AS full_name, is_active AS is_active FROM demo_owners;
CREATE VIEW src_drafts AS
  SELECT table_name AS table_name, column_name AS column_name, draft_text AS draft_text,
         glossary_term AS glossary_term, drafted_on AS drafted_on FROM demo_drafts;
CREATE VIEW src_review_log AS
  SELECT table_name AS table_name, column_name AS column_name, reviewer_email AS reviewer_email,
         decision AS decision, final_text AS final_text, decided_on AS decided_on FROM demo_review_log;
"""
# ---- END YOUR DATA -----------------------------------------------------------------------

DEMO = """
CREATE TABLE demo_owners(email VARCHAR, full_name VARCHAR, is_active BOOLEAN);
INSERT INTO demo_owners VALUES
  ('ana.reyes@example.com',  'Ana Reyes',  true),
  ('li.chen@example.com',    'Li Chen',    true),
  ('sam.okafor@example.com', 'Sam Okafor', true);

CREATE TABLE demo_glossary(term VARCHAR, definition VARCHAR, owner_email VARCHAR);
INSERT INTO demo_glossary VALUES
  ('Committee',          'The political committee that filed the report.',                'ana.reyes@example.com'),
  ('Contribution amount','The dollar amount of one itemized contribution.',               'li.chen@example.com'),
  ('Contribution date',  'The date the contribution was made.',                           'li.chen@example.com'),
  ('Contributor job',    'The contributor''s stated employer and occupation.',            'sam.okafor@example.com'),
  ('Filing',             'One report submitted to the regulator.',                        'ana.reyes@example.com');

CREATE TABLE demo_drafts(table_name VARCHAR, column_name VARCHAR, draft_text VARCHAR,
                         glossary_term VARCHAR, drafted_on DATE);
INSERT INTO demo_drafts VALUES
  ('indiv', 'CMTE_ID',          'Nine-character code that identifies the committee receiving the contribution.', 'Committee',           DATE '2026-11-01'),
  ('indiv', 'TRANSACTION_AMT',  'Dollar amount of the contribution, as reported on the filing.',                'Contribution amount', DATE '2026-11-01'),
  ('indiv', 'TRANSACTION_DT',   'Date the contribution was made.',                                              'Contribution date',   DATE '2026-11-01'),
  ('indiv', 'EMPLOYER',         'Employer the contributor stated on the filing.',                               'Contributor job',     DATE '2026-11-01'),
  ('indiv', 'OCCUPATION',       'Occupation the contributor stated on the filing.',                             'Contributor job',     DATE '2026-11-01'),
  ('indiv', 'FILE_NUM',         'Identifier of the report the row came from.',                                  'Filing',              DATE '2026-11-01'),
  ('indiv', 'MEMO_TEXT',        'Free-text note that describes the activity.',                                  'Filing',              DATE '2026-11-01'),
  ('indiv', 'SUB_ID',           'Unique identifier of this row.',                                               'Filing',              DATE '2026-11-01');

-- Six decisions are in; MEMO_TEXT and SUB_ID are still waiting.
CREATE TABLE demo_review_log(table_name VARCHAR, column_name VARCHAR, reviewer_email VARCHAR,
                             decision VARCHAR, final_text VARCHAR, decided_on DATE);
INSERT INTO demo_review_log VALUES
  ('indiv', 'CMTE_ID',         'ana.reyes@example.com',  'approved', 'Nine-character code that identifies the committee receiving the contribution.', DATE '2026-11-02'),
  ('indiv', 'TRANSACTION_AMT', 'li.chen@example.com',    'edited',   'Dollar amount of one itemized contribution, as reported on the filing.',      DATE '2026-11-02'),
  ('indiv', 'TRANSACTION_DT',  'li.chen@example.com',    'approved', 'Date the contribution was made.',                                              DATE '2026-11-02'),
  ('indiv', 'EMPLOYER',        'sam.okafor@example.com', 'approved', 'Employer the contributor stated on the filing.',                               DATE '2026-11-03'),
  ('indiv', 'OCCUPATION',      'sam.okafor@example.com', 'rejected', NULL,                                                                           DATE '2026-11-03'),
  ('indiv', 'FILE_NUM',        'ana.reyes@example.com',  'approved', 'Identifier of the report the row came from.',                                  DATE '2026-11-03');
"""

# Each fault is invented so the clean data cannot already contain it, and is planted by an UPDATE
# whose row count the script checks: a fault that did not land stops the run.
PLANTS = [
    ("wrong reviewer",    "UPDATE demo_review_log SET reviewer_email = 'LI.CHEN@example.com' WHERE column_name = 'CMTE_ID'"),
    ("text changed after approval",
                          "UPDATE demo_review_log SET final_text = 'Committee code (needs a rewrite).' WHERE column_name = 'TRANSACTION_DT'"),
    ("decision before draft", "UPDATE demo_review_log SET decided_on = DATE '2026-10-01' WHERE column_name = 'EMPLOYER'"),
    ("term not in glossary", "UPDATE demo_drafts SET glossary_term = 'Contributr job' WHERE column_name = 'MEMO_TEXT'"),
]

# The queue: every draft, its owner (from the glossary) and its latest decision.
QUEUE = """
CREATE TEMP TABLE queue AS
SELECT d.table_name, d.column_name, d.draft_text, d.glossary_term, d.drafted_on,
       g.owner_email,
       r.reviewer_email, r.decision, r.final_text, r.decided_on
FROM src_drafts d
LEFT JOIN src_glossary g ON lower(trim(g.term)) = lower(trim(d.glossary_term))
LEFT JOIN (
  SELECT * FROM src_review_log
  QUALIFY row_number() OVER (PARTITION BY table_name, column_name ORDER BY decided_on DESC) = 1
) r ON r.table_name = d.table_name AND r.column_name = d.column_name;
"""

# Four checks. Each returns the rows that break it. NULLs are handled with IS NULL / IS DISTINCT FROM,
# never NOT IN, so a NULL owner or reviewer cannot hide a failure.
CHECKS = [
    ("1 owner",
     "the draft's glossary term exists and its owner is an active person",
     """SELECT q.table_name, q.column_name FROM queue q
        WHERE q.owner_email IS NULL
           OR NOT EXISTS (SELECT 1 FROM src_owners o
                          WHERE lower(trim(o.email)) = lower(trim(q.owner_email)) AND o.is_active)"""),
    ("2 reviewer",
     "the person who decided is the term's owner",
     """SELECT table_name, column_name FROM queue
        WHERE decision IS NOT NULL
          AND lower(trim(reviewer_email)) IS DISTINCT FROM lower(trim(owner_email))"""),
    ("3 text",
     "an approved row publishes the draft unchanged; an edited row differs from it and is not empty",
     """SELECT table_name, column_name FROM queue
        WHERE (decision = 'approved' AND final_text IS DISTINCT FROM draft_text)
           OR (decision = 'edited' AND (final_text IS NULL OR trim(final_text) = '' OR final_text = draft_text))"""),
    ("4 order",
     "every decision and every decided draft has a date, the latest decision is alone on its date, and that decision came on or after the draft",
     """SELECT table_name, column_name FROM queue
        WHERE decision IS NOT NULL AND (decided_on IS NULL OR drafted_on IS NULL OR decided_on < drafted_on)
           OR (table_name, column_name) IN (
                SELECT table_name, column_name FROM (
                  SELECT table_name, column_name, decided_on,
                         max(decided_on) OVER (PARTITION BY table_name, column_name) AS latest
                  FROM src_review_log)
                GROUP BY table_name, column_name
                HAVING count(*) FILTER (WHERE decided_on IS NULL) > 0
                    OR count(*) FILTER (WHERE decided_on = latest) > 1)"""),
]

# Rows with a decision value we do not know are also Blocked.
STATUS = """
CREATE TEMP TABLE status AS
SELECT q.*,
  CASE
    WHEN b.column_name IS NOT NULL THEN 'Blocked'
    WHEN q.decision IS NULL THEN 'Waiting'
    WHEN q.decision = 'rejected' THEN 'Rejected'
    WHEN q.decision IN ('approved', 'edited') THEN 'Publish'
    ELSE 'Blocked'
  END AS status
FROM queue q
LEFT JOIN (SELECT DISTINCT table_name, column_name FROM blocked) b
  ON b.table_name = q.table_name AND b.column_name = q.column_name;
"""


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--plant-error", action="store_true", help="plant four faults in the demo data")
    ap.add_argument("--fail-on-blocked", action="store_true", help="exit 1 if any row is Blocked")
    args = ap.parse_args()

    con = duckdb.connect(DB_FILE) if DB_FILE else duckdb.connect()
    if DB_FILE is None:
        con.execute(DEMO)
        if args.plant_error:
            for name, sql in PLANTS:
                n = con.execute(sql).fetchall()[0][0]
                if n != 1:
                    sys.exit(f"could not plant '{name}': {n} rows changed")
                print(f"planted: {name}")
    elif args.plant_error:
        sys.exit("--plant-error only works on the demo data")
    names = ("src_glossary", "src_owners", "src_drafts", "src_review_log")
    taken = [r[0] for r in con.execute("SELECT table_name FROM information_schema.tables WHERE lower(table_name) "
                                       "IN (?, ?, ?, ?)", list(names)).fetchall()]
    if taken:
        sys.exit(f"your database already holds {', '.join(taken)}; the script names its own views that way")
    # Views over a file database must not outlive the run, so they are temporary.
    con.execute(SOURCES.replace("CREATE VIEW", "CREATE OR REPLACE TEMP VIEW"))
    dup = con.execute("""SELECT table_name || '.' || column_name FROM src_drafts
                         GROUP BY table_name, column_name HAVING count(*) > 1 ORDER BY 1""").fetchall()
    if dup:
        sys.exit("more than one draft for " + ", ".join(d[0] for d in dup) + ": keep only the current draft of each column")
    con.execute(QUEUE)

    print("\nChecks")
    con.execute("CREATE TEMP TABLE blocked(check_name VARCHAR, table_name VARCHAR, column_name VARCHAR)")
    failed = 0
    for name, what, sql in CHECKS:
        rows = con.execute(sql).fetchall()
        for t, c in rows:
            con.execute("INSERT INTO blocked VALUES (?, ?, ?)", [name, t, c])
        failed += len(rows)
        print(f"  check {name}: {'FAIL' if rows else 'pass'} ({len(rows)} rows) - {what}")
        for t, c in rows:
            print(f"      {t}.{c}")
    # Total rows must be accounted for: every draft ends in exactly one status.
    con.execute(STATUS)
    n_drafts = con.execute("SELECT count(*) FROM src_drafts").fetchone()[0]
    n_status = con.execute("SELECT count(*) FROM status").fetchone()[0]
    if n_drafts != n_status:
        sys.exit(f"queue lost or duplicated rows: {n_drafts} drafts, {n_status} statuses")

    print("\nReview queue")
    for r in con.execute("""SELECT table_name || '.' || column_name, coalesce(owner_email, '(no owner)'),
                                   coalesce(decision, '-'), status
                            FROM status ORDER BY 1""").fetchall():
        print(f"  {r[0]:<22} {r[1]:<26} {r[2]:<9} {r[3]}")

    print("\nCounts")
    counts = dict(con.execute("SELECT status, count(*) FROM status GROUP BY 1").fetchall())
    for s in ("Publish", "Waiting", "Rejected", "Blocked"):
        print(f"  {s:<9} {counts.get(s, 0)}")
    print(f"  checks failed: {failed}")

    print("\nReady to load into the catalog (Publish rows only)")
    for t, c, text in con.execute("""SELECT table_name, column_name,
                                            CASE WHEN decision = 'edited' THEN final_text ELSE draft_text END
                                     FROM status WHERE status = 'Publish' ORDER BY 1, 2""").fetchall():
        print(f"  {t}.{c}: {text}")

    if args.fail_on_blocked and counts.get("Blocked", 0):
        sys.exit(1)


if __name__ == "__main__":
    main()