-- Eric's 3D Services: new database schema (MySQL 8.0+, InnoDB, utf8mb4)
-- Draft v1. Designed from the old darktech_e3d_Prod schema plus everything the new prototype does.
-- Not yet run against a real MySQL server: load it into an empty test database first.
-- Conventions
--   * Every table has an INT UNSIGNED auto-increment id (except where noted).
--   * Money is DECIMAL(10,2) in US dollars. Prices and totals are always calculated on the server.
--   * Times are UTC DATETIME. The app converts to America/New_York for display.
--   * Tokens (password reset, magic link, tracking) are stored as SHA-256 hashes, never in plain text.
--   * Customer-facing identifiers (order_number, quote_number, tracking_token) are separate from ids.
--   * Soft delete (deleted_at) only where history matters: users, products, inventory.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- 1. ACCOUNTS, STAFF, PERMISSIONS
-- =====================================================================

CREATE TABLE roles (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug          VARCHAR(40)  NOT NULL UNIQUE,          -- owner, manager, helper, customer ...
  name          VARCHAR(80)  NOT NULL,
  description   VARCHAR(255) NULL,
  is_staff      TINYINT(1)   NOT NULL DEFAULT 0,       -- 1 = may sign in to /admin
  is_system     TINYINT(1)   NOT NULL DEFAULT 0,       -- built-in roles cannot be deleted
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
  role_id       INT UNSIGNED NOT NULL,
  permission    VARCHAR(60)  NOT NULL,                  -- e.g. orders.edit, quotes.price (see 03-security-checklist)
  PRIMARY KEY (role_id, permission),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email                VARCHAR(190) NOT NULL UNIQUE,
  password_hash        VARCHAR(255) NULL,               -- password_hash() argon2id; NULL = magic-link / social only
  must_change_password TINYINT(1)   NOT NULL DEFAULT 0,
  name                 VARCHAR(150) NULL,
  phone                VARCHAR(30)  NULL,
  avatar               VARCHAR(255) NULL,
  role_id              INT UNSIGNED NOT NULL,
  email_verified_at    DATETIME     NULL,
  birthday             DATE         NULL,
  newsletter_opt_in    TINYINT(1)   NOT NULL DEFAULT 0,
  newsletter_unsub_at  DATETIME     NULL,
  unsubscribe_token    CHAR(64)     NULL UNIQUE,        -- hash
  tax_exempt           TINYINT(1)   NOT NULL DEFAULT 0, -- no sales tax at checkout
  tax_company          VARCHAR(150) NULL,
  loyalty_points       INT          NOT NULL DEFAULT 0, -- cached sum of rewards_ledger
  referral_code        VARCHAR(20)  NULL UNIQUE,
  referred_by          INT UNSIGNED NULL,
  signup_affiliate_id  INT UNSIGNED NULL,
  referral_bonus_paid  TINYINT(1)   NOT NULL DEFAULT 0,
  birthday_bonus_year  SMALLINT     NULL,
  totp_secret_enc      VARBINARY(255) NULL,             -- encrypted with the app key, never plain
  totp_enabled         TINYINT(1)   NOT NULL DEFAULT 0,
  failed_logins        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until         DATETIME     NULL,
  last_login_at        DATETIME     NULL,
  last_seen_at         DATETIME     NULL,
  created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at           DATETIME     NULL,
  KEY idx_role (role_id),
  KEY idx_referred_by (referred_by),
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE totp_recovery_codes (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id   INT UNSIGNED NOT NULL,
  code_hash CHAR(64)     NOT NULL,
  used_at   DATETIME     NULL,
  KEY idx_user (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- One table for every emailed one-time link: verify email, reset password, magic sign-in, link social account.
CREATE TABLE auth_tokens (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  purpose     ENUM('verify_email','reset_password','magic_link','link_account') NOT NULL,
  token_hash  CHAR(64)     NOT NULL UNIQUE,
  expires_at  DATETIME     NOT NULL,
  used_at     DATETIME     NULL,
  request_ip  VARCHAR(45)  NULL,
  consume_ip  VARCHAR(45)  NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user_purpose (user_id, purpose),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sessions (
  id             CHAR(64)     NOT NULL PRIMARY KEY,     -- SHA-256 of the session cookie value
  user_id        INT UNSIGNED NULL,
  ip_address     VARCHAR(45)  NULL,
  user_agent     VARCHAR(255) NULL,
  label          VARCHAR(100) NULL,                     -- "Chrome on Windows" for the Security page
  data           MEDIUMTEXT   NULL,
  csrf_secret    CHAR(64)     NOT NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_activity  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at     DATETIME     NOT NULL,
  KEY idx_user (user_id),
  KEY idx_expires (expires_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_social_accounts (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id          INT UNSIGNED NOT NULL,
  provider         VARCHAR(20)  NOT NULL,
  provider_user_id VARCHAR(100) NOT NULL,
  provider_email   VARCHAR(190) NULL,
  provider_name    VARCHAR(150) NULL,
  linked_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_provider (provider, provider_user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE addresses (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  label       VARCHAR(50)  NULL,
  name        VARCHAR(150) NULL,
  line1       VARCHAR(255) NULL,
  line2       VARCHAR(255) NULL,
  city        VARCHAR(100) NULL,
  state       VARCHAR(50)  NULL,
  zip         VARCHAR(20)  NULL,
  country     CHAR(2)      NOT NULL DEFAULT 'US',
  phone       VARCHAR(50)  NULL,
  is_default  TINYINT(1)   NOT NULL DEFAULT 0,
  KEY idx_user (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE notification_prefs (
  user_id         INT UNSIGNED NOT NULL PRIMARY KEY,
  email_orders    TINYINT(1) NOT NULL DEFAULT 1,
  email_quotes    TINYINT(1) NOT NULL DEFAULT 1,
  email_support   TINYINT(1) NOT NULL DEFAULT 1,
  email_marketing TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE impersonation_sessions (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id     INT UNSIGNED NOT NULL,
  target_id    INT UNSIGNED NOT NULL,
  started_at   DATETIME NOT NULL,
  ended_at     DATETIME NULL,
  end_reason   ENUM('manual','session_expired','admin_logged_out') NULL,
  FOREIGN KEY (admin_id)  REFERENCES users(id),
  FOREIGN KEY (target_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- =====================================================================
-- 2. CATALOG
-- =====================================================================

CREATE TABLE categories (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id   INT UNSIGNED NULL,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(120) NOT NULL UNIQUE,
  description VARCHAR(500) NULL,
  image       VARCHAR(255) NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  visible     TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE products (
  id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name                    VARCHAR(200) NOT NULL,
  slug                    VARCHAR(200) NOT NULL UNIQUE,
  short_description       VARCHAR(255) NULL,
  description             TEXT NULL,
  details                 TEXT NULL,                              -- bullet points, one per line
  price                   DECIMAL(10,2) NOT NULL,
  compare_price           DECIMAL(10,2) NULL,
  price_unit              VARCHAR(40) NULL,
  weight_grams            INT NOT NULL DEFAULT 100,
  is_digital              TINYINT(1) NOT NULL DEFAULT 0,
  is_gift_card            TINYINT(1) NOT NULL DEFAULT 0,
  print_time_display      VARCHAR(60) NULL,
  image                   VARCHAR(255) NULL,
  video_url               VARCHAR(500) NULL,
  featured                TINYINT(1) NOT NULL DEFAULT 0,
  accepts_customer_upload TINYINT(1) NOT NULL DEFAULT 0,
  upload_prompt           VARCHAR(255) NULL,
  upload_required         TINYINT(1) NOT NULL DEFAULT 1,
  track_inventory         TINYINT(1) NOT NULL DEFAULT 0,
  stock                   INT NOT NULL DEFAULT 0,
  low_stock_threshold     INT NOT NULL DEFAULT 5,
  etsy_listing_ref        VARCHAR(50) NULL,
  meta_title              VARCHAR(200) NULL,
  meta_description        VARCHAR(300) NULL,
  sort_order              INT NOT NULL DEFAULT 0,
  status                  ENUM('active','draft','archived') NOT NULL DEFAULT 'draft',
  created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at              DATETIME NULL,
  KEY idx_status (status, featured, sort_order)
) ENGINE=InnoDB;

CREATE TABLE product_categories (
  product_id  INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, category_id),
  FOREIGN KEY (product_id)  REFERENCES products(id)   ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE product_images (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id  INT UNSIGNED NOT NULL,
  filename    VARCHAR(255) NOT NULL,
  alt_text    VARCHAR(255) NULL,
  is_primary  TINYINT(1) NOT NULL DEFAULT 0,
  sort_order  INT NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE product_options (          -- "Size", "Color"
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id  INT UNSIGNED NOT NULL,
  name        VARCHAR(50) NOT NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE product_option_values (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  option_id   INT UNSIGNED NOT NULL,
  value       VARCHAR(80) NOT NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  FOREIGN KEY (option_id) REFERENCES product_options(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE product_variants (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id       INT UNSIGNED NOT NULL,
  sku              VARCHAR(100) NULL UNIQUE,
  label            VARCHAR(150) NOT NULL,              -- "Red / Large", shown on cart and order lines
  price_override   DECIMAL(10,2) NULL,
  stock            INT NOT NULL DEFAULT 0,
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Replaces the old comma-separated option_value_ids column.
CREATE TABLE product_variant_values (
  variant_id        INT UNSIGNED NOT NULL,
  option_value_id   INT UNSIGNED NOT NULL,
  PRIMARY KEY (variant_id, option_value_id),
  FOREIGN KEY (variant_id)      REFERENCES product_variants(id)       ON DELETE CASCADE,
  FOREIGN KEY (option_value_id) REFERENCES product_option_values(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE product_custom_fields (    -- "Name to engrave", "Message"
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id     INT UNSIGNED NOT NULL,
  label          VARCHAR(100) NOT NULL,
  field_type     ENUM('text','textarea','select','checkbox') NOT NULL DEFAULT 'text',
  options        TEXT NULL,                              -- one choice per line (select only)
  placeholder    VARCHAR(200) NULL,
  max_length     INT NULL,
  is_required    TINYINT(1) NOT NULL DEFAULT 0,
  price_modifier DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  sort_order     INT NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE saved_products (            -- favorites
  user_id     INT UNSIGNED NOT NULL,
  product_id  INT UNSIGNED NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, product_id),
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE back_in_stock (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(190) NOT NULL,
  product_id  INT UNSIGNED NOT NULL,
  notified_at DATETIME NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_product (product_id, notified_at),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- 3. INVENTORY, SPOOLS, EXPENSES
-- =====================================================================

-- Per-gram pricing for the instant quote. One row per material the shop prints.
CREATE TABLE material_types (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(50) NOT NULL UNIQUE,            -- PLA, PETG, Silk PLA, Resin
  cost_per_gram  DECIMAL(8,4) NOT NULL,
  density        DECIMAL(5,3) NULL,
  hourly_rate    DECIMAL(8,2) NULL,                      -- machine cost per hour, overrides the global
  active         TINYINT(1) NOT NULL DEFAULT 1,
  sort_order     INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- Filament spools. Colors offered on the quote and product pages come from spools that are in stock and visible.
CREATE TABLE spools (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  material_type_id INT UNSIGNED NOT NULL,
  brand           VARCHAR(100) NULL,
  color_name      VARCHAR(100) NOT NULL,
  color_hex       CHAR(7) NULL,
  weight_original INT NOT NULL DEFAULT 1000,             -- grams
  weight_remaining INT NOT NULL DEFAULT 1000,
  cost            DECIMAL(10,2) NULL,
  low_threshold   INT NOT NULL DEFAULT 200,
  visible         TINYINT(1) NOT NULL DEFAULT 1,
  supplier        VARCHAR(120) NULL,
  supplier_url    VARCHAR(500) NULL,
  note            VARCHAR(500) NULL,
  opened_at       DATE NULL,
  archived_at     DATETIME NULL,
  KEY idx_visible (visible, archived_at),
  FOREIGN KEY (material_type_id) REFERENCES material_types(id)
) ENGINE=InnoDB;

CREATE TABLE spool_log (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  spool_id    INT UNSIGNED NOT NULL,
  change_g    INT NOT NULL,                              -- negative = used
  reason      VARCHAR(200) NULL,
  order_id    INT UNSIGNED NULL,
  user_id     INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_spool (spool_id, created_at),
  FOREIGN KEY (spool_id) REFERENCES spools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Parts, packaging and shipping supplies all live in one table (the old schema had three).
CREATE TABLE inventory_items (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type               ENUM('part','supply','packaging','other') NOT NULL DEFAULT 'part',
  sku                VARCHAR(100) NULL,
  name               VARCHAR(255) NOT NULL,
  category           VARCHAR(80) NULL,
  quantity           DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit               VARCHAR(20) NOT NULL DEFAULT 'each',
  low_stock_threshold DECIMAL(12,2) NOT NULL DEFAULT 0,
  cost_per_unit      DECIMAL(12,4) NULL,
  avg_unit_cost      DECIMAL(12,4) NULL,
  supplier           VARCHAR(120) NULL,
  supplier_url       VARCHAR(500) NULL,
  storage_location   VARCHAR(100) NULL,
  dimensions         VARCHAR(100) NULL,
  notes              TEXT NULL,
  last_reordered     DATE NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at         DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE inventory_log (            -- restocks, usage and corrections
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id     INT UNSIGNED NOT NULL,
  change_qty  DECIMAL(12,2) NOT NULL,
  unit_cost   DECIMAL(12,4) NULL,
  vendor      VARCHAR(120) NULL,
  order_id    INT UNSIGNED NULL,
  note        VARCHAR(255) NULL,
  user_id     INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_item (item_id, created_at),
  FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE etsy_inventory (           -- Etsy listing counts brought over by the importer
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id     VARCHAR(50) NULL,
  title          VARCHAR(255) NOT NULL,
  variant        VARCHAR(100) NULL,
  qty            INT NOT NULL DEFAULT 0,
  min_threshold  INT NOT NULL DEFAULT 5,
  restock_target INT NOT NULL DEFAULT 20,
  location       VARCHAR(100) NULL,
  sale_price     DECIMAL(10,2) NULL,
  last_sold      DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE waste_log (                -- failed prints and scrap
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    INT UNSIGNED NULL,
  spool_id    INT UNSIGNED NULL,
  grams       INT NOT NULL DEFAULT 0,
  reason      VARCHAR(255) NULL,
  cost        DECIMAL(10,2) NULL,
  user_id     INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (spool_id) REFERENCES spools(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE expenses (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expense_number  VARCHAR(40) NULL,
  category        VARCHAR(60) NOT NULL DEFAULT 'other',
  description     VARCHAR(255) NOT NULL,
  vendor          VARCHAR(120) NULL,
  quantity        DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit_cost       DECIMAL(10,2) NOT NULL DEFAULT 0,
  tax             DECIMAL(10,2) NOT NULL DEFAULT 0,
  shipping        DECIMAL(10,2) NOT NULL DEFAULT 0,
  grand_total     DECIMAL(10,2) NOT NULL DEFAULT 0,
  purchased_at    DATE NOT NULL,
  payment_method  VARCHAR(40) NULL,
  receipt_path    VARCHAR(255) NULL,
  tax_deductible  TINYINT(1) NOT NULL DEFAULT 1,
  spool_id        INT UNSIGNED NULL,
  inventory_item_id INT UNSIGNED NULL,
  notes           TEXT NULL,
  created_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_date (purchased_at),
  FOREIGN KEY (spool_id) REFERENCES spools(id) ON DELETE SET NULL,
  FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =====================================================================
-- 4. DISCOUNTS, GIFT CARDS, SHIPPING, TAX
-- =====================================================================

CREATE TABLE affiliates (                -- referral partners
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         INT UNSIGNED NOT NULL UNIQUE,
  affiliate_code  VARCHAR(40) NOT NULL UNIQUE,
  display_name    VARCHAR(150) NULL,
  status          ENUM('pending','active','paused','rejected','removed') NOT NULL DEFAULT 'pending',
  commission_pct  DECIMAL(5,2) NULL,
  total_clicks    INT UNSIGNED NOT NULL DEFAULT 0,
  total_conversions INT UNSIGNED NOT NULL DEFAULT 0,
  commission_earned DECIMAL(10,2) NOT NULL DEFAULT 0,
  commission_paid   DECIMAL(10,2) NOT NULL DEFAULT 0,
  applied_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  approved_at     DATETIME NULL,
  approved_by     INT UNSIGNED NULL,
  notes           TEXT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE affiliate_clicks (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  affiliate_id  INT UNSIGNED NOT NULL,
  ip_hash       CHAR(64) NULL,
  landing_path  VARCHAR(255) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_aff (affiliate_id, created_at),
  FOREIGN KEY (affiliate_id) REFERENCES affiliates(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE discounts (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code          VARCHAR(50) NOT NULL UNIQUE,
  kind          ENUM('percent','fixed','free_ship') NOT NULL DEFAULT 'percent',
  value         DECIMAL(10,2) NULL,
  min_order     DECIMAL(10,2) NOT NULL DEFAULT 0,
  max_uses      INT NULL,
  per_customer  INT NULL,                                -- NULL = unlimited; 1 = one use each (birthday, win-back)
  used_count    INT NOT NULL DEFAULT 0,
  starts_at     DATETIME NULL,
  ends_at       DATETIME NULL,
  active        TINYINT(1) NOT NULL DEFAULT 1,
  user_id       INT UNSIGNED NULL,                       -- set = code only works for this customer
  affiliate_id  INT UNSIGNED NULL,
  origin        VARCHAR(30) NULL,                        -- manual, birthday, winback, abandoned_cart
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)      REFERENCES users(id)      ON DELETE CASCADE,
  FOREIGN KEY (affiliate_id) REFERENCES affiliates(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE gift_cards (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code               VARCHAR(24) NOT NULL UNIQUE,
  initial_amount     DECIMAL(10,2) NOT NULL,
  balance            DECIMAL(10,2) NOT NULL,
  purchaser_email    VARCHAR(190) NULL,
  purchaser_user_id  INT UNSIGNED NULL,
  owner_user_id      INT UNSIGNED NULL,                  -- set when a customer adds it to their wallet
  recipient_name     VARCHAR(150) NULL,
  recipient_email    VARCHAR(190) NULL,
  message            TEXT NULL,
  status             ENUM('active','disabled','used') NOT NULL DEFAULT 'active',
  purchase_order_id  INT UNSIGNED NULL,
  deliver_at         DATE NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE gift_card_transactions (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gift_card_id  INT UNSIGNED NOT NULL,
  order_id      INT UNSIGNED NULL,
  amount        DECIMAL(10,2) NOT NULL,                  -- negative = spent, positive = issued/refunded/adjusted
  note          VARCHAR(200) NULL,
  user_id       INT UNSIGNED NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_card (gift_card_id, created_at),
  FOREIGN KEY (gift_card_id) REFERENCES gift_cards(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE shipping_zones (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(80) NOT NULL,
  countries   VARCHAR(255) NOT NULL DEFAULT 'US',        -- comma list of ISO codes
  states      VARCHAR(500) NULL,                         -- blank = whole country
  sort_order  INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE shipping_rates (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  zone_id        INT UNSIGNED NOT NULL,
  name           VARCHAR(80) NOT NULL,                   -- "Standard", "Priority"
  min_weight_g   INT NOT NULL DEFAULT 0,
  max_weight_g   INT NULL,
  min_subtotal   DECIMAL(10,2) NOT NULL DEFAULT 0,
  price          DECIMAL(10,2) NOT NULL,
  free_over      DECIMAL(10,2) NULL,
  est_days_min   TINYINT UNSIGNED NULL,
  est_days_max   TINYINT UNSIGNED NULL,
  active         TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (zone_id) REFERENCES shipping_zones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tax_rates (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  country     CHAR(2) NOT NULL DEFAULT 'US',
  state       VARCHAR(50) NOT NULL,
  rate        DECIMAL(6,4) NOT NULL,                     -- 0.0550 = 5.5%
  applies_to_shipping TINYINT(1) NOT NULL DEFAULT 0,
  active      TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uniq_region (country, state)
) ENGINE=InnoDB;

-- =====================================================================
-- 5. CARTS, ORDERS, PAYMENTS, SHIPMENTS, PRODUCTION
-- =====================================================================

CREATE TABLE carts (                     -- server copy of a cart, only needed for abandoned-cart emails and sign-in carry-over
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token         CHAR(64) NOT NULL UNIQUE,
  user_id       INT UNSIGNED NULL,
  email         VARCHAR(190) NULL,
  items         JSON NOT NULL,
  discount_code VARCHAR(50) NULL,
  reminder_sent_at DATETIME NULL,
  final_sent_at    DATETIME NULL,
  recovered_order_id INT UNSIGNED NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_updated (updated_at)
) ENGINE=InnoDB;

CREATE TABLE printers (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  model     VARCHAR(80) NULL,
  active    TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE orders (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_number       VARCHAR(50) NOT NULL UNIQUE,        -- E3D-1047, shown to customers
  tracking_token     CHAR(64) NOT NULL UNIQUE,           -- hash; lets guests track without signing in
  user_id            INT UNSIGNED NULL,
  email              VARCHAR(190) NOT NULL,
  name               VARCHAR(150) NULL,
  phone              VARCHAR(50) NULL,
  status             VARCHAR(30) NOT NULL DEFAULT 'received',
  subtotal           DECIMAL(10,2) NOT NULL,
  shipping           DECIMAL(10,2) NOT NULL DEFAULT 0,
  tax                DECIMAL(10,2) NOT NULL DEFAULT 0,
  tax_exempt         TINYINT(1) NOT NULL DEFAULT 0,
  discount           DECIMAL(10,2) NOT NULL DEFAULT 0,
  discount_code      VARCHAR(50) NULL,
  gift_card_applied  DECIMAL(10,2) NOT NULL DEFAULT 0,
  points_applied     INT NOT NULL DEFAULT 0,
  total              DECIMAL(10,2) NOT NULL,
  affiliate_id       INT UNSIGNED NULL,
  quote_id           INT UNSIGNED NULL,                  -- set when the order came from an approved quote
  -- shipping address is copied onto the order so later edits to the address book never change history
  ship_name          VARCHAR(150) NULL,
  ship_line1         VARCHAR(255) NULL,
  ship_line2         VARCHAR(255) NULL,
  ship_city          VARCHAR(100) NULL,
  ship_state         VARCHAR(50)  NULL,
  ship_zip           VARCHAR(20)  NULL,
  ship_country       CHAR(2) NOT NULL DEFAULT 'US',
  shipping_method    VARCHAR(100) NULL,
  is_pickup          TINYINT(1) NOT NULL DEFAULT 0,
  is_rush            TINYINT(1) NOT NULL DEFAULT 0,
  is_gift            TINYINT(1) NOT NULL DEFAULT 0,
  gift_message       TEXT NULL,
  customer_note      TEXT NULL,
  priority           TINYINT NOT NULL DEFAULT 0,
  assigned_to        INT UNSIGNED NULL,
  printer_id         INT UNSIGNED NULL,
  queue_position     INT NULL,
  estimated_delivery DATE NULL,
  reprint_of         INT UNSIGNED NULL,
  placed_ip          VARCHAR(45) NULL,
  payment_meta       JSON NULL,                              -- gift cards and discount used, kept for refunds and cancels
  idempotency_key    VARCHAR(64) NULL UNIQUE,                -- stops a double click from creating two orders
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  KEY idx_email (email),
  KEY idx_status (status, created_at),
  KEY idx_queue (printer_id, queue_position),
  FOREIGN KEY (user_id)     REFERENCES users(id)      ON DELETE SET NULL,
  FOREIGN KEY (affiliate_id) REFERENCES affiliates(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id)      ON DELETE SET NULL,
  FOREIGN KEY (printer_id)  REFERENCES printers(id)   ON DELETE SET NULL,
  FOREIGN KEY (reprint_of)  REFERENCES orders(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

-- Replaces the old JSON "items" text column so reports and returns can query lines.
CREATE TABLE order_items (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id      INT UNSIGNED NOT NULL,
  product_id    INT UNSIGNED NULL,
  variant_id    INT UNSIGNED NULL,
  name          VARCHAR(200) NOT NULL,                   -- copied at purchase time
  variant_label VARCHAR(150) NULL,
  sku           VARCHAR(100) NULL,
  color         VARCHAR(80) NULL,
  material      VARCHAR(50) NULL,
  unit_price    DECIMAL(10,2) NOT NULL,
  qty           INT UNSIGNED NOT NULL DEFAULT 1,
  line_total    DECIMAL(10,2) NOT NULL,
  custom_fields JSON NULL,                               -- what the customer typed into the product's custom fields
  upload_id     INT UNSIGNED NULL,
  weight_g      INT NULL,
  KEY idx_order (order_id),
  KEY idx_product (product_id),
  FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE order_status_history (      -- replaces the old received_at/queued_at/printing_at... column pile
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    INT UNSIGNED NOT NULL,
  status      VARCHAR(30) NOT NULL,
  note        VARCHAR(255) NULL,
  user_id     INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_order (order_id, created_at),
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_shipments (            -- split or partial shipments
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id        INT UNSIGNED NOT NULL,
  carrier         VARCHAR(50) NULL,
  tracking_number VARCHAR(100) NULL,
  label_url       VARCHAR(500) NULL,
  label_cost      DECIMAL(10,2) NULL,
  shipped_at      DATETIME NULL,
  delivered_at    DATETIME NULL,
  last_status     VARCHAR(80) NULL,
  last_checked_at DATETIME NULL,
  KEY idx_order (order_id),
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_shipment_items (
  shipment_id    INT UNSIGNED NOT NULL,
  order_item_id  INT UNSIGNED NOT NULL,
  qty            INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (shipment_id, order_item_id),
  FOREIGN KEY (shipment_id)   REFERENCES order_shipments(id) ON DELETE CASCADE,
  FOREIGN KEY (order_item_id) REFERENCES order_items(id)     ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (                   -- one row per charge or refund against an order
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id       INT UNSIGNED NOT NULL,
  provider       VARCHAR(20) NOT NULL DEFAULT 'paypal',
  kind           ENUM('charge','refund') NOT NULL DEFAULT 'charge',
  amount         DECIMAL(10,2) NOT NULL,
  provider_order_id   VARCHAR(100) NULL,
  provider_capture_id VARCHAR(100) NULL,
  status         VARCHAR(30) NOT NULL,
  raw_event      JSON NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_capture (provider, provider_capture_id),
  KEY idx_order (order_id),
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_material_usage (       -- grams of each spool used by an order, for true cost reports
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    INT UNSIGNED NOT NULL,
  spool_id    INT UNSIGNED NULL,
  grams       INT NOT NULL,
  cost        DECIMAL(10,2) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id)  ON DELETE CASCADE,
  FOREIGN KEY (spool_id) REFERENCES spools(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE returns (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  return_number VARCHAR(20) NOT NULL UNIQUE,             -- R-101
  order_id      INT UNSIGNED NOT NULL,
  user_id       INT UNSIGNED NULL,
  reason        VARCHAR(80) NOT NULL,
  want          ENUM('refund','replacement','store_credit') NOT NULL,
  note          TEXT NULL,
  status        ENUM('requested','approved','received','declined','refunded','replaced','credited','closed') NOT NULL DEFAULT 'requested',
  refund_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  customer_message TEXT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_order (order_id),
  KEY idx_status (status),
  FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

CREATE TABLE return_items (
  return_id      INT UNSIGNED NOT NULL,
  order_item_id  INT UNSIGNED NOT NULL,
  qty            INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (return_id, order_item_id),
  FOREIGN KEY (return_id)     REFERENCES returns(id)     ON DELETE CASCADE,
  FOREIGN KEY (order_item_id) REFERENCES order_items(id)
) ENGINE=InnoDB;

CREATE TABLE return_events (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  return_id  INT UNSIGNED NOT NULL,
  who        VARCHAR(80) NOT NULL,
  text       VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (return_id) REFERENCES returns(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- 6. QUOTES
-- =====================================================================

CREATE TABLE quotes (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_number     VARCHAR(50) NOT NULL UNIQUE,
  approval_token   CHAR(64) NULL UNIQUE,                 -- hash; email link lets the customer approve without signing in
  request_type     ENUM('file','design') NOT NULL DEFAULT 'file',
  user_id          INT UNSIGNED NULL,
  email            VARCHAR(190) NOT NULL,
  name             VARCHAR(150) NULL,
  status           VARCHAR(40) NOT NULL DEFAULT 'pending_review',
  material_type_id INT UNSIGNED NULL,
  color            VARCHAR(80) NULL,
  quantity         INT NOT NULL DEFAULT 1,
  infill           TINYINT UNSIGNED NOT NULL DEFAULT 20,
  layer_height     DECIMAL(3,2) NOT NULL DEFAULT 0.20,
  support_type     VARCHAR(20) NOT NULL DEFAULT 'auto',
  rush             TINYINT(1) NOT NULL DEFAULT 0,
  notes            TEXT NULL,
  design_brief     TEXT NULL,
  target_dimensions VARCHAR(120) NULL,
  intended_use     VARCHAR(255) NULL,
  reference_urls   TEXT NULL,
  budget_range     VARCHAR(50) NULL,
  target_deadline  DATE NULL,
  volume_cm3       DECIMAL(10,2) NULL,
  weight_g         DECIMAL(10,2) NULL,
  print_hours      DECIMAL(6,2) NULL,
  estimated_price  DECIMAL(10,2) NULL,                   -- instant estimate shown to the customer
  final_price      DECIMAL(10,2) NULL,                   -- set by Eric
  discount_code    VARCHAR(50) NULL,
  discount_amount  DECIMAL(10,2) NULL,
  customer_message TEXT NULL,
  admin_notes      TEXT NULL,
  valid_until      DATE NULL,
  converted_order_id INT UNSIGNED NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  KEY idx_status (status, created_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (material_type_id) REFERENCES material_types(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE quote_files (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_id       INT UNSIGNED NOT NULL,
  original_name  VARCHAR(255) NOT NULL,
  stored_name    VARCHAR(255) NOT NULL,                  -- random name; stored outside the web root
  size_bytes     INT UNSIGNED NULL,
  mime           VARCHAR(100) NULL,
  weight_g       DECIMAL(10,2) NULL,
  volume_cm3     DECIMAL(10,2) NULL,
  print_hours    DECIMAL(6,2) NULL,
  material_cost  DECIMAL(10,2) NULL,
  machine_cost   DECIMAL(10,2) NULL,
  support_cost   DECIMAL(10,2) NULL,
  analysis       JSON NULL,
  sort_order     INT NOT NULL DEFAULT 0,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE quote_events (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_id   INT UNSIGNED NOT NULL,
  who        VARCHAR(80) NOT NULL,
  text       VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE uploads (                    -- customer files attached to a cart line, order, ticket or quote
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NULL,
  cart_token    CHAR(64) NULL,
  order_item_id INT UNSIGNED NULL,
  ticket_id     INT UNSIGNED NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name   VARCHAR(255) NOT NULL,
  mime          VARCHAR(100) NULL,
  size_bytes    INT UNSIGNED NULL,
  analysis      JSON NULL,                -- what the 3D file analyzer measured
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_created (created_at)
) ENGINE=InnoDB;

-- =====================================================================
-- 7. SUPPORT
-- =====================================================================

CREATE TABLE tickets (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_number VARCHAR(20) NOT NULL UNIQUE,
  user_id       INT UNSIGNED NULL,
  email         VARCHAR(190) NOT NULL,
  name          VARCHAR(150) NULL,
  subject       VARCHAR(255) NOT NULL,
  category      VARCHAR(60) NOT NULL DEFAULT 'Other',
  priority      ENUM('Low','Normal','High','Urgent') NOT NULL DEFAULT 'Normal',
  status        ENUM('New','Open','Waiting on customer','On hold','Resolved','Closed') NOT NULL DEFAULT 'New',
  order_id      INT UNSIGNED NULL,
  quote_id      INT UNSIGNED NULL,
  assigned_to   INT UNSIGNED NULL,
  first_reply_at DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  closed_at     DATETIME NULL,
  KEY idx_status (status, priority),
  KEY idx_user (user_id),
  FOREIGN KEY (user_id)     REFERENCES users(id)  ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id)  ON DELETE SET NULL,
  FOREIGN KEY (order_id)    REFERENCES orders(id) ON DELETE SET NULL,
  FOREIGN KEY (quote_id)    REFERENCES quotes(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE ticket_messages (            -- first message and every reply (the old schema kept the first message on the ticket row)
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id   INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NULL,
  is_staff    TINYINT(1) NOT NULL DEFAULT 0,
  is_internal TINYINT(1) NOT NULL DEFAULT 0,             -- internal notes are never shown to the customer
  body        TEXT NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ticket (ticket_id, created_at),
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE canned_replies (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  category  VARCHAR(60) NULL,
  body      TEXT NOT NULL,
  active    TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE staff_signatures (
  user_id   INT UNSIGNED NOT NULL PRIMARY KEY,
  text      TEXT NULL,
  auto_add  TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE contact_submissions (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  email       VARCHAR(190) NOT NULL,
  message     TEXT NOT NULL,
  status      ENUM('new','read','replied') NOT NULL DEFAULT 'new',
  ticket_id   INT UNSIGNED NULL,
  ip_hash     CHAR(64) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE internal_notes (             -- staff notes on a customer, order or quote
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type  ENUM('user','order','quote') NOT NULL,
  entity_id    INT UNSIGNED NOT NULL,
  author_id    INT UNSIGNED NOT NULL,
  body         TEXT NOT NULL,
  pinned       TINYINT(1) NOT NULL DEFAULT 0,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_at    DATETIME NULL,
  deleted_at   DATETIME NULL,
  KEY idx_entity (entity_type, entity_id)
) ENGINE=InnoDB;

-- =====================================================================
-- 8. REWARDS, BADGES, REVIEWS, PORTFOLIO, CONTENT
-- =====================================================================

CREATE TABLE rewards_ledger (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  delta       INT NOT NULL,
  reason      VARCHAR(120) NOT NULL,                     -- purchase, review, birthday, referral, redeemed, adjustment
  order_id    INT UNSIGNED NULL,
  created_by  INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id, created_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE badges (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(60) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  icon        VARCHAR(40) NOT NULL DEFAULT 'award',
  category    VARCHAR(40) NOT NULL DEFAULT 'general',
  auto_rule   VARCHAR(255) NULL,                         -- e.g. orders>=3, reviews>=1; NULL = hand-awarded only
  is_active   TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE user_badges (
  user_id    INT UNSIGNED NOT NULL,
  badge_id   INT UNSIGNED NOT NULL,
  earned_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, badge_id),
  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
  FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE reviews (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id   INT UNSIGNED NULL,
  order_id     INT UNSIGNED NULL,
  user_id      INT UNSIGNED NULL,
  name         VARCHAR(150) NULL,
  rating       TINYINT UNSIGNED NOT NULL,
  title        VARCHAR(150) NULL,
  body         TEXT NULL,
  status       ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  reply        TEXT NULL,
  source       VARCHAR(30) NOT NULL DEFAULT 'site',      -- site, etsy, google
  external_id  VARCHAR(100) NULL UNIQUE,                 -- etsy_NNNN, blocks duplicate imports
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_product (product_id, status),
  CHECK (rating BETWEEN 1 AND 5),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE review_photos (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  review_id  INT UNSIGNED NOT NULL,
  filename   VARCHAR(255) NOT NULL,
  FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE portfolio (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title             VARCHAR(200) NOT NULL,
  slug              VARCHAR(200) NOT NULL UNIQUE,
  short_description VARCHAR(255) NULL,
  description       TEXT NULL,
  category          VARCHAR(100) NULL,
  tags              VARCHAR(255) NULL,
  materials         VARCHAR(255) NULL,
  print_time        VARCHAR(60) NULL,
  dimensions        VARCHAR(100) NULL,
  finish            VARCHAR(120) NULL,
  client_name       VARCHAR(120) NULL,
  testimonial       TEXT NULL,
  featured          TINYINT(1) NOT NULL DEFAULT 0,
  status            ENUM('published','draft') NOT NULL DEFAULT 'draft',
  sort_order        INT NOT NULL DEFAULT 0,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE portfolio_images (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id  INT UNSIGNED NOT NULL,
  filename      VARCHAR(255) NOT NULL,
  caption       VARCHAR(255) NULL,
  is_hero       TINYINT(1) NOT NULL DEFAULT 0,
  sort_order    INT NOT NULL DEFAULT 0,
  FOREIGN KEY (portfolio_id) REFERENCES portfolio(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE portfolio_comments (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id   INT UNSIGNED NOT NULL,
  parent_id      INT UNSIGNED NULL,
  user_id        INT UNSIGNED NOT NULL,
  body           TEXT NOT NULL,
  status         ENUM('visible','removed') NOT NULL DEFAULT 'visible',
  removed_by     INT UNSIGNED NULL,
  removed_reason VARCHAR(255) NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (portfolio_id) REFERENCES portfolio(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)      REFERENCES users(id)     ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE announcements (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  message    VARCHAR(500) NOT NULL,
  style      VARCHAR(20) NOT NULL DEFAULT 'info',
  cta_text   VARCHAR(100) NULL,
  cta_url    VARCHAR(255) NULL,
  active     TINYINT(1) NOT NULL DEFAULT 0,
  starts_at  DATETIME NULL,
  ends_at    DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE legal_pages (                -- edited text for the Terms, Privacy and other legal pages
  slug        VARCHAR(60) NOT NULL PRIMARY KEY,
  title       VARCHAR(150) NOT NULL,
  body_html   MEDIUMTEXT NULL,
  enabled     TINYINT(1) NOT NULL DEFAULT 1,
  updated_by  INT UNSIGNED NULL,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =====================================================================
-- 9. EMAIL: TEMPLATES, QUEUE, NEWSLETTERS
-- =====================================================================

CREATE TABLE email_templates (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug         VARCHAR(80) NOT NULL UNIQUE,              -- order_shipped, quote_ready ...
  name         VARCHAR(120) NOT NULL,
  grp          VARCHAR(40) NOT NULL,
  subject      VARCHAR(255) NOT NULL,
  html         MEDIUMTEXT NOT NULL,                      -- the LIVE version
  draft_subject VARCHAR(255) NULL,                       -- edits that are not live yet
  draft_html   MEDIUMTEXT NULL,
  enabled      TINYINT(1) NOT NULL DEFAULT 1,
  updated_by   INT UNSIGNED NULL,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE mail_queue (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient   VARCHAR(190) NOT NULL,
  template    VARCHAR(80)  NULL,
  subject     VARCHAR(255) NOT NULL,
  body_html   MEDIUMTEXT NOT NULL,
  body_text   MEDIUMTEXT NULL,
  status      ENUM('queued','sending','sent','failed','suppressed') NOT NULL DEFAULT 'queued',
  attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  error       VARCHAR(500) NULL,
  related_type VARCHAR(20) NULL,                         -- order, quote, ticket, return, newsletter
  related_id  INT UNSIGNED NULL,
  send_after  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at     DATETIME NULL,
  KEY idx_status (status, send_after)
) ENGINE=InnoDB;

CREATE TABLE email_suppressions (         -- bounced, complained or unsubscribed: never email these
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(190) NOT NULL UNIQUE,
  source     VARCHAR(60) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE newsletter_subscribers (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email             VARCHAR(190) NOT NULL UNIQUE,
  name              VARCHAR(120) NULL,
  source            VARCHAR(40) NOT NULL DEFAULT 'website',
  unsubscribe_token CHAR(64) NOT NULL UNIQUE,
  confirmed_at      DATETIME NULL,
  subscribed_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unsubscribed_at   DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE newsletters (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name             VARCHAR(150) NOT NULL,
  subject          VARCHAR(255) NULL,
  preheader        VARCHAR(255) NULL,
  mode             ENUM('visual','html') NOT NULL DEFAULT 'visual',
  blocks           JSON NULL,                            -- visual editor blocks
  html             MEDIUMTEXT NULL,                      -- rendered or hand-written HTML
  css              TEXT NULL,
  audience         VARCHAR(40) NOT NULL DEFAULT 'all',   -- all, customers, subscribers, specific
  manual_recipients TEXT NULL,
  status           ENUM('draft','sending','sent') NOT NULL DEFAULT 'draft',
  recipient_count  INT UNSIGNED NOT NULL DEFAULT 0,
  sent_count       INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count     INT UNSIGNED NOT NULL DEFAULT 0,
  created_by       INT UNSIGNED NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at          DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE newsletter_sends (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  newsletter_id  INT UNSIGNED NOT NULL,
  email          VARCHAR(190) NOT NULL,
  name           VARCHAR(150) NULL,
  status         ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  error          VARCHAR(500) NULL,
  sent_at        DATETIME NULL,
  UNIQUE KEY uniq_send (newsletter_id, email),
  FOREIGN KEY (newsletter_id) REFERENCES newsletters(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- One row per automatic email already sent, so a birthday, win-back, review request or reminder is never sent twice.
CREATE TABLE automation_sends (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kind        VARCHAR(40) NOT NULL,                      -- birthday, winback, abandoned_cart, abandoned_final, review_request, weekly_digest
  user_id     INT UNSIGNED NULL,
  email       VARCHAR(190) NULL,
  ref         VARCHAR(60) NOT NULL DEFAULT '',           -- e.g. year for birthdays, order id for review requests
  sent_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_once (kind, user_id, ref)
) ENGINE=InnoDB;

-- =====================================================================
-- 10. SYSTEM: SETTINGS, LOGS, JOBS, INTEGRATIONS, ANALYTICS
-- =====================================================================

CREATE TABLE settings (                   -- key/value: shop name, hours, cost.* pricing, returns.days, rewards rates ...
  skey        VARCHAR(100) NOT NULL PRIMARY KEY,
  svalue      TEXT NULL,
  updated_by  INT UNSIGNED NULL,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE audit_log (                  -- what staff did: "Marked Jane tax exempt"
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  area        VARCHAR(40) NOT NULL,
  action      VARCHAR(255) NOT NULL,
  level       ENUM('info','warn','danger') NOT NULL DEFAULT 'info',
  entity_type VARCHAR(30) NULL,
  entity_id   INT UNSIGNED NULL,
  ip          VARCHAR(45) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_created (created_at),
  KEY idx_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE logs (                        -- application and error log
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category    VARCHAR(40) NOT NULL,
  level       VARCHAR(20) NOT NULL DEFAULT 'INFO',
  user_id     INT UNSIGNED NULL,
  ip          VARCHAR(45) NULL,
  message     TEXT NULL,
  context     JSON NULL,
  url         VARCHAR(500) NULL,
  method      VARCHAR(10) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cat (category, created_at),
  KEY idx_level (level, created_at)
) ENGINE=InnoDB;

CREATE TABLE cron_runs (
  job         VARCHAR(60) NOT NULL PRIMARY KEY,
  last_run_at DATETIME NULL,
  last_status VARCHAR(20) NULL,
  last_message VARCHAR(500) NULL
) ENGINE=InnoDB;

CREATE TABLE integrations (
  id           VARCHAR(40) NOT NULL PRIMARY KEY,         -- paypal, shippo, google_analytics ...
  enabled      TINYINT(1) NOT NULL DEFAULT 0,
  config_enc   VARBINARY(4096) NULL,                     -- API keys, encrypted with the app key; never shown in full
  connected_at DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE webhook_deliveries (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  direction   ENUM('in','out') NOT NULL DEFAULT 'out',
  event       VARCHAR(60) NOT NULL,
  url         VARCHAR(500) NULL,
  http_status INT NULL,
  success     TINYINT(1) NOT NULL DEFAULT 0,
  error       VARCHAR(500) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE rate_limits (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bucket      VARCHAR(60) NOT NULL,                      -- login, register, contact, quote, magic_link
  identifier  VARCHAR(100) NOT NULL,                     -- hashed IP or email
  created_at  INT UNSIGNED NOT NULL,
  KEY idx_lookup (bucket, identifier, created_at)
) ENGINE=InnoDB;

CREATE TABLE visitors (                    -- first-party page views (no cookies beyond the session)
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id    CHAR(64) NULL,
  user_id       INT UNSIGNED NULL,
  ip_hash       CHAR(64) NULL,
  country       CHAR(2) NULL,
  user_agent    VARCHAR(255) NULL,
  referrer      VARCHAR(500) NULL,
  page          VARCHAR(500) NULL,
  utm_source    VARCHAR(100) NULL,
  utm_medium    VARCHAR(100) NULL,
  utm_campaign  VARCHAR(100) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE search_console_data (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  data_date   DATE NOT NULL,
  dimension   VARCHAR(20) NOT NULL,
  dim_value   VARCHAR(700) NOT NULL,
  clicks      INT UNSIGNED NOT NULL DEFAULT 0,
  impressions INT UNSIGNED NOT NULL DEFAULT 0,
  ctr         DECIMAL(6,4) NOT NULL DEFAULT 0,
  position    DECIMAL(6,2) NOT NULL DEFAULT 0,
  KEY idx_date (data_date, dimension)
) ENGINE=InnoDB;

CREATE TABLE saved_reports (               -- saved analytics views
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  name        VARCHAR(100) NOT NULL,
  config      JSON NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE import_runs (                 -- one row per run of the old-site importer, so it can be undone
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kind        VARCHAR(40) NOT NULL,
  user_id     INT UNSIGNED NULL,
  rows_added  INT UNSIGNED NOT NULL DEFAULT 0,
  rows_skipped INT UNSIGNED NOT NULL DEFAULT 0,
  undone_at   DATETIME NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE migrations (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL UNIQUE,
  applied_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- Starting data the site needs to run (not customer data)
-- =====================================================================
-- Other staff roles (Manager, Fulfillment, Support, Bookkeeper, Viewer) and their permissions are loaded by database/seed.php.
INSERT INTO roles (slug, name, is_staff, is_system) VALUES
  ('customer', 'Customer', 0, 1),
  ('owner',    'Owner',    1, 1);
INSERT INTO role_permissions (role_id, permission) SELECT id, '*' FROM roles WHERE slug = 'owner';

INSERT INTO migrations (name) VALUES ('001_initial_schema');
