CREATE TABLE IF NOT EXISTS users (
 id varchar(80) PRIMARY KEY, email varchar(190) NOT NULL UNIQUE, name varchar(190) NOT NULL,
 password_hash varchar(255) NOT NULL, role varchar(20) NOT NULL DEFAULT 'customer',
 profile longtext NOT NULL, session_version int NOT NULL DEFAULT 1, created varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS records (
 id varchar(100) PRIMARY KEY, kind varchar(30) NOT NULL, owner varchar(100) NOT NULL,
 data longtext NOT NULL, created varchar(32) NOT NULL, updated varchar(32) NOT NULL,
 INDEX idx_records_kind_owner(kind,owner)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoices (
 id varchar(100) PRIMARY KEY, owner varchar(80) NOT NULL, order_id varchar(100) NOT NULL,
 amount bigint unsigned NOT NULL, currency char(3) NOT NULL DEFAULT 'USD',
 status varchar(30) NOT NULL DEFAULT 'unpaid', gateway_id varchar(190) NULL UNIQUE,
 checkout_url text NULL, gateway_status varchar(40) NULL, created varchar(32) NOT NULL,
 paid_at varchar(32) NULL, period varchar(30) NOT NULL,
 INDEX idx_invoices_owner(owner), UNIQUE KEY idx_invoice_period(order_id,period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS webhook_events (id varchar(190) PRIMARY KEY, created varchar(32) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings (`key` varchar(100) PRIMARY KEY, value longtext NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS rate_limits (id char(64) PRIMARY KEY, count int NOT NULL DEFAULT 1, expires bigint NOT NULL, INDEX idx_limit_expiry(expires)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS password_resets (token_hash char(64) PRIMARY KEY, user_id varchar(80) NOT NULL, expires bigint NOT NULL, INDEX idx_reset_user(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE assertions (name varchar(100), passed int CHECK (passed=1));
INSERT INTO users(id,email,name,password_hash,role,profile,created) VALUES ('alice','alice@example.test','Alice','hash','customer','{}','2026-09-30T00:00:00Z');
INSERT INTO users(id,email,name,password_hash,role,profile,created) VALUES ('bob','bob@example.test','Bob','hash','customer','{}','2026-09-30T00:00:00Z');
INSERT INTO assertions SELECT 'customer role defaults',COUNT(*)=2 FROM users WHERE role='customer';
INSERT INTO records(id,kind,owner,data,created,updated) VALUES ('ord-1','order','alice','{"status":"awaiting_payment","amount":2400}','2026-09-30T00:00:00Z','2026-09-30T00:00:00Z');
INSERT INTO assertions SELECT 'customer owner filtering',COUNT(*)=0 FROM records WHERE kind='order' AND owner='bob';
INSERT INTO invoices(id,owner,order_id,amount,currency,status,created,period) VALUES ('inv-1','alice','ord-1',2400,'USD','unpaid','2026-09-30T00:00:00Z','initial');
INSERT IGNORE INTO invoices(id,owner,order_id,amount,currency,status,created,period) VALUES ('inv-2','alice','ord-1',2400,'USD','unpaid','2026-09-30T00:00:00Z','initial');
INSERT INTO assertions SELECT 'order period uniqueness',COUNT(*)=1 FROM invoices WHERE order_id='ord-1';
START TRANSACTION;
UPDATE invoices SET status='paid' WHERE id='inv-1';
ROLLBACK;
INSERT INTO assertions SELECT 'payment rollback',status='unpaid' FROM invoices WHERE id='inv-1';
INSERT INTO rate_limits(id,count,expires) VALUES (REPEAT('a',64),1,2000000000) ON DUPLICATE KEY UPDATE count=count+1;
INSERT INTO rate_limits(id,count,expires) VALUES (REPEAT('a',64),1,2000000000) ON DUPLICATE KEY UPDATE count=count+1;
INSERT INTO assertions SELECT 'rate limit atomic increment',count=2 FROM rate_limits;
INSERT INTO settings(`key`,value) VALUES ('store','{"salesEnabled":false}') ON DUPLICATE KEY UPDATE value=VALUES(value);
INSERT INTO settings(`key`,value) VALUES ('store','{"salesEnabled":true}') ON DUPLICATE KEY UPDATE value=VALUES(value);
INSERT INTO assertions SELECT 'settings upsert',value='{"salesEnabled":true}' FROM settings WHERE `key`='store';
INSERT IGNORE INTO webhook_events(id,created) VALUES ('e1','2026-09-30T00:00:00Z');
INSERT IGNORE INTO webhook_events(id,created) VALUES ('e1','2026-09-30T00:00:00Z');
INSERT INTO assertions SELECT 'event deduplication',COUNT(*)=1 FROM webhook_events;