-- Get Example Files - sample SQL script (SQLite / ANSI compatible)
-- Fictional outdoor-gear orders for testing database imports.

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY,
  order_code VARCHAR(20) NOT NULL UNIQUE,
  customer VARCHAR(80) NOT NULL,
  email VARCHAR(120) NOT NULL,
  city VARCHAR(60) NOT NULL,
  product VARCHAR(80) NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  department VARCHAR(40) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_orders_city ON orders (city);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status);

BEGIN TRANSACTION;
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (1, 'ORD-100001', 'Keira Everhart', 'keira.everhart1@example.com', 'Brightmoor', 'Tidewater Binoculars', 1, 38.94, 'processing', '2026-02-12', 'Operations');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (2, 'ORD-100002', 'Quinn Greystone', 'quinn.greystone2@example.com', 'Port Larkin', 'Canvas Daypack', 7, 203.34, 'shipped', '2026-02-18', 'Marketing');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (3, 'ORD-100003', 'Bastian Sterling', 'bastian.sterling3@example.com', 'Eastvale', 'Summit Tent 2P', 11, 302.84, 'pending', '2026-10-19', 'Marketing');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (4, 'ORD-100004', 'Bastian Hollins', 'bastian.hollins4@example.com', 'Port Larkin', 'Glacier Water Filter', 3, 142.21, 'shipped', '2026-09-04', 'Finance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (5, 'ORD-100005', 'Rosa Valdez', 'rosa.valdez5@example.com', 'Silverbrook', 'Canvas Daypack', 10, 276.11, 'shipped', '2026-06-04', 'Logistics');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (6, 'ORD-100006', 'Samir Brightwater', 'samir.brightwater6@example.com', 'Westmere', 'Summit Tent 2P', 8, 328.03, 'returned', '2026-06-15', 'Quality Assurance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (7, 'ORD-100007', 'Luca Jareth', 'luca.jareth7@example.com', 'Maple Hollow', 'Wildflower Seed Kit', 3, 336.87, 'shipped', '2026-02-19', 'Finance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (8, 'ORD-100008', 'Quinn Pemberton', 'quinn.pemberton8@example.com', 'Fernwood', 'Meadow Picnic Set', 12, 217.92, 'processing', '2026-02-04', 'Marketing');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (9, 'ORD-100009', 'Farid Zeller', 'farid.zeller9@example.com', 'Northwick', 'River Kayak Paddle', 8, 205.02, 'cancelled', '2026-02-25', 'Engineering');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (10, 'ORD-100010', 'Keira Whitlock', 'keira.whitlock10@example.com', 'Northwick', 'Pinecrest Sleeping Bag', 8, 280.24, 'returned', '2026-02-27', 'Logistics');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (11, 'ORD-100011', 'Ines Pemberton', 'ines.pemberton11@example.com', 'Stonebridge', 'Tidewater Binoculars', 2, 33.35, 'cancelled', '2026-05-21', 'Quality Assurance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (12, 'ORD-100012', 'Jonah Whitlock', 'jonah.whitlock12@example.com', 'Brightmoor', 'Lakeshore Folding Chair', 11, 169.5, 'returned', '2026-06-06', 'Logistics');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (13, 'ORD-100013', 'Pavel Brightwater', 'pavel.brightwater13@example.com', 'Maple Hollow', 'Wildflower Seed Kit', 5, 66.0, 'shipped', '2026-07-13', 'Quality Assurance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (14, 'ORD-100014', 'Celia Fairbanks', 'celia.fairbanks14@example.com', 'Harrowgate', 'Cloudlight Hammock', 9, 136.61, 'shipped', '2026-07-28', 'Finance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (15, 'ORD-100015', 'Wren Northcott', 'wren.northcott15@example.com', 'Northwick', 'Tidewater Binoculars', 7, 459.9, 'shipped', '2026-02-06', 'Customer Care');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (16, 'ORD-100016', 'Hiro Valdez', 'hiro.valdez16@example.com', 'Maple Hollow', 'Trail Lantern', 8, 399.68, 'shipped', '2026-05-10', 'Operations');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (17, 'ORD-100017', 'Elena Northcott', 'elena.northcott17@example.com', 'Lakeside Falls', 'Meadow Picnic Set', 10, 273.8, 'shipped', '2026-12-28', 'Operations');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (18, 'ORD-100018', 'Olivia Zeller', 'olivia.zeller18@example.com', 'Oakhaven', 'Tidewater Binoculars', 9, 191.08, 'returned', '2026-07-04', 'Quality Assurance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (19, 'ORD-100019', 'Uma Marlowe', 'uma.marlowe19@example.com', 'Port Larkin', 'Summit Tent 2P', 2, 472.71, 'returned', '2026-03-04', 'Engineering');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (20, 'ORD-100020', 'Tamsin Brightwater', 'tamsin.brightwater20@example.com', 'Eastvale', 'Trail Lantern', 10, 76.43, 'pending', '2026-06-20', 'Operations');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (21, 'ORD-100021', 'Celia Greystone', 'celia.greystone21@example.com', 'Westmere', 'Cloudlight Hammock', 3, 306.16, 'delivered', '2026-10-12', 'Quality Assurance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (22, 'ORD-100022', 'Darius Delacroix', 'darius.delacroix22@example.com', 'Oakhaven', 'Harbor Rain Jacket', 8, 232.93, 'delivered', '2026-02-05', 'Logistics');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (23, 'ORD-100023', 'Ximena Kingsley', 'ximena.kingsley23@example.com', 'Stonebridge', 'Alpine Thermos', 8, 398.62, 'shipped', '2026-09-01', 'Field Research');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (24, 'ORD-100024', 'Quinn Lindqvist', 'quinn.lindqvist24@example.com', 'Silverbrook', 'Sunrise Camp Stove', 9, 439.18, 'processing', '2026-05-21', 'Logistics');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (25, 'ORD-100025', 'Wren Ivers', 'wren.ivers25@example.com', 'Lakeside Falls', 'Meadow Picnic Set', 3, 173.63, 'shipped', '2026-09-18', 'Engineering');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (26, 'ORD-100026', 'Uma Hollins', 'uma.hollins26@example.com', 'Westmere', 'Wildflower Seed Kit', 4, 387.79, 'returned', '2026-12-26', 'Field Research');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (27, 'ORD-100027', 'Greta Quill', 'greta.quill27@example.com', 'Harrowgate', 'Meadow Picnic Set', 12, 18.28, 'pending', '2026-05-16', 'Finance');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (28, 'ORD-100028', 'Greta Whitlock', 'greta.whitlock28@example.com', 'Westmere', 'Meadow Picnic Set', 8, 388.97, 'cancelled', '2026-06-12', 'Logistics');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (29, 'ORD-100029', 'Hiro Delacroix', 'hiro.delacroix29@example.com', 'Maple Hollow', 'Harbor Rain Jacket', 4, 165.09, 'returned', '2026-10-20', 'Operations');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (30, 'ORD-100030', 'Pavel Underwood', 'pavel.underwood30@example.com', 'Northwick', 'Wildflower Seed Kit', 11, 44.81, 'cancelled', '2026-02-13', 'Field Research');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (31, 'ORD-100031', 'Pavel Fairbanks', 'pavel.fairbanks31@example.com', 'Brightmoor', 'Wildflower Seed Kit', 11, 162.61, 'cancelled', '2026-07-15', 'Marketing');
INSERT INTO orders (id, order_code, customer, email, city, product, quantity, unit_price, status, order_date, department) VALUES (32, 'ORD-100032', 'Ximena Calloway', 'ximena.calloway32@example.com', 'Stonebridge', 'River Kayak Paddle', 3, 476.72, 'pending', '2026-03-19', 'Quality Assurance');
COMMIT;

-- A traveling cartographer summarized the evening light across the lake for the fictional Get Example Files demo.
-- Example query
SELECT city, ROUND(SUM(quantity * unit_price), 2) AS revenue FROM orders WHERE status <> 'cancelled' GROUP BY city ORDER BY revenue DESC;