"""TRACE-01: isolated additive tables. Never touches the Event business ledger."""
from contextlib import contextmanager
from datetime import datetime, timedelta, timezone
import json
import os

KST = timezone(timedelta(hours=9))


def filters(value):
    if not isinstance(value, dict) or set(value) - {'camera','request','event','from','to'}:
        raise ValueError('TRACE_FILTER_INVALID')
    terms, args = [], []
    for key, col in [('camera','camera_id'),('request','request_id'),('event','event_id')]:
        if value.get(key) is not None:
            v = value[key]
            if not isinstance(v, str) or not 1 <= len(v) <= 128:
                raise ValueError('TRACE_ID_INVALID')
            terms.append(col+'=%s'); args.append(v)
    dates = {}
    for key in ('from','to'):
        if value.get(key):
            v = value[key]
            d = datetime.strptime(v, '%Y-%m-%d').replace(tzinfo=KST)
            if d.strftime('%Y-%m-%d') != v:
                raise ValueError('TRACE_DATE_INVALID')
            dates[key] = d
            terms.append('recorded_at >= %s' if key == 'from' else 'recorded_at < %s')
            args.append(d if key == 'from' else d+timedelta(days=1))
    if len(dates) == 2 and dates['from'] > dates['to']:
        raise ValueError('TRACE_DATE_ORDER')
    return terms, args


class Store:
    def __init__(self, server, dsn=None):
        self.server = server
        self.dsn = dsn or os.environ.get('DATABASE_URL')
        self.initialized = False

    @contextmanager
    def connection(self):
        import psycopg2
        from psycopg2.extras import RealDictCursor
        con = psycopg2.connect(self.dsn, connect_timeout=2, application_name='contextra-trace',
            options='-c statement_timeout=1500 -c lock_timeout=200 -c idle_in_transaction_session_timeout=3000')
        try:
            with con:
                with con.cursor(cursor_factory=RealDictCursor) as cur:
                    yield cur
        finally:
            con.close()

    def initialize(self):
        if self.initialized:
            return
        with self.connection() as cur:
            cur.execute('''CREATE TABLE IF NOT EXISTS contextra_trace_records (
                seq BIGSERIAL PRIMARY KEY, trace_id TEXT UNIQUE NOT NULL, server TEXT NOT NULL,
                camera_id TEXT, event_id TEXT, request_id TEXT, operation_id TEXT,
                recorded_at TIMESTAMPTZ NOT NULL, doc JSONB NOT NULL)''')
            for name, columns in [('time','server,recorded_at,seq'),
                    ('camera','server,camera_id,recorded_at,seq'),('request','server,request_id,seq'),
                    ('event','server,event_id,seq')]:
                cur.execute(f'CREATE INDEX IF NOT EXISTS contextra_trace_{name}_idx ON contextra_trace_records ({columns})')
        self.initialized = True

    def write(self, records):
        self.initialize()
        from psycopg2.extras import execute_values
        values = []
        for d in records:
            def ident(key):
                v = d.get(key)
                return str(v)[:128] if type(v) in (str,int) else None
            values.append((d['traceId'],self.server,ident('cameraId'),ident('eventId'),ident('requestId'),
                ident('operationId'),datetime.fromtimestamp(d['recordedAtMs']/1000,timezone.utc),json.dumps(d,ensure_ascii=False)))
        with self.connection() as cur:
            execute_values(cur, '''INSERT INTO contextra_trace_records
                    (trace_id,server,camera_id,event_id,request_id,operation_id,recorded_at,doc)
                    VALUES %s ON CONFLICT(trace_id) DO NOTHING''', values, page_size=16)

    def query(self, action, value):
        if value.get('server') != self.server:
            raise ValueError('TRACE_SERVER_MISMATCH')
        terms, args = filters(value.get('filters', {}))
        where = 'server=%s' + ''.join(' AND '+x for x in terms)
        args = [self.server, *args]
        limit = value.get('limit', 30)
        after = value.get('after', 0)
        cutoff = value.get('cutoff')
        if type(limit) is not int or not 1 <= limit <= 100 or type(after) is not int or after < 0:
            raise ValueError('TRACE_PAGE_INVALID')
        if cutoff is not None:
            if type(cutoff) is not int or cutoff < 0:
                raise ValueError('TRACE_CUTOFF_INVALID')
            where += ' AND seq<=%s'; args.append(cutoff)
        with self.connection() as cur:
            if action == 'plan':
                cur.execute('SELECT COALESCE(MAX(seq),0) AS cutoff,COUNT(*) AS count FROM contextra_trace_records WHERE '+where, args)
                return dict(cur.fetchone(), server=self.server, filters=value.get('filters', {}))
            if action == 'clean':
                if cutoff is None or value.get('confirm') != 'DELETE TRACE':
                    raise ValueError('TRACE_DELETE_CONFIRM_REQUIRED')
                cur.execute('''DELETE FROM contextra_trace_records WHERE seq IN (
                    SELECT seq FROM contextra_trace_records WHERE '''+where+'''
                    ORDER BY seq LIMIT 200 FOR UPDATE SKIP LOCKED) RETURNING seq''', args)
                return {'deleted':len(cur.fetchall()), 'diskReclaimedBytes':None}
            if action == 'list':
                full = value.get('detail') is True
                if full and limit > 5:
                    raise ValueError('TRACE_DETAIL_PAGE_LIMIT')
                columns = 'doc' if full else "doc - 'requestBody' - 'responseBody'"
                cur.execute('SELECT seq,'+columns+' AS doc FROM contextra_trace_records WHERE '+where+' AND seq>%s ORDER BY seq LIMIT %s', [*args,after,limit])
                rows = [dict(x) for x in cur.fetchall()]
                return {'rows':rows,'next':rows[-1]['seq'] if rows else after}
            if action == 'show':
                seq = value.get('seq')
                if type(seq) is not int or seq < 1:
                    raise ValueError('TRACE_SEQ_INVALID')
                cur.execute('SELECT seq,doc FROM contextra_trace_records WHERE '+where+' AND seq=%s', [*args,seq])
                row = cur.fetchone()
                return {'row':dict(row) if row else None}
            if action == 'status':
                cur.execute("SELECT pg_total_relation_size('contextra_trace_records') AS bytes")
                return {'tableBytes':cur.fetchone()['bytes'], 'autoCleanup':False}
            raise ValueError('TRACE_ACTION_INVALID')