products_schema_1.sql (11540B)
1 -- ============================================================ 2 -- PRODUCTS SCHEMA - PRODUCTS SERVICE 3 -- ============================================================ 4 -- Handles: Product catalog, categories, specifications, ratings 5 -- Schema: products 6 -- Port: 3001 7 -- 8 -- Updated: Added pricing, inventory, and hierarchical categories 9 -- to match UI requirements (ProductCard and Product interfaces) 10 11 -- Set search path to products schema 12 SET search_path TO products, public; 13 14 -- ============================================================ 15 -- CATEGORIES TABLE (Hierarchical) 16 -- ============================================================ 17 CREATE TABLE categories ( 18 id SERIAL PRIMARY KEY, 19 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 20 21 -- Category info 22 name VARCHAR(255) NOT NULL UNIQUE, 23 slug VARCHAR(255) NOT NULL UNIQUE, 24 description TEXT, 25 26 -- Hierarchical structure 27 parent_id INTEGER REFERENCES categories(id) ON DELETE CASCADE, 28 level INTEGER DEFAULT 0, -- 0 = root, 1 = child, 2 = grandchild, etc. 29 path TEXT, -- Materialized path: '1/23/45' for quick ancestor queries 30 31 -- Display order 32 sort_order INTEGER DEFAULT 0, 33 34 -- Timestamps 35 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 36 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 37 ); 38 39 CREATE INDEX idx_categories_uuid ON categories(uuid); 40 CREATE INDEX idx_categories_slug ON categories(slug); 41 CREATE INDEX idx_categories_parent_id ON categories(parent_id); 42 CREATE INDEX idx_categories_level ON categories(level); 43 CREATE INDEX idx_categories_path ON categories(path); 44 45 COMMENT ON TABLE categories IS 'Product categories with hierarchical support'; 46 COMMENT ON COLUMN categories.uuid IS 'External UUID (exposed as eid in API)'; 47 COMMENT ON COLUMN categories.parent_id IS 'Parent category for hierarchy (NULL for root)'; 48 COMMENT ON COLUMN categories.level IS 'Depth in hierarchy: 0=root, 1=subcategory, etc.'; 49 COMMENT ON COLUMN categories.path IS 'Materialized path for ancestor queries'; 50 51 -- ============================================================ 52 -- PRODUCTS TABLE (with pricing and inventory) 53 -- ============================================================ 54 CREATE TABLE products ( 55 id SERIAL PRIMARY KEY, 56 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 57 58 -- External IDs (from Amazon data) 59 product_id VARCHAR(50) NOT NULL UNIQUE, -- External product identifier (e.g., PROD-001) 60 asin VARCHAR(20) UNIQUE, 61 sku VARCHAR(100) UNIQUE, 62 gtin VARCHAR(50), 63 64 -- Basic information 65 product_name VARCHAR(500) NOT NULL, 66 brand VARCHAR(255), 67 description TEXT, 68 69 -- Category 70 category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL, 71 72 -- PRICING (denormalized from inventory service for initial development) 73 price DECIMAL(10, 2) NOT NULL DEFAULT 0, -- Current final price 74 initial_price DECIMAL(10, 2), -- Original price before discount 75 discount VARCHAR(10), -- Discount label: "20%", "$10 off", etc. 76 currency VARCHAR(10) DEFAULT 'USD', 77 78 -- INVENTORY (denormalized from inventory service for initial development) 79 stock_quantity INTEGER NOT NULL DEFAULT 0, -- Available stock 80 81 -- Product attributes (from Amazon) 82 sizes TEXT[], -- Array of sizes: ["S", "M", "L", "XL"] 83 colors TEXT[], -- Array of colors: ["Red", "Blue", "Green"] 84 85 -- URLs and media 86 url TEXT, 87 image_url TEXT, 88 89 -- Flags 90 available_for_delivery BOOLEAN DEFAULT true, 91 available_for_pickup BOOLEAN DEFAULT false, 92 free_returns BOOLEAN DEFAULT false, 93 is_active BOOLEAN DEFAULT true, 94 95 -- Timestamps 96 deleted_at TIMESTAMP WITH TIME ZONE, 97 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 98 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 99 data_timestamp TIMESTAMP WITH TIME ZONE 100 ); 101 102 CREATE INDEX idx_products_uuid ON products(uuid); 103 CREATE INDEX idx_products_product_id ON products(product_id); 104 CREATE INDEX idx_products_asin ON products(asin); 105 CREATE INDEX idx_products_sku ON products(sku); 106 CREATE INDEX idx_products_product_name ON products(product_name); 107 CREATE INDEX idx_products_category_id ON products(category_id); 108 CREATE INDEX idx_products_brand ON products(brand); 109 CREATE INDEX idx_products_price ON products(price); 110 CREATE INDEX idx_products_stock_quantity ON products(stock_quantity); 111 CREATE INDEX idx_products_is_active ON products(is_active); 112 CREATE INDEX idx_products_updated_at ON products(updated_at); 113 114 -- Full-text search index 115 CREATE INDEX idx_products_search ON products USING GIN( 116 to_tsvector('english', product_name || ' ' || COALESCE(description, '') || ' ' || COALESCE(brand, '')) 117 ); 118 119 COMMENT ON TABLE products IS 'Core product catalog with pricing and inventory'; 120 COMMENT ON COLUMN products.uuid IS 'External UUID (exposed as eid in API)'; 121 COMMENT ON COLUMN products.asin IS 'Amazon Standard Identification Number'; 122 COMMENT ON COLUMN products.price IS 'Current selling price (denormalized from inventory)'; 123 COMMENT ON COLUMN products.initial_price IS 'Original price before discount'; 124 COMMENT ON COLUMN products.discount IS 'Discount label for UI display'; 125 COMMENT ON COLUMN products.stock_quantity IS 'Available inventory (denormalized)'; 126 127 -- ============================================================ 128 -- RATINGS TABLE (simple user ratings) 129 -- ============================================================ 130 CREATE TABLE ratings ( 131 id SERIAL PRIMARY KEY, 132 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 133 product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE, 134 135 -- User reference (from users schema) 136 user_id UUID NOT NULL, 137 138 -- Rating (1-5 stars) 139 rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5), 140 141 -- Optional review text 142 review TEXT, 143 144 -- Timestamps 145 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 146 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 147 148 -- One rating per user per product 149 UNIQUE(product_id, user_id) 150 ); 151 152 CREATE INDEX idx_ratings_uuid ON ratings(uuid); 153 CREATE INDEX idx_ratings_product_id ON ratings(product_id); 154 CREATE INDEX idx_ratings_user_id ON ratings(user_id); 155 CREATE INDEX idx_ratings_rating ON ratings(rating); 156 157 COMMENT ON TABLE ratings IS 'User product ratings (1-5 stars)'; 158 COMMENT ON COLUMN ratings.user_id IS 'References users.uuid from users schema'; 159 COMMENT ON COLUMN ratings.rating IS 'Star rating: 1 (poor) to 5 (excellent)'; 160 161 -- ============================================================ 162 -- PRODUCT_SPECIFICATIONS TABLE (for detailed specs) 163 -- ============================================================ 164 CREATE TABLE product_specifications ( 165 id SERIAL PRIMARY KEY, 166 product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE, 167 spec_name VARCHAR(255) NOT NULL, 168 spec_value TEXT NOT NULL, 169 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 170 ); 171 172 CREATE INDEX idx_product_specs_product_id ON product_specifications(product_id); 173 174 COMMENT ON TABLE product_specifications IS 'Product technical specifications'; 175 176 -- ============================================================ 177 -- TRIGGERS FOR UPDATED_AT 178 -- ============================================================ 179 180 CREATE OR REPLACE FUNCTION update_updated_at_column() 181 RETURNS TRIGGER AS $$ 182 BEGIN 183 NEW.updated_at = CURRENT_TIMESTAMP; 184 RETURN NEW; 185 END; 186 $$ language 'plpgsql'; 187 188 CREATE TRIGGER update_categories_updated_at BEFORE UPDATE ON categories 189 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 190 191 CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products 192 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 193 194 CREATE TRIGGER update_ratings_updated_at BEFORE UPDATE ON ratings 195 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 196 197 -- ============================================================ 198 -- HELPER FUNCTIONS 199 -- ============================================================ 200 201 -- Function to get root category name for a product 202 CREATE OR REPLACE FUNCTION get_root_category_name(cat_id INTEGER) 203 RETURNS TEXT AS $$ 204 DECLARE 205 root_name TEXT; 206 BEGIN 207 WITH RECURSIVE category_tree AS ( 208 -- Start with the given category 209 SELECT id, name, parent_id, 0 as depth 210 FROM categories 211 WHERE id = cat_id 212 213 UNION ALL 214 215 -- Recursively get parent categories 216 SELECT c.id, c.name, c.parent_id, ct.depth + 1 217 FROM categories c 218 INNER JOIN category_tree ct ON c.id = ct.parent_id 219 ) 220 SELECT name INTO root_name 221 FROM category_tree 222 ORDER BY depth DESC 223 LIMIT 1; 224 225 RETURN root_name; 226 END; 227 $$ LANGUAGE plpgsql; 228 229 COMMENT ON FUNCTION get_root_category_name IS 'Get the root category name for a given category'; 230 231 -- ============================================================ 232 -- SAMPLE DATA - ROOT CATEGORIES 233 -- ============================================================ 234 235 -- Insert root categories first 236 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) VALUES 237 ('Electronics', 'electronics', 'Electronic devices and accessories', NULL, 0, '1', 1), 238 ('Clothing', 'clothing', 'Apparel and fashion items', NULL, 0, '2', 2), 239 ('Home & Garden', 'home-garden', 'Home improvement and garden supplies', NULL, 0, '3', 3), 240 ('Sports & Outdoors', 'sports-outdoors', 'Sports equipment and outdoor gear', NULL, 0, '4', 4), 241 ('Books & Media', 'books-media', 'Books, movies, and media', NULL, 0, '5', 5), 242 ('Health & Beauty', 'health-beauty', 'Health and beauty products', NULL, 0, '6', 6), 243 ('Toys & Games', 'toys-games', 'Toys, games, and hobbies', NULL, 0, '7', 7), 244 ('Automotive', 'automotive', 'Auto parts and accessories', NULL, 0, '8', 8) 245 ON CONFLICT (name) DO NOTHING; 246 247 -- Insert subcategories 248 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) 249 SELECT 'Smartphones', 'smartphones', 'Mobile phones and smartphones', id, 1, '1/' || id::text, 1 250 FROM categories WHERE slug = 'electronics' 251 ON CONFLICT (name) DO NOTHING; 252 253 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) 254 SELECT 'Laptops', 'laptops', 'Laptop computers', id, 1, '1/' || id::text, 2 255 FROM categories WHERE slug = 'electronics' 256 ON CONFLICT (name) DO NOTHING; 257 258 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) 259 SELECT 'Accessories', 'accessories', 'Phone and computer accessories', id, 1, '1/' || id::text, 3 260 FROM categories WHERE slug = 'electronics' 261 ON CONFLICT (name) DO NOTHING; 262 263 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) 264 SELECT 'Smart Home', 'smart-home', 'Smart home devices', id, 1, '1/' || id::text, 4 265 FROM categories WHERE slug = 'electronics' 266 ON CONFLICT (name) DO NOTHING; 267 268 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) 269 SELECT 'Men''s Clothing', 'mens-clothing', 'Clothing for men', id, 1, '2/' || id::text, 1 270 FROM categories WHERE slug = 'clothing' 271 ON CONFLICT (name) DO NOTHING; 272 273 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) 274 SELECT 'Women''s Clothing', 'womens-clothing', 'Clothing for women', id, 1, '2/' || id::text, 2 275 FROM categories WHERE slug = 'clothing' 276 ON CONFLICT (name) DO NOTHING; 277 278 INSERT INTO categories (name, slug, description, parent_id, level, path, sort_order) 279 SELECT 'Shoes', 'shoes', 'Footwear', id, 1, '2/' || id::text, 3 280 FROM categories WHERE slug = 'clothing' 281 ON CONFLICT (name) DO NOTHING; 282 283 -- Note: Actual product data will be generated and imported separately