-- ============================================================
-- HeardCheck — Complete Database Schema
-- Run: mysql -u root -p < heardcheck_schema.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS heardcheck_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE heardcheck_db;

-- ============================================================
-- 1. RESTAURANTS
-- ============================================================

CREATE TABLE restaurants (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(255) NOT NULL,
  slug            VARCHAR(100) NOT NULL UNIQUE,
  address         TEXT,
  timezone        VARCHAR(50) NOT NULL DEFAULT 'America/Chicago',
  plan            ENUM('free','premium') NOT NULL DEFAULT 'free',
  restaurant_code VARCHAR(6) NOT NULL UNIQUE,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_restaurant_code (restaurant_code)
) ENGINE=InnoDB;

-- ============================================================
-- 2. RESTAURANT SETTINGS
-- ============================================================

CREATE TABLE restaurant_settings (
  restaurant_id   INT UNSIGNED PRIMARY KEY,
  kds_sla_yellow  INT NOT NULL DEFAULT 600,       -- seconds before yellow warning (10 min)
  kds_sla_red     INT NOT NULL DEFAULT 900,        -- seconds before red alert (15 min)
  timezone        VARCHAR(50) NOT NULL DEFAULT 'America/Chicago',
  currency        VARCHAR(3) NOT NULL DEFAULT 'USD',
  stations        JSON,                             -- e.g. ["grill","fry","saute","cold","expo","prep"]

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 3. USERS
-- ============================================================

CREATE TABLE users (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id         INT UNSIGNED NOT NULL,
  phone                 VARCHAR(20),
  name                  VARCHAR(255) NOT NULL,
  email                 VARCHAR(255) NOT NULL,
  role                  ENUM('owner','manager','lead','cook','server','host','view') NOT NULL DEFAULT 'cook',
  side                  ENUM('boh','foh','both') NOT NULL DEFAULT 'boh',
  locale                VARCHAR(5) NOT NULL DEFAULT 'en',
  email_verified        TINYINT(1) NOT NULL DEFAULT 0,
  email_verify_code     VARCHAR(6) DEFAULT NULL,
  email_verify_sent_at  DATETIME DEFAULT NULL,
  pin_hash              VARCHAR(255) NOT NULL,
  avatar_url            TEXT,
  status                ENUM('active','inactive','pending') NOT NULL DEFAULT 'pending',
  created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uk_restaurant_email (restaurant_id, email),
  UNIQUE KEY uk_restaurant_phone (restaurant_id, phone),
  INDEX idx_restaurant_role (restaurant_id, role),
  INDEX idx_restaurant_status (restaurant_id, status),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 4. USER SESSIONS
-- ============================================================

CREATE TABLE user_sessions (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  token         VARCHAR(512) NOT NULL UNIQUE,
  device_info   VARCHAR(255),
  device_token  TEXT,                               -- APNs or FCM push token
  platform      ENUM('ios','android') DEFAULT 'ios',
  expires_at    DATETIME NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_user_sessions_token (token),
  INDEX idx_user_sessions_user (user_id),
  INDEX idx_user_sessions_expires (expires_at),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 5. MENU ITEMS
-- ============================================================

CREATE TABLE menu_items (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  name            VARCHAR(255) NOT NULL,
  category        VARCHAR(100),
  default_station VARCHAR(50),                      -- grill, fry, saute, cold, expo, etc.
  prep_time_est   INT DEFAULT NULL,                 -- estimated prep time in seconds
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_menu_restaurant (restaurant_id, is_active),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 6. ORDERS (KDS)
-- ============================================================

CREATE TABLE orders (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  source          ENUM('dine_in','takeout','delivery','online') NOT NULL DEFAULT 'dine_in',
  table_number    VARCHAR(20),
  priority        ENUM('standard','rush') NOT NULL DEFAULT 'standard',
  status          ENUM('new','in_progress','done','recalled') NOT NULL DEFAULT 'new',
  pos_order_id    VARCHAR(100),                     -- external POS reference
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  bumped_at       DATETIME DEFAULT NULL,
  done_at         DATETIME DEFAULT NULL,

  INDEX idx_orders_restaurant_status (restaurant_id, status),
  INDEX idx_orders_created (restaurant_id, created_at),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 7. ORDER ITEMS
-- ============================================================

CREATE TABLE order_items (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id        INT UNSIGNED NOT NULL,
  menu_item_id    INT UNSIGNED DEFAULT NULL,
  station         VARCHAR(50) NOT NULL,             -- grill, fry, saute, cold, expo, etc.
  name            VARCHAR(255) NOT NULL,
  quantity        INT NOT NULL DEFAULT 1,
  modifications   TEXT,
  status          ENUM('pending','cooking','done') NOT NULL DEFAULT 'pending',
  assigned_to     INT UNSIGNED DEFAULT NULL,
  bumped_at       DATETIME DEFAULT NULL,

  INDEX idx_order_items_order (order_id),
  INDEX idx_order_items_station (station, status),

  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 8. PREP TEMPLATES
-- ============================================================

CREATE TABLE prep_templates (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  name            VARCHAR(255) NOT NULL,
  day_of_week     TINYINT DEFAULT NULL,             -- 0=Sun, 1=Mon, ... 6=Sat. NULL=any day
  station         VARCHAR(50),
  created_by      INT UNSIGNED NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_prep_templates_restaurant (restaurant_id),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 9. PREP TEMPLATE ITEMS
-- ============================================================

CREATE TABLE prep_template_items (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_id     INT UNSIGNED NOT NULL,
  item_name       VARCHAR(255) NOT NULL,
  par_quantity    DECIMAL(10,2) NOT NULL DEFAULT 0,
  unit            VARCHAR(50),                      -- lbs, oz, each, qt, etc.
  sort_order      INT NOT NULL DEFAULT 0,
  notes           TEXT,

  INDEX idx_template_items_template (template_id),

  FOREIGN KEY (template_id) REFERENCES prep_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 10. PREP LISTS (daily instances)
-- ============================================================

CREATE TABLE prep_lists (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  template_id     INT UNSIGNED DEFAULT NULL,
  date            DATE NOT NULL,
  station         VARCHAR(50),
  status          ENUM('open','complete') NOT NULL DEFAULT 'open',
  created_by      INT UNSIGNED NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_prep_lists_restaurant_date (restaurant_id, date),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (template_id) REFERENCES prep_templates(id) ON SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 11. PREP ITEMS (daily instance items)
-- ============================================================

CREATE TABLE prep_items (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  prep_list_id        INT UNSIGNED NOT NULL,
  item_name           VARCHAR(255) NOT NULL,
  par_quantity        DECIMAL(10,2) NOT NULL DEFAULT 0,
  completed_quantity  DECIMAL(10,2) NOT NULL DEFAULT 0,
  unit                VARCHAR(50),
  assigned_to         INT UNSIGNED DEFAULT NULL,
  photo_url           TEXT,
  notes               TEXT,
  completed_at        DATETIME DEFAULT NULL,
  sort_order          INT NOT NULL DEFAULT 0,

  INDEX idx_prep_items_list (prep_list_id),

  FOREIGN KEY (prep_list_id) REFERENCES prep_lists(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 12. INVENTORY ITEMS
-- ============================================================

CREATE TABLE inventory_items (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id     INT UNSIGNED NOT NULL,
  name              VARCHAR(255) NOT NULL,
  category          VARCHAR(100),
  unit              VARCHAR(50),                    -- lbs, oz, each, case, etc.
  cost_per_unit     DECIMAL(10,4) DEFAULT NULL,
  vendor            VARCHAR(255),
  barcode           VARCHAR(100),
  par_level         DECIMAL(10,2) DEFAULT NULL,
  current_quantity  DECIMAL(10,2) NOT NULL DEFAULT 0,
  last_counted_at   DATETIME DEFAULT NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_inventory_restaurant (restaurant_id),
  INDEX idx_inventory_barcode (barcode),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 13. INVENTORY COUNTS
-- ============================================================

CREATE TABLE inventory_counts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id         INT UNSIGNED NOT NULL,
  counted_by      INT UNSIGNED NOT NULL,
  previous_qty    DECIMAL(10,2) NOT NULL,
  new_qty         DECIMAL(10,2) NOT NULL,
  count_date      DATE NOT NULL,
  notes           TEXT,

  INDEX idx_counts_item (item_id),

  FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE,
  FOREIGN KEY (counted_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 14. WASTE LOG
-- ============================================================

CREATE TABLE waste_log (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  item_id         INT UNSIGNED NOT NULL,
  quantity        DECIMAL(10,2) NOT NULL,
  reason          ENUM('expired','dropped','over_portioned','returned','other') NOT NULL,
  dollar_value    DECIMAL(10,2) DEFAULT NULL,
  logged_by       INT UNSIGNED NOT NULL,
  photo_url       TEXT,
  notes           TEXT,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_waste_restaurant (restaurant_id, created_at),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES inventory_items(id),
  FOREIGN KEY (logged_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 15. VENDOR ORDERS
-- ============================================================

CREATE TABLE vendor_orders (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  vendor_name     VARCHAR(255) NOT NULL,
  status          ENUM('draft','submitted','received') NOT NULL DEFAULT 'draft',
  total_cost      DECIMAL(10,2) DEFAULT NULL,
  order_date      DATE,
  received_date   DATE DEFAULT NULL,
  created_by      INT UNSIGNED NOT NULL,

  INDEX idx_vendor_orders_restaurant (restaurant_id, status),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 16. VENDOR ORDER ITEMS
-- ============================================================

CREATE TABLE vendor_order_items (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id        INT UNSIGNED NOT NULL,
  item_id         INT UNSIGNED NOT NULL,
  quantity        DECIMAL(10,2) NOT NULL,
  unit_cost       DECIMAL(10,4) DEFAULT NULL,

  INDEX idx_vendor_order_items_order (order_id),

  FOREIGN KEY (order_id) REFERENCES vendor_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES inventory_items(id)
) ENGINE=InnoDB;

-- ============================================================
-- 17. SCHEDULES
-- ============================================================

CREATE TABLE schedules (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  week_start      DATE NOT NULL,
  status          ENUM('draft','published') NOT NULL DEFAULT 'draft',
  published_at    DATETIME DEFAULT NULL,
  created_by      INT UNSIGNED NOT NULL,

  UNIQUE KEY uk_schedule_week (restaurant_id, week_start),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 18. SHIFTS
-- ============================================================

CREATE TABLE shifts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  schedule_id     INT UNSIGNED NOT NULL,
  user_id         INT UNSIGNED NOT NULL,
  station         VARCHAR(50),
  role            VARCHAR(50),                      -- role at time of shift (cook, server, etc.)
  start_time      DATETIME NOT NULL,
  end_time        DATETIME NOT NULL,
  notes           TEXT,

  INDEX idx_shifts_schedule (schedule_id),
  INDEX idx_shifts_user (user_id),

  FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 19. SHIFT SWAP REQUESTS
-- ============================================================

CREATE TABLE shift_swap_requests (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  original_shift_id INT UNSIGNED NOT NULL,
  requester_id      INT UNSIGNED NOT NULL,
  proposed_user_id  INT UNSIGNED DEFAULT NULL,
  status            ENUM('pending','approved','denied') NOT NULL DEFAULT 'pending',
  manager_id        INT UNSIGNED DEFAULT NULL,
  resolved_at       DATETIME DEFAULT NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_swaps_status (status),

  FOREIGN KEY (original_shift_id) REFERENCES shifts(id) ON DELETE CASCADE,
  FOREIGN KEY (requester_id) REFERENCES users(id),
  FOREIGN KEY (proposed_user_id) REFERENCES users(id),
  FOREIGN KEY (manager_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 20. AVAILABILITY
-- ============================================================

CREATE TABLE availability (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  day_of_week     TINYINT NOT NULL,                 -- 0=Sun, 1=Mon, ... 6=Sat
  start_time      TIME NOT NULL,
  end_time        TIME NOT NULL,
  is_available    TINYINT(1) NOT NULL DEFAULT 1,

  INDEX idx_availability_user (user_id),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 21. ANNOUNCEMENTS
-- ============================================================

CREATE TABLE announcements (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id   INT UNSIGNED NOT NULL,
  author_id       INT UNSIGNED NOT NULL,
  title           VARCHAR(255) NOT NULL,
  body            TEXT NOT NULL,
  body_es         TEXT,                              -- optional Spanish translation
  target_role     ENUM('all','boh','foh','managers','cooks','servers') NOT NULL DEFAULT 'all',
  is_pinned       TINYINT(1) NOT NULL DEFAULT 0,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_announcements_restaurant (restaurant_id, created_at),

  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- 22. CLOCK ENTRIES (PREMIUM)
-- ============================================================

CREATE TABLE clock_entries (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL,
  restaurant_id   INT UNSIGNED NOT NULL,
  clock_in        DATETIME NOT NULL,
  clock_out       DATETIME DEFAULT NULL,
  lat             DECIMAL(10,7) DEFAULT NULL,
  lng             DECIMAL(10,7) DEFAULT NULL,
  is_manual_entry TINYINT(1) NOT NULL DEFAULT 0,

  INDEX idx_clock_user (user_id, clock_in),
  INDEX idx_clock_restaurant (restaurant_id, clock_in),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- DONE
-- ============================================================
-- Tables: 22
-- To run: mysql -u root -p < heardcheck_schema.sql
-- ============================================================
