WebOrbiton
v1.0.0.0

StocketBase

← Changes All versions
217 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 NOT 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 CASCADE,
  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 NOT 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. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  127. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  128. UNIQUE KEY uq_page_slug (slug),
  129. CONSTRAINT fk_page_author FOREIGN KEY (author_id) REFERENCES team_accounts(id) ON DELETE CASCADE
  130. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  131. ​
  132. CREATE TABLE IF NOT EXISTS site_settings (
  133. setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
  134. setting_value LONGTEXT NULL,
  135. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  136. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  137. ​
  138. CREATE TABLE IF NOT EXISTS ads (
  139. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  140. name VARCHAR(120) NOT NULL,
  141. type ENUM('static_text','static_image','script') NOT NULL,
  142. placement VARCHAR(60) NOT NULL,
  143. content LONGTEXT NOT NULL,
  144. link_url VARCHAR(255) NULL,
  145. is_active TINYINT(1) NOT NULL DEFAULT 1,
  146. created_by INT UNSIGNED NOT NULL,
  147. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  148. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  149. CONSTRAINT fk_ad_creator FOREIGN KEY (created_by) REFERENCES team_accounts(id) ON DELETE CASCADE
  150. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  151. ​
  152. CREATE TABLE IF NOT EXISTS analytics_scripts (
  153. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  154. name VARCHAR(120) NOT NULL,
  155. script_code LONGTEXT NOT NULL,
  156. placement ENUM('head','body_start','body_end') NOT NULL DEFAULT 'head',
  157. requires_consent TINYINT(1) NOT NULL DEFAULT 1,
  158. is_active TINYINT(1) NOT NULL DEFAULT 1,
  159. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  160. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  161. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  162. ​
  163. CREATE TABLE IF NOT EXISTS nav_items (
  164. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  165. item_type ENUM('category','page','custom') NOT NULL,
  166. ref_id INT UNSIGNED NULL,
  167. label VARCHAR(120) NULL,
  168. url VARCHAR(255) NULL,
  169. target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
  170. icon_key VARCHAR(30) NULL,
  171. icon_svg MEDIUMTEXT NULL,
  172. sort_order INT NOT NULL DEFAULT 0,
  173. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  174. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  175. KEY idx_nav_sort (sort_order)
  176. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  177. ​
  178. CREATE TABLE IF NOT EXISTS discounts (
  179. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  180. code VARCHAR(60) NOT NULL,
  181. description VARCHAR(255) NULL,
  182. discount_type ENUM('percentage','fixed_amount') NOT NULL,
  183. value INT UNSIGNED NOT NULL,
  184. currency CHAR(3) NULL,
  185. min_order_cents INT UNSIGNED NULL,
  186. max_redemptions INT UNSIGNED NULL,
  187. times_redeemed INT UNSIGNED NOT NULL DEFAULT 0,
  188. starts_at DATETIME NULL,
  189. ends_at DATETIME NULL,
  190. is_active TINYINT(1) NOT NULL DEFAULT 1,
  191. created_by INT UNSIGNED NOT NULL,
  192. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  193. updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  194. UNIQUE KEY uq_discount_code (code),
  195. CONSTRAINT fk_discount_creator FOREIGN KEY (created_by) REFERENCES team_accounts(id) ON DELETE CASCADE
  196. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  197. ​
  198. CREATE TABLE IF NOT EXISTS product_files (
  199. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  200. product_id INT UNSIGNED NOT NULL,
  201. original_name VARCHAR(190) NOT NULL,
  202. stored_name CHAR(32) NOT NULL,
  203. mime_type VARCHAR(120) NOT NULL DEFAULT 'application/octet-stream',
  204. size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  205. sha256 CHAR(64) NULL,
  206. sort_order INT NOT NULL DEFAULT 0,
  207. product_title VARCHAR(255) NULL,
  208. detached_at DATETIME NULL,
  209. button_label VARCHAR(120) NULL,
  210. download_count INT UNSIGNED NOT NULL DEFAULT 0,
  211. created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  212. UNIQUE KEY uq_product_file_stored (stored_name),
  213. KEY idx_product_files_product (product_id)
  214. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  215. ​
  216. SET FOREIGN_KEY_CHECKS = 1;
  217. ​