Implementation Guide: Analyze sales mix by daypart, item, and server to optimize menu and staffing

speedtest-cli --simple

ssh admin@<gateway-ip> 'info'

nmap -sn 192.168.1.0/24 > site_survey_network_scan.txt

# enter in UniFi Controller > Settings > Networks

# VLANs:
VLAN 10: POS Network (192.168.10.0/24) — POS terminals, KDS, printers
VLAN 20: Management (192.168.20.0/24) — back-office PC, manager tablet
VLAN 30: Guest WiFi (192.168.30.0/24) — customer-facing, isolated
VLAN 40: IoT/Cameras (192.168.40.0/24) — security cameras, smart devices

# enter in UniFi Controller > Settings > Firewall & Security

# Firewall Rules:
Rule 1: ALLOW VLAN 10 (POS) → Internet (for cloud POS and payment processing)
Rule 2: ALLOW VLAN 20 (Mgmt) → VLAN 10 (POS) (for admin access to POS)
Rule 3: BLOCK VLAN 30 (Guest) → ALL other VLANs
Rule 4: BLOCK VLAN 40 (IoT) → VLAN 10 (POS)
Rule 5: ALLOW VLAN 20 (Mgmt) → Internet (for dashboard access)

# WiFi SSIDs:
SSID: [RestaurantName]-POS    → VLAN 10 (WPA3-Enterprise or WPA2-PSK with strong key)
SSID: [RestaurantName]-Office → VLAN 20 (WPA2/3-PSK)
SSID: [RestaurantName]-Guest  → VLAN 30 (WPA2-PSK, captive portal optional)

curl -X GET 'https://toast-api-server/orders/v2/orders' \
  -H 'Toast-Restaurant-External-ID: <restaurant-guid>' \
  -H 'Authorization: Bearer <access-token>' \
  -H 'Content-Type: application/json' \
  --data-urlencode 'startDate=2025-01-01T00:00:00.000+0000' \
  --data-urlencode 'endDate=2025-01-07T23:59:59.000+0000'

curl -X POST 'https://connect.squareup.com/v2/orders/search' \
  -H 'Square-Version: 2025-01-23' \
  -H 'Authorization: Bearer <access-token>' \
  -H 'Content-Type: application/json' \
  -d '{
    "location_ids": ["<location-id>"],
    "query": {
      "filter": {
        "date_time_filter": {
          "created_at": {
            "start_at": "2025-01-01T00:00:00Z",
            "end_at": "2025-01-07T23:59:59Z"
          }
        }
      }
    }
  }'

# retrieve transactions via Lightspeed Restaurant API

curl -X GET 'https://api.lightspeedrestaurant.com/transactions' \
  -H 'Authorization: Bearer <access-token>' \
  -H 'Content-Type: application/json'

ssh root@<droplet-ip>

apt update && apt upgrade -y

curl -fsSL https://get.docker.com | sh
apt install -y docker-compose-plugin

mkdir -p /opt/restaurant-analytics
cd /opt/restaurant-analytics

cat > docker-compose.yml << 'EOF'
version: '3.8'
services:
  postgres:
    image: postgres:16
    container_name: restaurant_db
    restart: always
    environment:
      POSTGRES_DB: restaurant_analytics
      POSTGRES_USER: analytics_admin
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init-scripts:/docker-entrypoint-initdb.d
    ports:
      - '127.0.0.1:5432:5432'
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U analytics_admin -d restaurant_analytics']
      interval: 10s
      timeout: 5s
      retries: 5

  metabase:
    image: metabase/metabase:latest
    container_name: metabase
    restart: always
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase_appdb
      MB_DB_PORT: 5432
      MB_DB_USER: analytics_admin
      MB_DB_PASS: ${POSTGRES_PASSWORD}
      MB_DB_HOST: postgres
      MB_SITE_URL: https://analytics.${MSP_DOMAIN}
      JAVA_TOOL_OPTIONS: '-Xmx4g'
    ports:
      - '127.0.0.1:3000:3000'
    depends_on:
      postgres:
        condition: service_healthy

  n8n:
    image: n8nio/n8n:latest
    container_name: n8n
    restart: always
    environment:
      N8N_BASIC_AUTH_ACTIVE: 'true'
      N8N_BASIC_AUTH_USER: ${N8N_USER}
      N8N_BASIC_AUTH_PASSWORD: ${N8N_PASSWORD}
      WEBHOOK_URL: https://automation.${MSP_DOMAIN}
    volumes:
      - n8n_data:/home/node/.n8n
    ports:
      - '127.0.0.1:5678:5678'

  caddy:
    image: caddy:latest
    container_name: caddy
    restart: always
    ports:
      - '80:80'
      - '443:443'
    volumes:
      - ./Caddyfile:/etc/caddy/Caddyfile
      - caddy_data:/data
      - caddy_config:/config

volumes:
  postgres_data:
  n8n_data:
  caddy_data:
  caddy_config:
EOF

cat > .env << 'EOF'
POSTGRES_PASSWORD=<generate-strong-password-here>
N8N_USER=msp_admin
N8N_PASSWORD=<generate-strong-password-here>
MSP_DOMAIN=yourmsp.com
EOF

cat > Caddyfile << 'EOF'
analytics.{$MSP_DOMAIN} {
    reverse_proxy metabase:3000
    header {
        Strict-Transport-Security max-age=31536000
        X-Content-Type-Options nosniff
        X-Frame-Options SAMEORIGIN
    }
}

automation.{$MSP_DOMAIN} {
    reverse_proxy n8n:5678
}
EOF

docker compose up -d

docker compose ps

docker exec -it restaurant_db psql -U analytics_admin -d restaurant_analytics

CREATE SCHEMA IF NOT EXISTS restaurant;

-- Dimension: Restaurants (multi-tenant support)
CREATE TABLE restaurant.dim_restaurant (
    restaurant_id SERIAL PRIMARY KEY,
    tenant_name VARCHAR(255) NOT NULL,
    pos_type VARCHAR(50) NOT NULL, -- 'toast', 'square', 'lightspeed'
    timezone VARCHAR(50) DEFAULT 'America/New_York',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Dimension: Dayparts
CREATE TABLE restaurant.dim_daypart (
    daypart_id SERIAL PRIMARY KEY,
    restaurant_id INT REFERENCES restaurant.dim_restaurant(restaurant_id),
    daypart_name VARCHAR(50) NOT NULL, -- 'Breakfast', 'Lunch', 'Dinner', 'Late Night'
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

-- Dimension: Menu Items
CREATE TABLE restaurant.dim_menu_item (
    menu_item_id SERIAL PRIMARY KEY,
    restaurant_id INT REFERENCES restaurant.dim_restaurant(restaurant_id),
    pos_item_id VARCHAR(255), -- external ID from POS system
    item_name VARCHAR(255) NOT NULL,
    category VARCHAR(100), -- 'Appetizer', 'Entree', 'Beverage', 'Dessert'
    subcategory VARCHAR(100),
    current_price DECIMAL(10,2),
    food_cost DECIMAL(10,2), -- from MarginEdge/recipe costing
    food_cost_pct DECIMAL(5,2), -- food_cost / current_price * 100
    is_active BOOLEAN DEFAULT TRUE,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Dimension: Servers/Employees
CREATE TABLE restaurant.dim_server (
    server_id SERIAL PRIMARY KEY,
    restaurant_id INT REFERENCES restaurant.dim_restaurant(restaurant_id),
    pos_employee_id VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role VARCHAR(50), -- 'Server', 'Bartender', 'Host'
    is_active BOOLEAN DEFAULT TRUE,
    hire_date DATE
);

-- Dimension: Date
CREATE TABLE restaurant.dim_date (
    date_id DATE PRIMARY KEY,
    day_of_week INT, -- 0=Monday, 6=Sunday
    day_name VARCHAR(10),
    week_number INT,
    month_number INT,
    month_name VARCHAR(20),
    quarter INT,
    year INT,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN DEFAULT FALSE
);

-- Fact: Order Line Items (the core transaction table)
CREATE TABLE restaurant.fact_order_item (
    order_item_id BIGSERIAL PRIMARY KEY,
    restaurant_id INT REFERENCES restaurant.dim_restaurant(restaurant_id),
    order_id VARCHAR(255) NOT NULL, -- POS order ID
    order_date DATE REFERENCES restaurant.dim_date(date_id),
    order_timestamp TIMESTAMP NOT NULL,
    daypart_id INT REFERENCES restaurant.dim_daypart(daypart_id),
    menu_item_id INT REFERENCES restaurant.dim_menu_item(menu_item_id),
    server_id INT REFERENCES restaurant.dim_server(server_id),
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2),
    gross_amount DECIMAL(10,2), -- quantity * unit_price
    discount_amount DECIMAL(10,2) DEFAULT 0,
    net_amount DECIMAL(10,2), -- gross - discount
    void_flag BOOLEAN DEFAULT FALSE,
    comp_flag BOOLEAN DEFAULT FALSE,
    order_type VARCHAR(20) DEFAULT 'dine-in' -- 'dine-in', 'takeout', 'delivery'
);

-- Fact: Labor/Shifts
CREATE TABLE restaurant.fact_labor (
    shift_id BIGSERIAL PRIMARY KEY,
    restaurant_id INT REFERENCES restaurant.dim_restaurant(restaurant_id),
    server_id INT REFERENCES restaurant.dim_server(server_id),
    shift_date DATE REFERENCES restaurant.dim_date(date_id),
    clock_in TIMESTAMP,
    clock_out TIMESTAMP,
    hours_worked DECIMAL(5,2),
    hourly_rate DECIMAL(8,2),
    labor_cost DECIMAL(10,2),
    role_during_shift VARCHAR(50)
);

-- Populate dim_date for next 3 years
INSERT INTO restaurant.dim_date (date_id, day_of_week, day_name, week_number, month_number, month_name, quarter, year, is_weekend)
SELECT d::date,
       EXTRACT(DOW FROM d)::INT,
       TO_CHAR(d, 'Day'),
       EXTRACT(WEEK FROM d)::INT,
       EXTRACT(MONTH FROM d)::INT,
       TO_CHAR(d, 'Month'),
       EXTRACT(QUARTER FROM d)::INT,
       EXTRACT(YEAR FROM d)::INT,
       EXTRACT(DOW FROM d) IN (0, 6)
FROM generate_series('2023-01-01'::date, '2027-12-31'::date, '1 day') AS d;

-- Create indexes for common query patterns
CREATE INDEX idx_fact_order_date ON restaurant.fact_order_item(order_date);
CREATE INDEX idx_fact_order_daypart ON restaurant.fact_order_item(daypart_id);
CREATE INDEX idx_fact_order_item ON restaurant.fact_order_item(menu_item_id);
CREATE INDEX idx_fact_order_server ON restaurant.fact_order_item(server_id);
CREATE INDEX idx_fact_order_restaurant ON restaurant.fact_order_item(restaurant_id);
CREATE INDEX idx_fact_labor_date ON restaurant.fact_labor(shift_date);
CREATE INDEX idx_fact_labor_server ON restaurant.fact_labor(server_id);

-- Create materialized views for common dashboard queries

-- Daily sales summary by daypart
CREATE MATERIALIZED VIEW restaurant.mv_daily_daypart_summary AS
SELECT 
    foi.restaurant_id,
    foi.order_date,
    dp.daypart_name,
    COUNT(DISTINCT foi.order_id) AS order_count,
    SUM(foi.net_amount) AS total_sales,
    AVG(foi.net_amount) AS avg_item_value,
    SUM(foi.quantity) AS items_sold
FROM restaurant.fact_order_item foi
JOIN restaurant.dim_daypart dp ON foi.daypart_id = dp.daypart_id
WHERE foi.void_flag = FALSE
GROUP BY foi.restaurant_id, foi.order_date, dp.daypart_name;

-- Menu item performance (for menu engineering matrix)
CREATE MATERIALIZED VIEW restaurant.mv_menu_item_performance AS
SELECT 
    foi.restaurant_id,
    mi.item_name,
    mi.category,
    mi.current_price,
    mi.food_cost,
    mi.food_cost_pct,
    mi.current_price - COALESCE(mi.food_cost, 0) AS contribution_margin,
    SUM(foi.quantity) AS total_qty_sold,
    SUM(foi.net_amount) AS total_revenue,
    AVG(foi.quantity) AS avg_daily_qty,
    COUNT(DISTINCT foi.order_date) AS days_on_menu
FROM restaurant.fact_order_item foi
JOIN restaurant.dim_menu_item mi ON foi.menu_item_id = mi.menu_item_id
WHERE foi.void_flag = FALSE
GROUP BY foi.restaurant_id, mi.item_name, mi.category, mi.current_price, mi.food_cost, mi.food_cost_pct;

-- Server performance scoreboard
CREATE MATERIALIZED VIEW restaurant.mv_server_performance AS
SELECT 
    foi.restaurant_id,
    s.first_name || ' ' || LEFT(s.last_name, 1) || '.' AS server_name,
    foi.order_date,
    COUNT(DISTINCT foi.order_id) AS orders_served,
    SUM(foi.net_amount) AS total_sales,
    SUM(foi.net_amount) / NULLIF(COUNT(DISTINCT foi.order_id), 0) AS avg_check_size,
    SUM(foi.quantity)::DECIMAL / NULLIF(COUNT(DISTINCT foi.order_id), 0) AS avg_items_per_order
FROM restaurant.fact_order_item foi
JOIN restaurant.dim_server s ON foi.server_id = s.server_id
WHERE foi.void_flag = FALSE
GROUP BY foi.restaurant_id, server_name, foi.order_date;

-- Grant Metabase read-only access
CREATE ROLE metabase_reader WITH LOGIN PASSWORD '<generate-password>';
GRANT USAGE ON SCHEMA restaurant TO metabase_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA restaurant TO metabase_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA restaurant GRANT SELECT ON TABLES TO metabase_reader;

// maps POS API response to warehouse schema

const orders = $input.all();
const orderItems = [];
for (const order of orders) {
  for (const item of order.json.checks[0].selections) {
    orderItems.push({
      json: {
        order_id: order.json.guid,
        order_timestamp: order.json.openedDate,
        order_date: order.json.openedDate.substring(0, 10),
        pos_item_id: item.item.guid,
        item_name: item.displayName,
        quantity: item.quantity,
        unit_price: item.price / 100,
        gross_amount: (item.price * item.quantity) / 100,
        discount_amount: (item.appliedDiscounts?.reduce((s,d) => s + d.discountAmount, 0) || 0) / 100,
        pos_employee_id: order.json.server?.guid,
        void_flag: item.voided || false,
        order_type: order.json.diningOption?.name || 'dine-in'
      }
    });
  }
}
return orderItems;

-- Materialized view refresh queries executed after upsert

REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant.mv_daily_daypart_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant.mv_menu_item_performance;
REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant.mv_server_performance;

SELECT daypart_name, total_sales
FROM restaurant.mv_daily_daypart_summary
WHERE restaurant_id = {{restaurant_id}}
  AND order_date = CURRENT_DATE - INTERVAL '1 day'
ORDER BY CASE daypart_name
  WHEN 'Breakfast' THEN 1
  WHEN 'Lunch' THEN 2
  WHEN 'Dinner' THEN 3
  WHEN 'Late Night' THEN 4 END;

SELECT item_name, category, total_qty_sold, contribution_margin,
  CASE
    WHEN total_qty_sold >= (SELECT AVG(total_qty_sold) FROM restaurant.mv_menu_item_performance WHERE restaurant_id = {{restaurant_id}})
     AND contribution_margin >= (SELECT AVG(contribution_margin) FROM restaurant.mv_menu_item_performance WHERE restaurant_id = {{restaurant_id}})
    THEN 'Star'
    WHEN total_qty_sold < (SELECT AVG(total_qty_sold) FROM restaurant.mv_menu_item_performance WHERE restaurant_id = {{restaurant_id}})
     AND contribution_margin >= (SELECT AVG(contribution_margin) FROM restaurant.mv_menu_item_performance WHERE restaurant_id = {{restaurant_id}})
    THEN 'Puzzle'
    WHEN total_qty_sold >= (SELECT AVG(total_qty_sold) FROM restaurant.mv_menu_item_performance WHERE restaurant_id = {{restaurant_id}})
     AND contribution_margin < (SELECT AVG(contribution_margin) FROM restaurant.mv_menu_item_performance WHERE restaurant_id = {{restaurant_id}})
    THEN 'Plow Horse'
    ELSE 'Dog'
  END AS menu_class
FROM restaurant.mv_menu_item_performance
WHERE restaurant_id = {{restaurant_id}};

ufw default deny incoming
ufw default allow outgoing
ufw allow from <msp-office-ip>/32 to any port 22 proto tcp comment 'SSH from MSP'
ufw allow 80/tcp comment 'HTTP for Caddy ACME'
ufw allow 443/tcp comment 'HTTPS'
ufw enable

sed -i 's/^#PasswordAuthentication yes/PasswordAuthentication no/' /etc/ssh/sshd_config
sed -i 's/^PasswordAuthentication yes/PasswordAuthentication no/' /etc/ssh/sshd_config
systemctl restart sshd

apt install -y fail2ban
cat > /etc/fail2ban/jail.local << 'EOF'
[sshd]
enabled = true
port = ssh
filter = sshd
logpath = /var/log/auth.log
maxretry = 3
bantime = 3600
EOF
systemctl enable fail2ban && systemctl start fail2ban

apt install -y unattended-upgrades
dpkg-reconfigure -plow unattended-upgrades

SELECT column_name, table_name FROM information_schema.columns
WHERE table_schema = 'restaurant'
AND (column_name ILIKE '%card%' OR column_name ILIKE '%pan%' OR column_name ILIKE '%credit%');
-- This should return zero rows

cat > /opt/restaurant-analytics/backup.sh << 'SCRIPT'
#!/bin/bash
BACKUP_DIR=/opt/restaurant-analytics/backups
mkdir -p $BACKUP_DIR
docker exec restaurant_db pg_dump -U analytics_admin restaurant_analytics | gzip > $BACKUP_DIR/backup_$(date +%Y%m%d).sql.gz
# Keep last 30 days
find $BACKUP_DIR -name 'backup_*.sql.gz' -mtime +30 -delete
# Upload to offsite storage (configure rclone or s3cmd)
# rclone copy $BACKUP_DIR/backup_$(date +%Y%m%d).sql.gz remote:backups/
SCRIPT
chmod +x /opt/restaurant-analytics/backup.sh

(crontab -l 2>/dev/null; echo '0 4 * * * /opt/restaurant-analytics/backup.sh') | crontab -

scp historical_orders.csv root@<droplet-ip>:/opt/restaurant-analytics/

docker exec -i restaurant_db psql -U analytics_admin -d restaurant_analytics << 'SQL'
CREATE TEMP TABLE staging_orders (
    order_id VARCHAR(255),
    order_timestamp TIMESTAMP,
    item_name VARCHAR(255),
    quantity INT,
    unit_price DECIMAL(10,2),
    gross_amount DECIMAL(10,2),
    discount_amount DECIMAL(10,2),
    server_name VARCHAR(255),
    void_flag BOOLEAN,
    order_type VARCHAR(20)
);

COPY staging_orders FROM '/opt/restaurant-analytics/historical_orders.csv'
WITH (FORMAT csv, HEADER true);

-- Transform and insert into fact table (with dimension lookups)
INSERT INTO restaurant.fact_order_item (
    restaurant_id, order_id, order_date, order_timestamp,
    daypart_id, menu_item_id, server_id,
    quantity, unit_price, gross_amount, discount_amount, net_amount,
    void_flag, order_type
)
SELECT 
    1, -- restaurant_id
    s.order_id,
    s.order_timestamp::date,
    s.order_timestamp,
    dp.daypart_id,
    mi.menu_item_id,
    sv.server_id,
    s.quantity,
    s.unit_price,
    s.gross_amount,
    s.discount_amount,
    s.gross_amount - s.discount_amount,
    s.void_flag,
    s.order_type
FROM staging_orders s
LEFT JOIN restaurant.dim_daypart dp
    ON dp.restaurant_id = 1
    AND s.order_timestamp::time BETWEEN dp.start_time AND dp.end_time
LEFT JOIN restaurant.dim_menu_item mi
    ON mi.restaurant_id = 1 AND mi.item_name = s.item_name
LEFT JOIN restaurant.dim_server sv
    ON sv.restaurant_id = 1
    AND sv.first_name || ' ' || sv.last_name = s.server_name;
SQL

docker exec -i restaurant_db psql -U analytics_admin -d restaurant_analytics << 'SQL'
REFRESH MATERIALIZED VIEW restaurant.mv_daily_daypart_summary;
REFRESH MATERIALIZED VIEW restaurant.mv_menu_item_performance;
REFRESH MATERIALIZED VIEW restaurant.mv_server_performance;
SQL

docker exec -i restaurant_db psql -U analytics_admin -d restaurant_analytics << 'SQL'
-- Compare monthly totals with POS reports
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(net_amount) AS total_revenue
FROM restaurant.fact_order_item
WHERE restaurant_id = 1 AND void_flag = FALSE
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
SQL

const DAYPARTS = [
  { name: 'Breakfast', id: 1, start: '06:00', end: '10:59' },
  { name: 'Lunch', id: 2, start: '11:00', end: '14:59' },
  { name: 'Dinner', id: 3, start: '15:00', end: '21:59' },
  { name: 'Late Night', id: 4, start: '22:00', end: '05:59' }
];

function timeToMinutes(timeStr) {
  const [hours, minutes] = timeStr.split(':').map(Number);
  return hours * 60 + minutes;
}

function classifyDaypart(timestamp) {
  const date = new Date(timestamp);
  const hours = date.getHours().toString().padStart(2, '0');
  const minutes = date.getMinutes().toString().padStart(2, '0');
  const timeStr = `${hours}:${minutes}`;
  const timeMinutes = timeToMinutes(timeStr);
  
  for (const dp of DAYPARTS) {
    const startMin = timeToMinutes(dp.start);
    const endMin = timeToMinutes(dp.end);
    
    // Handle overnight daypart (Late Night wraps past midnight)
    if (startMin > endMin) {
      if (timeMinutes >= startMin || timeMinutes <= endMin) {
        return { daypart_id: dp.id, daypart_name: dp.name };
      }
    } else {
      if (timeMinutes >= startMin && timeMinutes <= endMin) {
        return { daypart_id: dp.id, daypart_name: dp.name };
      }
    }
  }
  
  // Default to dinner if no match
  return { daypart_id: 3, daypart_name: 'Dinner' };
}

const items = $input.all();
const results = items.map(item => {
  const dp = classifyDaypart(item.json.order_timestamp);
  return {
    json: {
      ...item.json,
      daypart_id: dp.daypart_id,
      daypart_name: dp.daypart_name
    }
  };
});

return results;

-- Metabase SQL Native Question: Menu Engineering Matrix
-- Parameters: {{restaurant_id}}, {{start_date}}, {{end_date}}
-- Save as: 'Menu Engineering Matrix - [Client Name]'

WITH item_metrics AS (
    SELECT 
        mi.menu_item_id,
        mi.item_name,
        mi.category,
        mi.current_price,
        COALESCE(mi.food_cost, 0) AS food_cost,
        mi.current_price - COALESCE(mi.food_cost, 0) AS contribution_margin,
        COALESCE(mi.food_cost_pct, 0) AS food_cost_pct,
        SUM(foi.quantity) AS total_qty_sold,
        SUM(foi.net_amount) AS total_revenue,
        COUNT(DISTINCT foi.order_date) AS days_sold,
        SUM(foi.quantity)::DECIMAL / NULLIF(COUNT(DISTINCT foi.order_date), 0) AS avg_daily_qty
    FROM restaurant.fact_order_item foi
    JOIN restaurant.dim_menu_item mi ON foi.menu_item_id = mi.menu_item_id
    WHERE foi.restaurant_id = {{restaurant_id}}
      AND foi.order_date BETWEEN {{start_date}} AND {{end_date}}
      AND foi.void_flag = FALSE
      AND mi.is_active = TRUE
    GROUP BY mi.menu_item_id, mi.item_name, mi.category, mi.current_price, mi.food_cost, mi.food_cost_pct
),
averages AS (
    SELECT 
        AVG(total_qty_sold) AS avg_popularity,
        AVG(contribution_margin) AS avg_margin
    FROM item_metrics
),
classified AS (
    SELECT 
        im.*,
        a.avg_popularity,
        a.avg_margin,
        CASE
            WHEN im.total_qty_sold >= a.avg_popularity AND im.contribution_margin >= a.avg_margin THEN 'Star'
            WHEN im.total_qty_sold >= a.avg_popularity AND im.contribution_margin < a.avg_margin THEN 'Plow Horse'
            WHEN im.total_qty_sold < a.avg_popularity AND im.contribution_margin >= a.avg_margin THEN 'Puzzle'
            WHEN im.total_qty_sold < a.avg_popularity AND im.contribution_margin < a.avg_margin THEN 'Dog'
        END AS menu_class,
        CASE
            WHEN im.total_qty_sold >= a.avg_popularity AND im.contribution_margin >= a.avg_margin 
                THEN 'Maintain visibility. Feature prominently. Do not discount.'
            WHEN im.total_qty_sold >= a.avg_popularity AND im.contribution_margin < a.avg_margin 
                THEN 'Increase price cautiously. Reduce portion cost. Pair with high-margin sides.'
            WHEN im.total_qty_sold < a.avg_popularity AND im.contribution_margin >= a.avg_margin 
                THEN 'Increase visibility. Add to specials. Rename or reposition on menu.'
            WHEN im.total_qty_sold < a.avg_popularity AND im.contribution_margin < a.avg_margin 
                THEN 'Consider removing. Replace with new item. If kept, raise price significantly.'
        END AS recommended_action
    FROM item_metrics im
    CROSS JOIN averages a
)
SELECT 
    item_name,
    category,
    current_price,
    food_cost,
    contribution_margin,
    food_cost_pct,
    total_qty_sold,
    total_revenue,
    avg_daily_qty,
    menu_class,
    recommended_action,
    avg_popularity AS benchmark_popularity,
    avg_margin AS benchmark_margin
FROM classified
ORDER BY 
    CASE menu_class
        WHEN 'Star' THEN 1
        WHEN 'Puzzle' THEN 2
        WHEN 'Plow Horse' THEN 3
        WHEN 'Dog' THEN 4
    END,
    total_revenue DESC;

-- Metabase SQL Native Question: Server Performance Composite Score
-- Parameters: {{restaurant_id}}, {{start_date}}, {{end_date}}
-- Save as: 'Server Scorecard - [Client Name]'

WITH server_raw AS (
    SELECT 
        s.server_id,
        s.first_name || ' ' || LEFT(s.last_name, 1) || '.' AS server_display_name,
        s.role,
        COUNT(DISTINCT foi.order_id) AS total_orders,
        SUM(foi.net_amount) AS total_revenue,
        SUM(foi.net_amount) / NULLIF(COUNT(DISTINCT foi.order_id), 0) AS avg_check_size,
        SUM(foi.quantity)::DECIMAL / NULLIF(COUNT(DISTINCT foi.order_id), 0) AS avg_items_per_order,
        SUM(CASE WHEN foi.void_flag THEN 1 ELSE 0 END)::DECIMAL / NULLIF(COUNT(*), 0) * 100 AS void_pct,
        SUM(CASE WHEN foi.comp_flag THEN 1 ELSE 0 END)::DECIMAL / NULLIF(COUNT(*), 0) * 100 AS comp_pct,
        STDDEV_POP(foi.net_amount) AS revenue_stddev,
        COUNT(DISTINCT foi.order_date) AS days_worked
    FROM restaurant.fact_order_item foi
    JOIN restaurant.dim_server s ON foi.server_id = s.server_id
    WHERE foi.restaurant_id = {{restaurant_id}}
      AND foi.order_date BETWEEN {{start_date}} AND {{end_date}}
      AND s.role IN ('Server', 'Bartender')
    GROUP BY s.server_id, s.first_name, s.last_name, s.role
    HAVING COUNT(DISTINCT foi.order_date) >= 3  -- minimum 3 shifts to be scored
),
normalized AS (
    SELECT *,
        -- Normalize each metric to 0-100 scale using min-max normalization
        CASE WHEN MAX(avg_check_size) OVER() - MIN(avg_check_size) OVER() > 0
            THEN (avg_check_size - MIN(avg_check_size) OVER()) / 
                 (MAX(avg_check_size) OVER() - MIN(avg_check_size) OVER()) * 100
            ELSE 50 END AS norm_check_size,
        
        CASE WHEN MAX(avg_items_per_order) OVER() - MIN(avg_items_per_order) OVER() > 0
            THEN (avg_items_per_order - MIN(avg_items_per_order) OVER()) / 
                 (MAX(avg_items_per_order) OVER() - MIN(avg_items_per_order) OVER()) * 100
            ELSE 50 END AS norm_upsell,
        
        CASE WHEN MAX(total_revenue) OVER() - MIN(total_revenue) OVER() > 0
            THEN (total_revenue - MIN(total_revenue) OVER()) / 
                 (MAX(total_revenue) OVER() - MIN(total_revenue) OVER()) * 100
            ELSE 50 END AS norm_revenue,
        
        -- Consistency: lower stddev is better, so invert
        CASE WHEN MAX(revenue_stddev) OVER() - MIN(revenue_stddev) OVER() > 0
            THEN 100 - ((revenue_stddev - MIN(revenue_stddev) OVER()) / 
                 (MAX(revenue_stddev) OVER() - MIN(revenue_stddev) OVER()) * 100)
            ELSE 50 END AS norm_consistency,
        
        -- Void/comp: lower is better, so invert
        CASE WHEN MAX(void_pct + comp_pct) OVER() - MIN(void_pct + comp_pct) OVER() > 0
            THEN 100 - ((void_pct + comp_pct - MIN(void_pct + comp_pct) OVER()) / 
                 (MAX(void_pct + comp_pct) OVER() - MIN(void_pct + comp_pct) OVER()) * 100)
            ELSE 50 END AS norm_void_comp
    FROM server_raw
),
scored AS (
    SELECT *,
        -- Weighted composite score (weights sum to 1.0)
        ROUND((
            norm_check_size * 0.35 +
            norm_upsell * 0.25 +
            norm_revenue * 0.20 +
            norm_consistency * 0.10 +
            norm_void_comp * 0.10
        )::DECIMAL, 1) AS composite_score
    FROM normalized
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY composite_score DESC) AS rank,
    server_display_name,
    role,
    days_worked,
    total_orders,
    ROUND(total_revenue::DECIMAL, 2) AS total_revenue,
    ROUND(avg_check_size::DECIMAL, 2) AS avg_check_size,
    ROUND(avg_items_per_order::DECIMAL, 1) AS avg_items_per_order,
    ROUND(void_pct::DECIMAL, 2) AS void_pct,
    ROUND(comp_pct::DECIMAL, 2) AS comp_pct,
    composite_score,
    CASE 
        WHEN composite_score >= 80 THEN 'Top Performer'
        WHEN composite_score >= 60 THEN 'Solid'
        WHEN composite_score >= 40 THEN 'Developing'
        ELSE 'Coaching Needed'
    END AS performance_tier
FROM scored
ORDER BY composite_score DESC;

-- Daypart anomaly detection query (yesterday vs 4-week same-day baseline)

WITH yesterday AS (
    SELECT 
        daypart_name,
        total_sales,
        order_count,
        items_sold
    FROM restaurant.mv_daily_daypart_summary
    WHERE restaurant_id = 1
      AND order_date = CURRENT_DATE - INTERVAL '1 day'
),
baseline AS (
    SELECT 
        daypart_name,
        AVG(total_sales) AS avg_sales,
        STDDEV_POP(total_sales) AS stddev_sales,
        AVG(order_count) AS avg_orders
    FROM restaurant.mv_daily_daypart_summary
    WHERE restaurant_id = 1
      AND order_date BETWEEN CURRENT_DATE - INTERVAL '29 days' AND CURRENT_DATE - INTERVAL '2 days'
      AND EXTRACT(DOW FROM order_date) = EXTRACT(DOW FROM CURRENT_DATE - INTERVAL '1 day')
    GROUP BY daypart_name
)
SELECT 
    y.daypart_name,
    y.total_sales AS yesterday_sales,
    ROUND(b.avg_sales::DECIMAL, 2) AS baseline_avg,
    ROUND(((y.total_sales - b.avg_sales) / NULLIF(b.avg_sales, 0) * 100)::DECIMAL, 1) AS pct_deviation,
    CASE 
        WHEN ABS((y.total_sales - b.avg_sales) / NULLIF(b.avg_sales, 0)) > 0.20 THEN 'ALERT'
        ELSE 'NORMAL'
    END AS status
FROM yesterday y
JOIN baseline b ON y.daypart_name = b.daypart_name;

<h2>Daily Sales Anomaly Report</h2>
<p>The following dayparts showed unusual sales activity yesterday 
compared to the same day-of-week average over the past 4 weeks:</p>
<table border='1' cellpadding='8' style='border-collapse:collapse'>
  <tr style='background:#f0f0f0'>
    <th>Daypart</th><th>Yesterday</th><th>4-Week Avg</th><th>Deviation</th>
  </tr>
  {{#each $json}}
  <tr style='{{#if (eq status "ALERT")}}background:#fff3cd{{/if}}'>
    <td>{{daypart_name}}</td>
    <td>${{yesterday_sales}}</td>
    <td>${{baseline_avg}}</td>
    <td>{{pct_deviation}}%</td>
  </tr>
  {{/each}}
</table>
<p style='color:#666; font-size:12px'>This alert triggers when any daypart 
deviates ±20% from the trailing 4-week same-day average. Review the full 
dashboard at <a href='https://analytics.yourmsp.com'>analytics.yourmsp.com</a>.</p>

WITH yesterday_items AS (
    SELECT 
        mi.item_name,
        SUM(foi.quantity) AS qty_yesterday
    FROM restaurant.fact_order_item foi
    JOIN restaurant.dim_menu_item mi ON foi.menu_item_id = mi.menu_item_id
    WHERE foi.restaurant_id = 1
      AND foi.order_date = CURRENT_DATE - INTERVAL '1 day'
      AND foi.void_flag = FALSE
    GROUP BY mi.item_name
),
baseline_items AS (
    SELECT 
        mi.item_name,
        AVG(daily_qty) AS avg_daily_qty
    FROM (
        SELECT mi.item_name, foi.order_date, SUM(foi.quantity) AS daily_qty
        FROM restaurant.fact_order_item foi
        JOIN restaurant.dim_menu_item mi ON foi.menu_item_id = mi.menu_item_id
        WHERE foi.restaurant_id = 1
          AND foi.order_date BETWEEN CURRENT_DATE - INTERVAL '29 days' AND CURRENT_DATE - INTERVAL '2 days'
          AND foi.void_flag = FALSE
        GROUP BY mi.item_name, foi.order_date
    ) sub
    GROUP BY mi.item_name
    HAVING COUNT(*) >= 5
)
SELECT 
    y.item_name,
    y.qty_yesterday,
    ROUND(b.avg_daily_qty::DECIMAL, 1) AS avg_daily_qty,
    ROUND(((y.qty_yesterday - b.avg_daily_qty) / NULLIF(b.avg_daily_qty, 0) * 100)::DECIMAL, 1) AS pct_change
FROM yesterday_items y
JOIN baseline_items b ON y.item_name = b.item_name
WHERE ABS((y.qty_yesterday - b.avg_daily_qty) / NULLIF(b.avg_daily_qty, 0)) > 0.30
ORDER BY ABS(pct_change) DESC
LIMIT 10;

-- Visualization: Pivot Table or Heatmap (rows=hour, columns=day_of_week).
-- Parameters: {{restaurant_id}}, {{start_date}}, {{end_date}}. Save as:
-- 'Hourly Sales Heatmap - [Client Name]'

WITH hourly_sales AS (
    SELECT 
        EXTRACT(DOW FROM foi.order_timestamp)::INT AS day_of_week_num,
        CASE EXTRACT(DOW FROM foi.order_timestamp)::INT
            WHEN 0 THEN 'Sunday'
            WHEN 1 THEN 'Monday'
            WHEN 2 THEN 'Tuesday'
            WHEN 3 THEN 'Wednesday'
            WHEN 4 THEN 'Thursday'
            WHEN 5 THEN 'Friday'
            WHEN 6 THEN 'Saturday'
        END AS day_of_week,
        EXTRACT(HOUR FROM foi.order_timestamp)::INT AS hour_of_day,
        SUM(foi.net_amount) AS total_sales,
        COUNT(DISTINCT foi.order_id) AS order_count,
        COUNT(DISTINCT foi.order_date) AS num_days_in_range
    FROM restaurant.fact_order_item foi
    WHERE foi.restaurant_id = {{restaurant_id}}
      AND foi.order_date BETWEEN {{start_date}} AND {{end_date}}
      AND foi.void_flag = FALSE
    GROUP BY 1, 2, 3
),
avg_hourly AS (
    SELECT 
        day_of_week_num,
        day_of_week,
        hour_of_day,
        ROUND((total_sales / NULLIF(num_days_in_range, 0))::DECIMAL, 2) AS avg_hourly_sales,
        ROUND((order_count::DECIMAL / NULLIF(num_days_in_range, 0))::DECIMAL, 1) AS avg_hourly_orders,
        total_sales,
        order_count
    FROM hourly_sales
)
SELECT 
    -- Format hour for display
    CASE 
        WHEN hour_of_day = 0 THEN '12 AM'
        WHEN hour_of_day < 12 THEN hour_of_day || ' AM'
        WHEN hour_of_day = 12 THEN '12 PM'
        ELSE (hour_of_day - 12) || ' PM'
    END AS hour_label,
    hour_of_day,
    day_of_week,
    day_of_week_num,
    avg_hourly_sales,
    avg_hourly_orders,
    -- Staffing recommendation based on relative volume
    CASE 
        WHEN avg_hourly_sales >= (SELECT PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY avg_hourly_sales) FROM avg_hourly)
            THEN 'Peak - Full Staff'
        WHEN avg_hourly_sales >= (SELECT PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY avg_hourly_sales) FROM avg_hourly)
            THEN 'Moderate - Standard Staff'
        WHEN avg_hourly_sales >= (SELECT PERCENTILE_CONT(0.20) WITHIN GROUP (ORDER BY avg_hourly_sales) FROM avg_hourly)
            THEN 'Low - Reduced Staff'
        ELSE 'Minimal - Skeleton Crew'
    END AS staffing_recommendation
FROM avg_hourly
WHERE avg_hourly_sales > 0
ORDER BY hour_of_day, day_of_week_num;

WITH this_week AS (
    SELECT 
        SUM(net_amount) AS total_revenue,
        COUNT(DISTINCT order_id) AS total_orders,
        SUM(net_amount) / NULLIF(COUNT(DISTINCT order_id), 0) AS avg_check
    FROM restaurant.fact_order_item
    WHERE restaurant_id = 1
      AND order_date BETWEEN CURRENT_DATE - INTERVAL '8 days' AND CURRENT_DATE - INTERVAL '1 day'
      AND void_flag = FALSE
),
last_week AS (
    SELECT 
        SUM(net_amount) AS total_revenue,
        COUNT(DISTINCT order_id) AS total_orders,
        SUM(net_amount) / NULLIF(COUNT(DISTINCT order_id), 0) AS avg_check
    FROM restaurant.fact_order_item
    WHERE restaurant_id = 1
      AND order_date BETWEEN CURRENT_DATE - INTERVAL '15 days' AND CURRENT_DATE - INTERVAL '8 days'
      AND void_flag = FALSE
)
SELECT 
    tw.total_revenue AS this_week_revenue,
    lw.total_revenue AS last_week_revenue,
    ROUND(((tw.total_revenue - lw.total_revenue) / NULLIF(lw.total_revenue, 0) * 100)::DECIMAL, 1) AS revenue_change_pct,
    tw.total_orders AS this_week_orders,
    ROUND(tw.avg_check::DECIMAL, 2) AS this_week_avg_check,
    ROUND(lw.avg_check::DECIMAL, 2) AS last_week_avg_check
FROM this_week tw, last_week lw;

WITH this_week_items AS (
    SELECT mi.item_name, SUM(foi.quantity) AS qty
    FROM restaurant.fact_order_item foi
    JOIN restaurant.dim_menu_item mi ON foi.menu_item_id = mi.menu_item_id
    WHERE foi.restaurant_id = 1
      AND foi.order_date BETWEEN CURRENT_DATE - INTERVAL '8 days' AND CURRENT_DATE - INTERVAL '1 day'
      AND foi.void_flag = FALSE
    GROUP BY mi.item_name
),
last_week_items AS (
    SELECT mi.item_name, SUM(foi.quantity) AS qty
    FROM restaurant.fact_order_item foi
    JOIN restaurant.dim_menu_item mi ON foi.menu_item_id = mi.menu_item_id
    WHERE foi.restaurant_id = 1
      AND foi.order_date BETWEEN CURRENT_DATE - INTERVAL '15 days' AND CURRENT_DATE - INTERVAL '8 days'
      AND foi.void_flag = FALSE
    GROUP BY mi.item_name
)
SELECT 
    COALESCE(tw.item_name, lw.item_name) AS item_name,
    COALESCE(tw.qty, 0) AS this_week_qty,
    COALESCE(lw.qty, 0) AS last_week_qty,
    COALESCE(tw.qty, 0) - COALESCE(lw.qty, 0) AS qty_change,
    ROUND(((COALESCE(tw.qty, 0) - COALESCE(lw.qty, 0))::DECIMAL / NULLIF(lw.qty, 0) * 100)::DECIMAL, 1) AS pct_change
FROM this_week_items tw
FULL OUTER JOIN last_week_items lw ON tw.item_name = lw.item_name
ORDER BY qty_change DESC;

-- verify loaded row count against POS reported item count for the prior day

SELECT COUNT(*) FROM restaurant.fact_order_item WHERE order_date = CURRENT_DATE - INTERVAL '1 day';

# confirm PostgreSQL port is not exposed to the public internet (expected
# result: 'filtered' or 'closed')

nmap -p 5432 <droplet-ip>