script.js

const escapeHtml = (value = "") =>
  String(value)
    .replaceAll("&", "&")
    .replaceAll("<", "&lt;")
    .replaceAll(">", "&gt;")
    .replaceAll('"', "&quot;")
    .replaceAll("'", "&#39;");

const highlightTokens = {
  open: "__HL_OPEN__",
  close: "__HL_CLOSE__",
};

const applyHighlight = (value = "", className = "text-hl") => {
  const withTokens = String(value)
    .replaceAll("[[", highlightTokens.open)
    .replaceAll("]]", highlightTokens.close);
  const escaped = escapeHtml(withTokens);
  return escaped
    .replaceAll(highlightTokens.open, `<span class="${className}">`)
    .replaceAll(highlightTokens.close, "</span>");
};

const syntaxHighlightSql = (htmlText) => {
  const keywordList = [
    "SELECT",
    "FROM",
    "WHERE",
    "JOIN",
    "LEFT",
    "RIGHT",
    "INNER",
    "OUTER",
    "ON",
    "GROUP",
    "BY",
    "ORDER",
    "INSERT",
    "INTO",
    "VALUES",
    "UPDATE",
    "SET",
    "DELETE",
    "CREATE",
    "TABLE",
    "PRIMARY",
    "KEY",
    "FOREIGN",
    "REFERENCES",
    "NOT",
    "NULL",
    "UNIQUE",
    "DEFAULT",
    "CASE",
    "WHEN",
    "THEN",
    "ELSE",
    "END",
    "AS",
    "AND",
    "OR",
    "DISTINCT",
    "LIKE",
    "IN",
    "COUNT",
    "SUM",
    "AVG",
  ];
  const typeList = [
    "INT",
    "INTEGER",
    "VARCHAR",
    "DECIMAL",
    "DATE",
    "TEXT",
    "AUTO_INCREMENT",
  ];
  const keywordRegex = new RegExp(`\\b(${keywordList.join("|")})\\b`, "gi");
  const typeRegex = new RegExp(`\\b(${typeList.join("|")})\\b`, "gi");
  const numberRegex = /\b\d+(?:\.\d+)?\b/g;
  const stringRegex = /'[^']*'/g;
  const commentRegex = /--.*$/gm;

  const highlightSegment = (segment) => {
    const tokens = [];
    const protect = (regex, className) => {
      segment = segment.replace(regex, (match) => {
        const id = tokens.length;
        tokens.push({ className, value: match });
        return `__TOK_${id}__`;
      });
    };

    protect(commentRegex, "token-comment");
    protect(stringRegex, "token-string");
    protect(numberRegex, "token-number");

    segment = segment.replace(typeRegex, '<span class="token-type">$1</span>');
    segment = segment.replace(
      keywordRegex,
      '<span class="token-keyword">$1</span>',
    );

    segment = segment.replace(/__TOK_(\d+)__/g, (_, id) => {
      const token = tokens[Number(id)];
      return `<span class="${token.className}">${token.value}</span>`;
    });

    return segment;
  };

  return htmlText
    .split(/(<[^>]+>)/g)
    .map((part) => (part.startsWith("<") ? part : highlightSegment(part)))
    .join("");
};

const codeBlock = (title, lines) => {
  const withComment = [`-- ${title}`, ...lines];
  const raw = withComment.join("\n");
  const withTokens = raw
    .replaceAll("[[", highlightTokens.open)
    .replaceAll("]]", highlightTokens.close);
  const escaped = escapeHtml(withTokens)
    .replaceAll(highlightTokens.open, '<span class="code-hl">')
    .replaceAll(highlightTokens.close, "</span>");
  const highlighted = syntaxHighlightSql(escaped);
  return `
  <div class="content-block">
    <p class="mini-title">${escapeHtml(title)}</p>
    <pre class="code-block"><code>${highlighted}</code></pre>
  </div>
`;
};

const tableHtml = (headers, rows) => `
  <div class="table-wrap">
    <table>
      <thead>
        <tr>${headers.map((header) => `<th>${escapeHtml(header)}</th>`).join("")}</tr>
      </thead>
      <tbody>
        ${rows
          .map(
            (row) => `
              <tr>${row.map((cell) => `<td>${applyHighlight(cell)}</td>`).join("")}</tr>
            `,
          )
          .join("")}
      </tbody>
    </table>
  </div>
`;

const imageCard = (src, title, caption) => `
  <figure class="image-card">
    <img src="${escapeHtml(src)}" alt="${escapeHtml(title)}" />
    <figcaption>
      <p class="image-title">${escapeHtml(title)}</p>
      <p class="image-caption">${escapeHtml(caption)}</p>
    </figcaption>
  </figure>
`;

const bulletList = (items) => `
  <ul class="bullet-list">
    ${items.map((item) => `<li>${item}</li>`).join("")}
  </ul>
`;

const slides = [
  {
    section: "Intro",
    content: `
      <div class="slide__grid slide__grid--center text-center">
        <div style="display: flex; flex-direction: column; align-items: center; margin-top: 4.5rem;">
          <img src="UniversityLogo.png" alt="University Icon" style="width: 25rem; margin-bottom: 1.5rem; color: var(--muted);"></img>
          
          <h1 style="font-size: 4.8rem; margin-top: 1rem;">Bookstore POS</h1>
          <p class="lede" style="font-size: 2rem; color: var(--text); margin-bottom: 1.5rem;">
            Normalization and operational SQL walkthrough
          </p>
          <div style="color: var(--muted);">
            <i class="fa-solid fa-database" style="font-size: 3rem; margin: 0 1.2rem;"></i>
            <i class="fa-solid fa-server" style="font-size: 3rem; margin: 0 1.2rem;"></i>
            <i class="fa-solid fa-cash-register" style="font-size: 3rem; margin: 0 1.2rem;"></i>
          </div>
        </div>
      </div>
    `,
  },
  {
    section: "Intro",
    content: `
      <div class="slide__grid slide__grid--center text-center">
        <div style="margin-top: 4.5rem;">
          <h1 style="font-size: 3.8rem;">Group Members</h1>
          <ul style="list-style: none; padding: 0; font-size: 2rem; margin-top: 2.5rem; line-height: 2.4;">
            <li><strong>M.Haseeb</strong> (24014119-204)</li>
            <li><strong>Ali Husnain</strong> (24014119-100)</li>
            <li><strong>Mussab</strong> (24014119-131)</li>
          </ul>
          <p class="lede" style="margin-top: 1.5rem;">Database Systems - 4th Semester</p>
        </div>
      </div>
    `,
  },
  {
    section: "Intro",
    title: "Agenda and learning goals",
    content: `
      <p class="lede">We cover the problem, the normalization process, and the operational SQL used by a POS bookstore.</p>
      ${bulletList([
        "Unnormalized schema and the anomalies it creates",
        "Normalization path from 1NF to 3NF",
        "Complete 10-table 3NF schema with keys",
        "Operational queries: purchase, sales, inventory, and reporting",
        "ER diagram relationships and integrity rules",
      ])}
    `,
    media: `
      <div class="panel">
        <p class="tag"><i class="fa-solid fa-layer-group"></i> Detailed technical walkthrough</p>
        <p style="margin: 1rem 0 0;">50 slides, code-first, with actual query results.</p>
        <div class="stat-grid" style="margin-top: 1.5rem;">
          <div class="stat-card">
            <h4>10 normalized tables</h4>
            <p>3NF with PK/FK constraints</p>
          </div>
          <div class="stat-card">
            <h4>2 unnormalized tables</h4>
            <p>Redundancy and anomalies</p>
          </div>
        </div>
      </div>
    `,
  },
  {
    section: "Intro",
    title: "Bookstore POS scope",
    content: `
      <p class="lede">The system models inventory, purchases from suppliers, sales to customers, and analytics.</p>
      ${bulletList([
        "Purchasing workflow: suppliers, purchase orders, and line items",
        "Sales workflow: customers, sale orders, and line items",
        "Inventory control: stock, pricing, and reorder indicators",
        "Reporting layer: revenue, profit, and customer insights",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/SaleOrder.png",
        "POS transaction snapshot",
        "Sample sale order output used to validate the normalized schema.",
      )}
    `,
  },
  {
    section: "Intro",
    title: "Core entities and events",
    content: `
      <p class="lede">Each entity has a single responsibility so every fact lives in exactly one place.</p>
      ${bulletList([
        "Publishers, Authors, and Books define the catalog",
        "Customers and Suppliers represent external parties",
        "Orders capture events; line items capture quantities and prices",
        "Inventory status is derived from purchases and sales",
      ])}
    `,
    media: `
      ${tableHtml(
        ["Entity", "Purpose", "Key Example"],
        [
          ["Books", "Catalog items with price and stock", "ISBN + title"],
          ["Purchase Orders", "Inbound inventory", "Supplier + date"],
          ["Sale Orders", "Customer purchases", "Customer + total"],
          ["Line Items", "Quantities per order", "Book + quantity"],
        ],
      )}
    `,
  },
  {
    section: "Intro",
    title: "End-to-end data flow",
    content: `
      <p class="lede">The same book moves through purchase, stock, and sales workflows.</p>
      ${bulletList([
        "Suppliers deliver stock via Purchase_Orders",
        "Stock increases through Purchase_Order_Items",
        "Customers place Sale_Orders",
        "Stock decreases through Sale_Order_Items",
        "Reports aggregate data for management decisions",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/OrderPlaced.png",
        "Order lifecycle evidence",
        "Each query output validates the flow between catalog, orders, and inventory.",
      )}
    `,
  },
  {
    section: "Unnormalized DB",
    content: `
      <div class="slide__grid slide__grid--center text-center un-title-slide">
        <div style="margin-top: 8rem;">
          <i class="fa-solid fa-triangle-exclamation" style="font-size: 6rem; color: #d00000; margin-bottom: 2rem;"></i>
          <h1>Unnormalized Database</h1>
          <p class="lede" style="font-size: 2rem;">Flat structure leading to redundancy, NULLs, and anomalies</p>
        </div>
      </div>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Books table with repeated publisher + author",
    content: `
      <p class="lede">Publisher and author facts are stored inside every book row.</p>
      ${codeBlock("Books (Unnormalized)", [
        "CREATE TABLE Books (",
        "  book_id INT,",
        "  [[book_title VARCHAR(200)]],",
        "  [[book_isbn VARCHAR(20)]],",
        "  book_price DECIMAL(10,2),",
        "  book_category VARCHAR(100),",
        "  stock_quantity INT,",
        "  [[publisher_name VARCHAR(150)]],",
        "  publisher_address VARCHAR(250),",
        "  publisher_phone VARCHAR(20),",
        "  publisher_email VARCHAR(100),",
        "  [[author_name VARCHAR(150)]],",
        "  author_email VARCHAR(100),",
        "  author_phone VARCHAR(20),",
        "  author_nationality VARCHAR(100)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["book_title", "publisher_name", "author_name"],
        [
          ["Clean Code", "[[Pearson Education]]", "[[Robert C. Martin]]"],
          [
            "The Pragmatic Programmer",
            "[[Pearson Education]]",
            "[[David Thomas]]",
          ],
          ["Design Patterns", "[[Pearson Education]]", "[[Erich Gamma]]"],
        ],
      )}
      <p style="margin-top: 1.6rem; color: #b00020; font-weight: 700;">
        <i class="fa-solid fa-circle-xmark"></i> Same publisher and author repeated in every row.
      </p>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Orders table mixing purchase + sales",
    content: `
      <p class="lede">Single table stores both purchase and sale orders, producing heavy NULL columns.</p>
      ${codeBlock("Orders (Unnormalized)", [
        "CREATE TABLE Orders (",
        "  order_id INT,",
        "  order_date DATE,",
        "  order_type VARCHAR(10),",
        "  [[customer_name VARCHAR(150)]],",
        "  [[customer_phone VARCHAR(20)]],",
        "  [[supplier_name VARCHAR(150)]],",
        "  [[supplier_phone VARCHAR(20)]],",
        "  book_title VARCHAR(200),",
        "  quantity INT,",
        "  unit_price DECIMAL(10,2),",
        "  total_amount DECIMAL(10,2),",
        "  payment_status VARCHAR(20)",
        ");",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Why it fails</h3>
        ${bulletList([
          "Every order row has unused columns",
          "Customer data repeats on each sale",
          "Supplier data repeats on each purchase",
          "Difficult to enforce valid relationships",
        ])}
      </div>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Orders data with NULL columns",
    content: `
      <p class="lede">Purchase orders and sale orders occupy the same structure.</p>
      ${tableHtml(
        [
          "order_id",
          "order_type",
          "customer_name",
          "supplier_name",
          "book_title",
          "total_amount",
        ],
        [
          [
            "1001",
            "PURCHASE",
            "NULL",
            "[[BookMart Distributors]]",
            "Clean Code",
            "8000.00",
          ],
          ["2001", "SALE", "[[Ahmed Khan]]", "NULL", "Clean Code", "1198.00"],
          ["2002", "SALE", "[[Sara Ali]]", "NULL", "Atomic Habits", "1350.00"],
        ],
      )}
    `,
    media: `
      <div class="panel">
        <h3>Immediate impact</h3>
        ${bulletList([
          "Harder to query by party type",
          "Enforces no unique customer/supplier records",
          "Mixed semantics hurt reporting accuracy",
        ])}
      </div>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Update anomaly",
    content: `
      <p class="lede">Changing a publisher name requires updating every row that stores it.</p>
      ${codeBlock("Update publisher name", [
        "UPDATE Books",
        "SET publisher_name = 'Pearson Education (UK)'",
        "WHERE publisher_name = 'Pearson Education';",
        "-- Miss one row and data becomes inconsistent",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Why it matters</h3>
        ${bulletList([
          "Same fact stored in many rows",
          "High risk of partial updates",
          "Reports show conflicting publisher names",
        ])}
      </div>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Insert anomaly",
    content: `
      <p class="lede">You cannot add a new publisher without inventing a book row.</p>
      ${codeBlock("Insert publisher without a book?", [
        "INSERT INTO Books (",
        "  publisher_name, publisher_address",
        ") VALUES ('New Publisher', 'City, Country');",
        "-- Fails because book fields are required",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Symptoms</h3>
        ${bulletList([
          "Missing publishers until they publish a book",
          "Creates fake data to satisfy constraints",
          "Catalog planning becomes inaccurate",
        ])}
      </div>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Delete anomaly",
    content: `
      <p class="lede">Deleting the last book of a publisher removes the publisher itself.</p>
      ${codeBlock("Delete last book", [
        "DELETE FROM Books",
        "WHERE book_title = 'Design Patterns';",
        "-- Publisher data disappears if it existed only there",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Result</h3>
        ${bulletList([
          "Publisher record lost with book deletion",
          "No way to track historical relationships",
          "Weak audit trail",
        ])}
      </div>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Redundancy and NULL summary",
    content: `
      <p class="lede">A few rows contain dozens of repeated attributes and empty columns.</p>
      <div class="stat-grid">
        <div class="stat-card">
          <h4>Publisher fields</h4>
          <p>Repeated in every book row</p>
        </div>
        <div class="stat-card">
          <h4>Customer/Supplier</h4>
          <p>NULL half the time</p>
        </div>
        <div class="stat-card">
          <h4>Update cost</h4>
          <p>Multiple rows per fact</p>
        </div>
        <div class="stat-card">
          <h4>Query complexity</h4>
          <p>Mixed semantics in one table</p>
        </div>
      </div>
    `,
    media: `
      <div class="panel">
        <h3>Normalization goals</h3>
        ${bulletList([
          "Store each fact once",
          "Separate entity types",
          "Use foreign keys for relationships",
          "Prevent NULL-heavy tables",
        ])}
      </div>
    `,
  },
  {
    section: "Unnormalized DB",
    title: "Unnormalized ER view",
    content: `
      <p class="lede">Only two massive tables represent the entire business.</p>
      ${bulletList([
        "Books table merges book, author, and publisher",
        "Orders table merges customers, suppliers, and line items",
        "No explicit relationships or keys",
        "High data duplication across rows",
      ])}
    `,
    media: `
      ${imageCard(
        "ER-Diagram/Unormalized.svg",
        "Flat schema diagram",
        "Two tables attempt to represent many distinct entities.",
      )}
    `,
  },
  {
    section: "Normalization",
    title: "Normalization roadmap",
    content: `
      <p class="lede">We split the data in steps to reach 3NF while preserving meaning.</p>
      ${bulletList([
        "1NF: Ensure atomic values and remove repeating groups",
        "2NF: Separate partial dependencies in composite keys",
        "3NF: Remove transitive dependencies between non-keys",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Outcome</h3>
        ${bulletList([
          "10 focused tables",
          "Clean PK/FK relationships",
          "Flexible queries and reporting",
        ])}
      </div>
    `,
  },
  {
    section: "Normalization",
    title: "1NF: atomic fields",
    content: `
      <p class="lede">Every column stores a single, indivisible value.</p>
      ${codeBlock("Example cleanup", [
        "-- Each column stores one value",
        "book_title VARCHAR(200)",
        "author_name VARCHAR(150)",
        "publisher_name VARCHAR(150)",
        "-- No multi-value lists in a single field",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Checklist</h3>
        ${bulletList([
          "No comma-separated values",
          "Consistent data types",
          "Each row is unique",
        ])}
      </div>
    `,
  },
  {
    section: "Normalization",
    title: "2NF: split partial dependencies",
    content: `
      <p class="lede">Separate data that depends only on part of a key.</p>
      ${codeBlock("Split publisher out", [
        "CREATE TABLE Publishers (",
        "  publisher_id INT PRIMARY KEY,",
        "  [[publisher_name VARCHAR(150)]],",
        "  country VARCHAR(100)",
        ");",
        "-- Books now reference publisher_id",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Result</h3>
        ${bulletList([
          "Publisher data stored once",
          "Books table shrinks",
          "Updates happen in one place",
        ])}
      </div>
    `,
  },
  {
    section: "Normalization",
    title: "3NF: remove transitive dependencies",
    content: `
      <p class="lede">Non-key fields should depend only on the primary key.</p>
      ${codeBlock("Split orders by type", [
        "CREATE TABLE Sale_Orders (",
        "  so_id INT PRIMARY KEY,",
        "  customer_id INT,",
        "  total_amount DECIMAL(12,2)",
        ");",
        "CREATE TABLE Purchase_Orders (",
        "  po_id INT PRIMARY KEY,",
        "  supplier_id INT,",
        "  total_amount DECIMAL(12,2)",
        ");",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Impact</h3>
        ${bulletList([
          "No mixed customer/supplier columns",
          "Clear domain for each order type",
          "Consistent constraints",
        ])}
      </div>
    `,
  },
  {
    section: "Normalized DB",
    content: `
      <div class="slide__grid slide__grid--center text-center norm-title-slide">
        <div style="margin-top: 8rem;">
          <i class="fa-solid fa-check-circle" style="font-size: 6rem; color: #008800; margin-bottom: 2rem;"></i>
          <h1>Normalized Database</h1>
          <p class="lede" style="font-size: 2rem;">Structured into 3NF eliminating anomalies</p>
        </div>
      </div>
    `,
  },
  {
    section: "Normalized DB",
    title: "10-table schema overview",
    content: `
      <p class="lede">Each table has a single responsibility, linked by foreign keys.</p>
      ${tableHtml(
        ["Domain", "Tables"],
        [
          ["Catalog", "Publishers, Authors, Books, Book_Authors"],
          ["Customers", "Customers"],
          ["Suppliers", "Suppliers"],
          ["Purchasing", "Purchase_Orders, Purchase_Order_Items"],
          ["Sales", "Sale_Orders, Sale_Order_Items"],
        ],
      )}
    `,
    media: `
      ${imageCard(
        "ER-Diagram/Normalized.svg",
        "Normalized schema diagram",
        "Ten tables connected with explicit PK/FK relationships.",
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Publishers table",
    content: `
      <p class="lede">Dedicated table storing Publisher information exactly once.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Publishers (",
        "  [[publisher_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  publisher_name VARCHAR(150) NOT NULL,",
        "  address VARCHAR(250),",
        "  phone VARCHAR(20),",
        "  email VARCHAR(100) UNIQUE,",
        "  country VARCHAR(100)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["publisher_id", "publisher_name", "country"],
        [
          ["1", "Pearson Education", "UK"],
          ["2", "Penguin Random House", "USA"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Authors table",
    content: `
      <p class="lede">Authors live in their own table for a clean many-to-many link.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Authors (",
        "  [[author_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  author_name VARCHAR(150) NOT NULL,",
        "  email VARCHAR(100) UNIQUE,",
        "  phone VARCHAR(20),",
        "  nationality VARCHAR(100)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["author_id", "author_name", "nationality"],
        [
          ["1", "Robert C. Martin", "American"],
          ["4", "James Clear", "American"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Books table",
    content: `
      <p class="lede">Books reference Publishers and store pricing + stock.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Books (",
        "  [[book_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  isbn VARCHAR(20) UNIQUE,",
        "  title VARCHAR(200) NOT NULL,",
        "  category VARCHAR(100),",
        "  price DECIMAL(10,2) NOT NULL,",
        "  stock_quantity INT NOT NULL DEFAULT 0,",
        "  [[publisher_id INT]],",
        "  FOREIGN KEY (publisher_id)",
        "    REFERENCES Publishers(publisher_id)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["book_id", "title", "publisher_id", "stock_quantity"],
        [
          ["1", "Clean Code", "1", "50"],
          ["4", "Atomic Habits", "2", "80"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Book_Authors junction table",
    content: `
      <p class="lede">Many-to-many relationships are resolved with a junction table.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Book_Authors (",
        "  [[book_id INT]],",
        "  [[author_id INT]],",
        "  PRIMARY KEY (book_id, author_id),",
        "  FOREIGN KEY (book_id) REFERENCES Books(book_id),",
        "  FOREIGN KEY (author_id) REFERENCES Authors(author_id)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["book_id", "author_id", "meaning"],
        [
          ["1", "1", "Clean Code -> Robert C. Martin"],
          ["4", "4", "Atomic Habits -> James Clear"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Customers table",
    content: `
      <p class="lede">Customers are stored once and referenced by sale orders.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Customers (",
        "  [[customer_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  full_name VARCHAR(150) NOT NULL,",
        "  phone VARCHAR(20),",
        "  email VARCHAR(100) UNIQUE,",
        "  address VARCHAR(250)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["customer_id", "full_name", "phone"],
        [
          ["1", "Ahmed Khan", "+92-300-1111111"],
          ["2", "Sara Ali", "+92-321-2222222"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Suppliers table",
    content: `
      <p class="lede">Suppliers are separated for purchase workflows and analytics.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Suppliers (",
        "  [[supplier_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  supplier_name VARCHAR(150) NOT NULL,",
        "  phone VARCHAR(20),",
        "  email VARCHAR(100) UNIQUE,",
        "  address VARCHAR(250),",
        "  city VARCHAR(100)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["supplier_id", "supplier_name", "city"],
        [
          ["1", "BookMart Distributors", "Karachi"],
          ["3", "Global Books Supply", "Islamabad"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Purchase_Orders header",
    content: `
      <p class="lede">Header table captures supplier, date, and payment status.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Purchase_Orders (",
        "  [[po_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  po_date DATE NOT NULL,",
        "  [[supplier_id INT NOT NULL]],",
        "  total_amount DECIMAL(12,2) NOT NULL,",
        "  payment_status VARCHAR(20) NOT NULL,",
        "  FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["po_id", "po_date", "supplier_id", "payment_status"],
        [
          ["1", "2024-01-05", "1", "PAID"],
          ["3", "2024-02-01", "1", "PENDING"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Purchase_Order_Items line items",
    content: `
      <p class="lede">Each line item links one book with quantity and price.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Purchase_Order_Items (",
        "  [[po_item_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  [[po_id INT NOT NULL]],",
        "  [[book_id INT NOT NULL]],",
        "  quantity INT NOT NULL,",
        "  unit_price DECIMAL(10,2) NOT NULL,",
        "  FOREIGN KEY (po_id) REFERENCES Purchase_Orders(po_id),",
        "  FOREIGN KEY (book_id) REFERENCES Books(book_id)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["po_id", "book_id", "quantity", "unit_price"],
        [
          ["1", "1", "20", "400.00"],
          ["2", "4", "30", "300.00"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Sale_Orders header",
    content: `
      <p class="lede">Customer orders are separated from purchase orders.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Sale_Orders (",
        "  [[so_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  so_date DATE NOT NULL,",
        "  [[customer_id INT NOT NULL]],",
        "  total_amount DECIMAL(12,2) NOT NULL,",
        "  payment_status VARCHAR(20) NOT NULL,",
        "  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["so_id", "so_date", "customer_id", "payment_status"],
        [
          ["1", "2024-01-08", "1", "PAID"],
          ["4", "2024-02-05", "3", "PENDING"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Sale_Order_Items line items",
    content: `
      <p class="lede">Each sale line item links a book to a sale order.</p>
      ${codeBlock("Table format", [
        "CREATE TABLE Sale_Order_Items (",
        "  [[so_item_id INT PRIMARY KEY AUTO_INCREMENT]],",
        "  [[so_id INT NOT NULL]],",
        "  [[book_id INT NOT NULL]],",
        "  quantity INT NOT NULL,",
        "  unit_price DECIMAL(10,2) NOT NULL,",
        "  FOREIGN KEY (so_id) REFERENCES Sale_Orders(so_id),",
        "  FOREIGN KEY (book_id) REFERENCES Books(book_id)",
        ");",
      ])}
    `,
    media: `
      ${tableHtml(
        ["so_id", "book_id", "quantity", "unit_price"],
        [
          ["1", "1", "2", "599.00"],
          ["2", "4", "3", "450.00"],
        ],
      )}
    `,
  },
  {
    section: "Normalized DB",
    title: "Key constraints overview",
    content: `
      <p class="lede">Constraints enforce integrity between every table.</p>
      ${codeBlock("Example constraints", [
        "Books.publisher_id -> Publishers.publisher_id",
        "Book_Authors.book_id -> Books.book_id",
        "Book_Authors.author_id -> Authors.author_id",
        "Purchase_Orders.supplier_id -> Suppliers.supplier_id",
        "Sale_Orders.customer_id -> Customers.customer_id",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Integrity rules</h3>
        ${bulletList([
          "No orphaned line items",
          "Unique emails for publishers/customers",
          "Accurate joins for reporting",
          "Enforced data consistency",
        ])}
      </div>
    `,
  },
  {
    section: "ER Diagram",
    title: "Normalized ER diagram",
    content: `
      <p class="lede">Relationships are explicit and readable with PK/FK links.</p>
      ${bulletList([
        "Books link to Publishers via publisher_id",
        "Book_Authors maps the many-to-many relationship",
        "Orders link to Customers or Suppliers",
        "Line items connect orders to books",
      ])}
    `,
    media: `
      ${imageCard(
        "ER-Diagram/Normalized.svg",
        "Entity relationships",
        "Clear joins enable accurate analytics and consistent updates.",
      )}
    `,
  },
  {
    section: "ER Diagram",
    title: "Relationship rules",
    content: `
      <p class="lede">Each relationship type is intentional.</p>
      ${bulletList([
        "Publishers -> Books is one-to-many",
        "Books -> Book_Authors -> Authors is many-to-many",
        "Customers -> Sale_Orders is one-to-many",
        "Orders -> Line Items is one-to-many",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Why this helps</h3>
        ${bulletList([
          "Clean joins in SQL",
          "Efficient indexing",
          "No duplicate entity data",
        ])}
      </div>
    `,
  },
  {
    section: "Queries",
    content: `
      <div class="slide__grid slide__grid--center text-center query-title-slide">
        <div style="margin-top: 8rem;">
          <i class="fa-solid fa-terminal" style="font-size: 6rem; color: #0055d0; margin-bottom: 2rem;"></i>
          <h1>Queries</h1>
          <p class="lede" style="font-size: 2rem;">Operational SQL execution with results</p>
        </div>
      </div>
    `,
  },
  {
    section: "Queries",
    title: "Place a new purchase order",
    content: `
      <p class="lede">Creates a PO header, adds line items, then updates totals.</p>
      ${codeBlock("PO steps", [
        "INSERT INTO Purchase_Orders (po_date, supplier_id, total_amount, payment_status)",
        "VALUES (CURDATE(), 1, 0.00, 'PENDING');",
        "SET @new_po_id = LAST_INSERT_ID();",
        "INSERT INTO Purchase_Order_Items (po_id, book_id, quantity, unit_price)",
        "VALUES (@new_po_id, 3, 10, 620.00);",
        "UPDATE Purchase_Orders SET total_amount = (",
        "  SELECT SUM(quantity * unit_price) FROM Purchase_Order_Items",
        "  WHERE po_id = @new_po_id",
        ") WHERE po_id = @new_po_id;",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Expected output</h3>
        ${bulletList([
          "New PO header created",
          "Line items attached",
          "Total amount recalculated",
        ])}
      </div>
    `,
  },
  {
    section: "Queries",
    title: "Purchase orders list",
    content: `
      <p class="lede">Join purchase orders with suppliers for readable output.</p>
      ${codeBlock("PO list query", [
        "SELECT po.po_id, po.po_date, s.supplier_name,",
        "       s.city, po.total_amount, po.payment_status",
        "FROM Purchase_Orders po",
        "JOIN Suppliers s ON po.supplier_id = s.supplier_id",
        "ORDER BY po.po_date DESC;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/PurchaseOrdwe.png",
        "Purchase order report",
        "Supplier and payment status shown in a single result view.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Purchase order line items",
    content: `
      <p class="lede">Itemized view shows each book, quantity, and line total.</p>
      ${codeBlock("PO item query", [
        "SELECT poi.po_item_id, b.title, b.isbn,",
        "       poi.quantity, poi.unit_price,",
        "       (poi.quantity * poi.unit_price) AS line_total",
        "FROM Purchase_Order_Items poi",
        "JOIN Books b ON poi.book_id = b.book_id",
        "WHERE poi.po_id = 1;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/CleanPO.png",
        "Line items output",
        "Granular quantities and totals for each purchased book.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Mark purchase order as paid",
    content: `
      <p class="lede">Status updates are isolated to the header row.</p>
      ${codeBlock("Status update", [
        "UPDATE Purchase_Orders",
        "SET payment_status = 'PAID'",
        "WHERE po_id = 3;",
        "SELECT CONCAT('PO #3 marked as PAID.') AS Result;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/MarkedPaid.png",
        "Status confirmation",
        "Clean confirmation output after payment status change.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Place a sale order",
    content: `
      <p class="lede">Check stock, create header, add items, then update totals.</p>
      ${codeBlock("Sale order steps", [
        "SELECT book_id, title, stock_quantity",
        "FROM Books WHERE book_id IN (1, 6);",
        "INSERT INTO Sale_Orders (so_date, customer_id, total_amount, payment_status)",
        "VALUES (CURDATE(), 2, 0.00, 'PAID');",
        "SET @new_so_id = LAST_INSERT_ID();",
        "INSERT INTO Sale_Order_Items (so_id, book_id, quantity, unit_price)",
        "VALUES (@new_so_id, 1, 1, 599.00);",
        "UPDATE Sale_Orders SET total_amount = (",
        "  SELECT SUM(quantity * unit_price) FROM Sale_Order_Items WHERE so_id = @new_so_id",
        ") WHERE so_id = @new_so_id;",
      ])}
    `,
    media: `
      <div class="panel">
        <h3>Key checks</h3>
        ${bulletList([
          "Stock validated before sale",
          "Totals computed from line items",
          "Payment status saved per order",
        ])}
      </div>
    `,
  },
  {
    section: "Queries",
    title: "Sale order result",
    content: `
      <p class="lede">Sales output joins customers for readable reports.</p>
      ${codeBlock("Sale order list", [
        "SELECT so.so_id, so.so_date, c.full_name,",
        "       c.phone, so.total_amount, so.payment_status",
        "FROM Sale_Orders so",
        "JOIN Customers c ON so.customer_id = c.customer_id",
        "ORDER BY so.so_date DESC;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/SaleOrder.png",
        "Sale order report",
        "Customer details and totals shown per sale order.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Sale order with customer phone",
    content: `
      <p class="lede">Customer contact data is joined from Customers table.</p>
      ${codeBlock("Customer join", [
        "SELECT so.so_id, so.so_date, c.full_name,",
        "       c.phone, so.total_amount",
        "FROM Sale_Orders so",
        "JOIN Customers c ON so.customer_id = c.customer_id;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/SaleOrderWithphoneNumber.png",
        "Sale order with phone",
        "Normalized schema allows immediate access to contact data.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Inventory status report",
    content: `
      <p class="lede">Stock status is computed with CASE logic.</p>
      ${codeBlock("Inventory query", [
        "SELECT b.title, b.stock_quantity,",
        "CASE",
        "  WHEN b.stock_quantity = 0 THEN 'OUT OF STOCK'",
        "  WHEN b.stock_quantity < 10 THEN 'LOW STOCK'",
        "  WHEN b.stock_quantity < 30 THEN 'MODERATE'",
        "  ELSE 'IN STOCK'",
        "END AS stock_status",
        "FROM Books b ORDER BY b.stock_quantity ASC;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/BooksStock.png",
        "Inventory status",
        "Stock levels and labels for each book.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Low stock report",
    content: `
      <p class="lede">Identify books that need reordering with supplier contact info.</p>
      ${codeBlock("Low stock query", [
        "SELECT b.title, b.stock_quantity, s.supplier_name, s.phone",
        "FROM Books b",
        "JOIN Purchase_Order_Items poi ON poi.book_id = b.book_id",
        "JOIN Purchase_Orders po ON po.po_id = poi.po_id",
        "JOIN Suppliers s ON s.supplier_id = po.supplier_id",
        "WHERE b.stock_quantity < 30",
        "GROUP BY b.book_id, b.title, b.stock_quantity, s.supplier_name, s.phone;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/BreifBookStock.png",
        "Low stock shortlist",
        "Actionable list with supplier details.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Inventory value",
    content: `
      <p class="lede">Multiply stock quantity by price for each title.</p>
      ${codeBlock("Stock value query", [
        "SELECT b.title, b.stock_quantity, b.price,",
        "(b.stock_quantity * b.price) AS stock_value",
        "FROM Books b ORDER BY stock_value DESC;",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/InventoryValue.png",
        "Inventory value table",
        "Financial value of current stock for each book.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Total revenue",
    content: `
      <p class="lede">Aggregate sales to see overall revenue performance.</p>
      ${codeBlock("Revenue query", [
        "SELECT COUNT(so.so_id) AS total_orders,",
        "       SUM(so.total_amount) AS total_revenue,",
        "       AVG(so.total_amount) AS average_order_value",
        "FROM Sale_Orders so",
        "WHERE so.payment_status = 'PAID';",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/TotalRevenue.png",
        "Revenue summary",
        "Revenue, order count, and average order value.",
      )}
    `,
  },
  {
    section: "Queries",
    title: "Book lookup by author",
    content: `
      <p class="lede">Join Books, Book_Authors, and Authors for author search.</p>
      ${codeBlock("Author lookup", [
        "SELECT b.title, b.price, b.stock_quantity",
        "FROM Books b",
        "JOIN Book_Authors ba ON b.book_id = ba.book_id",
        "JOIN Authors a ON ba.author_id = a.author_id",
        "WHERE a.author_name LIKE '%James Clear%';",
      ])}
    `,
    media: `
      ${imageCard(
        "Result/BooksTableInDetail.png",
        "Catalog lookup",
        "Shows book-level details filtered by author.",
      )}
    `,
  },
  {
    section: "Conclusion",
    content: `
      <div class="slide__grid slide__grid--left text-left">
        <div style="margin-top: 4.5rem;">
          <h1 style="font-size: 4.8rem;">Key takeaways</h1>
          ${bulletList([
            "Normalization removes redundancy and anomalies",
            "3NF schema enables reliable joins and reporting",
            "Operational SQL becomes simpler and safer",
            "Query outputs validate real-world workflows",
          ])}
          <p class="lede" style="margin-top: 1.5rem;">Bookstore POS data is now clean, scalable, and report-ready.</p>
        </div>
      </div>
    `,
  },
  {
    section: "Conclusion",
    content: `
      <div class="slide__grid slide__grid--center text-center">
        <div style="margin-top: 4.5rem;">
          <h1 style="font-size: 5.8rem;">Thank you</h1>
          <p class="lede" style="font-size: 2.6rem;">Questions and discussion</p>
          <div style="margin-top: 2rem; color: var(--muted);">
            <i class="fa-solid fa-comments" style="font-size: 4rem; margin: 0 1rem;"></i>
            <i class="fa-solid fa-code" style="font-size: 4rem; margin: 0 1rem;"></i>
            <i class="fa-solid fa-database" style="font-size: 4rem; margin: 0 1rem;"></i>
          </div>
        </div>
      </div>
    `,
  },
  {
    section: "Conclusion",
    content: `
      <div class="slide__grid slide__grid--center text-center">
        <div style="margin-top: 4rem;">
          <h1 style="font-size: 3.4rem;">Source code</h1>
          <p class="lede" style="font-size: 1.4rem;">Scan the QR to view the full project source code.</p>
          <div style="display: flex; justify-content: center; margin-top: 2rem;">
            <img src="link-qrcode.png" alt="Source code QR" style="width: 500px; height: 500px;" />
          </div>
        </div>
      </div>
    `,
  },
];

const slidesTrack = document.getElementById("slidesTrack");
const sectionLabel = document.getElementById("sectionLabel");
const slideCounter = document.getElementById("slideCounter");
const progressFill = document.getElementById("progressFill");
const introScreen = document.getElementById("introScreen");
const startButton = document.getElementById("startButton");
const fullscreenButton = document.getElementById("fullscreenButton");
const appShell = document.getElementById("presentationApp");

let currentSlide = 0;
let isStarted = false;

const requestFullscreen = () => {
  const elem = document.documentElement;
  const request =
    elem.requestFullscreen ||
    elem.webkitRequestFullscreen ||
    elem.mozRequestFullScreen ||
    elem.msRequestFullscreen;
  if (request) {
    try {
      return request.call(elem);
    } catch (error) {
      return Promise.resolve();
    }
  }
  return Promise.resolve();
};

const startPresentation = () => {
  if (isStarted) {
    return;
  }
  isStarted = true;
  appShell?.classList.add("is-started");
  introScreen?.setAttribute("aria-hidden", "true");
  requestFullscreen();
  goToSlide(0);
};

const renderSlide = (slide, index) => {
  const hasMedia = !!slide.media;
  const isSingle = slide.layout === "single" || (!slide.title && !hasMedia);
  const gridClass =
    slide.layout === "wide" ? "slide__grid slide__grid--wide" : "slide__grid";

  return `
    <section class="slide" data-index="${index}" aria-label="Slide ${index + 1}">
      <div class="slide__frame">
        ${
          isSingle
            ? slide.content
            : `<div class="${gridClass}">
                <div class="slide__copy">
                  <h1>${escapeHtml(slide.title)}</h1>
                  ${slide.content}
                </div>
                <div class="slide__media">${slide.media}</div>
              </div>`
        }
      </div>
    </section>
  `;
};

const renderDeck = () => {
  slidesTrack.innerHTML = slides.map((s, i) => renderSlide(s, i)).join("");
};

const updateView = () => {
  slidesTrack.style.transform = `translateX(-${currentSlide * 100}%)`;
  slideCounter.textContent = `${currentSlide + 1} / ${slides.length}`;
  sectionLabel.textContent = slides[currentSlide]?.section || "";
  progressFill.style.width = `${((currentSlide + 1) / slides.length) * 100}%`;
};

const goToSlide = (index) => {
  currentSlide = Math.max(0, Math.min(slides.length - 1, index));
  updateView();
};

document.addEventListener("keydown", (event) => {
  if (!isStarted) {
    startPresentation();
    return;
  }
  if (
    event.key === "ArrowRight" ||
    event.key === " " ||
    event.key === "PageDown"
  ) {
    event.preventDefault();
    goToSlide(currentSlide + 1);
  } else if (event.key === "ArrowLeft" || event.key === "PageUp") {
    event.preventDefault();
    goToSlide(currentSlide - 1);
  }
});

slidesTrack.addEventListener("click", (event) => {
  if (!isStarted) {
    startPresentation();
    return;
  }
  const clickedRightHalf = event.clientX > window.innerWidth / 2;
  goToSlide(clickedRightHalf ? currentSlide + 1 : currentSlide - 1);
});

startButton?.addEventListener("click", startPresentation);
fullscreenButton?.addEventListener("click", requestFullscreen);

window.addEventListener("resize", updateView);

renderDeck();
updateView();