seed.sql

-- seed.sql
-- Mini music store database for the SQL Analyst Agent demo.
-- Run with: sqlite3 chinook.db < seed.sql

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS InvoiceLine;
DROP TABLE IF EXISTS Invoice;
DROP TABLE IF EXISTS Track;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Genre;
DROP TABLE IF EXISTS Customer;

CREATE TABLE Artist (
  ArtistId INTEGER PRIMARY KEY,
  Name     TEXT NOT NULL
);

CREATE TABLE Genre (
  GenreId INTEGER PRIMARY KEY,
  Name    TEXT NOT NULL
);

CREATE TABLE Album (
  AlbumId  INTEGER PRIMARY KEY,
  Title    TEXT NOT NULL,
  ArtistId INTEGER NOT NULL REFERENCES Artist(ArtistId)
);

CREATE TABLE Track (
  TrackId  INTEGER PRIMARY KEY,
  Name     TEXT NOT NULL,
  AlbumId  INTEGER NOT NULL REFERENCES Album(AlbumId),
  GenreId  INTEGER NOT NULL REFERENCES Genre(GenreId),
  Price    REAL NOT NULL
);

CREATE TABLE Customer (
  CustomerId INTEGER PRIMARY KEY,
  FirstName  TEXT NOT NULL,
  LastName   TEXT NOT NULL,
  Email      TEXT NOT NULL,
  Country    TEXT NOT NULL
);

CREATE TABLE Invoice (
  InvoiceId      INTEGER PRIMARY KEY,
  CustomerId     INTEGER NOT NULL REFERENCES Customer(CustomerId),
  InvoiceDate    TEXT NOT NULL,
  BillingCountry TEXT NOT NULL,
  Total          REAL NOT NULL
);

CREATE TABLE InvoiceLine (
  InvoiceLineId INTEGER PRIMARY KEY,
  InvoiceId     INTEGER NOT NULL REFERENCES Invoice(InvoiceId),
  TrackId       INTEGER NOT NULL REFERENCES Track(TrackId),
  UnitPrice     REAL NOT NULL,
  Quantity      INTEGER NOT NULL
);

-- Artists
INSERT INTO Artist VALUES
  (1, 'The Midnight Echo'),
  (2, 'Luna Park'),
  (3, 'Calle Sur'),
  (4, 'Berlin Drift'),
  (5, 'Sakura Static');

-- Genres
INSERT INTO Genre VALUES
  (1, 'Rock'),
  (2, 'Electronic'),
  (3, 'Latin'),
  (4, 'Jazz'),
  (5, 'Pop');

-- Albums
INSERT INTO Album VALUES
  (1, 'Neon Hours',       1),
  (2, 'Quiet Riot',       1),
  (3, 'Orbit',            2),
  (4, 'Costa',            3),
  (5, 'Nachtbahn',        4),
  (6, 'Hanami',           5),
  (7, 'Second Sun',       2);

-- Tracks
INSERT INTO Track VALUES
  (1,  'Headlights',        1, 1, 0.99),
  (2,  'Static Bloom',      1, 2, 0.99),
  (3,  'Soft Collapse',     2, 1, 0.99),
  (4,  'Gravity Well',      3, 2, 1.29),
  (5,  'Low Tide',          3, 2, 1.29),
  (6,  'Bahia',             4, 3, 0.99),
  (7,  'Madrugada',         4, 3, 0.99),
  (8,  'U-Bahn',            5, 2, 1.29),
  (9,  'Kreuzberg Nights',  5, 2, 1.29),
  (10, 'Petal',             6, 5, 0.99),
  (11, 'Kyoto Rain',        6, 4, 1.29),
  (12, 'Afterglow',         7, 2, 0.99),
  (13, 'Slow Star',         7, 5, 0.99);

-- Customers
INSERT INTO Customer VALUES
  (1, 'Anna',     'Schmidt',  'anna@example.com',    'Germany'),
  (2, 'Lukas',    'Weber',    'lukas@example.com',   'Germany'),
  (3, 'Sofia',    'Rossi',    'sofia@example.com',   'Italy'),
  (4, 'Mateo',    'García',   'mateo@example.com',   'Chile'),
  (5, 'Camila',   'Pérez',    'camila@example.com',  'Chile'),
  (6, 'Yuki',     'Tanaka',   'yuki@example.com',    'Japan'),
  (7, 'Haruto',   'Suzuki',   'haruto@example.com',  'Japan'),
  (8, 'Emma',     'Dubois',   'emma@example.com',    'France'),
  (9, 'Louis',    'Martin',   'louis@example.com',   'France'),
  (10,'Olivia',   'Brown',    'olivia@example.com',  'United Kingdom');

-- Invoices
INSERT INTO Invoice VALUES
  (1,  1, '2025-01-12', 'Germany',        3.96),
  (2,  2, '2025-01-20', 'Germany',        2.58),
  (3,  3, '2025-02-03', 'Italy',          5.94),
  (4,  4, '2025-02-15', 'Chile',         12.87),
  (5,  5, '2025-02-28', 'Chile',         10.32),
  (6,  6, '2025-03-05', 'Japan',          4.95),
  (7,  7, '2025-03-11', 'Japan',          7.74),
  (8,  8, '2025-03-19', 'France',         2.97),
  (9,  9, '2025-04-02', 'France',         6.45),
  (10, 1, '2025-04-10', 'Germany',        9.93),
  (11, 4, '2025-04-22', 'Chile',         15.45),
  (12, 6, '2025-05-01', 'Japan',          3.96),
  (13,10, '2025-05-08', 'United Kingdom', 8.91),
  (14, 5, '2025-05-19', 'Chile',         11.61),
  (15, 3, '2025-06-02', 'Italy',          4.95);

-- Invoice lines
INSERT INTO InvoiceLine VALUES
  (1,  1,  1, 0.99, 2),
  (2,  1,  3, 0.99, 2),
  (3,  2,  2, 0.99, 1),
  (4,  2, 10, 0.99, 1),
  (5,  3,  4, 1.29, 2),
  (6,  3, 11, 1.29, 2),
  (7,  4,  4, 1.29, 3),
  (8,  4,  8, 1.29, 3),
  (9,  4,  9, 1.29, 3),
  (10, 5,  5, 1.29, 4),
  (11, 5,  9, 1.29, 4),
  (12, 6,  6, 0.99, 3),
  (13, 6,  7, 0.99, 2),
  (14, 7,  8, 1.29, 3),
  (15, 7, 11, 1.29, 3),
  (16, 8, 12, 0.99, 3),
  (17, 9,  4, 1.29, 2),
  (18, 9, 13, 0.99, 4),
  (19,10,  1, 0.99, 5),
  (20,10,  3, 0.99, 5),
  (21,11,  4, 1.29, 4),
  (22,11,  5, 1.29, 4),
  (23,11,  9, 1.29, 4),
  (24,12, 10, 0.99, 4),
  (25,13,  6, 0.99, 5),
  (26,13,  7, 0.99, 4),
  (27,14,  5, 1.29, 5),
  (28,14,  8, 1.29, 4),
  (29,15,  2, 0.99, 5);

-- Sanity check
SELECT 'Artists'  AS Table_, COUNT(*) AS Rows FROM Artist
UNION ALL SELECT 'Albums',      COUNT(*) FROM Album
UNION ALL SELECT 'Tracks',      COUNT(*) FROM Track
UNION ALL SELECT 'Customers',   COUNT(*) FROM Customer
UNION ALL SELECT 'Invoices',    COUNT(*) FROM Invoice
UNION ALL SELECT 'InvoiceLines',COUNT(*) FROM InvoiceLine;