-- EVENT STORE事件儲存表
    CREATE TABLE events (
        sequence BIGINT UNSIGNED PRIMARY KEY,
        event_type VARCHAR(32) NOT NULL,
        aggregate_type VARCHAR(32) NOT NULL,
        aggregate_id CHAR(36) NULL,
        payload JSON NOT NULL,
        recorded_at DATETIME NOT NULL,
        KEY idx_event_type (event_type),
        KEY idx_aggregate_id (aggregate_id),
        KEY idx_aggregate_type (aggregate_type)
    ) //

-- EVENT STORE TRIGGERS觸發器
    -- Before Insert
    CREATE TRIGGER events_before_insert BEFORE INSERT ON events FOR EACH ROW
    BEGIN
        DECLARE next_sequence BIGINT DEFAULT 0;

        SELECT COALESCE(new.sequence, COALESCE(MAX(sequence), 0) + 1) FROM events INTO next_sequence;

        SET new.sequence = next_sequence;
        SET new.recorded_at = COALESCE(new.recorded_at, NOW());
    END //

    -- Before Update
    CREATE TRIGGER events_before_update BEFORE UPDATE ON events FOR EACH ROW
    BEGIN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '[E001] The event store is immutable (append only), updating events is not allowed.';
    END //

    -- Before Delete
    CREATE TRIGGER events_before_delete BEFORE DELETE ON events FOR EACH ROW
    BEGIN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '[E001] The event store is immutable (append only), deleting events is not allowed.';
    END //

-- EVENT STORE RECORDING MECHANISM
    CREATE PROCEDURE record_event (IN event_type VARCHAR(32), IN aggregate_type VARCHAR(32), IN aggregate_id CHAR(36), IN payload JSON)
    BEGIN
        DECLARE recorded_at DATETIME DEFAULT NOW();

        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            ROLLBACK;
            RESIGNAL;
        END;

        START TRANSACTION;
            INSERT INTO events (event_type, aggregate_type, aggregate_id, payload, recorded_at) VALUES (event_type, aggregate_type, aggregate_id, payload, recorded_at);
            CALL process_event(event_type, aggregate_id, payload, recorded_at);
        COMMIT;
    END //

-- EVENT HANDLING
    CREATE PROCEDURE process_event (IN event_type VARCHAR(32), IN aggregate_id CHAR(36), IN payload JSON, IN recorded_at DATETIME)
    BEGIN
        DECLARE handler_name VARCHAR(38) DEFAULT CONCAT('handle', event_type);

        DECLARE EXIT HANDLER FOR 1305 BEGIN END; -- Procedure does not exist, which is okay.

        SET @handler_sql = CONCAT('CALL ', handler_name, '(?,?,?)');

        PREPARE prepared_statement FROM @handler_sql;

        SET @aggregate_id = aggregate_id;
        SET @payload = payload;
        SET @recorded_at = recorded_at;

        EXECUTE prepared_statement USING @aggregate_id, @payload, @recorded_at;
    END //

-- HELPERS
    CREATE PROCEDURE map_error (errors JSON)
    BEGIN
        GET DIAGNOSTICS CONDITION 1
                @error_number = MYSQL_ERRNO,
                @error_message = MESSAGE_TEXT;

        SET @error_message = JSON_UNQUOTE(COALESCE(JSON_EXTRACT(errors, CONCAT('$.e', @error_number)), @error_message));

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_message;
    END //










-- SAMPLE IMPLEMENTATION
    CREATE TABLE customer (
        customer_id CHAR(36) PRIMARY KEY,
        first_name VARCHAR(64) NOT NULL,
        last_name VARCHAR(64) NOT NULL,
        phone_number VARCHAR(15) NOT NULL,
        email VARCHAR(255) NOT NULL,
        password CHAR(98) NOT NULL,
        registered DATETIME NOT NULL,
        UNIQUE KEY idx_email (email)
    ) //

    -- This is the signature all handlers should implement: (IN aggregate_id CHAR(36), IN payload JSON, IN recorded_at DATETIME)
    CREATE PROCEDURE handleUserRegistered (IN aggregate_id CHAR(36), IN payload JSON, IN recorded_at DATETIME)
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            CALL map_error(JSON_OBJECT(
                -- map e{error_code} to a more domain specific message
                'e1062', 'A customer with this e-mail address already exists'
            ));
        END;

        INSERT INTO customer (customer_id, first_name, last_name, phone_number, email, password, registered)
        VALUES (
            aggregate_id,
            payload->>"$.firstName",
            payload->>"$.lastName",
            payload->>"$.phoneNumber",
            payload->>"$.email",
            payload->>"$.passwordHash",
            recorded_at
        );
    END //

DELIMITER ;

-- This yields an entry in the customer table
CALL record_event ('UserRegistered', 'User', '5d1e0bcd-6cfb-47af-b4f8-8159f390163a', '{"firstName": "John", "lastName": "Doe", "phoneNumber": "+31612345678", "email": "john@doe.com", "passwordHash": "[xxx]"}');

-- Trying to create this customer again results in an error with the following message 'A customer with this e-mail address already exists'
CALL record_event ('UserRegistered', 'User', '5d1e0bcd-6cfb-47af-b4f8-8159f390163a', '{"firstName": "John", "lastName": "Doe", "phoneNumber": "+31612345678", "email": "john@doe.com", "passwordHash": "[xxx]"}');