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

export async function createProductsGallerysTable() {
  try {
    // Table creation
    const tableQuery = `
      CREATE TABLE IF NOT EXISTS products_gallerys (
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

        -- Product reference
        product_id UUID NOT NULL,

        -- Media storage (Cloudinary URLs)
        url JSONB NOT NULL,
        thumbnail JSONB NOT NULL, 

        -- Media details
        type VARCHAR(20) NOT NULL DEFAULT 'image'
          CHECK (type IN ('image', 'video', 'youtube', 'vimeo', 'gif')),

        media_format VARCHAR(10)
          CHECK (media_format IN ('jpg', 'png', 'webp', 'mp4', 'webm', 'gif')),

        caption TEXT,
        
        -- Cloudinary specific
        public_id VARCHAR(255), -- Cloudinary public_id
        resource_type VARCHAR(20) DEFAULT 'image'
          CHECK (resource_type IN ('image', 'video', 'raw')),

        -- Sorting and display
        display_order INTEGER DEFAULT 0,
        is_featured BOOLEAN DEFAULT false,
        is_primary BOOLEAN DEFAULT false,

        -- Status
        status VARCHAR(20) DEFAULT 'active'
          CHECK (status IN ('active', 'archived', 'processing')),

        -- Metadata
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

        -- Foreign key constraint
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,

        -- Ensure each product has only one primary image
        CONSTRAINT unique_primary_per_product
          UNIQUE (product_id, is_primary)
          DEFERRABLE INITIALLY DEFERRED
      );
    `;

    await database.query(tableQuery);

    // Index creation for better performance
    const indexes = [
      // Foreign key index
      `CREATE INDEX IF NOT EXISTS idx_gallery_product_id ON products_gallerys(product_id);`,

      // For sorting and filtering
      `CREATE INDEX IF NOT EXISTS idx_gallery_type ON products_gallerys(type);`,
      `CREATE INDEX IF NOT EXISTS idx_gallery_status ON products_gallerys(status);`,
      `CREATE INDEX IF NOT EXISTS idx_gallery_display_order ON products_gallerys(display_order);`,
      `CREATE INDEX IF NOT EXISTS idx_gallery_is_featured ON products_gallerys(is_featured) WHERE is_featured = true;`,
      `CREATE INDEX IF NOT EXISTS idx_gallery_is_primary ON products_gallerys(is_primary) WHERE is_primary = true;`,
      `CREATE INDEX IF NOT EXISTS idx_gallery_created_at ON products_gallerys(created_at DESC);`,

      // For JSONB queries (if you need to search inside url/thumbnail JSON)
      `CREATE INDEX IF NOT EXISTS idx_gallery_url ON products_gallerys USING GIN(url);`,
      `CREATE INDEX IF NOT EXISTS idx_gallery_thumbnail ON products_gallerys USING GIN(thumbnail);`,
    ];

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

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