Convert SQL Server to SQLite

-- Written by IntoSQL from a SQL Server dump. Load it with: sqlite3 -bail <database> < <file>
PRAGMA foreign_keys = OFF;
PRAGMA synchronous = OFF;
BEGIN;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    status VARCHAR(10) DEFAULT 'pending' NOT NULL,
    total TEXT NOT NULL,
    items INTEGER DEFAULT 1 NOT NULL,
    ordered_at DATETIME NOT NULL,
    shipped_at DATETIME,
    note TEXT,
    details TEXT,
    order_ref TEXT NOT NULL,
    total_with_tax DECIMAL(12,2) GENERATED ALWAYS AS (CAST(total * (1.16) AS NUMERIC)) STORED,
    CONSTRAINT orders_items_check CHECK (items >= 0),
    CONSTRAINT orders_check2 CHECK (status = 'cancelled' OR status = 'shipped' OR status = 'paid' OR status = 'pending'),
    CONSTRAINT orders_check3 CHECK (total >= (0)),
    CONSTRAINT orders_fkey FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE customers ( -- People who bought at least once
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL,
    country VARCHAR(2) DEFAULT 'US' NOT NULL,
    is_active INTEGER DEFAULT 1 NOT NULL,
    signed_up DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    row_version BLOB,
    CONSTRAINT customers_is_active_check CHECK (is_active IN (0, 1))
);

INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(1,'Ana Martínez','ana@example.com','MX',1,'2023-01-15','2023-01-15 10:24:00.0000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(2,'Zoë Müller','zoe@example.de','DE',1,'2023-02-03','2023-02-03 08:11:42.0000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(3,'José Peña','jose@example.es','ES',0,'2023-03-21','2023-03-21 19:05:17.0000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(4,'李雷','lilei@example.cn','CN',1,'2023-05-09','2023-05-09 02:40:00.0000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(5,'Noah O''Brien','noah@example.ie','IE',1,NULL,'2023-06-30 14:00:00.0000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(6,'Amélie Laurent','amelie@example.fr','FR',1,'2024-01-02','2024-01-02 09:30:00.0000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(7,'Ravi Patel','ravi@example.in','IN',0,'2024-02-29','2024-02-29 23:59:59.0000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'2024-04-12','2024-04-12 12:12:12.0000000');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(1,1,'shipped','129.9000',3,'2024-03-01T10:00:00.0000000','2024-03-02T16:30:00.1234567',NULL,'{"gift": false}','0b7a4c2e-3f1d-4e8a-9c55-1a2b3c4d5e6f');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(2,2,'paid','54.0000',1,'2024-03-02T11:15:00.0000000',NULL,'Gift wrap 🎁','{"gift": true, "wrap": "red"}','1c8b5d3f-4a2e-4f9b-8d66-2b3c4d5e6f70');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(3,1,'cancelled','12.5000',1,'2024-03-03T09:45:00.0000000',NULL,'Customer changed their mind',NULL,'2d9c6e4a-5b3f-4a0c-9e77-3c4d5e6f7081');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(4,4,'shipped','980.0000',12,'2024-03-05T02:10:00.0000000','2024-03-07T08:00:00.0000000','请小心轻放','{"fragile": true}','3e0d7f5b-6c4a-4b1d-8f88-4d5e6f708192');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(5,5,'pending','45.7500',2,'2024-03-08T17:20:00.0000000',NULL,'Leave at the door; ring twice',NULL,'4f1e806c-7d5b-4c2e-9a99-5e6f708192a3');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(6,6,'shipped','310.4000',5,'2024-03-10T13:00:00.0000000','2024-03-11T09:00:00.0000000','Fragile: "glass"','{"fragile": true}','50f2917d-8e6c-4d3f-8baa-6f708192a3b4');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(7,8,'paid','76.2000',2,'2024-03-11T18:45:00.0000000',NULL,NULL,NULL,'6103a28e-9f7d-4e40-9cbb-708192a3b4c5');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(8,2,'shipped','19.9900',1,'2024-03-12T08:00:00.0000000','9999-12-31T23:59:59.9999999','Shipped date set to the maximum by an old import',NULL,'7214b39f-a08e-4f51-8dcc-8192a3b4c5d6');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(9,3,'shipped','64.0000',2,'2024-03-14T15:30:00.0000000','2024-03-15T10:00:00.0000000','Two parcels','{"parcels": [1, 2]}','8325c4a0-b19f-4062-9edd-92a3b4c5d6e7');
INSERT INTO orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) VALUES
(10,7,'paid','1499.0000',1,'2024-03-15T21:05:00.0000000',NULL,'C:\Users\ravi\invoice.pdf',NULL,'9436d5b1-c2a0-4173-8fee-a3b4c5d6e7f8');

-- Indexes, triggers and counters, after the rows so each index builds once.
CREATE INDEX orders_idx_orders_customer ON orders (customer_id);
CREATE INDEX orders_idx_orders_status_date ON orders (status, ordered_at);
CREATE UNIQUE INDEX customers_email ON customers (email);
UPDATE sqlite_sequence SET seq = max(seq, 0) WHERE name = 'orders';
INSERT INTO sqlite_sequence (name, seq) SELECT 'orders', 0 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'orders');
UPDATE sqlite_sequence SET seq = max(seq, 0) WHERE name = 'customers';
INSERT INTO sqlite_sequence (name, seq) SELECT 'customers', 0 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'customers');
COMMIT;

-- Rows whose foreign key finds no parent, if any: the load doesn't stop for them.
PRAGMA foreign_key_check;