#!/usr/bin/env python3
# -*- coding: utf-8 -*-
'''OK ITHAKI V249374 community lexicon service.

Keeps unknown terms from the existing unknown_translation_terms.csv, records every
manual correction as an immutable revision, and imports the current approved value
into the existing local ITHAKI dictionary through add_custom_translation().
'''
from __future__ import annotations
from pathlib import Path
from typing import Any
import csv, hashlib, json, sqlite3, threading, time, uuid

_LOCK = threading.RLock()


def _deps():
    from local_dictionary_service import DICT_DIR, UNKNOWN_CSV, add_custom_translation, norm_lang, norm_key_loose
    return DICT_DIR, UNKNOWN_CSV, add_custom_translation, norm_lang, norm_key_loose


def _paths():
    d, unknown, *_ = _deps()
    d.mkdir(parents=True, exist_ok=True)
    return d, unknown, d / 'community_lexicon_v249374.sqlite3', d / 'community_translation_helper_v249374.csv'


def _conn():
    _, _, db, _ = _paths()
    con=sqlite3.connect(str(db),timeout=30)
    con.row_factory=sqlite3.Row
    con.execute('PRAGMA journal_mode=WAL')
    con.execute('PRAGMA synchronous=NORMAL')
    con.execute('PRAGMA busy_timeout=30000')
    return con


def _clean(value: Any, limit: int=500) -> str:
    return ' '.join(str(value or '').replace('\x00',' ').split())[:limit]


def _lang(value: Any, fallback='el') -> str:
    *_, norm_lang, _ = _deps()
    v=norm_lang(_clean(value,12))
    return v if v in ('el','en') else fallback


def _kind(value: Any, source='') -> str:
    v=_clean(value,16).lower()
    return 'phrase' if v=='phrase' or ' ' in _clean(source,500) else 'word'


def entry_key(src_lang: str, dst_lang: str, source: str, kind: str='word') -> str:
    *_, norm_key_loose = _deps()
    src=_lang(src_lang,'el'); dst=_lang(dst_lang,'en'); source=_clean(source,500); kind=_kind(kind,source)
    norm=norm_key_loose(source) or source.lower()
    return hashlib.sha256(f'{src}\x1f{dst}\x1f{kind}\x1f{norm}'.encode('utf-8')).hexdigest()


def init_db():
    with _LOCK, _conn() as con:
        con.executescript('''
        CREATE TABLE IF NOT EXISTS lexicon_entries(
          entry_key TEXT PRIMARY KEY,
          src_lang TEXT NOT NULL,
          dst_lang TEXT NOT NULL,
          source TEXT NOT NULL,
          kind TEXT NOT NULL DEFAULT 'word',
          context TEXT NOT NULL DEFAULT '',
          first_seen REAL NOT NULL,
          status TEXT NOT NULL DEFAULT 'pending',
          current_target TEXT NOT NULL DEFAULT '',
          revision_count INTEGER NOT NULL DEFAULT 0,
          last_editor_id TEXT NOT NULL DEFAULT '',
          last_editor_name TEXT NOT NULL DEFAULT '',
          last_editor_avatar TEXT NOT NULL DEFAULT '',
          last_public_credit INTEGER NOT NULL DEFAULT 0,
          updated_at REAL NOT NULL,
          reopened_at REAL NOT NULL DEFAULT 0
        );
        CREATE TABLE IF NOT EXISTS lexicon_revisions(
          revision_id TEXT PRIMARY KEY,
          entry_key TEXT NOT NULL,
          target TEXT NOT NULL,
          editor_id TEXT NOT NULL DEFAULT '',
          editor_name TEXT NOT NULL DEFAULT '',
          editor_avatar TEXT NOT NULL DEFAULT '',
          public_credit INTEGER NOT NULL DEFAULT 0,
          notes TEXT NOT NULL DEFAULT '',
          created_at REAL NOT NULL,
          FOREIGN KEY(entry_key) REFERENCES lexicon_entries(entry_key)
        );
        CREATE INDEX IF NOT EXISTS idx_lexicon_entries_status ON lexicon_entries(status,updated_at DESC);
        CREATE INDEX IF NOT EXISTS idx_lexicon_revisions_entry ON lexicon_revisions(entry_key,created_at DESC);
        CREATE INDEX IF NOT EXISTS idx_lexicon_revisions_editor ON lexicon_revisions(editor_id,created_at DESC);
        ''')
        con.commit()
    sync_unknowns()
    export_helper_csv()
    return True


def sync_unknowns() -> dict[str,Any]:
    d, unknown, *_ = _deps()
    d.mkdir(parents=True,exist_ok=True)
    if not unknown.exists():
        unknown.write_text('created_at,src_lang,dst_lang,term,suggested_translation,kind,context,notes\n',encoding='utf-8-sig')
    added=0; seen=0
    with _LOCK, _conn() as con:
        with unknown.open('r',encoding='utf-8-sig',errors='replace',newline='') as f:
            for row in csv.DictReader(f):
                source=_clean(row.get('term'),500)
                if not source: continue
                src=_lang(row.get('src_lang'),'el'); dst=_lang(row.get('dst_lang'),'en'); kind=_kind(row.get('kind'),source)
                key=entry_key(src,dst,source,kind); seen+=1
                try: first=float(row.get('created_at') or time.time())
                except Exception: first=time.time()
                context=_clean(row.get('context'),700)
                suggested=_clean(row.get('suggested_translation'),500)
                cur=con.execute('SELECT entry_key,current_target,status FROM lexicon_entries WHERE entry_key=?',(key,)).fetchone()
                if cur is None:
                    con.execute('''INSERT INTO lexicon_entries(entry_key,src_lang,dst_lang,source,kind,context,first_seen,status,current_target,updated_at)
                                   VALUES(?,?,?,?,?,?,?,?,?,?)''',(key,src,dst,source,kind,context,first,'pending',suggested,time.time()))
                    added+=1
                elif context:
                    con.execute('UPDATE lexicon_entries SET context=CASE WHEN context="" THEN ? ELSE context END WHERE entry_key=?',(context,key))
        con.commit()
    return {'ok':True,'source_rows':seen,'new_entries':added,'unknown_file':str(unknown)}


def _row_out(r: sqlite3.Row|dict) -> dict[str,Any]:
    d=dict(r)
    d['public_editor'] = bool(d.pop('last_public_credit',0))
    if not d['public_editor']:
        d['last_editor_name']=''; d['last_editor_avatar']=''; d['last_editor_id']=''
    return d


def list_entries(status='pending',q='',limit=100,offset=0) -> dict[str,Any]:
    init_db_if_needed()
    sync_unknowns()
    status=_clean(status,20).lower(); q=_clean(q,160).lower(); limit=max(1,min(int(limit or 100),300)); offset=max(0,int(offset or 0))
    where=[]; args=[]
    if status in ('pending','reopened'):
        where.append("status IN ('pending','reopened')")
    elif status=='translated': where.append("status='translated'")
    elif status=='all': pass
    else: where.append("status IN ('pending','reopened')")
    if q:
        where.append('(lower(source) LIKE ? OR lower(current_target) LIKE ? OR lower(context) LIKE ?)')
        like=f'%{q}%'; args += [like,like,like]
    clause=(' WHERE '+' AND '.join(where)) if where else ''
    with _conn() as con:
        total=con.execute('SELECT COUNT(*) c FROM lexicon_entries'+clause,args).fetchone()['c']
        rows=con.execute('SELECT * FROM lexicon_entries'+clause+' ORDER BY CASE status WHEN "reopened" THEN 0 WHEN "pending" THEN 1 ELSE 2 END, updated_at DESC LIMIT ? OFFSET ?',args+[limit,offset]).fetchall()
        counts={r['status']:r['c'] for r in con.execute('SELECT status,COUNT(*) c FROM lexicon_entries GROUP BY status').fetchall()}
    return {'ok':True,'status':status,'total':total,'counts':counts,'items':[_row_out(r) for r in rows]}


def save_translation(payload: dict[str,Any]) -> dict[str,Any]:
    init_db_if_needed()
    source=_clean(payload.get('source'),500); target=_clean(payload.get('target'),500)
    if not source or not target: return {'ok':False,'note':'Χρειάζεται λέξη/φράση και μετάφραση.'}
    src=_lang(payload.get('src_lang'),'el'); dst=_lang(payload.get('dst_lang'),'en')
    if src==dst: return {'ok':False,'note':'Η γλώσσα προέλευσης και προορισμού πρέπει να είναι διαφορετική.'}
    kind=_kind(payload.get('kind'),source); key=_clean(payload.get('entry_key'),80) or entry_key(src,dst,source,kind)
    editor_id=_clean(payload.get('editor_id'),140); editor_name=_clean(payload.get('editor_name'),100) or 'Ανώνυμος μεταφραστής'
    editor_avatar=_clean(payload.get('editor_avatar'),1200); public_credit=1 if bool(payload.get('public_credit')) else 0
    notes=_clean(payload.get('notes'),500); context=_clean(payload.get('context'),700); ts=time.time(); rid='lexrev_'+uuid.uuid4().hex
    _,_,add_custom_translation,_,_=_deps()
    # This immediately adds the correction to the existing local dictionary used by bridge_translate().
    dict_result=add_custom_translation(source,target,src,dst,kind)
    with _LOCK, _conn() as con:
        con.execute('''INSERT OR IGNORE INTO lexicon_entries(entry_key,src_lang,dst_lang,source,kind,context,first_seen,status,current_target,updated_at)
                       VALUES(?,?,?,?,?,?,?,?,?,?)''',(key,src,dst,source,kind,context,ts,'pending','',ts))
        con.execute('''INSERT INTO lexicon_revisions(revision_id,entry_key,target,editor_id,editor_name,editor_avatar,public_credit,notes,created_at)
                       VALUES(?,?,?,?,?,?,?,?,?)''',(rid,key,target,editor_id,editor_name,editor_avatar,public_credit,notes,ts))
        con.execute('''UPDATE lexicon_entries SET src_lang=?,dst_lang=?,source=?,kind=?,context=CASE WHEN ?<>'' THEN ? ELSE context END,
                       status='translated',current_target=?,revision_count=revision_count+1,last_editor_id=?,last_editor_name=?,last_editor_avatar=?,last_public_credit=?,updated_at=?
                       WHERE entry_key=?''',(src,dst,source,kind,context,context,target,editor_id,editor_name,editor_avatar,public_credit,ts,key))
        con.commit()
    export_helper_csv()
    return {'ok':True,'entry_key':key,'revision_id':rid,'source':source,'target':target,'dictionary':dict_result,'note':'Αποθηκεύτηκε στο βοηθητικό λεξικό και είναι διαθέσιμο για επόμενες μεταφράσεις.'}


def reopen_entry(payload: dict[str,Any]) -> dict[str,Any]:
    init_db_if_needed(); key=_clean(payload.get('entry_key'),80); ts=time.time()
    if not key: return {'ok':False,'note':'Λείπει η καταχώριση.'}
    with _LOCK, _conn() as con:
        cur=con.execute("UPDATE lexicon_entries SET status='reopened',reopened_at=?,updated_at=? WHERE entry_key=?",(ts,ts,key))
        con.commit()
    return {'ok':bool(cur.rowcount),'entry_key':key,'note':'Η λέξη εμφανίζεται ξανά για επεξεργασία.'}


def revisions(entry_key_value: str,limit=50) -> dict[str,Any]:
    init_db_if_needed(); key=_clean(entry_key_value,80); limit=max(1,min(int(limit or 50),100))
    with _conn() as con:
        rows=con.execute('SELECT * FROM lexicon_revisions WHERE entry_key=? ORDER BY created_at DESC LIMIT ?',(key,limit)).fetchall()
    out=[]
    for r in rows:
        d=dict(r); pub=bool(d.get('public_credit'))
        if not pub: d['editor_id']=d['editor_name']=d['editor_avatar']=''
        d['public_credit']=pub; out.append(d)
    return {'ok':True,'entry_key':key,'items':out}


def contributors(limit=100) -> dict[str,Any]:
    init_db_if_needed(); limit=max(1,min(int(limit or 100),200))
    with _conn() as con:
        rows=con.execute('''SELECT editor_id,MAX(editor_name) editor_name,MAX(editor_avatar) editor_avatar,
                            COUNT(*) revisions,COUNT(DISTINCT entry_key) entries,MAX(created_at) last_at
                            FROM lexicon_revisions WHERE public_credit=1 AND trim(editor_name)<>''
                            GROUP BY CASE WHEN trim(editor_id)<>'' THEN editor_id ELSE editor_name END
                            ORDER BY entries DESC,revisions DESC,last_at DESC LIMIT ?''',(limit,)).fetchall()
    return {'ok':True,'items':[dict(r) for r in rows]}


def export_helper_csv() -> dict[str,Any]:
    d,_,_,export_path=_paths(); d.mkdir(parents=True,exist_ok=True)
    with _LOCK, _conn() as con:
        rows=con.execute("SELECT src_lang,dst_lang,source,current_target target,kind,revision_count,updated_at FROM lexicon_entries WHERE trim(current_target)<>'' ORDER BY src_lang,dst_lang,source").fetchall()
    tmp=export_path.with_suffix('.tmp')
    with tmp.open('w',encoding='utf-8-sig',newline='') as f:
        w=csv.writer(f); w.writerow(['src_lang','dst_lang','source','target','kind','priority','origin','revision_count','updated_at'])
        for r in rows: w.writerow([r['src_lang'],r['dst_lang'],r['source'],r['target'],r['kind'],100,'community-lexicon-v249374',r['revision_count'],int(r['updated_at'])])
    tmp.replace(export_path)
    return {'ok':True,'rows':len(rows),'path':str(export_path)}


def status() -> dict[str,Any]:
    init_db_if_needed(); sync=sync_unknowns(); d,unknown,db,export_path=_paths()
    with _conn() as con:
        counts={r['status']:r['c'] for r in con.execute('SELECT status,COUNT(*) c FROM lexicon_entries GROUP BY status').fetchall()}
        revisions_count=con.execute('SELECT COUNT(*) c FROM lexicon_revisions').fetchone()['c']
        contributors_count=con.execute("SELECT COUNT(DISTINCT CASE WHEN trim(editor_id)<>'' THEN editor_id ELSE editor_name END) c FROM lexicon_revisions WHERE public_credit=1 AND trim(editor_name)<>''").fetchone()['c']
    return {'ok':True,'version':'V249374','counts':counts,'revisions':revisions_count,'public_contributors':contributors_count,'unknown_file':str(unknown),'helper_db':str(db),'helper_csv':str(export_path),'sync':sync}


def init_db_if_needed():
    d,unknown,db,export_path=_paths()
    if db.exists(): return
    # Avoid recursive init_db -> sync_unknowns -> _conn issue by creating schema directly.
    with _LOCK, _conn() as con:
        con.executescript('''
        CREATE TABLE IF NOT EXISTS lexicon_entries(entry_key TEXT PRIMARY KEY,src_lang TEXT NOT NULL,dst_lang TEXT NOT NULL,source TEXT NOT NULL,kind TEXT NOT NULL DEFAULT 'word',context TEXT NOT NULL DEFAULT '',first_seen REAL NOT NULL,status TEXT NOT NULL DEFAULT 'pending',current_target TEXT NOT NULL DEFAULT '',revision_count INTEGER NOT NULL DEFAULT 0,last_editor_id TEXT NOT NULL DEFAULT '',last_editor_name TEXT NOT NULL DEFAULT '',last_editor_avatar TEXT NOT NULL DEFAULT '',last_public_credit INTEGER NOT NULL DEFAULT 0,updated_at REAL NOT NULL,reopened_at REAL NOT NULL DEFAULT 0);
        CREATE TABLE IF NOT EXISTS lexicon_revisions(revision_id TEXT PRIMARY KEY,entry_key TEXT NOT NULL,target TEXT NOT NULL,editor_id TEXT NOT NULL DEFAULT '',editor_name TEXT NOT NULL DEFAULT '',editor_avatar TEXT NOT NULL DEFAULT '',public_credit INTEGER NOT NULL DEFAULT 0,notes TEXT NOT NULL DEFAULT '',created_at REAL NOT NULL);
        CREATE INDEX IF NOT EXISTS idx_lexicon_entries_status ON lexicon_entries(status,updated_at DESC);
        CREATE INDEX IF NOT EXISTS idx_lexicon_revisions_entry ON lexicon_revisions(entry_key,created_at DESC);
        CREATE INDEX IF NOT EXISTS idx_lexicon_revisions_editor ON lexicon_revisions(editor_id,created_at DESC);
        '''); con.commit()


def import_corrections(payload: dict[str,Any]) -> dict[str,Any]:
    init_db_if_needed()
    items = payload.get('items') or []
    if not isinstance(items, list):
        return {'ok':False,'note':'items must be a list'}
    items = items[:5000]
    imported=0; failed=0; errors=[]
    default_editor=_clean(payload.get('editor_name'),100) or 'Ιδιωτική εισαγωγή CSV'
    public_credit=bool(payload.get('public_credit'))
    for i,row in enumerate(items, start=1):
        if not isinstance(row,dict):
            failed+=1; continue
        data=dict(row)
        data.setdefault('editor_name',default_editor)
        data.setdefault('public_credit',public_credit)
        data.setdefault('notes','r10f41-csv-import')
        result=save_translation(data)
        if result.get('ok'): imported+=1
        else:
            failed+=1
            if len(errors)<20: errors.append({'row':i,'note':result.get('note','failed')})
    export=export_helper_csv()
    return {'ok':True,'imported':imported,'failed':failed,'errors':errors,'export':export}

def sync_unknowns_report() -> dict[str,Any]:
    init_db_if_needed()
    sync=sync_unknowns()
    with _conn() as con:
        counts={r['status']:r['c'] for r in con.execute('SELECT status,COUNT(*) c FROM lexicon_entries GROUP BY status').fetchall()}
    return {'ok':True,**sync,'pending':int(counts.get('pending',0))+int(counts.get('reopened',0)),'translated':int(counts.get('translated',0))}

if __name__=='__main__':
    init_db(); print(json.dumps(status(),ensure_ascii=False,indent=2))
