setup.sql

-- ============================================================
-- Data Lineage Interview: Database Setup
-- Engine : MySQL 8.0+
-- Run    : mysql -u root -p < setup.sql
--          (or paste into MySQL Workbench / DBeaver)
-- ============================================================

CREATE DATABASE IF NOT EXISTS ecommerce_db;
USE ecommerce_db;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE  IF EXISTS order_items;
DROP TABLE  IF EXISTS order_status_log;
DROP TABLE  IF EXISTS orders;
DROP TABLE  IF EXISTS orders_staging;
DROP TABLE  IF EXISTS customers;
DROP TABLE  IF EXISTS customer_tier_rules;
DROP TABLE  IF EXISTS products;
DROP VIEW   IF EXISTS vw_customer_summary;
DROP VIEW   IF EXISTS vw_high_value_customers;
DROP VIEW   IF EXISTS vw_orders_detail;
DROP PROCEDURE IF EXISTS sp_process_staged_orders;
DROP PROCEDURE IF EXISTS sp_set_customer_tier;
SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- BASE TABLES
-- ============================================================

CREATE TABLE customer_tier_rules (
    tier_name     VARCHAR(20)   PRIMARY KEY,
    min_orders    INT           NOT NULL,
    max_orders    INT           NULL,           -- NULL means no upper bound
    discount_pct  DECIMAL(5,2)  NOT NULL DEFAULT 0.00
);

CREATE TABLE customers (
    customer_id      INT           PRIMARY KEY AUTO_INCREMENT,
    email            VARCHAR(255)  NOT NULL UNIQUE,
    first_name       VARCHAR(100),
    last_name        VARCHAR(100),
    customer_tier    VARCHAR(20)   NOT NULL DEFAULT 'STANDARD',
    lifetime_orders  INT           NOT NULL DEFAULT 0,
    created_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME      NULL,
    last_modified_by VARCHAR(100)  NULL
);

CREATE TABLE products (
    product_id    INT           PRIMARY KEY AUTO_INCREMENT,
    sku           VARCHAR(50)   NOT NULL UNIQUE,
    product_name  VARCHAR(255)  NOT NULL,
    category_code CHAR(3)       NOT NULL,
    list_price    DECIMAL(10,2) NOT NULL,
    cost_price    DECIMAL(10,2) NOT NULL,
    is_active     TINYINT(1)    NOT NULL DEFAULT 1
);

CREATE TABLE orders (
    order_id         INT           PRIMARY KEY AUTO_INCREMENT,
    customer_id      INT           NOT NULL,
    order_date       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_code      CHAR(2)       NOT NULL DEFAULT 'PE',
    subtotal         DECIMAL(12,2) NOT NULL,
    discount_amount  DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount     DECIMAL(12,2) NOT NULL,
    source_system    VARCHAR(20)   NOT NULL DEFAULT 'WEB',
    processed_by     VARCHAR(100)  NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    item_id     INT           PRIMARY KEY AUTO_INCREMENT,
    order_id    INT           NOT NULL,
    product_id  INT           NOT NULL,
    quantity    INT           NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL,
    line_total  DECIMAL(12,2) NULL,       -- intentionally nullable; see triggers
    FOREIGN KEY (order_id)   REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE orders_staging (
    staging_id         INT           PRIMARY KEY AUTO_INCREMENT,
    external_order_ref VARCHAR(100)  NOT NULL,
    raw_status         VARCHAR(50)   NULL,      -- human-readable from source system
    raw_customer_email VARCHAR(255)  NULL,
    raw_total          VARCHAR(50)   NULL,      -- arrives as VARCHAR from feed
    loaded_at          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed          TINYINT(1)    NOT NULL DEFAULT 0
);

CREATE TABLE order_status_log (
    log_id      INT           PRIMARY KEY AUTO_INCREMENT,
    order_id    INT           NOT NULL,
    old_status  CHAR(2)       NULL,
    new_status  CHAR(2)       NOT NULL,
    changed_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by  VARCHAR(100)  NULL
);

-- ============================================================
-- VIEWS
-- ============================================================

-- Renames several columns and adds a joined column from customer_tier_rules
CREATE OR REPLACE VIEW vw_customer_summary AS
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
    c.email                                AS contact_email,
    c.customer_tier                        AS loyalty_tier,
    c.lifetime_orders,
    r.discount_pct                         AS tier_discount
FROM customers c
LEFT JOIN customer_tier_rules r ON c.customer_tier = r.tier_name;

-- Built on top of vw_customer_summary (nested view)
CREATE OR REPLACE VIEW vw_high_value_customers AS
SELECT
    cs.customer_id,
    cs.full_name,
    cs.contact_email,
    cs.tier_discount,
    totals.grand_total
FROM vw_customer_summary cs
JOIN (
    SELECT customer_id, SUM(total_amount) AS grand_total
    FROM orders
    GROUP BY customer_id
) totals ON cs.customer_id = totals.customer_id
WHERE cs.loyalty_tier IN ('GOLD', 'PLATINUM');

-- Translates the 2-char status_code into a human-readable label
CREATE OR REPLACE VIEW vw_orders_detail AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    CASE o.status_code
        WHEN 'PE' THEN 'Pending'
        WHEN 'CF' THEN 'Confirmed'
        WHEN 'SH' THEN 'Shipped'
        WHEN 'DL' THEN 'Delivered'
        WHEN 'CN' THEN 'Cancelled'
        WHEN 'RF' THEN 'Refunded'
        ELSE            'Unknown'
    END                AS order_status,
    o.subtotal,
    o.discount_amount,
    o.total_amount     AS order_total,
    o.source_system
FROM orders o;

-- ============================================================
-- TRIGGERS
-- ============================================================

DELIMITER $$

-- Computes line_total automatically; application never sets it directly
CREATE TRIGGER trg_order_items_before_insert
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    SET NEW.line_total = NEW.quantity * NEW.unit_price;
END$$

CREATE TRIGGER trg_order_items_before_update
BEFORE UPDATE ON order_items
FOR EACH ROW
BEGIN
    SET NEW.line_total = NEW.quantity * NEW.unit_price;
END$$

-- After each new order, recalculate the customer's lifetime count and tier
CREATE TRIGGER trg_update_customer_tier
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE v_new_count INT;
    DECLARE v_new_tier  VARCHAR(20);

    SET v_new_count = (
        SELECT lifetime_orders + 1
        FROM customers
        WHERE customer_id = NEW.customer_id
    );

    SELECT tier_name INTO v_new_tier
    FROM customer_tier_rules
    WHERE min_orders <= v_new_count
      AND (max_orders IS NULL OR max_orders >= v_new_count)
    ORDER BY min_orders DESC
    LIMIT 1;

    UPDATE customers
    SET lifetime_orders  = v_new_count,
        customer_tier    = COALESCE(v_new_tier, customer_tier),
        updated_at       = NOW(),
        last_modified_by = 'TRIGGER:trg_update_customer_tier'
    WHERE customer_id = NEW.customer_id;
END$$

-- Audits direct customer updates; auto-stamps who made the change
CREATE TRIGGER trg_customers_audit
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
    -- Only auto-fill last_modified_by when the caller did not explicitly change it
    IF NEW.last_modified_by = OLD.last_modified_by THEN
        SET NEW.last_modified_by = USER();
    END IF;
END$$

-- Logs every status_code change on orders into order_status_log
CREATE TRIGGER trg_orders_status_change
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status_code <> NEW.status_code THEN
        INSERT INTO order_status_log (order_id, old_status, new_status, changed_by)
        VALUES (NEW.order_id, OLD.status_code, NEW.status_code, USER());
    END IF;
END$$

DELIMITER ;

-- ============================================================
-- STORED PROCEDURES
-- ============================================================

DELIMITER $$

-- Loads pending rows from orders_staging into the orders table.
-- Maps the free-text raw_status from the external feed to internal status_code.
CREATE PROCEDURE sp_process_staged_orders()
BEGIN
    INSERT INTO orders (
        customer_id, status_code, subtotal, discount_amount,
        total_amount, source_system, processed_by
    )
    SELECT
        c.customer_id,
        CASE LOWER(TRIM(s.raw_status))
            WHEN 'new'       THEN 'PE'
            WHEN 'confirmed' THEN 'CF'
            WHEN 'shipped'   THEN 'SH'
            WHEN 'cancelled' THEN 'CN'
            ELSE 'PE'
        END,
        CAST(s.raw_total AS DECIMAL(12,2)),
        0.00,
        CAST(s.raw_total AS DECIMAL(12,2)),
        'IMPORT',
        'sp_process_staged_orders'
    FROM orders_staging s
    JOIN customers c ON c.email = s.raw_customer_email
    WHERE s.processed = 0;

    UPDATE orders_staging
    SET processed = 1
    WHERE processed = 0;
END$$

-- Admin override: lets an operator manually assign a customer tier
CREATE PROCEDURE sp_set_customer_tier(
    IN p_customer_id INT,
    IN p_tier        VARCHAR(20),
    IN p_operator    VARCHAR(100)
)
BEGIN
    UPDATE customers
    SET customer_tier    = p_tier,
        last_modified_by = p_operator
    WHERE customer_id = p_customer_id;
END$$

DELIMITER ;

-- ============================================================
-- SAMPLE DATA
-- (insert tier rules before customers so the trigger can resolve tiers)
-- ============================================================

INSERT INTO customer_tier_rules (tier_name, min_orders, max_orders, discount_pct) VALUES
('STANDARD',  0,   4,    0.00),
('SILVER',    5,   14,   5.00),
('GOLD',      15,  29,  10.00),
('PLATINUM',  30,  NULL, 15.00);

INSERT INTO customers (email, first_name, last_name) VALUES
('alice@example.com',  'Alice', 'Nguyen'),
('bob@example.com',    'Bob',   'Patel'),
('carol@example.com',  'Carol', 'Okonkwo');

INSERT INTO products (sku, product_name, category_code, list_price, cost_price) VALUES
('WDG-001', 'Widget Pro',     'WDG', 49.99, 12.00),
('WDG-002', 'Widget Lite',    'WDG', 19.99,  5.00),
('GDG-001', 'Gadget Deluxe',  'GDG', 99.99, 35.00),
('ACC-001', 'Accessory Pack', 'ACC', 14.99,  3.50);

-- Alice: 5 orders -> SILVER tier (trigger fires on each insert)
INSERT INTO orders (customer_id, status_code, subtotal, discount_amount, total_amount, source_system) VALUES
(1, 'DL',  49.99, 0.00,  49.99, 'WEB'),
(1, 'DL',  19.99, 0.00,  19.99, 'WEB'),
(1, 'DL',  99.99, 5.00,  94.99, 'WEB'),
(1, 'DL',  49.99, 0.00,  49.99, 'WEB'),
(1, 'SH',  14.99, 0.00,  14.99, 'MOBILE');

-- Bob: 1 order -> stays STANDARD
INSERT INTO orders (customer_id, status_code, subtotal, discount_amount, total_amount, source_system) VALUES
(2, 'PE',  99.99, 0.00,  99.99, 'WEB');

-- Order items (line_total intentionally omitted; trigger will populate it)
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 49.99),
(2, 2, 1, 19.99),
(3, 3, 1, 99.99),
(4, 1, 1, 49.99),
(5, 4, 1, 14.99),
(6, 3, 1, 99.99);

-- Demonstrate a status change so order_status_log gets a row
UPDATE orders SET status_code = 'CF' WHERE order_id = 6;
UPDATE orders SET status_code = 'SH' WHERE order_id = 6;

-- Pending rows for Carol and a second Bob order (not yet processed)
INSERT INTO orders_staging (external_order_ref, raw_status, raw_customer_email, raw_total) VALUES
('EXT-9001', 'confirmed', 'carol@example.com', '149.98'),
('EXT-9002', 'new',       'bob@example.com',   '19.99');