USE db_abos_v0.1; -- ============================================================================= -- Web module — Store (Phase A of the Webflow/Shopify-style Website upgrade). -- -- New, self-contained schema for storefront commerce: catalog (products + -- variants), storefront customers, carts, and orders. Deliberately NOT reusing -- the back-office `inv_products`/`fact_orders` tables — those have no -- site/business scoping, no images/slug/SEO, no variants, and no order-line -- model, so mixing them with public storefront data would conflate two -- different concerns. Follows the exact tenant/business/site/audit column -- convention already used by dim_web_sites/dim_web_collections/fact_web_entries -- (see projects_foundation_refactor.sql "10-14"). -- -- Safe to re-run: CREATE TABLE IF NOT EXISTS throughout. -- ============================================================================= -- ── 1. dim_web_products ────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS dim_web_products ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, business_id INT NULL, site_id BIGINT NOT NULL, slug VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, description TEXT NULL, images_json JSON NULL, status VARCHAR(20) NOT NULL DEFAULT 'draft', category_key VARCHAR(100) NULL, currency VARCHAR(10) NOT NULL DEFAULT 'USD', base_price DECIMAL(15,2) NULL, meta_title VARCHAR(255) NULL, meta_description VARCHAR(500) NULL, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), created_by INT NULL, updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), updated_by INT NULL, is_deleted TINYINT NOT NULL DEFAULT 0, UNIQUE KEY uq_dim_web_products_slug (site_id, slug), INDEX idx_dim_web_products_tenant (tenant_id), INDEX idx_dim_web_products_public (site_id, status, is_deleted, sort_order), INDEX idx_dim_web_products_business (tenant_id, business_id, is_deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── 2. dim_web_product_variants ────────────────────────────────────────────── -- One product may have many purchasable variants (size/color/etc, each with -- its own SKU/price/stock). A product with no real options still gets exactly -- one variant row (is_default=1) so checkout/cart/order-item logic always -- operates on a variant_id, never a bare product_id. CREATE TABLE IF NOT EXISTS dim_web_product_variants ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, business_id INT NULL, site_id BIGINT NOT NULL, product_id BIGINT NOT NULL, sku VARCHAR(100) NOT NULL, option_values_json JSON NULL, price DECIMAL(15,2) NOT NULL, compare_at_price DECIMAL(15,2) NULL, stock_qty INT NOT NULL DEFAULT 0, is_default TINYINT NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), created_by INT NULL, updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), updated_by INT NULL, is_deleted TINYINT NOT NULL DEFAULT 0, UNIQUE KEY uq_dim_web_product_variants_sku (site_id, sku), INDEX idx_dim_web_product_variants_tenant (tenant_id), INDEX idx_dim_web_product_variants_product (product_id, is_deleted, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── 3. dim_web_customers ───────────────────────────────────────────────────── -- Storefront customers — deliberately separate from internal `users` (a -- storefront customer never logs into ABOS itself). CREATE TABLE IF NOT EXISTS dim_web_customers ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, business_id INT NULL, site_id BIGINT NOT NULL, email VARCHAR(255) NOT NULL, first_name VARCHAR(150) NULL, last_name VARCHAR(150) NULL, phone VARCHAR(50) NULL, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), created_by INT NULL, updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), updated_by INT NULL, is_deleted TINYINT NOT NULL DEFAULT 0, UNIQUE KEY uq_dim_web_customers_email (site_id, email), INDEX idx_dim_web_customers_tenant (tenant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── 4. fact_web_carts ──────────────────────────────────────────────────────── -- cart_token is the credential an anonymous storefront visitor's browser holds -- (cookie), the same "unguessable public credential" shape as -- dim_web_sites.public_site_key — never the numeric id. CREATE TABLE IF NOT EXISTS fact_web_carts ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, business_id INT NULL, site_id BIGINT NOT NULL, customer_id BIGINT NULL, cart_token CHAR(48) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'open', currency VARCHAR(10) NOT NULL DEFAULT 'USD', created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), is_deleted TINYINT NOT NULL DEFAULT 0, UNIQUE KEY uq_fact_web_carts_token (cart_token), INDEX idx_fact_web_carts_tenant (tenant_id), INDEX idx_fact_web_carts_site (site_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── 5. fact_web_cart_items ─────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS fact_web_cart_items ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, cart_id BIGINT NOT NULL, variant_id BIGINT NOT NULL, quantity INT NOT NULL DEFAULT 1, unit_price DECIMAL(15,2) NOT NULL, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE KEY uq_fact_web_cart_items (cart_id, variant_id), INDEX idx_fact_web_cart_items_tenant (tenant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── 6. fact_web_orders ─────────────────────────────────────────────────────── -- status mirrors fact_orders' vocabulary (Pending/Processing/Shipped/ -- Delivered/Cancelled) for consistency; payment_status is a separate axis -- since a Shipped order and an unpaid order are different failure modes. CREATE TABLE IF NOT EXISTS fact_web_orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, business_id INT NULL, site_id BIGINT NOT NULL, customer_id BIGINT NULL, order_no VARCHAR(50) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'Pending', payment_status VARCHAR(20) NOT NULL DEFAULT 'Unpaid', currency VARCHAR(10) NOT NULL DEFAULT 'USD', subtotal_amount DECIMAL(15,2) NOT NULL DEFAULT 0, tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0, shipping_amount DECIMAL(15,2) NOT NULL DEFAULT 0, discount_amount DECIMAL(15,2) NOT NULL DEFAULT 0, total_amount DECIMAL(15,2) NOT NULL DEFAULT 0, customer_email VARCHAR(255) NOT NULL, shipping_name VARCHAR(255) NULL, shipping_address VARCHAR(500) NULL, shipping_city VARCHAR(150) NULL, shipping_state VARCHAR(150) NULL, shipping_postal_code VARCHAR(30) NULL, shipping_country VARCHAR(100) NULL, stripe_checkout_session_id VARCHAR(255) NULL, stripe_payment_intent_id VARCHAR(255) NULL, notes TEXT NULL, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), created_by INT NULL, updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), updated_by INT NULL, is_deleted TINYINT NOT NULL DEFAULT 0, UNIQUE KEY uq_fact_web_orders_no (site_id, order_no), INDEX idx_fact_web_orders_tenant (tenant_id), INDEX idx_fact_web_orders_site (site_id, status, is_deleted), INDEX idx_fact_web_orders_business (tenant_id, business_id, is_deleted), INDEX idx_fact_web_orders_stripe_session (stripe_checkout_session_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── 7. fact_web_order_items ────────────────────────────────────────────────── -- product_name/sku/unit_price are snapshotted at purchase time — an order -- must never change retroactively because a product/variant was edited or -- deleted later. CREATE TABLE IF NOT EXISTS fact_web_order_items ( id BIGINT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, order_id BIGINT NOT NULL, variant_id BIGINT NULL, product_name VARCHAR(255) NOT NULL, sku VARCHAR(100) NOT NULL, unit_price DECIMAL(15,2) NOT NULL, quantity INT NOT NULL, line_total DECIMAL(15,2) NOT NULL, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), INDEX idx_fact_web_order_items_tenant (tenant_id), INDEX idx_fact_web_order_items_order (order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;