template-saas-database-v2/ddl/schemas/portfolio/02-tables.sql

-- ============================================
-- TEMPLATE-SAAS: Portfolio Tables
-- Version: 1.0.0
-- Module: SAAS-019
-- ============================================

-- ============================================
-- Categories (hierarchical product categories)
-- ============================================
CREATE TABLE portfolio.categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Hierarchy
    parent_id UUID REFERENCES portfolio.categories(id) ON DELETE SET NULL,

    -- Category info
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    description TEXT,

    -- Display
    position INT NOT NULL DEFAULT 0,
    image_url VARCHAR(500),
    color VARCHAR(7) DEFAULT '#3B82F6',
    icon VARCHAR(50),

    -- Status
    is_active BOOLEAN DEFAULT TRUE,

    -- SEO
    meta_title VARCHAR(200),
    meta_description TEXT,

    -- Custom fields
    custom_fields JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID REFERENCES users.users(id) ON DELETE SET NULL,
    deleted_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT unique_category_slug UNIQUE (tenant_id, slug),
    CONSTRAINT unique_category_position UNIQUE (tenant_id, parent_id, position)
);

COMMENT ON TABLE portfolio.categories IS 'Hierarchical product categories per tenant';
COMMENT ON COLUMN portfolio.categories.parent_id IS 'Parent category for hierarchical structure';

-- ============================================
-- Products (main product catalog)
-- ============================================
CREATE TABLE portfolio.products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Category reference
    category_id UUID REFERENCES portfolio.categories(id) ON DELETE SET NULL,

    -- Basic info
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(280) NOT NULL,
    sku VARCHAR(100),
    barcode VARCHAR(100),
    description TEXT,
    short_description VARCHAR(500),

    -- Product type
    product_type portfolio.product_type DEFAULT 'physical' NOT NULL,
    status portfolio.product_status DEFAULT 'draft' NOT NULL,

    -- Pricing (base price, can be overridden by variants/prices)
    base_price DECIMAL(15, 2) DEFAULT 0,
    cost_price DECIMAL(15, 2),
    compare_at_price DECIMAL(15, 2),
    currency VARCHAR(3) DEFAULT 'USD',

    -- Inventory
    track_inventory BOOLEAN DEFAULT TRUE,
    stock_quantity INT DEFAULT 0,
    low_stock_threshold INT DEFAULT 5,
    allow_backorder BOOLEAN DEFAULT FALSE,

    -- Physical properties
    weight DECIMAL(10, 3),
    weight_unit VARCHAR(10) DEFAULT 'kg',
    length DECIMAL(10, 2),
    width DECIMAL(10, 2),
    height DECIMAL(10, 2),
    dimension_unit VARCHAR(10) DEFAULT 'cm',

    -- Media
    images JSONB DEFAULT '[]'::jsonb,
    featured_image_url VARCHAR(500),

    -- SEO
    meta_title VARCHAR(200),
    meta_description TEXT,
    tags JSONB DEFAULT '[]'::jsonb,

    -- Visibility
    is_visible BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,

    -- Attributes for variants
    has_variants BOOLEAN DEFAULT FALSE,
    variant_attributes JSONB DEFAULT '[]'::jsonb,

    -- Custom fields
    custom_fields JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID REFERENCES users.users(id) ON DELETE SET NULL,
    published_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT unique_product_slug UNIQUE (tenant_id, slug),
    CONSTRAINT unique_product_sku UNIQUE (tenant_id, sku),
    CONSTRAINT check_prices CHECK (base_price >= 0 AND (cost_price IS NULL OR cost_price >= 0))
);

COMMENT ON TABLE portfolio.products IS 'Product catalog with multi-tenant support';
COMMENT ON COLUMN portfolio.products.images IS 'JSON array of image URLs';
COMMENT ON COLUMN portfolio.products.variant_attributes IS 'JSON array of attribute names used for variants';

-- ============================================
-- Variants (product variants: size, color, etc.)
-- ============================================
CREATE TABLE portfolio.variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES portfolio.products(id) ON DELETE CASCADE,

    -- Variant identification
    sku VARCHAR(100),
    barcode VARCHAR(100),
    name VARCHAR(255),

    -- Attributes (e.g., {"color": "red", "size": "L"})
    attributes JSONB DEFAULT '{}'::jsonb NOT NULL,

    -- Pricing
    price DECIMAL(15, 2),
    cost_price DECIMAL(15, 2),
    compare_at_price DECIMAL(15, 2),

    -- Inventory
    stock_quantity INT DEFAULT 0,
    low_stock_threshold INT,

    -- Physical properties (can override product)
    weight DECIMAL(10, 3),

    -- Media
    image_url VARCHAR(500),

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    position INT DEFAULT 0,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    deleted_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT unique_variant_sku UNIQUE (tenant_id, sku),
    CONSTRAINT check_variant_prices CHECK (price IS NULL OR price >= 0)
);

COMMENT ON TABLE portfolio.variants IS 'Product variants (size, color, etc.)';
COMMENT ON COLUMN portfolio.variants.attributes IS 'JSON object with attribute key-value pairs';

-- ============================================
-- Prices (multi-currency pricing)
-- ============================================
CREATE TABLE portfolio.prices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Reference (either product or variant)
    product_id UUID REFERENCES portfolio.products(id) ON DELETE CASCADE,
    variant_id UUID REFERENCES portfolio.variants(id) ON DELETE CASCADE,

    -- Pricing
    price_type portfolio.price_type DEFAULT 'one_time' NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    amount DECIMAL(15, 2) NOT NULL,
    compare_at_amount DECIMAL(15, 2),

    -- Recurring pricing (for subscriptions)
    billing_period VARCHAR(20),
    billing_interval INT,

    -- Tiered pricing
    min_quantity INT DEFAULT 1,
    max_quantity INT,

    -- Validity
    valid_from TIMESTAMPTZ,
    valid_until TIMESTAMPTZ,

    -- Priority (for overlapping prices)
    priority INT DEFAULT 0,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    deleted_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT check_price_reference CHECK (
        (product_id IS NOT NULL AND variant_id IS NULL) OR
        (product_id IS NULL AND variant_id IS NOT NULL)
    ),
    CONSTRAINT check_amount CHECK (amount >= 0),
    CONSTRAINT check_quantity_range CHECK (
        min_quantity > 0 AND (max_quantity IS NULL OR max_quantity >= min_quantity)
    )
);

COMMENT ON TABLE portfolio.prices IS 'Multi-currency pricing for products and variants';
COMMENT ON COLUMN portfolio.prices.billing_period IS 'day, week, month, year for recurring prices';

-- ============================================
-- Triggers for updated_at
-- ============================================
CREATE OR REPLACE FUNCTION portfolio.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_categories_updated_at
    BEFORE UPDATE ON portfolio.categories
    FOR EACH ROW
    EXECUTE FUNCTION portfolio.update_updated_at();

CREATE TRIGGER trg_products_updated_at
    BEFORE UPDATE ON portfolio.products
    FOR EACH ROW
    EXECUTE FUNCTION portfolio.update_updated_at();

CREATE TRIGGER trg_variants_updated_at
    BEFORE UPDATE ON portfolio.variants
    FOR EACH ROW
    EXECUTE FUNCTION portfolio.update_updated_at();

CREATE TRIGGER trg_prices_updated_at
    BEFORE UPDATE ON portfolio.prices
    FOR EACH ROW
    EXECUTE FUNCTION portfolio.update_updated_at();