schema_ddl_enriched.sql

-- TRAY POS Schema DDL v2 (dbt-Verified Column Names + Sample Values)
-- Source: INFORMATION_SCHEMA + dbt int_tray_items_clean, int_tray_checks_clean, fact_tray_check_day
-- CRITICAL: ITEMS has NO VOIDED column. Void detection: ACTION LIKE '%VOID%'
-- CRITICAL: Use CHECKID not CHECK_ID, EMPLOYEEID not EMPLOYEE_ID
-- CRITICAL: Use SITE_NAME not LOCATIONNAME, NAME not MENUITEMNAME/TITLE

CREATE TABLE FLORAOS.TRAY.TRAY_SITES (
  ID FLOAT, -- Primary key
  NAME VARCHAR, -- Site name (identical to SITE_NAME)
  SITE_NAME VARCHAR, -- example: ['IHOP #1425', 'IHOP #1427', 'IHOP #1409']
  SITE_ID FLOAT, -- Alternate site identifier
  STORETIMEZONE VARCHAR, -- example: ['America/Chicago', 'America/New_York']
  ADDRESS VARCHAR, -- Physical address
  STORENUMBER VARCHAR, -- Store number
  MAXREGULARDAYHRS FLOAT, -- Max regular hours per day before OT
  MAXREGULARWEEKHRS FLOAT, -- Max regular hours per week before OT
  OTRATEMULTIPLIER FLOAT, -- Overtime rate multiplier (e.g. 1.5)
  BUSINESSSTARTHOUR VARCHAR -- Business day start hour
  -- PRIMARY KEY: ID
);

CREATE TABLE FLORAOS.TRAY.TRAY_CHECKS (
  ID FLOAT, -- Primary key (referenced as CHECKID from other tables)
  SITE_ID FLOAT, -- FK → TRAY_SITES.ID
  OPENTIME VARCHAR, -- example: ['2026-04-13 11:23:45 UTC']
  CLOSETIME VARCHAR, -- example: ['2026-04-13 12:01:33 UTC', NULL (0.14%)]
  EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID (NOT EMPLOYEE_ID)
  VOIDED BOOLEAN, -- Check-level void flag (ITEMS has NO VOIDED column)
  STATUS VARCHAR, -- Check status
  POSTRAFFICCOUNT FLOAT, -- Guest/party count
  CLIENTPLATFORM VARCHAR, -- e.g. 'thirdparty' for delivery orders
  REVENUECENTERID FLOAT -- FK → revenue centers
  -- PRIMARY KEY: ID
);

CREATE TABLE FLORAOS.TRAY.TRAY_ITEMS (
  ID FLOAT, -- Primary key (action row ID)
  CHECKID FLOAT, -- FK → TRAY_CHECKS.ID (NOT CHECK_ID)
  SITE_ID FLOAT, -- FK → TRAY_SITES.ID
  ACTION VARCHAR, -- example: ['SALE', 'VOID', 'DISCOUNT_APPLIED', 'COMP_APPLIED', 'TRANSFERRED_TO']
  PRICE FLOAT, -- example: [8.99, 12.50, 3.25]
  TAX FLOAT, -- example: [0.72, 1.00, 0.26]
  QUANTITY FLOAT, -- Quantity ordered
  ISMODIFIER BOOLEAN, -- TRUE for modifiers (exclude from revenue)
  ITEMTYPE VARCHAR, -- example: ['', 'GIFTCARD', 'SERVICECHARGE']
  ITEMPLU VARCHAR, -- PLU code — links to TRAY_PRODUCTS.PLU
  ACTIONTIME VARCHAR, -- UTC text timestamp of the action
  EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID
  DISCOUNTID VARCHAR, -- Discount reference (NOT the FK to discount_types)
  COMBOPARENTID FLOAT -- Combo parent item ID (non-zero = combo component)
  -- PRIMARY KEY: ID
  -- WARNING: NO VOIDED column. Void detection: ACTION LIKE '%VOID%' or ACTION IN ('VOID','VOIDED','ITEM_VOID')
);

CREATE TABLE FLORAOS.TRAY.TRAY_PAYMENTS (
  ID FLOAT, -- Primary key
  CHECKID FLOAT, -- FK → TRAY_CHECKS.ID (NOT CHECK_ID)
  SITE_ID FLOAT, -- FK → TRAY_SITES.ID
  TENDERTYPE VARCHAR, -- example: ['Cash', 'Visa', 'Mastercard', 'DoorDash']
  TIPAMOUNT FLOAT, -- example: [0.00, 3.50, 5.00]
  AMOUNT FLOAT, -- Payment amount
  CARDTYPE VARCHAR, -- Card brand detail
  EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID
  ADDEDGRATUITY FLOAT -- Auto-gratuity amount
  -- PRIMARY KEY: ID
);

CREATE TABLE FLORAOS.TRAY.TRAY_EMPLOYEES (
  ID FLOAT, -- Primary key
  FIRSTNAME VARCHAR, -- First name
  LASTNAME VARCHAR, -- Last name
  SITE_ID FLOAT, -- FK → TRAY_SITES.ID
  STATUS VARCHAR, -- Active/Inactive
  HIREDATE VARCHAR, -- Hire date text
  ISTIPABLE BOOLEAN -- Eligible for tips
  -- PRIMARY KEY: ID
);

CREATE TABLE FLORAOS.TRAY.TRAY_PRODUCTS (
  ID FLOAT, -- Primary key
  NAME VARCHAR, -- Product name (NOT MENUITEMNAME)
  PLU VARCHAR, -- PLU code — links to TRAY_ITEMS.ITEMPLU
  CATEGORYNAME VARCHAR, -- example: ['Pancakes', 'Breakfast Combos', 'Beverages']
  SITE_ID FLOAT, -- FK → TRAY_SITES.ID
  TYPE VARCHAR, -- Product type
  STATUS VARCHAR -- Active/Inactive
  -- PRIMARY KEY: ID
);

CREATE TABLE FLORAOS.TRAY.TRAY_DISCOUNTS (
  ID FLOAT, -- Primary key
  CHECKID FLOAT, -- FK → TRAY_CHECKS.ID
  DISCOUNTEID FLOAT, -- FK → TRAY_DISCOUNT_TYPES.EID (NOT DISCOUNTID or DISCOUNTTYPEID)
  AMOUNT FLOAT, -- Discount dollar amount
  EMPLOYEEID FLOAT, -- Who applied the discount
  ITEMID FLOAT, -- Item the discount was applied to
  DATE VARCHAR -- Date text
  -- PRIMARY KEY: ID
);

CREATE TABLE FLORAOS.TRAY.TRAY_DISCOUNT_TYPES (
  EID FLOAT, -- Primary key (40x Airbyte duplication — MUST dedupe)
  ID FLOAT, -- Alternate ID
  NAME VARCHAR, -- example: ['Employee Meal', 'Manager Comp', 'Senior Discount']
  STATUS VARCHAR, -- Active/Inactive
  CHECKLEVEL BOOLEAN, -- Check-level vs item-level discount
  DISCOUNTMAINCATEGORY VARCHAR -- Main discount category
  -- PRIMARY KEY: EID
  -- WARNING: 40x Airbyte duplication. MUST dedupe: ROW_NUMBER() OVER (PARTITION BY EID ORDER BY _AIRBYTE_EXTRACTED_AT DESC)
);

CREATE TABLE FLORAOS.TRAY.TRAY_TIME_ENTRIES (
  ID FLOAT, -- Primary key
  EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID
  SITE_ID FLOAT, -- FK → TRAY_SITES.ID
  TIMEIN VARCHAR, -- Clock-in UTC text
  TIMEOUT VARCHAR, -- Clock-out UTC text
  BASERATE FLOAT, -- example: [10.00, 12.50, 15.00]
  ACTUALRATE FLOAT, -- Actual rate paid
  JOBID FLOAT, -- FK → TRAY_JOBS
  NETSALES FLOAT, -- Net sales during shift
  TIPS FLOAT -- Tips earned during shift
  -- PRIMARY KEY: ID
);

CREATE TABLE FLORAOS.TRAY.TRAY_JOBS (
  EID FLOAT, -- Primary key (17x Airbyte duplication — MUST dedupe)
  ID FLOAT, -- Alternate ID
  NAME VARCHAR, -- example: ['Server', 'Cook', 'Host', 'Manager']
  STATUS VARCHAR, -- Active/Inactive
  SITE_ID FLOAT -- FK → TRAY_SITES.ID
  -- PRIMARY KEY: EID
  -- WARNING: 17x Airbyte duplication. MUST dedupe: ROW_NUMBER() OVER (PARTITION BY EID ORDER BY _AIRBYTE_EXTRACTED_AT DESC)
);

-- FOREIGN KEY GRAPH (dbt-verified join paths):
-- TRAY_ITEMS.CHECKID → TRAY_CHECKS.ID
-- TRAY_CHECKS.SITE_ID → TRAY_SITES.ID
-- TRAY_CHECKS.EMPLOYEEID → TRAY_EMPLOYEES.ID
-- TRAY_ITEMS.ITEMPLU = TRAY_PRODUCTS.PLU (+ SITE_ID)
-- TRAY_PAYMENTS.CHECKID → TRAY_CHECKS.ID
-- TRAY_DISCOUNTS.CHECKID → TRAY_CHECKS.ID
-- TRAY_DISCOUNTS.DISCOUNTEID → TRAY_DISCOUNT_TYPES.EID
-- TRAY_TIME_ENTRIES.EMPLOYEEID → TRAY_EMPLOYEES.ID
-- TRAY_TIME_ENTRIES.SITE_ID → TRAY_SITES.ID
-- TRAY_JOBS.SITE_ID → TRAY_SITES.ID

-- REVENUE FORMULA (from dbt int_tray_check_day_item_metrics):
-- SUM(PRICE) WHERE ACTION='SALE' AND ISMODIFIER=FALSE
--   AND ITEMTYPE NOT IN ('GIFTCARD','SERVICECHARGE')
--   AND product not a donation (CATEGORYNAME not in ('donation','donations'))
-- Simplified: SUM(PRICE + TAX) WHERE ACTION='SALE' AND ISMODIFIER=FALSE

-- BUSINESS DATE (from dbt int_tray_checks_clean):
-- DATE(DATEADD('hour', -4, CONVERT_TIMEZONE('UTC',
--   COALESCE(NULLIF(s.STORETIMEZONE,''), 'America/Chicago'),
--   TRY_TO_TIMESTAMP(COALESCE(c.CLOSETIME, c.OPENTIME), 'YYYY-MM-DD HH24:MI:SS UTC'))))

-- ANTI-PATTERNS (NEVER DO):
-- NEVER join TRAY_ITEMS directly to TRAY_PAYMENTS on CHECKID (N×M fanout)
-- NEVER use PAYMENTTYPE (correct: TENDERTYPE)
-- NEVER use CHECK_ID on items/payments (correct: CHECKID)
-- NEVER use EMPLOYEE_ID (correct: EMPLOYEEID)
-- NEVER use LOCATIONNAME (correct: SITE_NAME)
-- NEVER use i.VOIDED (column doesn't exist on ITEMS)
-- NEVER use HOURLYRATE (correct: BASERATE)
-- NEVER use TITLE on jobs (correct: NAME)
-- NEVER use MENUITEMNAME on products (correct: NAME)