CREATE TABLE IF NOT EXISTS schema_migrations (
  id VARCHAR(191) NOT NULL,
  applied_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_state (
  id TINYINT UNSIGNED NOT NULL,
  settings LONGTEXT NOT NULL,
  traffic LONGTEXT NOT NULL,
  fastspring_test LONGTEXT NOT NULL,
  extra LONGTEXT NOT NULL,
  updated_at DATETIME(3) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_admins (
  id VARCHAR(191) NOT NULL,
  email VARCHAR(191) NOT NULL,
  name VARCHAR(191) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY platform_admins_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurants (
  id VARCHAR(191) NOT NULL,
  slug VARCHAR(191) NOT NULL,
  name VARCHAR(191) NOT NULL,
  contact_name VARCHAR(191) NOT NULL DEFAULT '',
  contact_email VARCHAR(191) NOT NULL DEFAULT '',
  contact_phone VARCHAR(80) NOT NULL DEFAULT '',
  subscription LONGTEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  data LONGTEXT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY restaurants_slug_unique (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_records (
  kind VARCHAR(80) NOT NULL,
  id VARCHAR(191) NOT NULL,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (kind, id),
  KEY platform_records_kind_idx (kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_state (
  restaurant_id VARCHAR(191) NOT NULL,
  settings LONGTEXT NOT NULL,
  stats LONGTEXT NOT NULL,
  food_platform_integration LONGTEXT NOT NULL,
  extra LONGTEXT NOT NULL,
  updated_at DATETIME(3) NOT NULL,
  PRIMARY KEY (restaurant_id),
  CONSTRAINT restaurant_state_restaurant_fk FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_menus (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  CONSTRAINT restaurant_menus_restaurant_fk FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_categories (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  menu_id VARCHAR(191) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  KEY restaurant_categories_menu_idx (restaurant_id, menu_id, sort_order),
  CONSTRAINT restaurant_categories_menu_fk FOREIGN KEY (restaurant_id, menu_id) REFERENCES restaurant_menus(restaurant_id, id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_products (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  category_id VARCHAR(191) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  is_available TINYINT(1) NOT NULL DEFAULT 1,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  KEY restaurant_products_category_idx (restaurant_id, category_id),
  CONSTRAINT restaurant_products_category_fk FOREIGN KEY (restaurant_id, category_id) REFERENCES restaurant_categories(restaurant_id, id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_product_options (
  restaurant_id VARCHAR(191) NOT NULL,
  product_id VARCHAR(191) NOT NULL,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, product_id),
  CONSTRAINT restaurant_product_options_product_fk FOREIGN KEY (restaurant_id, product_id) REFERENCES restaurant_products(restaurant_id, id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_campaigns (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  product_id VARCHAR(191) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  CONSTRAINT restaurant_campaigns_product_fk FOREIGN KEY (restaurant_id, product_id) REFERENCES restaurant_products(restaurant_id, id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_tables (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  table_number INT NOT NULL,
  qr_token VARCHAR(191) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  UNIQUE KEY restaurant_tables_number_unique (restaurant_id, table_number),
  UNIQUE KEY restaurant_tables_qr_unique (restaurant_id, qr_token),
  CONSTRAINT restaurant_tables_restaurant_fk FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_staff_users (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  email VARCHAR(191) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(40) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  UNIQUE KEY restaurant_staff_users_email_unique (restaurant_id, email),
  KEY restaurant_staff_users_login_idx (restaurant_id, email, is_active),
  CONSTRAINT restaurant_staff_users_restaurant_fk FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_bills (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  table_id VARCHAR(191) NULL,
  status VARCHAR(40) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  CONSTRAINT restaurant_bills_table_fk FOREIGN KEY (restaurant_id, table_id) REFERENCES restaurant_tables(restaurant_id, id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_orders (
  restaurant_id VARCHAR(191) NOT NULL,
  id VARCHAR(191) NOT NULL,
  table_id VARCHAR(191) NULL,
  bill_id VARCHAR(191) NULL,
  status VARCHAR(40) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME(3) NULL,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, id),
  KEY restaurant_orders_table_idx (restaurant_id, table_id, created_at),
  CONSTRAINT restaurant_orders_table_fk FOREIGN KEY (restaurant_id, table_id) REFERENCES restaurant_tables(restaurant_id, id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT restaurant_orders_bill_fk FOREIGN KEY (restaurant_id, bill_id) REFERENCES restaurant_bills(restaurant_id, id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_order_items (
  restaurant_id VARCHAR(191) NOT NULL,
  order_id VARCHAR(191) NOT NULL,
  position INT NOT NULL,
  product_id VARCHAR(191) NULL,
  quantity DECIMAL(12,3) NOT NULL DEFAULT 0,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, order_id, position),
  CONSTRAINT restaurant_order_items_order_fk FOREIGN KEY (restaurant_id, order_id) REFERENCES restaurant_orders(restaurant_id, id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_bill_orders (
  restaurant_id VARCHAR(191) NOT NULL,
  bill_id VARCHAR(191) NOT NULL,
  order_id VARCHAR(191) NOT NULL,
  position INT NOT NULL,
  PRIMARY KEY (restaurant_id, bill_id, order_id),
  CONSTRAINT restaurant_bill_orders_bill_fk FOREIGN KEY (restaurant_id, bill_id) REFERENCES restaurant_bills(restaurant_id, id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT restaurant_bill_orders_order_fk FOREIGN KEY (restaurant_id, order_id) REFERENCES restaurant_orders(restaurant_id, id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_records (
  restaurant_id VARCHAR(191) NOT NULL,
  kind VARCHAR(80) NOT NULL,
  id VARCHAR(191) NOT NULL,
  data LONGTEXT NOT NULL,
  PRIMARY KEY (restaurant_id, kind, id),
  KEY restaurant_records_kind_idx (restaurant_id, kind),
  CONSTRAINT restaurant_records_restaurant_fk FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;