218 lines
7.2 KiB
JavaScript
218 lines
7.2 KiB
JavaScript
#!/usr/bin/env node
|
||
const { pool, query } = require("./config/database");
|
||
|
||
async function applyPartialFixes() {
|
||
console.log("🔧 Applying Database Fixes (User-Level)...\n");
|
||
|
||
try {
|
||
console.log("1️⃣ Creating Indexes...");
|
||
|
||
// Products indexes
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_products_isactive ON products(isactive) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_products_isactive");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_products_isfeatured ON products(isfeatured, createdat DESC) WHERE isfeatured = true AND isactive = true`
|
||
);
|
||
console.log(" ✅ idx_products_isfeatured");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_products_isbestseller ON products(isbestseller, createdat DESC) WHERE isbestseller = true AND isactive = true`
|
||
);
|
||
console.log(" ✅ idx_products_isbestseller");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_products_category ON products(category, createdat DESC) WHERE isactive = true AND category IS NOT NULL`
|
||
);
|
||
console.log(" ✅ idx_products_category");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_products_createdat ON products(createdat DESC) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_products_createdat");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_products_price ON products(price) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_products_price");
|
||
|
||
// Portfolio indexes
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_portfolio_isactive ON portfolioprojects(isactive) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_portfolio_isactive");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_portfolio_category ON portfolioprojects(category) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_portfolio_category");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_portfolio_displayorder ON portfolioprojects(displayorder ASC, createdat DESC) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_portfolio_displayorder");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_portfolio_createdat ON portfolioprojects(createdat DESC) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_portfolio_createdat");
|
||
|
||
// Pages indexes
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_pages_slug ON pages(slug) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_pages_slug");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_pages_isactive ON pages(isactive) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_pages_isactive");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_pages_createdat ON pages(createdat DESC) WHERE isactive = true`
|
||
);
|
||
console.log(" ✅ idx_pages_createdat");
|
||
|
||
// Product images indexes
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_product_images_color_variant ON product_images(color_variant) WHERE color_variant IS NOT NULL`
|
||
);
|
||
console.log(" ✅ idx_product_images_color_variant");
|
||
|
||
await query(
|
||
`CREATE INDEX IF NOT EXISTS idx_product_images_color_code ON product_images(color_code) WHERE color_code IS NOT NULL`
|
||
);
|
||
console.log(" ✅ idx_product_images_color_code");
|
||
|
||
console.log("\n2️⃣ Adding Foreign Keys...");
|
||
try {
|
||
await query(`
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.table_constraints
|
||
WHERE constraint_name = 'fk_product_images_product'
|
||
) THEN
|
||
ALTER TABLE product_images
|
||
ADD CONSTRAINT fk_product_images_product
|
||
FOREIGN KEY (product_id) REFERENCES products(id)
|
||
ON DELETE CASCADE;
|
||
END IF;
|
||
END $$;
|
||
`);
|
||
console.log(" ✅ product_images -> products");
|
||
} catch (e) {
|
||
console.log(" ⚠️ product_images FK:", e.message);
|
||
}
|
||
|
||
try {
|
||
await query(`
|
||
DO $$
|
||
BEGIN
|
||
UPDATE uploads SET folder_id = NULL
|
||
WHERE folder_id NOT IN (SELECT id FROM media_folders);
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.table_constraints
|
||
WHERE constraint_name = 'fk_uploads_folder'
|
||
) THEN
|
||
ALTER TABLE uploads
|
||
ADD CONSTRAINT fk_uploads_folder
|
||
FOREIGN KEY (folder_id) REFERENCES media_folders(id)
|
||
ON DELETE SET NULL;
|
||
END IF;
|
||
END $$;
|
||
`);
|
||
console.log(" ✅ uploads -> media_folders");
|
||
} catch (e) {
|
||
console.log(" ⚠️ uploads FK:", e.message);
|
||
}
|
||
|
||
console.log("\n3️⃣ Adding Unique Constraints...");
|
||
try {
|
||
await query(`
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'unique_products_slug') THEN
|
||
WITH duplicates AS (
|
||
SELECT slug, array_agg(id) as ids
|
||
FROM products
|
||
WHERE slug IS NOT NULL
|
||
GROUP BY slug
|
||
HAVING COUNT(*) > 1
|
||
)
|
||
UPDATE products p
|
||
SET slug = p.slug || '-' || substring(p.id, 1, 8)
|
||
WHERE p.id IN (SELECT unnest(ids[2:]) FROM duplicates);
|
||
|
||
ALTER TABLE products ADD CONSTRAINT unique_products_slug UNIQUE(slug);
|
||
END IF;
|
||
END $$;
|
||
`);
|
||
console.log(" ✅ products.slug unique constraint");
|
||
} catch (e) {
|
||
console.log(" ⚠️ products.slug:", e.message);
|
||
}
|
||
|
||
try {
|
||
await query(`
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'unique_pages_slug') THEN
|
||
WITH duplicates AS (
|
||
SELECT slug, array_agg(id) as ids
|
||
FROM pages
|
||
WHERE slug IS NOT NULL
|
||
GROUP BY slug
|
||
HAVING COUNT(*) > 1
|
||
)
|
||
UPDATE pages p
|
||
SET slug = p.slug || '-' || p.id::text
|
||
WHERE p.id IN (SELECT unnest(ids[2:]) FROM duplicates);
|
||
|
||
ALTER TABLE pages ADD CONSTRAINT unique_pages_slug UNIQUE(slug);
|
||
END IF;
|
||
END $$;
|
||
`);
|
||
console.log(" ✅ pages.slug unique constraint");
|
||
} catch (e) {
|
||
console.log(" ⚠️ pages.slug:", e.message);
|
||
}
|
||
|
||
console.log("\n4️⃣ Running ANALYZE...");
|
||
await query("ANALYZE products");
|
||
await query("ANALYZE product_images");
|
||
await query("ANALYZE portfolioprojects");
|
||
await query("ANALYZE blogposts");
|
||
await query("ANALYZE pages");
|
||
console.log(" ✅ Tables analyzed");
|
||
|
||
console.log("\n📊 Final Status:");
|
||
const indexCount = await query(`
|
||
SELECT COUNT(*) as count
|
||
FROM pg_indexes
|
||
WHERE schemaname = 'public'
|
||
AND tablename IN ('products', 'product_images', 'portfolioprojects', 'blogposts', 'pages')
|
||
`);
|
||
console.log(` Total indexes: ${indexCount.rows[0].count}`);
|
||
|
||
const fkCount = await query(`
|
||
SELECT COUNT(*) as count
|
||
FROM information_schema.table_constraints
|
||
WHERE constraint_type = 'FOREIGN KEY' AND table_schema = 'public'
|
||
`);
|
||
console.log(` Foreign keys: ${fkCount.rows[0].count}`);
|
||
|
||
console.log("\n✅ Database fixes applied successfully!");
|
||
} catch (error) {
|
||
console.error("❌ Error:", error.message);
|
||
process.exit(1);
|
||
} finally {
|
||
await pool.end();
|
||
}
|
||
}
|
||
|
||
applyPartialFixes();
|