-- sysbench Lua script for MariaDB Cascading FK Test
-- Fixed for sysbench 1.0.20 API

-- 1. Define custom options (This MUST be global)
sysbench.cmdline.options = {
   table_size = {"Number of parent rows", 10000},
   child_ratio = {"Number of child rows per parent", 10}
}

-- 2. Thread initialization (standard for 1.0.x)
function thread_init()
   drv = sysbench.sql.driver()
   con = drv:connect()
end

-- 3. Schema Preparation
function prepare()
   -- We fetch the options inside the function to avoid 'nil' errors
   local t_size = sysbench.opt.table_size
   local c_ratio = sysbench.opt.child_ratio

   local drv = sysbench.sql.driver()
   local con = drv:connect()

   print("Cleaning up old tables...")
   con:query("DROP TABLE IF EXISTS t2")
   con:query("DROP TABLE IF EXISTS t1")

   print("Creating parent table 't1'...")
   con:query("CREATE TABLE t1 (id INT NOT NULL, val CHAR(50), PRIMARY KEY (id)) ENGINE=InnoDB")

   print("Creating child table 't2' with Virtual Column and FK Cascade...")
   con:query([[
      CREATE TABLE t2 (
          id INT NOT NULL AUTO_INCREMENT,
          t1_id INT NOT NULL,
          v_col INT AS (t1_id + 1) VIRTUAL,
          data CHAR(50),
          PRIMARY KEY (id),
          KEY (t1_id),
          KEY (v_col),
          CONSTRAINT fk_t1 FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
      ) ENGINE=InnoDB
   ]])

   print(string.format("Populating %d parents with %d children each...", t_size, c_ratio))
   con:query("SET FOREIGN_KEY_CHECKS=0")
   for i = 1, t_size do
      con:query(string.format("INSERT INTO t1 (id, val) VALUES (%d, 'p')", i))
      for j = 1, c_ratio do
         con:query(string.format("INSERT INTO t2 (t1_id, data) VALUES (%d, 'c')", i))
      end
   end
   con:query("SET FOREIGN_KEY_CHECKS=1")
   print("Done.")
end

-- 4. The Runtime Event
function event()
   local t_size = sysbench.opt.table_size
   local c_ratio = sysbench.opt.child_ratio
   
   local id = sysbench.rand.default(1, t_size)

   -- Wrap in a transaction to prevent other threads from 
   -- seeing a "missing" parent row during the swap.
   con:query("BEGIN")

   -- 1. Trigger the cascade
   con:query(string.format("DELETE FROM t1 WHERE id = %d", id))

   -- 2. Re-insert parent
   con:query(string.format("INSERT INTO t1 (id, val) VALUES (%d, 'p')", id))
   
   -- 3. Re-insert children
   for j = 1, c_ratio do
      con:query(string.format("INSERT INTO t2 (t1_id, data) VALUES (%d, 'c')", id))
   end

   con:query("COMMIT")
end

-- 5. Cleanup
function cmd_cleanup()
    local drv = sysbench.sql.driver()
    local con = drv:connect()
    con:query("DROP TABLE IF EXISTS t2")
    con:query("DROP TABLE IF EXISTS t1")
end