1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

services:
  mysql:
    image: mysql:8.0
    container_name: mysql80
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: root123456
      MYSQL_DATABASE: demo
      MYSQL_USER: app
      MYSQL_PASSWORD: app123456
      TZ: Asia/Shanghai
    ports:
      - "3306:3306"
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_0900_ai_ci
      - --default-time-zone=+08:00
      - --sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    volumes:
      - mysql_data:/var/lib/mysql
volumes:
  mysql_data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

USE demo;

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(32) NOT NULL,
  email VARCHAR(128) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_email(email)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user_ct(user_id, created_at)
) ENGINE=InnoDB;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL,
  email VARCHAR(128) NOT NULL,
  city VARCHAR(32),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_email(email)
) ENGINE=InnoDB;

CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  category VARCHAR(32) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cat_price(category, price)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,        -- 0=created 1=paid 2=shipped 3=done 4=cancel
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user_ct(user_id, created_at),
  KEY idx_status_ct(status, created_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  KEY idx_order(order_id),
  KEY idx_product(product_id)
) ENGINE=InnoDB;

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225

-- =========================================================
-- dataseed for stage-1 project queries
-- Tables: users, products, orders, order_items
-- MySQL 8.0+
-- =========================================================

SET NAMES utf8mb4;
SET sql_safe_updates = 0;

USE demo;

-- -----------------------------
-- 0) Clean & reset
-- -----------------------------
-- If you have FK in future, use TRUNCATE with FK checks disabled.
SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE order_items;
TRUNCATE TABLE orders;
TRUNCATE TABLE products;
TRUNCATE TABLE users;

SET FOREIGN_KEY_CHECKS = 1;

-- -----------------------------
-- 1) Users (12 users, some with NULL/empty city)
-- -----------------------------
INSERT INTO users (id, name, email, city, created_at) VALUES
(1,  'alice',  'alice@test.com',  'Tokyo',   '2026-01-05 09:10:00'),
(2,  'bob',    'bob@test.com',    'Tokyo',   '2026-01-20 12:00:00'),
(3,  'cathy',  'cathy@test.com',  'Osaka',   '2026-01-25 18:30:00'),
(4,  'david',  'david@test.com',  'Osaka',   '2026-02-01 08:00:00'),
(5,  'eric',   'eric@test.com',   'Nagoya',  '2026-02-05 10:15:00'),
(6,  'fiona',  'fiona@test.com',  'Nagoya',  '2026-02-07 14:20:00'),
(7,  'gary',   'gary@test.com',   NULL,      '2026-02-08 11:11:00'), -- city NULL
(8,  'helen',  'helen@test.com',  '',        '2026-02-09 09:09:00'), -- city empty string
(9,  'ivan',   'ivan@test.com',   'Tokyo',   '2026-02-10 16:45:00'),
(10, 'jane',   'jane@test.com',   'Osaka',   '2026-02-11 20:00:00'),
(11, 'kate',   'kate@test.com',   'Tokyo',   '2026-02-12 07:30:00'),
(12, 'leo',    'leo@test.com',    'Nagoya',  '2026-02-12 22:10:00');

-- -----------------------------
-- 2) Products (12 products, 4 categories)
-- categories: phone, laptop, accessory, book
-- -----------------------------
INSERT INTO products (id, name, category, price, created_at) VALUES
(1,  'Phone A',          'phone',     699.00, '2026-01-10 10:00:00'),
(2,  'Phone B Pro',      'phone',     999.00, '2026-01-28 10:00:00'),
(3,  'Laptop Air',       'laptop',   1299.00, '2026-01-15 10:00:00'),
(4,  'Laptop Pro 14',    'laptop',   2199.00, '2026-02-01 10:00:00'),
(5,  'USB-C Cable',      'accessory',   19.90, '2026-01-05 10:00:00'),
(6,  'Charger 65W',      'accessory',   39.90, '2026-02-05 10:00:00'),
(7,  'Earbuds',          'accessory',   89.00, '2026-02-08 10:00:00'),
(8,  'Laptop Sleeve',    'accessory',   29.90, '2026-01-22 10:00:00'),
(9,  'SQL Book Basic',   'book',        49.00, '2026-01-12 10:00:00'),
(10, 'SQL Book Advanced','book',        79.00, '2026-02-03 10:00:00'),
(11, 'Mouse Pro',        'accessory',   59.00, '2026-01-30 10:00:00'),
(12, 'Keyboard Mech',    'accessory',  109.00, '2026-02-07 10:00:00');

-- -----------------------------
-- 3) Orders (18 orders)
-- status: 0=created 1=paid 2=shipped 3=done 4=cancel
-- We include:
-- - users with no orders: user 7,8
-- - created/unpaid, cancel
-- - an order with no items (for anomaly query)
-- - an order with mismatched total_amount vs items sum (for anomaly query)
-- -----------------------------
INSERT INTO orders (id, user_id, status, total_amount, created_at) VALUES
(1001, 1,  3,  788.90, '2026-01-06 10:00:00'), -- done (old)
(1002, 1,  4,   89.00, '2026-01-18 12:00:00'), -- cancel
(1003, 2,  1,  129.80, '2026-01-29 09:30:00'), -- paid
(1004, 3,  2, 1299.00, '2026-02-02 13:00:00'), -- shipped
(1005, 4,  0,   39.90, '2026-02-09 10:05:00'), -- created (unpaid)
(1006, 4,  1,  718.90, '2026-02-10 10:10:00'), -- paid
(1007, 5,  3,  999.00, '2026-02-07 18:00:00'), -- done (recent 7d)
(1008, 5,  3,   98.00, '2026-02-11 08:15:00'), -- done
(1009, 6,  1,  149.80, '2026-02-12 09:00:00'), -- paid
(1010, 6,  4, 2199.00, '2026-02-12 21:00:00'), -- cancel
(1011, 9,  3,  738.90, '2026-02-08 12:00:00'), -- done
(1012, 9,  1,  999.00, '2026-02-13 09:00:00'), -- paid (today)
(1013, 10, 2,  118.90, '2026-02-06 20:00:00'), -- shipped
(1014, 10, 0,  1299.00,'2026-02-13 10:30:00'), -- created (unpaid)
(1015, 11, 1,  227.00, '2026-02-11 22:00:00'), -- paid
(1016, 12, 3,  259.00, '2026-02-10 23:00:00'), -- done
(1017, 2,  3,  109.00, '2026-02-05 09:00:00'), -- done
(1018, 3,  1,   79.00, '2026-02-04 09:00:00'); -- paid

-- -----------------------------
-- 4) Order items
-- Notes:
-- - Order 1002 canceled but has items
-- - Order 1005 created/unpaid has items
-- - Order 1014 created/unpaid has NO items (anomaly)
-- - Order 1015 total_amount mismatched intentionally (anomaly)
-- -----------------------------

-- order 1001 total 788.90 = Phone A(699) + Earbuds(89.0) + Cable(0?) => We'll do 699 + 89 + 0.90? no.
-- Keep it simple: 699 + 89 + 0.90 doesn't exist. We'll set items sum = 788.90 using Cable 19.90 instead:
-- 699 + 89 + 19.90 = 807.90, not 788.90.
-- So adjust: set order 1001 total to 807.90? but already inserted 788.90.
-- We'll create mismatch? No, mismatch is for 1015. Let's fix 1001 by updating total_amount after items inserted.
-- We'll insert items then UPDATE order total for 1001 to match.

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
-- 1001: 699 + 89 + 19.90 = 807.90
(1001, 1, 1, 699.00),
(1001, 7, 1,  89.00),
(1001, 5, 1,  19.90),

-- 1002 canceled: 89.00
(1002, 7, 1,  89.00),

-- 1003 paid: 2x Cable(19.90) + Charger(39.90) + Sleeve(29.90) = 129.60? Actually 39.80 + 39.90 + 29.90 = 109.60
-- We'll do: 2x Cable + 1x Keyboard(109.00) = 148.80; no.
-- Let's align to 129.80 (inserted). Use: Cable(19.90)*2=39.80 + Mouse(59) + Sleeve(29.90)=128.70; not.
-- Use: Cable*2=39.80 + Charger(39.90)=79.70 + Earbuds(49?) no.
-- We'll just adjust order 1003 total later to match items to reduce confusion.
(1003, 5, 2,  19.90),
(1003, 6, 1,  39.90),
(1003, 8, 1,  29.90),

-- 1004 shipped: Laptop Air 1299
(1004, 3, 1, 1299.00),

-- 1005 created/unpaid: Charger 39.90
(1005, 6, 1,  39.90),

-- 1006 paid: Phone A 699 + Charger 39.90 - but inserted 718.90 => need extra  -20? We'll add Cable 19.90 => 758.80
-- We'll adjust order total later.
(1006, 1, 1, 699.00),
(1006, 6, 1,  39.90),
(1006, 5, 1,  19.90),

-- 1007 done: Phone B Pro 999
(1007, 2, 1, 999.00),

-- 1008 done: SQL Basic 49 + SQL Adv 79? = 128, but order total inserted 98
-- We'll make: SQL Basic 49 *2 = 98
(1008, 9, 2,  49.00),

-- 1009 paid: Cable 19.90*1 + Charger 39.90*1 + Mouse 59*1 + Sleeve 29.90*1 = 148.70 (close to 149.80)
-- We'll add 1 more Cable => +19.90 => 168.60. not.
-- We'll just adjust order 1009 total later to match items.
(1009, 5, 1,  19.90),
(1009, 6, 1,  39.90),
(1009, 11,1,  59.00),
(1009, 8, 1,  29.90),

-- 1010 cancel: Laptop Pro 14 2199
(1010, 4, 1, 2199.00),

-- 1011 done: Phone A 699 + Earbuds 89 + Charger 39.90 = 827.90, but total inserted 738.90
-- We'll make it: Phone A 699 + Earbuds 39.90? not.
-- We'll use: Phone A 699 + Mouse 39.90? not.
-- We'll use: Phone A 699 + Cable 19.90 + Sleeve 29.90 = 748.80 (close)
-- Let's do: Phone A 699 + Sleeve 29.90 + Mouse 59 = 787.90
-- Still off. We'll adjust order total later.
(1011, 1, 1, 699.00),
(1011, 8, 1,  29.90),
(1011, 11,1,  59.00),

-- 1012 paid today: Phone B Pro 999
(1012, 2, 1, 999.00),

-- 1013 shipped: Earbuds 89 + Mouse 59 - total inserted 118.90 => make 89 + 29.90 = 118.90
(1013, 7, 1,  89.00),
(1013, 8, 1,  29.90),

-- 1014 created/unpaid: NO ITEMS (intentional anomaly)

-- 1015 paid: mismatch intentionally
-- items sum = 49 + 79 + 59 = 187, but order total inserted 227.00 (mismatch +40)
(1015, 9, 1,  49.00),
(1015, 10,1,  79.00),
(1015, 11,1,  59.00),

-- 1016 done: Keyboard 109 + Mouse 59 + Cable 19.90 + Sleeve 29.90 = 217.80, but total inserted 259 (we can align later)
(1016, 12,1, 109.00),
(1016, 11,1,  59.00),
(1016, 5, 1,  19.90),
(1016, 8, 1,  29.90),

-- 1017 done: Keyboard 109
(1017, 12,1, 109.00),

-- 1018 paid: SQL Adv 79
(1018, 10,1,  79.00);

-- -----------------------------
-- 5) Fix totals (keep most orders consistent except 1015 mismatch and 1014 no-items)
-- -----------------------------
-- Update order totals to match item sums for these orders:
UPDATE orders o
JOIN (
  SELECT order_id, ROUND(SUM(quantity * unit_price), 2) AS item_sum
  FROM order_items
  GROUP BY order_id
) x ON x.order_id = o.id
SET o.total_amount = x.item_sum
WHERE o.id IN (1001,1003,1006,1009,1011,1016);

-- Keep 1015 mismatch intentionally
-- Keep 1014 no-items intentionally

-- -----------------------------
-- 6) Quick sanity checks (optional)
-- -----------------------------
-- 6.1 Users with no orders
-- SELECT u.id, u.name FROM users u LEFT JOIN orders o ON o.user_id=u.id WHERE o.id IS NULL;

-- 6.2 Orders with no items
-- SELECT o.id FROM orders o LEFT JOIN order_items oi ON oi.order_id=o.id WHERE oi.id IS NULL;

-- 6.3 Orders with total mismatch (excluding no-items)
-- SELECT o.id, o.total_amount, x.item_sum
-- FROM orders o
-- JOIN (
--   SELECT order_id, ROUND(SUM(quantity*unit_price),2) AS item_sum
--   FROM order_items
--   GROUP BY order_id
-- ) x ON x.order_id=o.id
-- WHERE o.total_amount <> x.item_sum;

-- Done.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248

-- =========================================================
-- Stable data generator for MySQL 8.0.45 (NO CTE, NO reopen)
-- Tables: users, products, orders, order_items
-- =========================================================

USE demo;

-- ----------------------------
-- 0) Parameters (adjust here)
-- ----------------------------
SET @USER_CNT            := 20000;    -- users
SET @PRODUCT_CNT         := 500;      -- products
SET @ORDER_CNT           := 100000;   -- orders
SET @MAX_ITEMS_PER_ORDER := 5;        -- 1..5 items per order
SET @DAYS_RANGE          := 180;      -- orders distributed in last N days
SET @ANOMALY_RATE        := 0.002;    -- e.g. 0.2% anomalies

-- Derived max N for sequence table (only generate what we need)
SET @MAX_N := GREATEST(@USER_CNT, @PRODUCT_CNT, @ORDER_CNT);

-- For speed (dev env only)
SET SESSION autocommit = 0;
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;

-- ----------------------------
-- 1) Truncate target tables
-- ----------------------------
TRUNCATE TABLE order_items;
TRUNCATE TABLE orders;
TRUNCATE TABLE products;
TRUNCATE TABLE users;

-- ----------------------------
-- 2) Build sequence table seq(n) = 1..@MAX_N
--    Key point: DO NOT reference the same (temporary) table multiple times.
--    We build seq using ONLY inline derived digits sets.
-- ----------------------------
DROP TABLE IF EXISTS seq;
CREATE TABLE seq (
  n INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

-- Insert numbers 1..@MAX_N
INSERT INTO seq(n)
SELECT x.n
FROM (
  SELECT
    (d0.n
     + d1.n*10
     + d2.n*100
     + d3.n*1000
     + d4.n*10000
     + d5.n*100000) + 1 AS n
  FROM
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d0
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d5
) x
WHERE x.n <= @MAX_N;

ANALYZE TABLE seq;

-- ----------------------------
-- 3) Insert users
-- ----------------------------
INSERT INTO users (name, email, city, created_at)
SELECT
  CONCAT('user_', s.n) AS name,
  CONCAT('user_', s.n, '@example.com') AS email,
  CASE (s.n % 8)
    WHEN 0 THEN 'Tokyo'
    WHEN 1 THEN 'Osaka'
    WHEN 2 THEN 'Nagoya'
    WHEN 3 THEN 'Fukuoka'
    WHEN 4 THEN 'Sapporo'
    WHEN 5 THEN 'Kyoto'
    WHEN 6 THEN NULL
    ELSE ''
  END AS city,
  NOW() - INTERVAL (s.n % 365) DAY - INTERVAL (s.n % 86400) SECOND AS created_at
FROM seq s
WHERE s.n <= @USER_CNT;

-- ----------------------------
-- 4) Insert products
-- ----------------------------
INSERT INTO products (name, category, price, created_at)
SELECT
  CONCAT(
    CASE (s.n % 4)
      WHEN 0 THEN 'Phone'
      WHEN 1 THEN 'Laptop'
      WHEN 2 THEN 'Accessory'
      ELSE 'Book'
    END,
    ' ', s.n
  ) AS name,
  CASE (s.n % 4)
    WHEN 0 THEN 'phone'
    WHEN 1 THEN 'laptop'
    WHEN 2 THEN 'accessory'
    ELSE 'book'
  END AS category,
  CASE (s.n % 4)
    WHEN 0 THEN 399 + (s.n % 800)          -- phone: 399~1198
    WHEN 1 THEN 899 + (s.n % 2200)         -- laptop: 899~3098
    WHEN 2 THEN 9.90 + (s.n % 200)         -- accessory: 9.90~209.90
    ELSE 19 + (s.n % 120)                  -- book: 19~139
  END AS price,
  NOW() - INTERVAL (s.n % 180) DAY AS created_at
FROM seq s
WHERE s.n <= @PRODUCT_CNT;

-- ----------------------------
-- 5) Insert orders (total_amount initially 0)
-- ----------------------------
INSERT INTO orders (user_id, status, total_amount, created_at)
SELECT
  1 + (s.n % @USER_CNT) AS user_id,
  CASE
    WHEN (s.n % 100) < 10 THEN 0          -- 10% created
    WHEN (s.n % 100) < 60 THEN 1          -- 50% paid
    WHEN (s.n % 100) < 75 THEN 2          -- 15% shipped
    WHEN (s.n % 100) < 95 THEN 3          -- 20% done
    ELSE 4                               -- 5% cancel
  END AS status,
  0.00 AS total_amount,
  NOW()
    - INTERVAL (s.n % @DAYS_RANGE) DAY
    - INTERVAL (s.n % 86400) SECOND AS created_at
FROM seq s
WHERE s.n <= @ORDER_CNT;

-- Record inserted orders id range (TRUNCATE => usually starts at 1, but query it anyway)
SELECT MIN(id), MAX(id) INTO @ORDER_ID_START, @ORDER_ID_END FROM orders;

-- ----------------------------
-- 6) Insert order_items
--    Each order has 1..@MAX_ITEMS_PER_ORDER items (determined by order_id%MAX)
--    product_id computed deterministically from (order_id, item_idx)
-- ----------------------------
-- Inline item indices 1..5 (expand if you set MAX_ITEMS_PER_ORDER > 5)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
  o.id AS order_id,
  p.id AS product_id,
  1 + ((o.id + i.k) % 3) AS quantity,
  p.price AS unit_price
FROM orders o
JOIN (
  SELECT 1 AS k UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) i
  ON i.k <= 1 + (o.id % @MAX_ITEMS_PER_ORDER)
JOIN products p
  ON p.id = 1 + ((o.id * 17 + i.k * 31) % @PRODUCT_CNT)
WHERE o.id BETWEEN @ORDER_ID_START AND @ORDER_ID_END;

-- ----------------------------
-- 7) Backfill orders.total_amount = SUM(items)
-- ----------------------------
UPDATE orders o
JOIN (
  SELECT oi.order_id, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS item_sum
  FROM order_items oi
  GROUP BY oi.order_id
) x ON x.order_id = o.id
SET o.total_amount = x.item_sum;

-- ----------------------------
-- 8) Inject anomalies (optional)
--    A) no-items orders: delete items of last @ANOMALY_ORDERS orders
--    B) amount mismatch: add +10 to another chunk of orders
-- ----------------------------
SET @ANOMALY_ORDERS := CEIL(@ORDER_CNT * @ANOMALY_RATE);
SET @ANOMALY_ORDERS := IF(@ANOMALY_ORDERS < 1, 1, @ANOMALY_ORDERS);

-- A) Delete items for last N orders => "orders with no items"
DELETE FROM order_items
WHERE order_id > (@ORDER_ID_END - @ANOMALY_ORDERS);

-- B) Pick previous chunk and corrupt total_amount (+10) => "amount mismatch"
UPDATE orders
SET total_amount = total_amount + 10
WHERE id BETWEEN (@ORDER_ID_END - 2*@ANOMALY_ORDERS)
            AND (@ORDER_ID_END - @ANOMALY_ORDERS - 1);

-- Re-align totals for orders that still have items (keep anomalies as anomalies)
UPDATE orders o
JOIN (
  SELECT oi.order_id, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS item_sum
  FROM order_items oi
  GROUP BY oi.order_id
) x ON x.order_id = o.id
SET o.total_amount = x.item_sum
WHERE o.id <= (@ORDER_ID_END - @ANOMALY_ORDERS);

-- ----------------------------
-- 9) Analyze & commit
-- ----------------------------
ANALYZE TABLE users;
ANALYZE TABLE products;
ANALYZE TABLE orders;
ANALYZE TABLE order_items;

COMMIT;

SET SESSION foreign_key_checks = 1;
SET SESSION unique_checks = 1;
SET SESSION autocommit = 1;

-- ----------------------------
-- 10) Sanity checks
-- ----------------------------
SELECT 'users' AS tbl, COUNT(*) AS cnt FROM users
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

-- Orders with no items
SELECT COUNT(*) AS orders_no_items
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE oi.id IS NULL;

-- Amount mismatch orders (only those that still have items)
SELECT COUNT(*) AS orders_amount_mismatch
FROM orders o
JOIN (
  SELECT order_id, ROUND(SUM(quantity * unit_price), 2) AS item_sum
  FROM order_items
  GROUP BY order_id
) x ON x.order_id = o.id
WHERE o.total_amount <> x.item_sum;