const express = require('express');
const router = express.Router();
const db = require('../db');



const multer = require('multer');
const path = require('path');
const fs = require('fs');

const uploadDir = path.join(__dirname, '..', 'public', 'uploads', 'blogs');
if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir, { recursive: true });

const storage = multer.diskStorage({
  destination: (_req, _file, cb) => cb(null, uploadDir),
  filename: (_req, file, cb) => {
    const unique = Date.now() + '-' + Math.round(Math.random() * 1e9);
    cb(null, file.fieldname + '-' + unique + path.extname(file.originalname));
  }
});
const upload = multer({ storage });

// const storage = multer.diskStorage({
//   destination: function (req, file, cb) {
//     const dir = path.join(__dirname, 'public/uploads/blogs');
//     if (!fs.existsSync(dir)) {
//       fs.mkdirSync(dir, { recursive: true });
//     }
//     cb(null, dir);
//   },
//   filename: function (req, file, cb) {
//     const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
//     const ext = path.extname(file.originalname);
//     cb(null, file.fieldname + '-' + uniqueSuffix + ext);
//   }
// });

// const upload = multer({ storage });



// Middleware
const isLoggedIn = (req, res, next) => {
  if (req.session.admin) return next();
  res.redirect('/admin/login');
};

// Login Page
router.get('/login', (req, res) => {
  res.render('admin-login');
});

router.post('/login', (req, res) => {
  const { username, password } = req.body;
  db.query('SELECT * FROM admin WHERE username = ? AND password = ?', [username, password], (err, result) => {
    if (err || result.length === 0) return res.send('Invalid credentials');
    req.session.admin = true;
    res.redirect('/admin/dashboard');
  });
});

// Logout
router.get('/logout', (req, res) => {
  req.session.destroy();
  res.redirect('/admin/login');
});

// Dashboard
router.get('/dashboard', isLoggedIn, (req, res) => {
  db.query('SELECT * FROM blogs', (err, blogs) => {
    res.render('admin-dashboard', { blogs });
  });
});

// Add blog
router.get('/add', isLoggedIn, (req, res) => {
  res.render('add-blog');
});


// ADD BLOG ROUTE
router.post(
  '/add',
  isLoggedIn,
  upload.fields([
    { name: 'imgbg', maxCount: 1 },
    { name: 'Img1',  maxCount: 1 },
    { name: 'Img2',  maxCount: 1 },
    { name: 'Img3',  maxCount: 1 },
    { name: 'og_image_url', maxCount: 1 }
  ]),
  (req, res) => {
    const files = req.files;
    const {
      title, slug, pg1, pg2, tags, hed1, hpg1, hed2, hpg2,
      seo_title, seo_description, seo_canonical,
      seo_robots_index, seo_robots_follow,
      og_title, og_description, og_type, og_url, og_site_name, og_locale,
      article_modified_time, twitter_card, is_published, focus_keyphrase,
      imgbg_alt, img1_alt, img2_alt, img3_alt, og_image_alt
    } = req.body;

    // 1️⃣ Insert basic post (no images yet)
    const insertSql = `INSERT INTO blogs (
      title, slug, pg1, pg2, tags, hed1, hpg1, hed2, hpg2,
      seo_title, seo_description, seo_canonical,
      seo_robots_index, seo_robots_follow,
      og_title, og_description, og_type, og_url, og_site_name, og_locale,
      article_modified_time, twitter_card, is_published, focus_keyphrase
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

    const vals = [
      title, slug, pg1, pg2, tags, hed1, hpg1, hed2, hpg2,
      seo_title, seo_description, seo_canonical,
      seo_robots_index, seo_robots_follow,
      og_title, og_description, og_type, og_url, og_site_name, og_locale,
      article_modified_time, twitter_card,
      is_published === '1' ? 1 : 0,
      focus_keyphrase
    ];

    db.query(insertSql, vals, (err, result) => {
      if (err) {
        console.error('Insert error:', err);
        return res.status(500).send('Error saving blog');
      }

      const blogId = result.insertId;

      // // helper: rename and return public URL or null
      // const handle = field => {
      //   if (!files[field]) return null;
      //   const tmp = files[field][0];
      //   const newName = `${blogId}_${field}${path.extname(tmp.originalname)}`;
      //   const newPath = path.join(uploadDir, newName);
      //   fs.renameSync(tmp.path, newPath);
      //   return `/uploads/blogs/${newName}`;
      // };

      const handle = field => {
  if (!files[field]) return null;
  const tmp = files[field][0];
  const newName = `${blogId}_${field}${path.extname(tmp.originalname)}`;
  const newPath = path.join(uploadDir, newName);
  fs.renameSync(tmp.path, newPath);
  return newName; // Only the filename, not a path
};

      const Imgbg       = handle('imgbg');
      const Img1        = handle('Img1');
      const Img2        = handle('Img2');
      const Img3        = handle('Img3');
      const og_image_url = handle('og_image_url');

      // 2️⃣ Update with image URLs + alt texts
      const updateSql = `UPDATE blogs SET
        Imgbg = ?, Img1 = ?, Img2 = ?, Img3 = ?, og_image_url = ?,
        imgbg_alt = ?, img1_alt = ?, img2_alt = ?, img3_alt = ?, og_image_alt = ?
       WHERE id = ?`;

      const upVals = [
        Imgbg, Img1, Img2, Img3, og_image_url,
        imgbg_alt || null, img1_alt || null, img2_alt || null, img3_alt || null, og_image_alt || null,
        blogId
      ];

      db.query(updateSql, upVals, upErr => {
        if (upErr) {
          console.error('Update error:', upErr);
          return res.status(500).send('Error updating blog images');
        }
        res.redirect('/admin/dashboard');
      });
    });
  }
);






router.get('/edit/:id', isLoggedIn, (req, res) => {
  db.query('SELECT * FROM blogs WHERE id = ?', [req.params.id], (err, result) => {
    if (err || result.length === 0) return res.send('Blog not found');
    res.render('edit-blog', { blog: result[0] });
  });
});


// Edit blog - Update DB
// Edit blog - Update DB

// router.post('/edit/:id', isLoggedIn, (req, res) => {
//   const blogId = req.params.id;

//   const {
//     title, slug, Imgbg, pg1, pg2,
//     Img1, Img2, Img3, tags,
//     hed1, hpg1, hed2, hpg2,
//     seo_title, seo_description, seo_canonical,
//     seo_robots_index, seo_robots_follow,
//     og_title, og_description, og_type, og_url, og_site_name, og_locale,
//     og_image_url, og_image_width, og_image_height, og_image_type,
//     article_modified_time, twitter_card, is_published,
//     focus_keyphrase
//   } = req.body;


//   const sql = `
//     UPDATE blogs SET
//       title = ?, slug = ?, Imgbg = ?, pg1 = ?, pg2 = ?,
//       Img1 = ?, Img2 = ?, Img3 = ?, tags = ?,
//       hed1 = ?, hpg1 = ?, hed2 = ?, hpg2 = ?,
//       seo_title = ?, seo_description = ?, seo_canonical = ?,
//       seo_robots_index = ?, seo_robots_follow = ?,
//       og_title = ?, og_description = ?, og_type = ?,
//       og_url = ?, og_site_name = ?, og_locale = ?,
//       og_image_url = ?, og_image_width = ?, og_image_height = ?, og_image_type = ?,
//       article_modified_time = ?, twitter_card = ?, is_published = ?, focus_keyphrase = ?
//     WHERE id = ?
//   `;

//   const values = [
//     title, slug, Imgbg, pg1, pg2,
//     Img1, Img2, Img3, tags,
//     hed1, hpg1, hed2, hpg2,
//     seo_title, seo_description, seo_canonical,
//     seo_robots_index, seo_robots_follow,
//     og_title, og_description, og_type, og_url, og_site_name, og_locale,
//     og_image_url, og_image_width, og_image_height, og_image_type,
//     article_modified_time, twitter_card, is_published === '1' ? 1 : 0, focus_keyphrase,
//     blogId
//   ];



//   db.query(sql, values, err => {
//     if (err) {
//       console.error('SQL Error:', err.sqlMessage || err.message || err);
//       return res.send(`Error: ${err.sqlMessage || err.message || err}`);
//     }

//     res.redirect('/admin/dashboard');
//   });
// });

// router.post('/edit/:id', isLoggedIn,
// upload.fields([
//     { name: 'imgbg', maxCount: 1 },
//     { name: 'Img1', maxCount: 1 },
//     { name: 'Img2', maxCount: 1 },
//     { name: 'Img3', maxCount: 1 },
//     { name: 'og_image_url', maxCount: 1 }
//   ]),
//   async (req, res) => {
//     const blogId = req.params.id;
//     const files = req.files;
//     const {
//       title, slug, pg1, pg2, tags, hed1, hpg1, hed2, hpg2,
//       seo_title, seo_description, seo_canonical,
//       seo_robots_index, seo_robots_follow,
//       og_title, og_description, og_type, og_url, og_site_name, og_locale,
//       og_image_width, og_image_height, og_image_type,
//       article_modified_time, twitter_card, is_published,
//       focus_keyphrase,
//       imgbg_alt, img1_alt, img2_alt, img3_alt, og_image_alt
//     } = req.body;

//     // Get existing image paths
//     db.query('SELECT Imgbg, Img1, Img2, Img3, og_image_url FROM blogs WHERE id = ?', [blogId], (err, rows) => {
//       if (err || rows.length === 0) {
//         console.error('Fetch error:', err);
//         return res.send('Error fetching existing blog');
//       }

//       const old = rows[0];

//       const renameFile = (field, fileArray, oldPath) => {
//         if (!fileArray || fileArray.length === 0) return oldPath;
//         if (oldPath && oldPath.startsWith('/uploads/blogs/')) {
//           const absoluteOldPath = path.join(__dirname, '..', oldPath);
//           if (fs.existsSync(absoluteOldPath)) {
//             fs.unlinkSync(absoluteOldPath);
//           }
//         }
//         const file = fileArray[0];
//         const ext = path.extname(file.originalname);
//         const newName = `${blogId}_${field}${ext}`;
//         const newPath = path.join(__dirname, '..', 'public/uploads/blogs', newName);
//         fs.renameSync(file.path, newPath);
//         return `/uploads/blogs/${newName}`;
//       };




//       const Imgbg = renameFile('imgbg', files.imgbg, old.Imgbg);
//       const Img1 = renameFile('Img1', files.Img1, old.Img1);
//       const Img2 = renameFile('Img2', files.Img2, old.Img2);
//       const Img3 = renameFile('Img3', files.Img3, old.Img3);
//       const og_image_url = renameFile('og_image_url', files.og_image_url, old.og_image_url);

//       const sql = `
//         UPDATE blogs SET
//           title = ?, slug = ?, Imgbg = ?, pg1 = ?, pg2 = ?,
//           Img1 = ?, Img2 = ?, Img3 = ?, tags = ?,
//           hed1 = ?, hpg1 = ?, hed2 = ?, hpg2 = ?,
//           seo_title = ?, seo_description = ?, seo_canonical = ?,
//           seo_robots_index = ?, seo_robots_follow = ?,
//           og_title = ?, og_description = ?, og_type = ?,
//           og_url = ?, og_site_name = ?, og_locale = ?,
//           og_image_url = ?, og_image_width = ?, og_image_height = ?, og_image_type = ?,
//           article_modified_time = ?, twitter_card = ?, is_published = ?, focus_keyphrase = ?,
//           imgbg_alt = ?, img1_alt = ?, img2_alt = ?, img3_alt = ?, og_image_alt = ?
//         WHERE id = ?
//       `;

//       const values = [
//         title, slug, Imgbg, pg1, pg2,
//         Img1, Img2, Img3, tags,
//         hed1, hpg1, hed2, hpg2,
//         seo_title, seo_description, seo_canonical,
//         seo_robots_index, seo_robots_follow,
//         og_title, og_description, og_type,
//         og_url, og_site_name, og_locale,
//         og_image_url, og_image_width, og_image_height, og_image_type,
//         article_modified_time, twitter_card,
//         is_published === '1' ? 1 : 0, focus_keyphrase,
//         imgbg_alt, img1_alt, img2_alt, img3_alt, og_image_alt,
//         blogId
//       ];

//       db.query(sql, values, (updateErr) => {
//         if (updateErr) {
//           console.error('Update error:', updateErr);
//           return res.send('Error updating blog');
//         }
//         res.redirect('/admin/dashboard');
//       });
//     });
//   }
// );

router.post(
  '/edit/:id',
  isLoggedIn,
  upload.fields([
    { name: 'imgbg', maxCount: 1 },
    { name: 'Img1',  maxCount: 1 },
    { name: 'Img2',  maxCount: 1 },
    { name: 'Img3',  maxCount: 1 },
    { name: 'og_image_url', maxCount: 1 }
  ]),
  (req, res) => {
    const blogId = req.params.id;
    const files = req.files;
    const body  = req.body;

    // 1️⃣ Fetch existing paths
    db.query(
      'SELECT Imgbg, Img1, Img2, Img3, og_image_url FROM blogs WHERE id = ?',
      [blogId],
      (e, rows) => {
        if (e || !rows.length) {
          console.error('Fetch error:', e);
          return res.status(404).send('Blog not found');
        }
        const old = rows[0];

        // helper: delete old & rename new, or keep old
        const handleEdit = field => {
          if (!files[field]) return old[field];
          const tmp = files[field][0];
          // delete old file if exists
          if (old[field] && old[field].startsWith('/uploads/blogs/')) {
            const p = path.join(__dirname, '..', 'public', old[field]);
            if (fs.existsSync(p)) fs.unlinkSync(p);
          }
          const newName = `${blogId}_${field}${path.extname(tmp.originalname)}`;
          const newPath = path.join(uploadDir, newName);
          fs.renameSync(tmp.path, newPath);
          return `${newName}`;
        };

        // build SET values
        const updated = {
          Imgbg: handleEdit('imgbg'),
          Img1:  handleEdit('Img1'),
          Img2:  handleEdit('Img2'),
          Img3:  handleEdit('Img3'),
          og_image_url: handleEdit('og_image_url'),
          imgbg_alt: body.imgbg_alt || null,
          img1_alt:  body.img1_alt  || null,
          img2_alt:  body.img2_alt  || null,
          img3_alt:  body.img3_alt  || null,
          og_image_alt: body.og_image_alt || null
        };

        // 2️⃣ Perform full UPDATE
        const sql = `UPDATE blogs SET
          title=?, slug=?, Imgbg=?, pg1=?, pg2=?, tags=?, hed1=?, hpg1=?, hed2=?, hpg2=?,
          seo_title=?, seo_description=?, seo_canonical=?, seo_robots_index=?, seo_robots_follow=?,
          og_title=?, og_description=?, og_type=?, og_url=?, og_site_name=?, og_locale=?,
          og_image_url=?, og_image_width=?, og_image_height=?, og_image_type=?,
          article_modified_time=?, twitter_card=?, is_published=?, focus_keyphrase=?,
          imgbg_alt=?, img1_alt=?, img2_alt=?, img3_alt=?, og_image_alt=?
         WHERE id=?`;

        const vals = [
          body.title, body.slug,
          updated.Imgbg, body.pg1, body.pg2, body.tags,
          body.hed1, body.hpg1, body.hed2, body.hpg2,
          body.seo_title, body.seo_description, body.seo_canonical,
          body.seo_robots_index, body.seo_robots_follow,
          body.og_title, body.og_description, body.og_type, body.og_url,
          body.og_site_name, body.og_locale,
          updated.og_image_url, body.og_image_width, body.og_image_height, body.og_image_type,
          body.article_modified_time, body.twitter_card,
          body.is_published === '1' ? 1 : 0, body.focus_keyphrase,
          updated.imgbg_alt, updated.img1_alt, updated.img2_alt, updated.img3_alt, updated.og_image_alt,
          blogId
        ];

        db.query(sql, vals, err2 => {
          if (err2) {
            console.error('Edit update error:', err2);
            return res.status(500).send('Error updating blog');
          }
          res.redirect('/admin/dashboard');
        });
      }
    );
  }
);









// Delete blog
// Delete a blog post by slug
router.post('/delete/:slug', (req, res) => {
  const slug = req.params.slug;

  if (!slug) {
    return res.status(400).send('Slug is required');
  }

  db.query('DELETE FROM blogs WHERE slug = ?', [slug], (err, result) => {
    if (err) {
      console.error('Error deleting blog by slug:', err);
      return res.status(500).send('Database error');
    }

    if (result.affectedRows === 0) {
      return res.status(404).send('Blog not found');
    }

    console.log(`Blog with slug "${slug}" deleted successfully`);
    res.redirect('/admin/dashboard');
  });
});



// GET /api/blogs
router.get('/api/blogs', (req, res) => {
  const sql = 'SELECT * FROM blogs WHERE is_published = 1 ORDER BY id DESC';

  db.query(sql, (err, results) => {
    if (err) {
      console.error('Error fetching blogs:', err);
      return res.status(500).json({ error: 'Failed to retrieve blogs' });
    }

    // Prefix image URLs
    const blogs = results.map(blog => ({
      ...blog,
      Imgbg: blog.Imgbg ? `/uploads/${blog.Imgbg}` : null,
      Img1: blog.Img1 ? `/uploads/${blog.Img1}` : null,
      Img2: blog.Img2 ? `/uploads/${blog.Img2}` : null,
      Img3: blog.Img3 ? `/uploads/${blog.Img3}` : null,
      og_image_url: blog.og_image_url ? `/uploads/${blog.og_image_url}` : null,
    }));

    res.json(blogs);
  });
});

// GET /api/blogs/:slug
router.get('/api/blogs/:slug', (req, res) => {
  const { slug } = req.params;
  const sql = 'SELECT * FROM blogs WHERE slug = ? AND is_published = 1 LIMIT 1';

  db.query(sql, [slug], (err, results) => {
    if (err) {
      console.error('Error fetching blog:', err);
      return res.status(500).json({ error: 'Failed to retrieve blog' });
    }

    if (results.length === 0) {
      return res.status(404).json({ error: 'Blog not found' });
    }

    const blog = results[0];
    blog.Imgbg = blog.Imgbg ? `/uploads/${blog.Imgbg}` : null;
    blog.Img1 = blog.Img1 ? `/uploads/${blog.Img1}` : null;
    blog.Img2 = blog.Img2 ? `/uploads/${blog.Img2}` : null;
    blog.Img3 = blog.Img3 ? `/uploads/${blog.Img3}` : null;
    blog.og_image_url = blog.og_image_url ? `/uploads/${blog.og_image_url}` : null;

    res.json(blog);
  });
});



module.exports = router;
