import db, {dbName}  from '../../config/db.js';
const imageBaseURL = "https://bibleapi.nuatransmedia.in/files/Illustrator";
const newImageBaseURL = "https://bibleapi.nuatransmedia.in/files/New_Illustration";



const getAllAudios = async (req,res) => {
        try{
        const {limit,chapterName, lang = 'French'} = req.query;
        const numberOfPage = (limit == 1 ? limit - 1 : limit) + ',' + 10; 
        const [rows] = await db.query(`SELECT m.id AS id, m.testament AS testament,m.chapter_name AS chapter_name, m.audio_counts AS audio_counts FROM ${dbName}.${lang}_audio m`);

        let bibleArry = []; 
        let imgCreation = imageBaseURL + '/' + "Genesis/";
        let iPhone = '/@1x/';
        let ipad = '/@3x/';
        
        let jsonData = {status:200,message:"SUCCESS",count:rows.length,data:rows}
        res.json(jsonData);
    } catch (err) {
        console.log(err);
        res.status(500).json({status:200,message: err,count:0});
    }
}
 

const getAllillustrationssold = async (req,res) => {
         try{
        const {limit,chapterName, lang = 'French', cid = 1 , id = 1} = req.query;
        const numberOfPage = (limit == 1 ? limit - 1 : limit) + ',' + 10;
//        const [rows] = await db.query(`SELECT m.chapter_id as cpid, m.sub_chapter_id as scpid, m.id AS id, c.id AS cid, c.sub_chapter_name AS ChapterName,m.image_name,m.verses FROM ${dbName}.${lang}_illustration_bible m INNER JOIN ${dbName}.${lang}_sub_chapter c ON c.id = m.sub_chapter_id`);

        const [rows] = await db.query(`SELECT * FROM French_illustration_bible WHERE chapter_id = 1 and sub_chapter_id= 1;`);


//        let jsonData = {status:200,message:"SUCCESS",count:rows.length,data:rows}
//        res.json(jsonData);


        let bibleArry = [];

        let iPhone = '/@1x/';
        let ipad = '/@3x/';
        rows.forEach(async element => {
            let dashAdd = element.ChapterName.replace(/\s+/g, '-');
            bibleArry.push({id:element.id, cid:element.chapter_id, verses:element.verses,iPhone: imageBaseURL  + '/' + element.chapter_id + '/' + element.sub_chapter_id + iPhone + element.image_name,iPad: imageBaseURL  + '/' + element.chapter_id + '/' +  element.sub_chapter_id + ipad + element.image_name});
        });
          
        
        let jsonData = {status:200,message:"SUCCESS",count:rows.length,data:rows, dat:bibleArry}
        res.json(jsonData); 
    } catch (err) {
        console.log(err);
        res.status(500).json({status:200,message: err,count:0});
    }
}


const getAllillustrationss = async (req, res) => {
    try {
        const { limit = 10, chapterName, lang = 'French', cid = 1, id = 1 } = req.query;
        const numberOfPage = (limit == 1 ? limit - 1 : limit) + ',' + 10;
        
        const [rows] = await db.query(
            `SELECT * FROM ${lang}_illustration_bible WHERE chapter_id = ? AND sub_chapter_id = ?`,
            [cid, id]
        );

        let bibleArry = [];
        let iPhone = '/@1x/';
        let ipad = '/@3x/';
        
        for (let element of rows) {
            let dashAdd = element.ChapterName ? element.ChapterName.replace(/\s+/g, '-') : '';
            bibleArry.push({
                id: element.id,
                cid: element.chapter_id,
                verses: element.verses,
                iPhone: `${imageBaseURL}/${element.chapter_id}/${element.sub_chapter_id}${iPhone}${element.image_name}`,
                iPad: `${imageBaseURL}/${element.chapter_id}/${element.sub_chapter_id}${ipad}${element.image_name}`
            });
        }

        res.json({ status: 200, message: "SUCCESS", count: rows.length, data: bibleArry });
    } catch (err) {
        console.log(err);
        res.status(500).json({ status: 500, message: err.message, count: 0 });
    }
};


const getAllillustrationsChapterName = async (req, res) => {
  try {

    const {lang = 'French'} = req.query;
//    const [rows] = await db.query(`SELECT id,testament,chapter_name FROM ${dbName}.${lang}_chapter`);

    const [rows] = await db.query(`SELECT ch.*, COUNT(sub.id) AS sub_chapter_count FROM French_chapter AS ch LEFT JOIN French_sub_chapter AS sub ON sub.chapter_id = ch.id GROUP BY ch.id;`);

    let oldTestamentArray = [];
    let newTesttamenetArray = [];
    rows.forEach(async element => {
      if (element.testament === 1) {
        oldTestamentArray.push({ ChapterId: element.id, ChapterName: element.chapter_name, SubChapterCount: element.sub_chapter_count })
      } else {
        newTesttamenetArray.push({ ChapterId: element.id, ChapterName: element.chapter_name, SubChapterCount: element.sub_chapter_count })
      }
    });

    let jsonData = { status: 200, message: "SUCCESS", oldtestament: oldTestamentArray, newtestament: newTesttamenetArray }
    res.json(jsonData);
  } catch (err) {
    console.log(err);
    res.status(500).json({ status: 500, message: "FAIL"});
  }
}

const getSubChapter = async (req,res) => {
  try {
    const {subchapterid, lang = 'French'} = req.query;
//  const [rows] = await db.query("SELECT id,chapter_id,sub_chapter_name FROM ${dbName}.${lang}_sub_chapter WHERE chapter_id = " + subchapterid);



  const [rows] = await db.query(`SELECT id,chapter_id,sub_chapter_name FROM French_Canada_sub_chapter WHERE chapter_id = ${subchapterid};`);

  let jsonData = {status: 200,message: "SUCCESS",count:rows.length, data:rows};
  res.json(jsonData);
  } catch (error) {
    res.status(500).json({status: 500, message: "FAIL",count:0,data:[]})
  }
}


const getSubChapterIllsuration = async (req,res) => {
  try {
    const {subchapterid, lang = 'French'} = req.query;
 //   const [rows] = await db.query(`SELECT id,chapter_id,sub_chapter_name FROM French_Canada_sub_chapter WHERE chapter_id = ${subchapterid};`);


    const [rows] = await db.query(`SELECT fcsc.*, fcsc.id AS sub_chapter_id, fcib.image_name FROM ${dbName}.${lang}_sub_chapter fcsc JOIN ( SELECT sub_chapter_id, MIN(id) AS first_image_id FROM ${dbName}.${lang}_illustration_bible GROUP BY sub_chapter_id ) first_images ON fcsc.id = first_images.sub_chapter_id JOIN ${dbName}.${lang}_illustration_bible fcib ON first_images.first_image_id = fcib.id WHERE fcsc.chapter_id = ${subchapterid};`);

       let bibleArry = [];
        let iPhone = '/@1x/';
        let ipad = '/@3x/';

     for (let element of rows) {
            let dashAdd = element.ChapterName ? element.Ch4apterName.replace(/\s+/g, '-') : '';
            bibleArry.push({
                id: element.id,
                chapter_id: element.chapter_id,
                sub_chapter_name: element.sub_chapter_name,
                iPhone: `${imageBaseURL}/${element.chapter_id}/${element.sub_chapter_id}${iPhone}${element.image_name}`,
                iPad: `${imageBaseURL}/${element.chapter_id}/${element.sub_chapter_id}${ipad}${element.image_name}`
            });
        }

     res.json({ status: 200, message: "SUCCESS", count: rows.length, data: bibleArry });



   let jsonData = {status: 200,message: "SUCCESS",count:rows.length, data:rows};
    res.json(jsonData);

  } catch (error) {
     res.status(500).json({status: 500, message: "FAIL",count:0,data:error})
  }

}


const getillustrationImg = async (req,res) => {
  try {
    const {lang  = 'French',chapter,limit} = req.query;
    
    const numberOfPage = (limit == 1 ? limit - 1 : limit) + ',' + 10;
    const [rows] = await db.query(`SELECT m.id AS id,c.chapter_name AS ChapterName,m.image_name,m.verses,m.sub_chapter_id FROM ${dbName}.${lang}_illustration_bible m INNER JOIN ${dbName}.${lang}_chapter c ON c.id = ${chapter}  LIMIT ${numberOfPage}`);
    let bibleArry = [];
    let imgCreation = newImageBaseURL + '/' ;
    let iPhone = '/@1x/';
    let ipad = '/@3x/';
    let chapterName = '';
    rows.forEach(async element => {
        chapterName = element.ChapterName;
        bibleArry.push({id:element.id,verses:element.verses,iPhone: imgCreation + chapterName + '/' + element.sub_chapter_id + iPhone + element.image_name,iPad: imgCreation + chapterName + '/'  + element.sub_chapter_id + ipad + element.image_name});
    });
    let jsonData = {status:200,message:"SUCCESS",chapterName,count:rows.length,data:bibleArry}
     res.json(jsonData);
  } catch (error) {
    console.log(err);
        res.status(500).json({status:500,message: "FAIL",count:0});
  }
}

export default {
    getAllAudios,
    getAllillustrationss,
    getAllillustrationsChapterName,
    getSubChapter,
    getillustrationImg,
    getSubChapterIllsuration
}   
