/* Bookstore relational model: normalized tables + joins */

-- Data definition
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Update (insert sample data)
INSERT INTO customers(customer_id, customer_name) VALUES
  (1, 'Jordan Lee'),
  (2, 'Priya Nair'),
  (3, 'Mateo Garcia');

INSERT INTO orders(order_id, customer_id, order_date) VALUES
  (10, 1, '2026-01-15'),
  (11, 1, '2026-02-03'),
  (12, 2, '2026-02-20');

INSERT INTO order_items(item_id, order_id, product_name, quantity) VALUES
  (100, 10, 'Notebook', 2),
  (101, 10, 'Pen', 5),
  (102, 11, 'Backpack', 1),
  (103, 12, 'Eraser', 3);

-- Retrieval using declarative SQL joins
SELECT
  c.customer_id,
  c.customer_name,
  o.order_id,
  o.order_date,
  oi.product_name,
  oi.quantity
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.order_id
ORDER BY c.customer_id, o.order_id, oi.item_id;

/* Sports league relational model: conditional aggregation + ordered event list */

-- Data definition
CREATE TABLE teams (
  team_id INTEGER PRIMARY KEY,
  team_name TEXT NOT NULL
);

CREATE TABLE matches (
  match_id INTEGER PRIMARY KEY,
  home_team_id INTEGER NOT NULL,
  away_team_id INTEGER NOT NULL,
  match_date TEXT NOT NULL,
  FOREIGN KEY (home_team_id) REFERENCES teams(team_id),
  FOREIGN KEY (away_team_id) REFERENCES teams(team_id)
);

CREATE TABLE match_events (
  event_id INTEGER PRIMARY KEY,
  match_id INTEGER NOT NULL,
  minute INTEGER NOT NULL,
  event_type TEXT NOT NULL,
  scoring_team_id INTEGER,
  FOREIGN KEY (match_id) REFERENCES matches(match_id),
  FOREIGN KEY (scoring_team_id) REFERENCES teams(team_id)
);

-- Update (insert sample data)
INSERT INTO teams(team_id, team_name) VALUES
  (1, 'North FC'),
  (2, 'East United'),
  (3, 'South City');

INSERT INTO matches(match_id, home_team_id, away_team_id, match_date) VALUES
  (1000, 1, 2, '2026-03-01'),
  (1001, 2, 3, '2026-03-08');

-- Match 1000 has events; match 1001 has no events (to test LEFT JOIN)
INSERT INTO match_events(event_id, match_id, minute, event_type, scoring_team_id) VALUES
  (1, 1000, 12, 'GOAL', 1),
  (2, 1000, 25, 'YELLOW_CARD', NULL),
  (3, 1000, 40, 'GOAL', 2),
  (4, 1000, 78, 'GOAL', 1),
  (5, 1000, 88, 'RED_CARD', NULL);

-- Retrieval: one row per match with conditional aggregation and ordered event list
SELECT
  m.match_id,
  ht.team_name AS home_team,
  at.team_name AS away_team,
  SUM(CASE WHEN me.event_type = 'GOAL' AND me.scoring_team_id = ht.team_id THEN 1 ELSE 0 END) AS home_goals,
  SUM(CASE WHEN me.event_type = 'GOAL' AND me.scoring_team_id = at.team_id THEN 1 ELSE 0 END) AS away_goals,
  GROUP_CONCAT(me.event_type, ',') AS event_types_in_order
FROM matches m
JOIN teams ht ON ht.team_id = m.home_team_id
JOIN teams at ON at.team_id = m.away_team_id
LEFT JOIN match_events me ON me.match_id = m.match_id
GROUP BY
  m.match_id,
  ht.team_id,
  ht.team_name,
  at.team_id,
  at.team_name
ORDER BY m.match_id;

/* Problem 1: EXISTS vs JOIN+DISTINCT for customers with at least one order */

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

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_total_cents INTEGER NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers(customer_id, full_name) VALUES
  (1, 'Avery Chen'),
  (2, 'Sam Rivera'),
  (3, 'Jordan Patel'),
  (4, 'Taylor Brooks');

INSERT INTO orders(order_id, customer_id, order_total_cents) VALUES
  (10, 1, 2500),
  (11, 1, 9900),
  (12, 2, 1500);

-- Query A (EXISTS): customers who have at least one order
SELECT c.customer_id, c.full_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

-- Query B (JOIN + DISTINCT): logically equivalent set of customers
SELECT DISTINCT c.customer_id, c.full_name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id;

-- Update: add a new order for a customer who previously had no orders
INSERT INTO orders(order_id, customer_id, order_total_cents)
VALUES (13, 4, 4200);

-- Re-run Query A
SELECT c.customer_id, c.full_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

-- Re-run Query B
SELECT DISTINCT c.customer_id, c.full_name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id;

/* Problem 2: Authors with books both before 2010 and in 2010 or later */

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

CREATE TABLE authors (
  author_id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL
);

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  publication_year INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO authors(author_id, full_name) VALUES
  (1, 'Avery Chen'),
  (2, 'Sam Rivera'),
  (3, 'Jordan Patel'),
  (4, 'Taylor Brooks'),
  (5, 'Morgan Lee');

-- Data setup:
-- author 1: has both <2010 and >=2010
-- author 2: only >=2010
-- author 3: only <2010
-- author 4: has both
-- author 5: no books
INSERT INTO books(book_id, author_id, title, publication_year) VALUES
  (100, 1, 'Old Tales', 2005),
  (101, 1, 'New Horizons', 2012),

  (102, 2, 'Future Plans', 2018),
  (103, 2, 'Modern Systems', 2020),

  (104, 3, 'Ancient Myths', 1999),

  (105, 4, 'Pre-2010 Volume', 2001),
  (106, 4, 'Post-2010 Volume', 2011);

-- Query A (EXISTS): intersection of two existence conditions
SELECT a.author_id, a.full_name
FROM authors a
WHERE EXISTS (
  SELECT 1
  FROM books b
  WHERE b.author_id = a.author_id
    AND b.publication_year >= 2010
)
AND EXISTS (
  SELECT 1
  FROM books b
  WHERE b.author_id = a.author_id
    AND b.publication_year < 2010
)
ORDER BY a.author_id;

-- Query B (JOIN + DISTINCT): two joins with different year filters
SELECT DISTINCT a.author_id, a.full_name
FROM authors a
JOIN books b_ge ON b_ge.author_id = a.author_id
  AND b_ge.publication_year >= 2010
JOIN books b_lt ON b_lt.author_id = a.author_id
  AND b_lt.publication_year < 2010
ORDER BY a.author_id;

/* Simulates a NoSQL key-value/document store pattern in JavaScript. */

const kvStore = new Map();

function upsertOrderDocument(orderId, doc) {
  kvStore.set(String(orderId), doc);
}

function getOrderSummaryByKey(orderId) {
  const doc = kvStore.get(String(orderId));
  if (!doc) return null;

  return {
    order_id: orderId,
    customer_name: doc.customer_name,
    address: doc.address ?? null,
    items: doc.items ?? [],
    total_cents: doc.total_cents
  };
}

// Seed documents (NoSQL style: embed related data inside one document)
upsertOrderDocument(101, {
  customer_name: 'Jordan Lee',
  address: { street: '1 Pine Rd', city: 'Austin' },
  items: [
    { sku: 'A1', qty: 2 },
    { sku: 'B2', qty: 1 }
  ],
  total_cents: 3499
});

upsertOrderDocument(202, {
  customer_name: 'Taylor Brooks',
  items: [{ sku: 'C3', qty: 3 }],
  total_cents: 1599
});

upsertOrderDocument(303, {
  customer_name: 'Morgan Patel',
  address: { street: '77 Lake St', city: 'Denver' },
  total_cents: 799
});

console.log('Order 101 summary:', getOrderSummaryByKey(101));
console.log('Order 202 summary:', getOrderSummaryByKey(202));
console.log('Order 999 summary (missing):', getOrderSummaryByKey(999));

// Update one order by upserting a new document version
upsertOrderDocument(202, {
  customer_name: 'Taylor Brooks',
  address: { street: '9 Cedar Ave', city: 'Portland' },
  items: [
    { sku: 'C3', qty: 3 },
    { sku: 'D4', qty: 2 }
  ],
  total_cents: 2599
});

console.log('Order 202 summary after update:', getOrderSummaryByKey(202));

/* Simulates a NoSQL key-value/document store with per-key versioning in JavaScript. */

const kvStore = new Map();

function upsertUserProfileDocument(userId, doc) {
  const key = String(userId);
  const existing = kvStore.get(key);
  const nextVersion = existing ? existing.version + 1 : 1;

  // Replace the entire document (NoSQL upsert), but attach version.
  kvStore.set(key, {
    version: nextVersion,
    doc: {
      user_id: userId,
      full_name: doc.full_name,
      address: doc.address ?? null,
      phones: doc.phones ?? []
    }
  });
}

function getUserProfileByKey(userId) {
  const key = String(userId);
  const record = kvStore.get(key);
  if (!record) return null;

  // Return the stored document with embedded fields and version.
  return {
    ...record.doc,
    version: record.version
  };
}

function updatePhoneListByKey(userId, newPhones) {
  const key = String(userId);
  const record = kvStore.get(key);
  if (!record) {
    throw new Error(`User ${userId} not found`);
  }

  if (!Array.isArray(newPhones)) {
    throw new Error('newPhones must be an array');
  }

  for (const p of newPhones) {
    if (typeof p !== 'string' || p.trim().length === 0) {
      throw new Error('Each phone must be a non-empty string');
    }
  }

  const nextVersion = record.version + 1;

  // Update only phones, preserve other embedded fields.
  const updatedDoc = {
    ...record.doc,
    phones: newPhones
  };

  kvStore.set(key, {
    version: nextVersion,
    doc: updatedDoc
  });
}

// Seed two users
upsertUserProfileDocument(1, {
  full_name: 'Avery Chen',
  address: { street: '12 Market St', city: 'Springfield' },
  phones: ['+1-555-0101']
});

upsertUserProfileDocument(2, {
  full_name: 'Sam Rivera',
  phones: ['+1-555-0202']
});

console.log('Initial user 1:', getUserProfileByKey(1));
console.log('Initial user 2:', getUserProfileByKey(2));

// Replace user 2 document via upsert (version must increment)
upsertUserProfileDocument(2, {
  full_name: 'Sam Rivera',
  address: { street: '9 Elm St', city: 'Shelbyville' },
  phones: ['+1-555-0202']
});

console.log('User 2 after upsert replace:', getUserProfileByKey(2));

// Update only phones for user 1
updatePhoneListByKey(1, ['+1-555-0111', '+1-555-0122']);

console.log('User 1 after phone update:', getUserProfileByKey(1));

// Demonstrate validation (commented out to keep output clean)
// updatePhoneListByKey(1, ['   ', '+1-555-0999']); // should throw

/* Medium: transactional transfer with lost-update prevention and negative-balance rollback. */

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  balance INTEGER NOT NULL
);

INSERT INTO wallets(wallet_id, balance) VALUES
  (1, 1000),
  (2, 300);

-- Transfer 150 from wallet 1 to wallet 2 with concurrency-safe locking
BEGIN TRANSACTION;

-- Lock source row
SELECT balance
FROM wallets
WHERE wallet_id = 1
FOR UPDATE;

-- Lock destination row
SELECT balance
FROM wallets
WHERE wallet_id = 2
FOR UPDATE;

-- Integrity check: ensure source has enough funds
-- If your DB supports procedural IF, you would branch.
-- Here we emulate the check by validating and rolling back manually.
-- For a runnable script, we use a conditional pattern via a second check.

-- Re-check source balance (still locked)
-- If balance < 150, rollback; otherwise proceed.
-- NOTE: Many DBs do not allow ROLLBACK inside a plain SQL script without procedural code.
-- This script assumes manual execution flow: run the check, then decide.

-- Validation query
SELECT balance AS source_balance
FROM wallets
WHERE wallet_id = 1;

-- If source_balance >= 150, apply updates:
UPDATE wallets
SET balance = balance - 150
WHERE wallet_id = 1;

UPDATE wallets
SET balance = balance + 150
WHERE wallet_id = 2;

COMMIT;

-- Verify final state
SELECT *
FROM wallets
ORDER BY wallet_id;

/* Hard: batch transfer with all-or-nothing validation and row-level locking. */

CREATE TABLE ledger (
  account_id INTEGER PRIMARY KEY,
  balance INTEGER NOT NULL
);

INSERT INTO ledger(account_id, balance) VALUES
  (1, 500),
  (2, 120),
  (3, 300);

-- Batch plan:
-- Outgoing transfers:
-- 1 -> 2 : 200
-- 1 -> 3 : 150
-- 2 -> 3 : 50
-- Validation requirement: every source must have enough funds for its total outgoing.

BEGIN TRANSACTION;

-- Lock all accounts that will be involved (sources and destinations)
-- Sources: 1, 2
-- Destinations: 2, 3
SELECT balance FROM ledger WHERE account_id IN (1, 2, 3) FOR UPDATE;

-- Compute and validate total outgoing per source using validation queries.
-- Source 1 total outgoing = 200 + 150 = 350
-- Source 2 total outgoing = 50

-- Validation query for source 1
SELECT balance AS source1_balance
FROM ledger
WHERE account_id = 1;

-- Validation query for source 2
SELECT balance AS source2_balance
FROM ledger
WHERE account_id = 2;

-- If both validations pass (source1_balance >= 350 AND source2_balance >= 50), apply updates.
-- Otherwise, rollback.

-- Apply updates (all-or-nothing if you only COMMIT after successful validation)
UPDATE ledger
SET balance = balance - 200
WHERE account_id = 1;

UPDATE ledger
SET balance = balance + 200
WHERE account_id = 2;

UPDATE ledger
SET balance = balance - 150
WHERE account_id = 1;

UPDATE ledger
SET balance = balance + 150
WHERE account_id = 3;

UPDATE ledger
SET balance = balance - 50
WHERE account_id = 2;

UPDATE ledger
SET balance = balance + 50
WHERE account_id = 3;

COMMIT;

-- Verify final state
SELECT *
FROM ledger
ORDER BY account_id;