-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "ltree";

-- Create products table with array field for categories
CREATE TABLE IF NOT EXISTS products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  description TEXT,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  compare_at_price NUMERIC(10, 2) CHECK (compare_at_price >= 0),
  sku TEXT,
  inventory_quantity INTEGER NOT NULL DEFAULT 0 CHECK (inventory_quantity >= 0),
  is_active BOOLEAN NOT NULL DEFAULT true,
  image_1 TEXT,
  image_2 TEXT,
  image_3 TEXT,
  image_4 TEXT,
  image_5 TEXT,
  price_id TEXT,
  categories TEXT[] DEFAULT '{}', -- Store categories as array instead of separate table
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Create categories table
CREATE TABLE IF NOT EXISTS categories (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  description TEXT,
  parent_id UUID REFERENCES categories(id), -- For hierarchical categories
  path ltree, -- Using ltree for hierarchical queries
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Create cart_items table
CREATE TABLE IF NOT EXISTS cart_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID, -- Can be null for anonymous users
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
  added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Create index for faster cart queries
CREATE INDEX IF NOT EXISTS cart_items_user_id_idx ON cart_items(user_id);
CREATE INDEX IF NOT EXISTS cart_items_product_id_idx ON cart_items(product_id);

-- Create index for faster product category queries
CREATE INDEX IF NOT EXISTS products_categories_idx ON products USING gin (categories);

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = now();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for updated_at
CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();

CREATE TRIGGER update_categories_updated_at
BEFORE UPDATE ON categories
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();

-- Create RLS policies for products
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Anyone can view active products
CREATE POLICY "Anyone can view active products" 
  ON products FOR SELECT 
  USING (is_active = true);

-- Only authenticated users with appropriate role can insert/update products
CREATE POLICY "Authenticated users can manage products" 
  ON products FOR ALL 
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- Create RLS policies for cart_items
ALTER TABLE cart_items ENABLE ROW LEVEL SECURITY;

-- Users can view their own cart items (including anonymous users)
CREATE POLICY "Users can view their own cart items" 
  ON cart_items FOR SELECT 
  USING (user_id = auth.uid() OR user_id::text = coalesce(current_setting('request.headers.x-anonymous-id', true), ''));

-- Users can insert their own cart items (including anonymous users)
CREATE POLICY "Users can insert their own cart items" 
  ON cart_items FOR INSERT 
  WITH CHECK (user_id = auth.uid() OR user_id::text = coalesce(current_setting('request.headers.x-anonymous-id', true), ''));

-- Users can update their own cart items (including anonymous users)
CREATE POLICY "Users can update their own cart items" 
  ON cart_items FOR UPDATE 
  USING (user_id = auth.uid() OR user_id::text = coalesce(current_setting('request.headers.x-anonymous-id', true), ''));

-- Users can delete their own cart items (including anonymous users)
CREATE POLICY "Users can delete their own cart items" 
  ON cart_items FOR DELETE 
  USING (user_id = auth.uid() OR user_id::text = coalesce(current_setting('request.headers.x-anonymous-id', true), ''));

-- Create RLS policies for categories
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;

-- Anyone can view categories
CREATE POLICY "Anyone can view categories" 
  ON categories FOR SELECT 
  USING (true);

-- Only authenticated users with appropriate role can modify categories
CREATE POLICY "Authenticated users can manage categories" 
  ON categories FOR ALL 
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- Function to update product categories
CREATE OR REPLACE FUNCTION update_product_categories(
  product_id UUID,
  category_ids UUID[]
)
RETURNS VOID AS $$
DECLARE
  category_names TEXT[];
BEGIN
  -- Get the category names from the ids
  SELECT array_agg(name) INTO category_names
  FROM categories
  WHERE id = ANY(category_ids);
  
  -- Update the product's categories array
  UPDATE products
  SET categories = category_names
  WHERE id = product_id;
END;
$$ LANGUAGE plpgsql;

-- Populate some initial categories for testing
INSERT INTO categories (name, slug, description, path)
VALUES 
  ('Electronics', 'electronics', 'Electronic devices', 'electronics'),
  ('Clothing', 'clothing', 'Apparel and accessories', 'clothing'),
  ('Home & Kitchen', 'home-kitchen', 'Products for home and kitchen', 'home_kitchen'),
  ('Books', 'books', 'Books and publications', 'books'),
  ('Sports', 'sports', 'Sports equipment and accessories', 'sports')
ON CONFLICT (name) DO NOTHING;

-- Drop existing policies on cart_items table if they exist
DROP POLICY IF EXISTS "Users can view their own cart items" ON cart_items;
DROP POLICY IF EXISTS "Anyone can insert cart items" ON cart_items;
DROP POLICY IF EXISTS "Users can update their own cart items" ON cart_items;
DROP POLICY IF EXISTS "Users can delete their own cart items" ON cart_items;

-- Create improved policies for cart_items table that better support anonymous users
-- Allow anyone to view their own cart items (authenticated or anonymous)
CREATE POLICY "Users can view their own cart items"
  ON cart_items FOR SELECT USING (
    user_id = auth.uid() OR 
    (auth.role() IS NULL AND user_id = (current_setting('request.headers.x-anonymous-id', true))::uuid)
  );

-- Allow anyone to insert items to their own cart (authenticated or anonymous)
CREATE POLICY "Anyone can insert cart items"
  ON cart_items FOR INSERT WITH CHECK (true);

-- Allow anyone to update their own cart items (authenticated or anonymous)
CREATE POLICY "Users can update their own cart items"
  ON cart_items FOR UPDATE USING (
    user_id = auth.uid() OR 
    (auth.role() IS NULL AND user_id = (current_setting('request.headers.x-anonymous-id', true))::uuid)
  );

-- Allow anyone to delete their own cart items (authenticated or anonymous)
CREATE POLICY "Users can delete their own cart items"
  ON cart_items FOR DELETE USING (
    user_id = auth.uid() OR 
    (auth.role() IS NULL AND user_id = (current_setting('request.headers.x-anonymous-id', true))::uuid)
  )