import re
from decimal import Decimal

def ident(v):
    if not re.fullmatch(r'[A-Za-z0-9_]+',v): raise ValueError('Unsupported identifier')
    return '"'+v.upper()+'"'

def literal(v):
    if v is None:return 'NULL'
    parts=[]
    for p in re.split(r'([\x00-\x1f\x7f])',str(v)):
        if not p:continue
        parts.append('CHR('+str(ord(p))+')' if len(p)==1 and (ord(p)<32 or ord(p)==127) else "'"+p.replace("'","''")+"'")
    return '||'.join(parts) if parts else "''"

def numeric(v):
    if not re.fullmatch(r'-?\d+(?:\.\d+)?(?:[Ee][+-]?\d+)?',v) or not Decimal(v).is_finite():raise ValueError('Invalid numeric')
    return v

def target_type(c):
    t=c['DATA_TYPE'];u='unsigned' in c['COLUMN_TYPE']
    if t=='tinyint':return 'SMALLINT'
    if t=='int':return 'BIGINT' if u else 'INT'
    if t=='bigint':return 'DECIMAL(20,0)' if u else 'BIGINT'
    if t=='decimal':return 'DECIMAL(%s,%s)'%(c['NUMERIC_PRECISION'],c['NUMERIC_SCALE'])
    if t=='double':return 'DOUBLE'
    if t in ('varchar','enum'):return 'VARCHAR(%d CHAR)'%c['CHARACTER_MAXIMUM_LENGTH']
    if t.endswith('text'):return 'CLOB'
    if t=='json':return 'JSONB'
    if t=='date':return 'DATE'
    if t in ('datetime','timestamp'):return 'TIMESTAMP(%d)'%(c['DATETIME_PRECISION'] or 0)
    raise ValueError('Unreviewed type '+t)

NUMBERS={'tinyint','int','bigint','decimal','double'}
def default_sql(c):
    d=c['COLUMN_DEFAULT']
    if d is None or 'auto_increment' in c['EXTRA']:return ''
    if re.fullmatch(r'CURRENT_TIMESTAMP(?:\(\d+\))?',str(d),re.I):return ' DEFAULT '+str(d).upper()
    if c['DATA_TYPE'] in NUMBERS:return ' DEFAULT '+numeric(str(d))
    if c['DATA_TYPE'] in ('date','datetime','timestamp') and str(d).startswith('0000-'):raise ValueError('Zero default requires decision')
    return ' DEFAULT '+literal(d)

def value_sql(c,v):
    if v is None:return 'NULL'
    if c['DATA_TYPE'] in NUMBERS:return numeric(v)
    if c['DATA_TYPE'] in ('date','datetime','timestamp'):
        if not re.match(r'\d{4}-\d{2}-\d{2}',v) or v[:4]=='0000' or v[5:7]=='00' or v[8:10]=='00':raise ValueError('Zero/invalid temporal requires decision')
    return literal(v)