#!/usr/bin/env python3
"""Generate a finite independent table-only proposal; performs no DB access."""
import json
from pathlib import Path
root=Path(__file__).parent
spec={'status':'PROPOSAL_ONLY_NOT_AUTHORIZED','marker':'CDXSS7','budget':{'tables_per_lane':1,'rows_max_per_table':6,'DML_attempts_per_lane':19,'DDL_B':2,'DDL_C':4,'SELECT_per_lane_max':120,'HTTP':0,'business_table_writes':0,'external':0},'lanes':{}}
for lane in ('B','C'):
 table='cdx_ss7_261011'if lane=='B'else'ZHCT_APP.CDX_SS7_261011'
 if lane=='B':
  create='CREATE TABLE '+table+''' (ID INT NOT NULL PRIMARY KEY,NULL_V VARCHAR(8) NULL,NULL_N INT NULL,NULL_T DATETIME(0) NULL,LOB_VALUE MEDIUMTEXT NOT NULL,DINGTALK_AVATAR VARCHAR(1024) NOT NULL DEFAULT '',UPDATE_TIME DATETIME(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,DINGTALK_USERID VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,PRIMARY_DEPARTMENT_UUID CHAR(36) NULL DEFAULT NULL,DINGTALK_LAST_SEEN_FULL_BATCH_ID BIGINT UNSIGNED NULL DEFAULT NULL,DINGTALK_SYNC_TIME DATETIME(0) NULL DEFAULT NULL,DINGTALK_SYNC_MD5 CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',UNIQUE KEY UK_CDX_SS7_USERID(DINGTALK_USERID)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci'''
  index=None;cleanup=['DROP TABLE '+table]
 else:
  create='CREATE TABLE '+table+''' (ID INT NOT NULL PRIMARY KEY,NULL_V VARCHAR(8 CHAR) NULL,NULL_N INT NULL,NULL_T DATETIME(0) NULL,LOB_VALUE CLOB NOT NULL,DINGTALK_AVATAR VARCHAR(1024 CHAR) DEFAULT '' NOT NULL,UPDATE_TIME DATETIME(0) DEFAULT CURRENT_TIMESTAMP(0) NOT NULL,DINGTALK_USERID VARCHAR(128 CHAR) DEFAULT NULL,PRIMARY_DEPARTMENT_UUID CHAR(36 CHAR) DEFAULT NULL,DINGTALK_LAST_SEEN_FULL_BATCH_ID DECIMAL(20,0) DEFAULT NULL,DINGTALK_SYNC_TIME DATETIME(0) DEFAULT NULL,DINGTALK_SYNC_MD5 CHAR(32) DEFAULT '' NOT NULL,CONSTRAINT CK_CDX_SS7_U64 CHECK (DINGTALK_LAST_SEEN_FULL_BATCH_ID IS NULL OR DINGTALK_LAST_SEEN_FULL_BATCH_ID BETWEEN 0 AND CAST('18446744073709551615' AS DECIMAL(20,0))),CONSTRAINT CK_CDX_SS7_ASCII CHECK (LENGTHB(DINGTALK_SYNC_MD5)=LENGTH(DINGTALK_SYNC_MD5)))'''
  index='CREATE UNIQUE INDEX ZHCT_APP.UX_CDX_SS7_USERID ON '+table+' (BINARY(DINGTALK_USERID))'
  cleanup=['DROP INDEX ZHCT_APP.UX_CDX_SS7_USERID','DROP TABLE '+table]
 steps=[];insert='INSERT INTO '+table+' (ID,NULL_V,LOB_VALUE,DINGTALK_USERID) VALUES (?,?,?,?)'
 for label,pk,value,hypothesis in [('S01-null1',1,None,'accept'),('S02-null2',2,None,'accept'),('S03-upper',3,'A','accept'),('S04-lower',4,'a','accept'),('S05-tail-duplicate',5,'A ','duplicate-reject'),('S06-empty',5,'','accept'),('S07-space-duplicate',6,' ','duplicate-reject'),('S08-empty-duplicate',6,'','duplicate-reject')]:
  steps.append({'label':label,'SQL':insert,'binds':[pk,'CDXSS7','',value],'hypothesis':hypothesis})
 steps.append({'label':'S09-max-capacities','SQL':'INSERT INTO '+table+' (ID,NULL_V,LOB_VALUE,DINGTALK_USERID,DINGTALK_AVATAR,PRIMARY_DEPARTMENT_UUID,DINGTALK_LAST_SEEN_FULL_BATCH_ID,DINGTALK_SYNC_TIME,DINGTALK_SYNC_MD5) VALUES (?,?,?,?,?,?,?,?,?)','binds':[6,'CDXSS7','','😀'*128,'😀'*1024,'12345678-1234-1234-1234-123456789ABC','18446744073709551615','2001-01-01 01:01:01','AbCd'*8],'hypothesis':'accept-full-characters-bytes-unsigned-max'})
 for number,value in enumerate(['0','9007199254740991','9007199254740993','9223372036854775807','18446744073709551615',None],10):
  steps.append({'label':'S'+str(number)+'-uint64','SQL':'UPDATE '+table+' SET DINGTALK_LAST_SEEN_FULL_BATCH_ID=? WHERE ID=?','binds':[value,6],'hypothesis':'accept-storage-text-exact','binding_type':'PDO_PARAM_STR_except_NULL'})
 for number,value in [(16,'A '),(17,'a '),(18,'é')]:
  steps.append({'label':'S'+str(number)+'-md5-char','SQL':'UPDATE '+table+' SET DINGTALK_SYNC_MD5=? WHERE ID=?','binds':[value,6],'hypothesis':'source-observed-CHAR-padding-and-ASCII-invalid-input; never force-target-green'})
 steps.append({'label':'S19-avatar-last-byte','SQL':'UPDATE '+table+' SET DINGTALK_AVATAR=?,DINGTALK_USERID=? WHERE ID=?','binds':['😀'*1023+'😁','😀'*127+'😁',6],'hypothesis':'accept-capacity-last-character-preserved'})
 assert len(steps)==19
 spec['lanes'][lane]={'table':table,'create_table':create,'create_unique_index':index,'steps':steps,'cleanup':cleanup}
output=root/'probe-character-unique-uint64-proposal.json'
assert not output.exists(),'Do not replace a reviewed proposal'
output.write_text(json.dumps(spec,ensure_ascii=False,indent=2)+'\n')
print(output.name)