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

export async function createProductsTable() {
  try {
    // Table creation
    const tableQuery = `
    CREATE TABLE IF NOT EXISTS products (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      
      -- Basic product information
      name VARCHAR(100) NOT NULL CHECK (char_length(name) >= 3),
      slug VARCHAR(120) NOT NULL UNIQUE,
      bio VARCHAR(255),
      logo JSONB,
      description TEXT NOT NULL,
      
      -- Category reference
      category_id UUID,
      
      -- Product ranking and metrics
      rank INTEGER DEFAULT 0,
      tags JSONB DEFAULT '[]'::JSONB,
      version VARCHAR(20) DEFAULT '1.0.0',
      upvotes INTEGER DEFAULT 0,
      rating DECIMAL(3,2) DEFAULT 0.00 CHECK (rating >= 0 AND rating <= 5),
      followers INTEGER DEFAULT 0,
      views INTEGER DEFAULT 0,
      
      -- Creator information
      creator_id UUID NOT NULL,
      creator_type VARCHAR(20) NOT NULL DEFAULT 'user' 
        CHECK (creator_type IN ('user', 'company')),
      
      -- Links and URLs
      website VARCHAR(250),
      github_url VARCHAR(250),
      documentation_url VARCHAR(250),
      
      -- Status and flags
      status VARCHAR(20) DEFAULT 'active' 
        CHECK (status IN ('active', 'draft', 'archived', 'pending')),
      is_featured BOOLEAN DEFAULT false,
      is_verified BOOLEAN DEFAULT false,
      is_sponsored BOOLEAN DEFAULT false,
      
      -- Dates
      launch_date DATE,
      last_release_date DATE,
      
      -- SEO and metadata
      meta_title VARCHAR(255),
      meta_description TEXT,
      meta_keywords JSONB DEFAULT '[]'::JSONB,
      
      -- Statistics (can be updated via triggers)
      review_count INTEGER DEFAULT 0,
      
      -- Timestamps
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      last_viewed_at TIMESTAMP,
      
      -- Foreign key constraints
      FOREIGN KEY (creator_id) REFERENCES accounts(id) ON DELETE CASCADE,
      FOREIGN KEY (category_id) REFERENCES products_categories(id) ON DELETE SET NULL,
      
      -- Additional constraints
      CONSTRAINT valid_rank CHECK (rank >= 0),
      CONSTRAINT valid_upvotes CHECK (upvotes >= 0),
      CONSTRAINT valid_followers CHECK (followers >= 0),
      CONSTRAINT valid_views CHECK (views >= 0),
      CONSTRAINT valid_review_count CHECK (review_count >= 0),
      CONSTRAINT valid_slug CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$')
    );`;

    await database.query(tableQuery);

    // Index creation for better performance
    const indexes = [
      // Foreign key indexes
      `CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);`,
      `CREATE INDEX IF NOT EXISTS idx_products_creator_id ON products(creator_id);`,

      // Search and filtering indexes
      `CREATE INDEX IF NOT EXISTS idx_products_name ON products(name);`,
      `CREATE INDEX IF NOT EXISTS idx_products_slug ON products(slug);`,
      `CREATE INDEX IF NOT EXISTS idx_products_status ON products(status);`,
      `CREATE INDEX IF NOT EXISTS idx_products_is_featured ON products(is_featured) WHERE is_featured = true;`,
      `CREATE INDEX IF NOT EXISTS idx_products_is_verified ON products(is_verified) WHERE is_verified = true;`,
      `CREATE INDEX IF NOT EXISTS idx_products_is_sponsored ON products(is_sponsored) WHERE is_sponsored = true;`,

      // Ranking and sorting indexes
      `CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating DESC NULLS LAST);`,
      `CREATE INDEX IF NOT EXISTS idx_products_upvotes ON products(upvotes DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_products_followers ON products(followers DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_products_views ON products(views DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_products_rank ON products(rank DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_products_created_at ON products(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_products_launch_date ON products(launch_date DESC NULLS LAST);`,

      // JSONB indexes for efficient array searches
      `CREATE INDEX IF NOT EXISTS idx_products_tags ON products USING GIN(tags);`,
      `CREATE INDEX IF NOT EXISTS idx_products_meta_keywords ON products USING GIN(meta_keywords);`,

      // Composite indexes for common queries
      `CREATE INDEX IF NOT EXISTS idx_products_status_featured ON products(status, is_featured) WHERE status = 'active' AND is_featured = true;`,
      `CREATE INDEX IF NOT EXISTS idx_products_category_status ON products(category_id, status) WHERE status = 'active';`,
      `CREATE INDEX IF NOT EXISTS idx_products_creator_type ON products(creator_id, creator_type);`,

      // Full-text search indexes
      `CREATE INDEX IF NOT EXISTS idx_products_search_name ON products USING gin(to_tsvector('english', name));`,
      `CREATE INDEX IF NOT EXISTS idx_products_search_description ON products USING gin(to_tsvector('english', description));`,
      `CREATE INDEX IF NOT EXISTS idx_products_search_bio ON products USING gin(to_tsvector('english', bio));`,

      // For date-based queries
      `CREATE INDEX IF NOT EXISTS idx_products_recently_updated ON products(updated_at DESC) WHERE status = 'active';`,
      `CREATE INDEX IF NOT EXISTS idx_products_new_launches ON products(launch_date DESC) WHERE launch_date IS NOT NULL;`,
    ];

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

    // Create a function to automatically update the updated_at timestamp
    const updateTriggerFunction = `
      CREATE OR REPLACE FUNCTION update_products_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(updateTriggerFunction);

    // Drop trigger if exists (IMPORTANT)
    const dropTrigger = `
      DROP TRIGGER IF EXISTS set_products_updated_at ON products
    ;`;
    await database.query(dropTrigger);

    // Create trigger for automatic updated_at
    const updateTrigger = `
      CREATE TRIGGER set_products_updated_at
      BEFORE UPDATE ON products
      FOR EACH ROW
      EXECUTE FUNCTION update_products_updated_at();
    `;

    await database.query(updateTrigger);

    // Create function to generate slug from name
    const slugFunction = `
      CREATE OR REPLACE FUNCTION generate_product_slug(product_name TEXT)
      RETURNS TEXT AS $$
      BEGIN
        RETURN lower(regexp_replace(
          regexp_replace(product_name, '[^a-zA-Z0-9\s-]', '', 'g'),
          '\s+', '-', 'g'
        ));
      END;
      $$ LANGUAGE plpgsql IMMUTABLE;
    `;

    await database.query(slugFunction);

    // console.log("✅ Products table created successfully");
  } catch (error) {
    console.error("❌ Failed To Create Products Table.", error);
    process.exit(1);
  }
}
