#!/usr/bin/env python3
"""Generate a reviewable scratch plan; performs no HTTP, DB, DDL, or DML."""
import hashlib
import json
from pathlib import Path

base = 'CDXUTCCFF927311-Alpha'
old = '2001-01-01 01:01:01'
explicit = '2002-02-02 02:02:02'
long_a, long_b = 'a' * 127 + 'A', 'a' * 127 + 'B'
tests = [
    ('T01-noop-self', base, 'id=id,name=name,flag=flag', 'old', 1),
    ('T02-flag', base, 'flag=1', 'clock', 1),
    ('T03-name', base, "name='CDXUTCCFF927311-Beta'", 'clock', 1),
    ('T04-id', base, 'id=2', 'clock', 2),
    ('T05-explicit-value', base, "flag=1,update_time='" + explicit + "'", 'explicit', 1),
    ('T06-explicit-self', base, 'flag=1,update_time=update_time', 'old', 1),
    ('T07-case', base, "name='CDXUTCCFF927311-alpha'", 'clock', 1),
    ('T08-tail-space', base, "name='CDXUTCCFF927311-Alpha '", 'clock', 1),
    ('T09-accent', base, "name='CDXUTCCFF927311-Álpha'", 'clock', 1),
    ('T10-noop-literal', base, "name='" + base + "'", 'old', 1),
    ('T11-capacity128', long_a, "name='" + long_b + "'", 'clock', 1),
    ('T12-unicode', 'CDXUTCCFF927311-中文é', "name='CDXUTCCFF927311-中文É'", 'clock', 1),
]
trigger = '''CREATE TRIGGER TRG_CDX_CCFF927311_UT
BEFORE UPDATE ON CDX_CCFF927311_UT_SCRATCH
FOR EACH ROW
BEGIN
  IF NOT UPDATING('UPDATE_TIME') AND (
      :NEW.ID <> :OLD.ID
      OR :NEW.FLAG <> :OLD.FLAG
      OR LENGTHB(:NEW.NAME) <> LENGTHB(:OLD.NAME)
      OR UTL_RAW.COMPARE(
           CAST(CHARTOBIN(:NEW.NAME) AS VARBINARY(512)),
           CAST(CHARTOBIN(:OLD.NAME) AS VARBINARY(512))
         ) <> 0
  ) THEN
    :NEW.UPDATE_TIME := TO_TIMESTAMP(
      TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS'),
      'YYYY-MM-DD HH24:MI:SS'
    );
  END IF;
END;'''
plan = {'authorization': 'PROPOSAL_ONLY_NOT_EXECUTED', 'marker': 'CDXUTCCFF927311', 'budget': {'HTTP': 0, 'tables_total': 2, 'rows_per_table_max': 1, 'DDL_B': 2, 'DDL_C': 4, 'DML_per_lane': 25, 'read_SELECT_per_lane_max': 100, 'external': 0}, 'lanes': {}}
for lane in ('B', 'C'):
    table = 'cdx_ccff927311_ut_scratch' if lane == 'B' else 'CDX_CCFF927311_UT_SCRATCH'
    create = "CREATE TABLE " + table + " (id INT NOT NULL PRIMARY KEY,name VARCHAR(128) NOT NULL,flag " + ('TINYINT' if lane == 'B' else 'SMALLINT') + " NOT NULL DEFAULT 0,update_time " + ('DATETIME(0)' if lane == 'B' else 'TIMESTAMP(0)') + " NOT NULL DEFAULT CURRENT_TIMESTAMP" + (' ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci' if lane == 'B' else ')')
    cases, current_id = [], 1
    for label, name, assignment, expected, next_id in tests:
        cases.append({'label': label, 'reset_SQL': f"UPDATE {table} SET id=1,name='{name}',flag=0,update_time='{old}' WHERE id={current_id}", 'test_SQL': f'UPDATE {table} SET {assignment} WHERE id=1', 'readback_SQL': f'SELECT * FROM {table}', 'expected_timestamp_hypothesis': expected, 'old': old, 'explicit': explicit})
        current_id = next_id
    plan['lanes'][lane] = {'table': table, 'create_table': create, 'create_trigger': trigger if lane == 'C' else None, 'seed_SQL': f"INSERT INTO {table} (id,name,flag) VALUES (1,'{base}',0)", 'cases': cases, 'cleanup': (['DROP TRIGGER TRG_CDX_CCFF927311_UT'] if lane == 'C' else []) + ['DROP TABLE ' + table]}
output = Path(__file__).parent / 'scratch-proposal.json'
data = json.dumps(plan, ensure_ascii=False, indent=2) + '\n'
output.write_text(data)
print(json.dumps({'proposal_only': True, 'path': output.name, 'sha256': hashlib.sha256(data.encode()).hexdigest(), 'cases_per_lane': len(tests), 'DML_per_lane': 1 + len(tests) * 2}))