"""Canonical submission events with independent source and decision evidence."""
from datetime import datetime, timezone
import re
from paper_atlas.io.publication_ids import mlsys_id


PUBLICATIONS={'pmlr','acl_anthology','colm','neurips','mlsys','cvf','ecva','dblp'}


def timestamp(value=None):
    date=datetime.now(timezone.utc) if value is None else datetime.fromisoformat(value.replace('Z','+00:00'))
    if date.tzinfo is None:raise ValueError('Decision timestamp requires a timezone')
    return date.astimezone(timezone.utc).isoformat(timespec='milliseconds').replace('+00:00','Z')


def identities_for_row(row):
    identities=list(row.get('submission_identifiers') or [])
    own=row.get('identifiers') or {}
    if row['source']=='mlsys':
        value=own.get('publication_mlsys')
        if (not isinstance(value,str) or not re.fullmatch(str(row['year'])+r'/[a-f0-9]{32}',value)
                or row['venue']!='MLSys'):
            raise ValueError('Invalid authoritative MLSys publisher identity')
    if own.get('openreview'):
        identities.append({'scheme':'openreview','value':own['openreview']})
    if row['source'] in PUBLICATIONS and row.get('status')=='accepted' and own.get('doi'):
        doi=re.sub(r'^https?://(?:dx\.)?doi\.org/','',str(own['doi']),flags=re.I).strip().lower()
        if re.fullmatch(r'10\.\d{4,9}/\S+',doi):identities.append({'scheme':'publication_doi','value':doi})
    publication_mlsys = own.get('publication_mlsys')
    if not publication_mlsys and row['source']=='dblp' and row['venue']=='MLSys':
        # Older normalized DBLP files retain their own publisher links even
        # when a newly supported historical URL label was not yet parsed.
        links = (row.get('metadata') or {}).get('bibliography', {}).get('ee', [])
        candidates = {identity for link in links if (identity := mlsys_id(link))}
        if len(candidates)==1:
            publication_mlsys = candidates.pop()
    if row['source'] in PUBLICATIONS and row.get('status')=='accepted' and publication_mlsys:
        identities.append({'scheme':'publication_mlsys','value':publication_mlsys})
    meta=row.get('metadata') or {}
    if row['source']=='iclr_program' and row['venue']=='ICLR' and meta.get('program_identity_type')=='submission' and meta.get('program_submission_number_verified') is True:
        identities.append({'scheme':'iclr_number','value':str(meta['program_source_id'])})
    result=set()
    for identity in identities:
        scheme,value=identity['scheme'],str(identity['value']).strip()
        if scheme=='openreview':
            valid=re.fullmatch(r'[A-Za-z0-9_-]+',value) and not re.match(r'20\d{2}-Oral-',value)
        elif scheme=='iclr_number':valid=re.fullmatch(r'[1-9]\d*',value) and row['venue']=='ICLR'
        elif scheme=='publication_doi':valid=re.fullmatch(r'10\.\d{4,9}/\S+',value)
        elif scheme=='publication_mlsys':
            valid=(re.fullmatch(str(row['year'])+r'/[a-f0-9]{32}',value)
                   and row['venue']=='MLSys' and row['source'] in PUBLICATIONS and row.get('status')=='accepted')
        else:valid=False
        if not valid:raise ValueError('Invalid authoritative submission identity')
        result.add((scheme,value))
    return sorted(result)


def migrate_sources(c):
    if c.execute("SELECT 1 FROM schema_migrations WHERE name='submission_source_baseline_v1'").fetchone():return
    with c:
        c.execute('INSERT INTO submission_sources SELECT id,source_record_id,source_submission_id FROM submissions WHERE 1=1 ON CONFLICT DO NOTHING')
        c.execute("INSERT INTO decision_sources SELECT d.id,s.source_record_id,'' FROM decisions d JOIN submissions s ON s.id=d.submission_id WHERE 1=1 ON CONFLICT DO NOTHING")
        c.execute("INSERT INTO schema_migrations(name) VALUES('submission_source_baseline_v1')")


def next_id(c,table,audit,former):
    # Merged IDs are kept in audit; never allocate them to a later unrelated event.
    if getattr(c, 'postgres', False):
        return c.next_id(table)
    return max(c.execute('SELECT COALESCE(max(id),0) FROM '+table).fetchone()[0],c.execute('SELECT COALESCE(max('+former+'),0) FROM '+audit).fetchone()[0])+1


def latest_status(c, submission):
    row = c.execute('''SELECT status FROM decisions WHERE submission_id=?
        ORDER BY observed_at DESC,COALESCE(decision_date,observed_at) DESC,id DESC LIMIT 1''', (submission,)).fetchone()
    return row[0] if row else None


def merge_submissions(c,target,other):
    if target==other:return
    editions=c.execute('SELECT id,edition_id FROM submissions WHERE id IN (?,?)',(target,other)).fetchall()
    if len(editions)!=2 or editions[0][1]!=editions[1][1]:raise ValueError('Cannot combine submissions from different editions')
    c.execute('INSERT INTO submission_sources SELECT ?,source_record_id,source_submission_id FROM submission_sources WHERE submission_id=? ON CONFLICT DO NOTHING',(target,other))
    c.execute('DELETE FROM submission_sources WHERE submission_id=?',(other,))
    for decision,status,raw,url,observed,date in c.execute('SELECT id,status,raw_value,evidence_url,observed_at,decision_date FROM decisions WHERE submission_id=?',(other,)).fetchall():
        duplicate=c.execute('SELECT id,decision_date FROM decisions WHERE submission_id=? AND status=? AND raw_value=? AND evidence_url=? AND observed_at=?',(target,status,raw,url,observed)).fetchone()
        if duplicate:
            surviving=duplicate[0]
            if date and duplicate[1] and date!=duplicate[1]:raise ValueError('Conflicting dates for the same decision observation')
            if date and not duplicate[1]:c.execute('UPDATE decisions SET decision_date=? WHERE id=?',(date,surviving))
            c.execute('INSERT INTO decision_sources SELECT ?,source_record_id,source_decision_id FROM decision_sources WHERE decision_id=? ON CONFLICT DO NOTHING',(surviving,decision))
            c.execute('DELETE FROM decision_sources WHERE decision_id=?',(decision,))
            c.execute('UPDATE decision_merges SET surviving_decision_id=? WHERE surviving_decision_id=?',(surviving,decision))
            c.execute("INSERT INTO decision_merges(former_decision_id,surviving_decision_id,reason) VALUES(?,?,'same decision observation on merged submission')",(decision,surviving))
            c.execute('DELETE FROM decisions WHERE id=?',(decision,))
        else:c.execute('UPDATE decisions SET submission_id=? WHERE id=?',(target,decision))
    c.execute('UPDATE submission_identifiers SET submission_id=? WHERE submission_id=?',(target,other))
    c.execute('UPDATE submission_merges SET surviving_submission_id=? WHERE surviving_submission_id=?',(target,other))
    c.execute("INSERT INTO submission_merges(former_submission_id,surviving_submission_id,reason) VALUES(?,?,'shared authoritative submission identity')",(other,target))
    c.execute('DELETE FROM submissions WHERE id=?',(other,))


def attach_submission(store,edition,paper,record,external_id,identities):
    c=store.conn
    matches={r[0] for r in c.execute('SELECT s.id FROM submission_sources p JOIN submissions s ON s.id=p.submission_id WHERE p.source_record_id=? AND p.source_submission_id=? AND s.edition_id=?',(record,external_id,edition))}
    matches.update(r[0] for r in c.execute('SELECT id FROM submissions WHERE source_record_id=? AND source_submission_id=? AND edition_id=?',(record,external_id,edition)))
    for scheme,value in identities:
        found=c.execute('SELECT submission_id FROM submission_identifiers WHERE edition_id=? AND scheme=? AND value=?',(edition,scheme,value)).fetchone()
        if found:matches.add(found[0])
    for scheme in ('openreview','iclr_number'):
        values={value for kind,value in identities if kind==scheme}
        for sid in matches:
            values.update(value for value, in c.execute('SELECT value FROM submission_identifiers WHERE submission_id=? AND scheme=?',(sid,scheme)))
        if len(values)>1:raise ValueError('Conflicting authoritative submission identities: '+scheme)
    if matches:
        papers={paper}|{c.execute('SELECT paper_id FROM submissions WHERE id=?',(sid,)).fetchone()[0] for sid in matches}
        surviving=min(papers)
        for other in sorted(papers-{surviving}):store.merge(surviving,other,'shared authoritative conference submission identity')
        submission=min(matches)
        for other in sorted(matches-{submission}):merge_submissions(c,submission,other)
        c.execute('UPDATE submissions SET paper_id=? WHERE id=?',(surviving,submission))
    else:
        submission=next_id(c,'submissions','submission_merges','former_submission_id')
        c.execute('INSERT INTO submissions(id,paper_id,edition_id,source_record_id,source_submission_id) VALUES(?,?,?,?,?)',(submission,paper,edition,record,external_id))
    c.execute('INSERT INTO submission_sources VALUES(?,?,?) ON CONFLICT DO NOTHING',(submission,record,external_id))
    for scheme,value in identities:
        c.execute('INSERT INTO submission_identifiers VALUES(?,?,?,?) ON CONFLICT DO NOTHING',(edition,scheme,value,submission))
    return submission


def write_decision(store,submission,record,status,raw,url,observed_at=None,decision_date=None,source_decision_id=''):
    c=store.conn
    observed=timestamp(observed_at)
    date=timestamp(decision_date) if decision_date else None
    # Re-observing unchanged evidence adds provenance, not a new decision event.
    old=c.execute('SELECT id FROM decisions WHERE submission_id=? AND status=? AND raw_value=? AND evidence_url=? AND COALESCE(decision_date,\'\')=COALESCE(?,\'\')',(submission,status,raw,url,date)).fetchone()
    if old:decision=old[0]
    else:
        decision=next_id(c,'decisions','decision_merges','former_decision_id')
        c.execute('INSERT INTO decisions(id,submission_id,status,raw_value,evidence_url,observed_at,decision_date) VALUES(?,?,?,?,?,?,?)',(decision,submission,status,raw,url,observed,date))
    c.execute('INSERT INTO decision_sources VALUES(?,?,?) ON CONFLICT DO NOTHING',(decision,record,source_decision_id or ''))
    return decision