-- Database Schema Fixes and Optimizations
-- Generated: 2026-01-16

-- ==========================================
-- 1. ADD MISSING COLUMNS
-- ==========================================

-- Add missing timestamps to products if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'products' AND column_name = 'deleted_at'
  ) THEN
    ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP;
  END IF;
END $$;

-- Add missing order columns
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'orders' AND column_name = 'customer_id'
  ) THEN
    ALTER TABLE orders ADD COLUMN customer_id UUID;
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customer 
      FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL;
  END IF;
END $$;

DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'orders' AND column_name = 'shipping_address'
  ) THEN
    ALTER TABLE orders ADD COLUMN shipping_address JSONB;
    ALTER TABLE orders ADD COLUMN billing_address JSONB;
    ALTER TABLE orders ADD COLUMN payment_method VARCHAR(50);
    ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100);
    ALTER TABLE orders ADD COLUMN notes TEXT;
    ALTER TABLE orders ADD COLUMN created_at TIMESTAMP DEFAULT NOW();
  END IF;
END $$;

-- ==========================================
-- 2. FIX DATA TYPE INCONSISTENCIES
-- ==========================================

-- Ensure consistent boolean defaults
ALTER TABLE products ALTER COLUMN isfeatured SET DEFAULT false;
ALTER TABLE products ALTER COLUMN isbestseller SET DEFAULT false;
ALTER TABLE products ALTER COLUMN isactive SET DEFAULT true;

ALTER TABLE product_images ALTER COLUMN is_primary SET DEFAULT false;
ALTER TABLE product_images ALTER COLUMN display_order SET DEFAULT 0;
ALTER TABLE product_images ALTER COLUMN variant_stock SET DEFAULT 0;

ALTER TABLE blogposts ALTER COLUMN ispublished SET DEFAULT false;
ALTER TABLE blogposts ALTER COLUMN isactive SET DEFAULT true;

ALTER TABLE pages ALTER COLUMN ispublished SET DEFAULT true;
ALTER TABLE pages ALTER COLUMN isactive SET DEFAULT true;

ALTER TABLE portfolioprojects ALTER COLUMN isactive SET DEFAULT true;

-- ==========================================
-- 3. ADD MISSING INDEXES FOR PERFORMANCE
-- ==========================================

-- Products: Add indexes for common queries
CREATE INDEX IF NOT EXISTS idx_products_active_bestseller 
  ON products(isactive, isbestseller) WHERE isactive = true AND isbestseller = true;

CREATE INDEX IF NOT EXISTS idx_products_category_active 
  ON products(category, isactive) WHERE isactive = true;

CREATE INDEX IF NOT EXISTS idx_products_price_range 
  ON products(price) WHERE isactive = true;

CREATE INDEX IF NOT EXISTS idx_products_stock 
  ON products(stockquantity) WHERE isactive = true;

-- Product Images: Optimize joins
CREATE INDEX IF NOT EXISTS idx_product_images_product_order 
  ON product_images(product_id, display_order);

-- Blog: Add missing indexes
CREATE INDEX IF NOT EXISTS idx_blogposts_published_date 
  ON blogposts(publisheddate DESC) WHERE ispublished = true;

CREATE INDEX IF NOT EXISTS idx_blogposts_category 
  ON blogposts(category) WHERE ispublished = true;

-- Pages: Add index for active lookup
CREATE INDEX IF NOT EXISTS idx_pages_slug_active 
  ON pages(slug) WHERE isactive = true AND ispublished = true;

-- Orders: Add indexes for queries
CREATE INDEX IF NOT EXISTS idx_orders_customer 
  ON orders(customer_id) WHERE customer_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_orders_status 
  ON orders(orderstatus);

CREATE INDEX IF NOT EXISTS idx_orders_date 
  ON orders(orderdate DESC);

CREATE INDEX IF NOT EXISTS idx_orders_number 
  ON orders(ordernumber);

-- Customers: Add indexes
CREATE INDEX IF NOT EXISTS idx_customers_email_active 
  ON customers(email) WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_customers_created 
  ON customers(created_at DESC);

-- ==========================================
-- 4. ADD MISSING CONSTRAINTS
-- ==========================================

-- Ensure products have valid prices
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'chk_products_price_positive'
  ) THEN
    ALTER TABLE products ADD CONSTRAINT chk_products_price_positive 
      CHECK (price >= 0);
  END IF;
END $$;

DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'chk_products_stock_nonnegative'
  ) THEN
    ALTER TABLE products ADD CONSTRAINT chk_products_stock_nonnegative 
      CHECK (stockquantity >= 0);
  END IF;
END $$;

-- Ensure product images have valid ordering
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'chk_product_images_order_nonnegative'
  ) THEN
    ALTER TABLE product_images ADD CONSTRAINT chk_product_images_order_nonnegative 
      CHECK (display_order >= 0);
  END IF;
END $$;

DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'chk_product_images_stock_nonnegative'
  ) THEN
    ALTER TABLE product_images ADD CONSTRAINT chk_product_images_stock_nonnegative 
      CHECK (variant_stock >= 0);
  END IF;
END $$;

-- Ensure orders have valid amounts
ALTER TABLE orders DROP CONSTRAINT IF EXISTS chk_orders_amounts;
ALTER TABLE orders ADD CONSTRAINT chk_orders_amounts 
  CHECK (subtotal >= 0 AND total >= 0);

-- ==========================================
-- 5. ADD CASCADE DELETE FOR ORPHANED DATA
-- ==========================================

-- Ensure product images are deleted when product is deleted
ALTER TABLE product_images DROP CONSTRAINT IF EXISTS product_images_product_id_fkey;
ALTER TABLE product_images ADD CONSTRAINT product_images_product_id_fkey 
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE;

-- ==========================================
-- 6. CREATE MISSING TABLES
-- ==========================================

-- Create order_items if missing
CREATE TABLE IF NOT EXISTS order_items (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::TEXT,
  order_id TEXT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id TEXT REFERENCES products(id) ON DELETE SET NULL,
  product_name VARCHAR(255) NOT NULL,
  product_sku VARCHAR(100),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
  total_price NUMERIC(10,2) NOT NULL CHECK (total_price >= 0),
  color_variant VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id);

-- Create reviews table if missing
CREATE TABLE IF NOT EXISTS product_reviews (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::TEXT,
  product_id TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  customer_id UUID REFERENCES customers(id) ON DELETE CASCADE,
  rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
  title VARCHAR(200),
  comment TEXT,
  is_verified_purchase BOOLEAN DEFAULT false,
  is_approved BOOLEAN DEFAULT false,
  helpful_count INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_reviews_product ON product_reviews(product_id);
CREATE INDEX IF NOT EXISTS idx_reviews_customer ON product_reviews(customer_id);
CREATE INDEX IF NOT EXISTS idx_reviews_approved ON product_reviews(is_approved) WHERE is_approved = true;

-- ==========================================
-- 7. ADD TRIGGERS FOR AUTOMATIC TIMESTAMPS
-- ==========================================

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

-- Add triggers for products
DROP TRIGGER IF EXISTS update_products_updatedat ON products;
CREATE TRIGGER update_products_updatedat 
  BEFORE UPDATE ON products 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Add triggers for blogposts
DROP TRIGGER IF EXISTS update_blogposts_updatedat ON blogposts;
CREATE TRIGGER update_blogposts_updatedat 
  BEFORE UPDATE ON blogposts 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Add triggers for pages
DROP TRIGGER IF EXISTS update_pages_updatedat ON pages;
CREATE TRIGGER update_pages_updatedat 
  BEFORE UPDATE ON pages 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ==========================================
-- 8. UPDATE STATISTICS FOR QUERY PLANNER
-- ==========================================

ANALYZE products;
ANALYZE product_images;
ANALYZE blogposts;
ANALYZE pages;
ANALYZE portfolioprojects;
ANALYZE orders;
ANALYZE customers;

-- ==========================================
-- COMPLETE
-- ==========================================

SELECT 'Database schema fixes applied successfully!' AS status;