let mysql = require('mysql');

let con = mysql.createConnection({
  host: "192.168.10.68",
  user: "admin",
  //port: 3306,
  password: "210875",
  database: "wpconferences"
});

async function getConferences(){
  let confResult

  let selectConferences = `SELECT  
                CAST(P.ID as CHAR(10)) as id,
                P.post_title AS title,
                UNIX_TIMESTAMP(P.post_date) AS date,
                P.post_name AS slug,
                P.post_content AS content,
                CASE
                    WHEN P.post_status = 'private' THEN true
                    ELSE false
                END AS private,
                MD.activity AS duration,
                MD.activity AS activity,
                MD.city AS city,
                MD.state AS state,
                MD.country AS country,
                PMA.meta_value AS thumbnail,
                MF.youtube,
                MF.video,
                MF.audio,
                MF.pdf,
                MF.pdf_simple,
                MF.videofile,
                MF.audiofile,
                MF.pdffile,
                MF.pdfsimplefile
                FROM
                    wp_posts P
                LEFT JOIN wp_messagedata MD ON
                    P.id = MD.post_id
                LEFT JOIN wp_postmeta PM ON
                    PM.post_id = P.id AND PM.meta_key = '_thumbnail_id'
                LEFT JOIN wp_postmeta PMA ON
                    PM.meta_value = PMA.post_id AND PMA.meta_key = '_wp_attached_file'
                LEFT JOIN wp_messagefiles MF ON
                    MF.post_id = P.id
                WHERE
                    P.post_type = 'conferencias'`;

  con.connect(function(err) {
    if (err) throw err;
    con.query( selectConferences, function (err, result, fields) {
      if (err) throw err;
      confResult = result
      return confResult
    });
  });
  
}

let conferences = getConferences().then( console.log ('DONE' ));

console.log( conferences )

// {
//   //"id": "51031",
//   //"type": "activities",
//   //"title": "Jóvenes guiados por la Columna de Fuego en la Enseñanza bajo Carpa",
//   //"date": 1703879492,
//   //"activity": "1",
//   //"bible_study": "",
//   //"place": "La Gran Carpa Catedral",
//   //"city": "Cayey",
//   //"state": null,
//   //"country": "PR",
//   //"duration": 0,
//   //"body": "a",
//   //"private": false,
//   //"year": "2023",
//   //"month": "2023 > 12",
//   //"slug": "jovenes-guiados-por-la-columna-de-fuego-en-la-ensenanza-bajo-carpa",
//   "links": {
//     "youtube": "",
//     "audio": "2023/12/2023-12-29-1-JBP-jovenes_guiados_por_la_columna_de_fuego.mp3",
//     "booklet": "2023/12/2023-12-29-1-JBP-jovenes_guiados_por_la_columna_de_fuego-booklet.pdf",
//     "simple": "2023/12/2023-12-29-1-JBP-jovenes_guiados_por_la_columna_de_fuego-sencillo.pdf"
//   },
//   "relations": {
//     "translations": [
//       {
//         "locale":"en",
//         "id":"5030"
//       },
//       {
//         "locale":"en",
//         "id":"5030"
//       },
//     ]
//   }
// }