import database from "../../config/db";

export async function createProductsCategoriesTable() {
  try {
    // Table creation
    const tableQuery = `
    CREATE TABLE IF NOT EXISTS products_categories (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      
      -- Category basic information
      name VARCHAR(100) NOT NULL UNIQUE,
      slug VARCHAR(120) NOT NULL UNIQUE,
      description TEXT,
      
      -- Category images
      icon VARCHAR(100),
      cover_image JSONB,
      
      -- Hierarchy and organization
      parent_id UUID,
      sort_order INTEGER DEFAULT 0,
      level INTEGER DEFAULT 1 CHECK (level >= 1),
      
      -- SEO and metadata
      meta_title VARCHAR(255),
      meta_description TEXT,
      meta_keywords TEXT[] DEFAULT '{}',
      
      -- Statistics and tracking
      product_count INTEGER DEFAULT 0,
      view_count INTEGER DEFAULT 0,
      
      -- Status and visibility
      is_active BOOLEAN DEFAULT true,
      is_featured BOOLEAN DEFAULT false,
      
      -- Timestamps
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      
      -- Foreign key for hierarchical structure
      FOREIGN KEY (parent_id) REFERENCES products_categories(id) ON DELETE SET NULL,
      
      -- Constraints
      CONSTRAINT valid_sort_order CHECK (sort_order >= 0),
      CONSTRAINT valid_product_count CHECK (product_count >= 0),
      CONSTRAINT valid_view_count CHECK (view_count >= 0),
      
      -- Ensure slug follows URL format
      CONSTRAINT valid_slug CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$')
    );`;

    await database.query(tableQuery);

    // Index creation for better performance
    const indexes = [
      // Foreign key index
      `CREATE INDEX IF NOT EXISTS idx_categories_parent_id ON products_categories(parent_id);`,
      // For sorting and filtering
      `CREATE INDEX IF NOT EXISTS idx_categories_name ON products_categories(name);`,
      `CREATE INDEX IF NOT EXISTS idx_categories_slug ON products_categories(slug);`,
      `CREATE INDEX IF NOT EXISTS idx_categories_is_active ON products_categories(is_active) WHERE is_active = true;`,
      `CREATE INDEX IF NOT EXISTS idx_categories_is_featured ON products_categories(is_featured) WHERE is_featured = true;`,
      `CREATE INDEX IF NOT EXISTS idx_categories_sort_order ON products_categories(sort_order);`,
      `CREATE INDEX IF NOT EXISTS idx_categories_level ON products_categories(level);`,
      `CREATE INDEX IF NOT EXISTS idx_categories_product_count ON products_categories(product_count DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_categories_created_at ON products_categories(created_at DESC);`,
      // For full-text search on name and description
      `CREATE INDEX IF NOT EXISTS idx_categories_search_name ON products_categories USING gin(to_tsvector('english', name));`,
      `CREATE INDEX IF NOT EXISTS idx_categories_search_description ON products_categories USING gin(to_tsvector('english', description));`,
    ];

    for (const indexQuery of indexes) {
      await database.query(indexQuery);
    }

  } catch (error) {
    console.error("❌ Failed To Create Products Categories Table.", error);
    process.exit(1);
  }
}
