WebOrbiton
v1.0.0.1

StocketBase

← Changes All versions
218 lines · 9.7 KB
  1. SET NAMES utf8mb4;
  2. SET time_zone = '+00:00';
  3. SET FOREIGN_KEY_CHECKS = 0;
  4. ​
  5. CREATE TABLE IF NOT EXISTS team_accounts (
  6. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  7. username VARCHAR(64) NOT NULL,
  8. email VARCHAR(190) NULL,
  9. password_hash VARCHAR(255) NOT NULL,
  10. display_name VARCHAR(120) NOT NULL,
  11. role ENUM('super_admin','store_owner','store_manager','catalog_editor','product_editor','catalog_assistant') NOT NULL,
  12. avatar_path VARCHAR(255) NULL,
  13. bio TEXT NULL,
  14. totp_secret VARCHAR(64) NULL,
  15. totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
  16. status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  17. last_login_at DATETIME NULL,
  18. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  19. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  20. UNIQUE KEY uq_team_username (username),
  21. UNIQUE KEY uq_team_email (email)
  22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  23. ​
  24. CREATE TABLE IF NOT EXISTS team_sessions (
  25. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  26. team_account_id INT UNSIGNED NOT NULL,
  27. session_token VARCHAR(128) NOT NULL,
  28. ip_address VARCHAR(64) NULL,
  29. user_agent VARCHAR(255) NULL,
  30. expires_at DATETIME NOT NULL,
  31. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  32. UNIQUE KEY uq_team_session_token (session_token),
  33. KEY idx_team_session_account (team_account_id),
  34. CONSTRAINT fk_team_session_account FOREIGN KEY (team_account_id) REFERENCES team_accounts(id) ON DELETE CASCADE
  35. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  36. ​
  37. CREATE TABLE IF NOT EXISTS categories (
  38. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  39. name VARCHAR(120) NOT NULL,
  40. slug VARCHAR(140) NOT NULL,
  41. description VARCHAR(255) NULL,
  42. parent_id INT UNSIGNED NULL,
  43. sort_order INT NOT NULL DEFAULT 0,
  44. show_in_menu TINYINT(1) NOT NULL DEFAULT 1,
  45. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  46. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  47. UNIQUE KEY uq_category_slug (slug),
  48. KEY idx_category_parent (parent_id),
  49. CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
  50. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  51. ​
  52. CREATE TABLE IF NOT EXISTS products (
  53. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  54. created_by INT UNSIGNED NULL,
  55. category_id INT UNSIGNED NULL,
  56. title VARCHAR(255) NOT NULL,
  57. slug VARCHAR(255) NOT NULL,
  58. excerpt VARCHAR(500) NULL,
  59. content_blocks LONGTEXT NOT NULL,
  60. cover_image_path VARCHAR(255) NULL,
  61. sku VARCHAR(64) NULL,
  62. product_kind ENUM('physical','digital','saas_plan') NOT NULL DEFAULT 'physical',
  63. price_cents INT UNSIGNED NULL,
  64. compare_at_price_cents INT UNSIGNED NULL,
  65. currency CHAR(3) NULL,
  66. billing_interval ENUM('month','year','one_time') NULL,
  67. track_inventory TINYINT(1) NOT NULL DEFAULT 0,
  68. stock_quantity INT NULL,
  69. weight_grams INT NULL,
  70. stripe_price_id VARCHAR(190) NULL,
  71. polar_product_id VARCHAR(190) NULL,
  72. sales_count INT UNSIGNED NOT NULL DEFAULT 0,
  73. status ENUM('draft','pending_review','scheduled','published','rejected','archived') NOT NULL DEFAULT 'draft',
  74. rejection_reason VARCHAR(500) NULL,
  75. seo_title VARCHAR(255) NULL,
  76. seo_description VARCHAR(500) NULL,
  77. views_count INT UNSIGNED NOT NULL DEFAULT 0,
  78. published_at DATETIME NULL,
  79. scheduled_at DATETIME NULL,
  80. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  81. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  82. UNIQUE KEY uq_product_slug (slug),
  83. KEY idx_product_author (created_by),
  84. KEY idx_product_category (category_id),
  85. KEY idx_product_status (status),
  86. KEY idx_product_published (published_at),
  87. CONSTRAINT fk_product_author FOREIGN KEY (created_by) REFERENCES team_accounts(id) ON DELETE SET NULL,
  88. CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
  89. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  90. ​
  91. CREATE TABLE IF NOT EXISTS product_variants (
  92. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  93. product_id INT UNSIGNED NOT NULL,
  94. name VARCHAR(120) NOT NULL,
  95. sku VARCHAR(64) NULL,
  96. price_cents INT UNSIGNED NULL,
  97. stock_quantity INT NULL,
  98. sort_order INT NOT NULL DEFAULT 0,
  99. is_default TINYINT(1) NOT NULL DEFAULT 0,
  100. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  101. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  102. KEY idx_variant_product (product_id),
  103. CONSTRAINT fk_variant_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
  104. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  105. ​
  106. CREATE TABLE IF NOT EXISTS product_images (
  107. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  108. product_id INT UNSIGNED NOT NULL,
  109. image_path VARCHAR(255) NOT NULL,
  110. alt_text VARCHAR(255) NULL,
  111. sort_order INT NOT NULL DEFAULT 0,
  112. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  113. KEY idx_product_image_product (product_id),
  114. CONSTRAINT fk_product_image_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
  115. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  116. ​
  117. CREATE TABLE IF NOT EXISTS pages (
  118. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  119. author_id INT UNSIGNED NULL,
  120. title VARCHAR(255) NOT NULL,
  121. slug VARCHAR(255) NOT NULL,
  122. content_blocks LONGTEXT NOT NULL,
  123. status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  124. show_in_menu TINYINT(1) NOT NULL DEFAULT 0,
  125. sort_order INT NOT NULL DEFAULT 0,
  126. is_home TINYINT(1) NOT NULL DEFAULT 0,
  127. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  128. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  129. UNIQUE KEY uq_page_slug (slug),
  130. CONSTRAINT fk_page_author FOREIGN KEY (author_id) REFERENCES team_accounts(id) ON DELETE SET NULL
  131. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  132. ​
  133. CREATE TABLE IF NOT EXISTS site_settings (
  134. setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
  135. setting_value LONGTEXT NULL,
  136. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  137. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  138. ​
  139. CREATE TABLE IF NOT EXISTS ads (
  140. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  141. name VARCHAR(120) NOT NULL,
  142. type ENUM('static_text','static_image','script') NOT NULL,
  143. placement VARCHAR(60) NOT NULL,
  144. content LONGTEXT NOT NULL,
  145. link_url VARCHAR(255) NULL,
  146. is_active TINYINT(1) NOT NULL DEFAULT 1,
  147. created_by INT UNSIGNED NULL,
  148. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  149. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  150. CONSTRAINT fk_ad_creator FOREIGN KEY (created_by) REFERENCES team_accounts(id) ON DELETE SET NULL
  151. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  152. ​
  153. CREATE TABLE IF NOT EXISTS analytics_scripts (
  154. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  155. name VARCHAR(120) NOT NULL,
  156. script_code LONGTEXT NOT NULL,
  157. placement ENUM('head','body_start','body_end') NOT NULL DEFAULT 'head',
  158. requires_consent TINYINT(1) NOT NULL DEFAULT 1,
  159. is_active TINYINT(1) NOT NULL DEFAULT 1,
  160. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  161. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  162. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  163. ​
  164. CREATE TABLE IF NOT EXISTS nav_items (
  165. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  166. item_type ENUM('category','page','custom') NOT NULL,
  167. ref_id INT UNSIGNED NULL,
  168. label VARCHAR(120) NULL,
  169. url VARCHAR(255) NULL,
  170. target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
  171. icon_key VARCHAR(30) NULL,
  172. icon_svg MEDIUMTEXT NULL,
  173. sort_order INT NOT NULL DEFAULT 0,
  174. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  175. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  176. KEY idx_nav_sort (sort_order)
  177. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  178. ​
  179. CREATE TABLE IF NOT EXISTS discounts (
  180. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  181. code VARCHAR(60) NOT NULL,
  182. description VARCHAR(255) NULL,
  183. discount_type ENUM('percentage','fixed_amount') NOT NULL,
  184. value INT UNSIGNED NOT NULL,
  185. currency CHAR(3) NULL,
  186. min_order_cents INT UNSIGNED NULL,
  187. max_redemptions INT UNSIGNED NULL,
  188. times_redeemed INT UNSIGNED NOT NULL DEFAULT 0,
  189. starts_at DATETIME NULL,
  190. ends_at DATETIME NULL,
  191. is_active TINYINT(1) NOT NULL DEFAULT 1,
  192. created_by INT UNSIGNED NULL,
  193. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  194. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  195. UNIQUE KEY uq_discount_code (code),
  196. CONSTRAINT fk_discount_creator FOREIGN KEY (created_by) REFERENCES team_accounts(id) ON DELETE SET NULL
  197. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  198. ​
  199. CREATE TABLE IF NOT EXISTS product_files (
  200. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  201. product_id INT UNSIGNED NOT NULL,
  202. original_name VARCHAR(190) NOT NULL,
  203. stored_name CHAR(32) NOT NULL,
  204. mime_type VARCHAR(120) NOT NULL DEFAULT 'application/octet-stream',
  205. size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  206. sha256 CHAR(64) NULL,
  207. sort_order INT NOT NULL DEFAULT 0,
  208. product_title VARCHAR(255) NULL,
  209. detached_at DATETIME NULL,
  210. button_label VARCHAR(120) NULL,
  211. download_count INT UNSIGNED NOT NULL DEFAULT 0,
  212. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  213. UNIQUE KEY uq_product_file_stored (stored_name),
  214. KEY idx_product_files_product (product_id)
  215. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  216. ​
  217. SET FOREIGN_KEY_CHECKS = 1;
  218. ​