-- Simple approach — works on MySQL 5.x
-- Downside: VARCHAR(36) uses 36 bytes vs 16 bytes for BINARY(16)
-- Also causes B-tree index fragmentation with random UUIDs

CREATE TABLE users (
    id         VARCHAR(36)  NOT NULL DEFAULT (UUID()),
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

SELECT id, name FROM users;

-- BINARY(16) with swap_flag=1 — best for high-insert-rate tables
-- swap_flag reorders time bits so sequential UUIDs are adjacent in the index
-- This eliminates B-tree page splits and dramatically improves INSERT performance

CREATE TABLE events (
    id         BINARY(16)   NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
    event_type VARCHAR(50)  NOT NULL,
    payload    JSON,
    created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    INDEX idx_created (created_at)
);

-- Generate UUID in application and pass it in
-- (recommended for idempotent inserts)
INSERT INTO events (id, event_type, payload)
VALUES (UUID_TO_BIN('6ccd780c-baba-1026-9564-0040f4311e29', 1), 'order.created', '{"orderId": 42}');

-- Read back as UUID string
SELECT BIN_TO_UUID(id, 1) AS id, event_type, created_at
FROM events
ORDER BY created_at DESC
LIMIT 10;

DELIMITER $$

CREATE PROCEDURE create_order(
    IN  p_customer VARCHAR(100),
    IN  p_total    DECIMAL(10,2),
    OUT p_id       VARCHAR(36)
)
BEGIN
    SET p_id = UUID();
    INSERT INTO orders (id, customer, total)
    VALUES (UUID_TO_BIN(p_id, 1), p_customer, p_total);
END$$

DELIMITER ;

-- Call the procedure
CALL create_order('Alice', 99.99, @order_id);
SELECT @order_id AS created_order_id;