test-15-jan.sql

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

DROP TABLE IF EXISTS seq_1000;
DROP TABLE IF EXISTS seq_10000;


CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  country VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status ENUM('paid','pending','cancelled') NOT NULL
);

CREATE TABLE payments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  paid_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  method ENUM('card','transfer','cash') NOT NULL
);

-- =========================
-- Helpers: seq_10000 (0..9999)
-- =========================
CREATE TABLE seq_10000 (
  n INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO seq_10000 (n)
SELECT
  d0.d + d1.d*10 + d2.d*100 + d3.d*1000 AS n
FROM
  (SELECT 0 d 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 d 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 d 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 d 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;

-- =========================
-- Helpers: seq_1000 (0..999)
-- =========================
CREATE TABLE seq_1000 (
  n INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO seq_1000 (n)
SELECT
  d0.d + d1.d*10 + d2.d*100 AS n
FROM
  (SELECT 0 d 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 d 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 d 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;

-- =========================
-- Insertar 100,000 customers
-- =========================
INSERT INTO customers (name, country)
SELECT
  CONCAT('Customer ', s.n + d0.d*10000 + 1) AS name,
  CASE ((s.n + d0.d*10000) % 6)
    WHEN 0 THEN 'Mexico'
    WHEN 1 THEN 'USA'
    WHEN 2 THEN 'Canada'
    WHEN 3 THEN 'Spain'
    WHEN 4 THEN 'Colombia'
    ELSE 'Argentina'
  END AS country
FROM seq_10000 s
CROSS JOIN (
  SELECT 0 d 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;

-- =========================
-- Insertar 10,000,000 orders
-- =========================
INSERT INTO orders (customer_id, order_date, total, status)
SELECT
  1 + ((a.n + b.n*10000) % 100000) AS customer_id,
  DATE_ADD('2023-01-01', INTERVAL ((a.n + b.n*10000) % 1095) DAY) AS order_date,
  ROUND(10 + ((a.n + b.n*10000) % 250000) / 10, 2) AS total,
  CASE
    WHEN ((a.n + b.n*10000) % 100) < 50 THEN 'paid'
    WHEN ((a.n + b.n*10000) % 100) < 85 THEN 'pending'
    ELSE 'cancelled'
  END AS status
FROM seq_10000 a
CROSS JOIN seq_1000 b;

-- =========================
-- Payments (opcional)
-- =========================
INSERT INTO payments (order_id, paid_at, amount, method)
SELECT
  o.id AS order_id,
  DATE_ADD(CONCAT(o.order_date, ' 08:00:00'), INTERVAL (o.id % 720) MINUTE) AS paid_at,
  o.total AS amount,
  CASE (o.id % 3)
    WHEN 0 THEN 'card'
    WHEN 1 THEN 'transfer'
    ELSE 'cash'
  END AS method
FROM orders o
WHERE o.status = 'paid';

DROP TABLE seq_1000;
DROP TABLES seq_10000;