94 lines
2.7 KiB
PL/PgSQL
94 lines
2.7 KiB
PL/PgSQL
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Create enum for categories
|
|
CREATE TYPE product_category AS ENUM (
|
|
'food',
|
|
'beverages',
|
|
'household',
|
|
'personal_care',
|
|
'clothing',
|
|
'electronics',
|
|
'other'
|
|
);
|
|
|
|
-- Products table
|
|
CREATE TABLE products (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
company TEXT NOT NULL,
|
|
country_of_origin TEXT NOT NULL,
|
|
category product_category NOT NULL,
|
|
image_url TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
|
);
|
|
|
|
-- Alternatives table
|
|
CREATE TABLE alternatives (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
product_id UUID REFERENCES products(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
company TEXT NOT NULL,
|
|
category product_category NOT NULL,
|
|
image_url TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
|
);
|
|
|
|
-- Opportunities table
|
|
CREATE TABLE opportunities (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
category product_category NOT NULL,
|
|
description TEXT NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
|
);
|
|
|
|
-- Create updated_at trigger function
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Add triggers for updated_at
|
|
CREATE TRIGGER update_products_updated_at
|
|
BEFORE UPDATE ON products
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_alternatives_updated_at
|
|
BEFORE UPDATE ON alternatives
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_opportunities_updated_at
|
|
BEFORE UPDATE ON opportunities
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Enable Row Level Security
|
|
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE alternatives ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE opportunities ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create policies
|
|
CREATE POLICY "Enable read access for all users" ON products
|
|
FOR SELECT USING (true);
|
|
|
|
CREATE POLICY "Enable read access for all users" ON alternatives
|
|
FOR SELECT USING (true);
|
|
|
|
CREATE POLICY "Enable read access for all users" ON opportunities
|
|
FOR SELECT USING (true);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_products_category ON products(category);
|
|
CREATE INDEX idx_alternatives_product_id ON alternatives(product_id);
|
|
CREATE INDEX idx_alternatives_category ON alternatives(category);
|
|
CREATE INDEX idx_opportunities_category ON opportunities(category); |