-- 1. SETUP: Create local InnoDB tables and one remote SPIDER table
-- (Note: 'testdb_tms_proxy' and 'testdb' are schema names used in the original test case)

CREATE SCHEMA IF NOT EXISTS testdb;
CREATE SCHEMA IF NOT EXISTS testdb_tms_proxy;

CREATE SERVER tps_master
FOREIGN DATA WRAPPER mysql
OPTIONS (
  HOST '127.0.0.1',
  PORT 54321,
  USER 'user',
  PASSWORD 'pwd',
  DATABASE 'testdb'
);


-- Local table to be updated
CREATE TABLE testdb.test (
    id INT auto_increment NOT NULL,
    name varchar(100) NULL,
    CONSTRAINT test_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO testdb.test(id, name) VALUES(1,'test1');

-- Remote table target (can be on the same server for testing)
CREATE TABLE testdb.test2 (
    id INT auto_increment NOT NULL,
    name varchar(100) NULL,
    CONSTRAINT test2_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

-- SPIDER table pointing to the remote target
-- Replace 'tps_master' with a valid server link
CREATE TABLE testdb.test3 (
    id INT auto_increment NOT NULL,
    name varchar(100) NULL,
    CONSTRAINT test3_pk PRIMARY KEY (id)
) ENGINE=SPIDER COMMENT='wrapper "mysql", srv "tps_master", table "test2"';


-- 2. SETUP: Create a procedure and trigger that reference the SPIDER table

DELIMITER $$

CREATE PROCEDURE testdb.testProc()
BEGIN
    -- This conditional logic is intentionally FALSE.
    -- The SELECT on the SPIDER table will never run.
    SET @t := FALSE;
    IF @t = TRUE THEN
        SELECT name INTO @b FROM testdb.test3 WHERE id = 1;
    END IF;
END$$

CREATE TRIGGER testdb.test_au
AFTER UPDATE
ON testdb.test FOR EACH ROW
BEGIN
    -- This condition will also be false in our test case.
    IF NEW.name = '3' THEN
        CALL testdb.testProc();
    END IF;
END$$

DELIMITER ;


-- 3. TEST CASE 1: The working two-phase commit
SELECT "Using 2PC...";
XA START 'TESTTRX_2P';
UPDATE testdb.test SET name = 'abc' WHERE ID = 1;
SHOW WARNINGS;
XA END 'TESTTRX_2P';
XA PREPARE 'TESTTRX_2P';
XA COMMIT 'TESTTRX_2P';
-- Result: SUCCESS. Query OK.


-- 4. TEST CASE 2: The failing one-phase commit

SELECT "Using 1PC...";
XA START 'TESTTRX_1P';
UPDATE testdb.test SET name = 'abc' WHERE ID = 1;
SHOW WARNINGS;
XA END 'TESTTRX_1P';
XA COMMIT 'TESTTRX_1P' ONE PHASE;
-- Result: FAILURE.

