// thierry . 2015 04 15
// purpose: 
//  1. view contents of databases from application droidscripts
//  2. test sqlite commandes
// verifier si base de donnee ouverte ou fermee
var opened=0;
  
// pour chercher les images: var target="/Sys/Img";
// pour chercher les databases sqlite de droidscripts
var target="/data/data/com.smartphoneremote.androidscriptfree/databases/";

var selectedDb='';
var selectedTable='';
var selectedColumn='';


//Called when application is started.
function OnStart()
{
    //Loading other files into the app
    //app.ShowProgress("...Loading Scripts...");
    //app.LoadScript("Misc/Folderpicker.js");
    //app.HideProgress();
    
 //Create a layout with objects vertically centered.
 lay = app.CreateLayout( "linear", "VTop,FillXY" );  

 // je cherchais aussi le langage
 var country= app.GetCountry() ;
 var countrycode= app.GetCountryCode() ;
 var language= app.GetLanguage() ;
 var languagecode= app.GetLanguageCode() ;

 // affiche mon titre
 titre = app.CreateText( "Yep Database View", 1 );
  titre.SetTextSize(20);
  titre.SetTextColor( "#8855ff" ); 
  titre.SetTextShadow( 10, 5,10, "#ff88ff" ); 
  titre.SetOnTouchUp(about);
  titre.SetOnTouchDown(longabout);
  lay.AddChild( titre );
  
 lh0=app.CreateLayout("linear","horizontal");
 lh1=app.CreateLayout("linear","horizontal");
 lh2=app.CreateLayout("linear","horizontal");
 lh3=app.CreateLayout("linear","horizontal");
  lay.AddChild( lh0 );
  lay.AddChild( lh1 );
  lay.AddChild( lh2 );
  lay.AddChild( lh3 );
  lh0.SetBackGradient( "#444444", "#888888" );
  lh1.SetBackGradient( "#888888", "#444444" );
    
 remindDb=app.CreateText("Db:",0.33);
  lh0.AddChild(remindDb);
 remindTable=app.CreateText("T:",0.33);
  lh0.AddChild(remindTable);
 remindColumn=app.CreateText("C:",0.33);
  lh0.AddChild(remindColumn);
 var colorTitle="#77BBFF";
  remindDb.SetTextColor( colorTitle );
  remindTable.SetTextColor( colorTitle );
  remindColumn.SetTextColor( colorTitle );
    
  // titles 3 columns,  text or image if found
  colorTitle="#0033dd";
  if (app.FileExists( "Img/database.png")){
    tColGauche= app.CreateImage("Img/database.png",0.07); 
    tColGauche.SetMargins( 0.1,0.003,0,0.004 ); 
  } else {
    tColGauche=app.CreateText("db files",0.33);
    tColGauche.SetTextColor( colorTitle );
  }
  lh1.AddChild(tColGauche);
    tColGauche.SetOnTouch( btnD_OnTouch );
  
  if (app.FileExists( "Img/table.png")){
    tColCentre=app.CreateImage("Img/table.png",0.07); 
    tColCentre.SetMargins( 0.29,0.003,0.29,0.004 );
   } else {
    tColCentre=app.CreateText("tables",0.33);
    tColCentre.SetTextColor( colorTitle );
  }
  lh1.AddChild(tColCentre);
    tColCentre.SetOnTouch( btnT_OnTouch );
    
 if (app.FileExists( "Img/column.png")){
    tColDroite=app.CreateImage("Img/column.png",0.07); 
    tColDroite.SetMargins( 0,0.003,0.1,0.004 );
   } else {
    tColDroite=app.CreateText("column",0.33);
    tColDroite.SetTextColor( colorTitle );
  }
  lh1.AddChild(tColDroite);
    tColDroite.SetOnTouch( btnF_OnTouch );
    

 // faire des listes
 lstLeft = app.CreateList( "", 0.7, 0.6 ); 
  loadLeftList();
  lh2.AddChild( lstLeft );
  lstLeft.SetOnTouch( lstLeft_OnTouch );
  lstLeft.SetOnLongTouch( lstLeft_OnLongTouch );

 lstCentre = app.CreateList( "", 0.15, 0.6 );
  lh2.AddChild( lstCentre );
  lstCentre.SetOnTouch( lstCentre_OnTouch );
  
 lstRight= app.CreateList( "", 0.15, 0.6 );
  lh2.AddChild( lstRight );
  lstRight.SetOnTouch( lstRight_OnTouch );
  lstRight.SetOnLongTouch( lstRight_OnLongTouch );
  
  //Create buttons width columns.
 btnD = app.CreateButton( "Db", 0.3 ,-1,"alumn");
   lh3.AddChild( btnD );
   btnD.SetOnTouch( btnD_OnTouch );
 btnT = app.CreateButton( "Table", 0.3 ,-1,"alumn");
   lh3.AddChild( btnT );
   btnT.SetOnTouch( btnT_OnTouch );
 btnF = app.CreateButton( "Column", 0.3 ,-1,"alumn");
   lh3.AddChild( btnF );
   btnF.SetOnTouch( btnF_OnTouch );
 
 //Add layout to app.    
 app.AddLayout( lay );
}


function about() { app.ShowPopup( target );}
function longabout() { app.ShowPopup( "Thierry 2015 04 15. bis40@free.fr" );}

function loadLeftList() {
 var F = app.ListFolder( target );
 for (var i in F ) {
  var text=F[i];var rien;
  if( text.indexOf("journal") >-1) rien++ 
  else lstLeft.AddItem( text );
 }
}

//Called when user touches our button
function btnD_OnTouch(){resizeListes(0.8,0.1,0.1);}
function btnT_OnTouch(){resizeListes(0.1,0.8,0.1);}
function btnF_OnTouch(){resizeListes(0.1,0.1,0.8);}
function resizeListes(a,b,c){
 lstLeft.SetSize(a);
 lstCentre.SetSize(b);
 lstRight.SetSize(c);
}


function emptyListSlow(list) {
 // pas certain des separateurs: virgule ou CR ou autre?
 var itemString=list.GetList(",");
 var itemArray=itemString.split(",");
 for (var i in itemArray ) {
   list.RemoveItem(itemArray[i] );
 } 
 var itemString=list.GetList("\n");
 var itemArray=itemString.split("\n");
 for (var i in itemArray ) {
   list.RemoveItem(itemArray[i] );
 }
}
function emptyList(list)  {
  // I learnt this is possible 
  list.SetList("") ;
 } 


function lstLeft_OnTouch( database ){
  if (opened) {  db.Close(); }
  selectedDb = database ;
  db=app.OpenDatabase( database ); 
  opened=1; 
  emptyList(lstCentre); 
  emptyList( lstRight );
  remindDb.SetText( database );
  resizeListes(0.15,0.7,0.15);
  db.ExecuteSql("SELECT * FROM sqlite_master where type='table' ",[],OnFoundTables);  
}  

function OnFoundTables(results)    
{
 var len = results.rows.length;   
 for(var i = 0; i < len; i++ )    
    {   
     var item = results.rows.item(i)   ;
     lstCentre.AddItem(item.tbl_name); 
    }   
}   



function lstLeft_OnLongTouch( item )
{
 //Create dialog window.
 dlgTxt=app.CreateDialog("confirm delete database?");
 var txtSize = 12 ;
 //Create a layout for dialog gui
 layDlg = app.CreateLayout( "linear", "vertical,fillxy,left" );
   //layDlg.SetPadding( 0.02, 0, 0.02, 0.02 );
   dlgTxt.AddLayout( layDlg );
 descItem=app.CreateText( item, 1, 0.06);
   layDlg.AddChild(descItem);
   descItem.SetTextSize( txtSize );
   //Create btns
 btnYDlg = app.CreateButton( "delete "+item, 1, 0.06 );
 btnNDlg = app.CreateButton( "no", 1,0.1);
   
   btnYDlg.SetTextColor( "#ff2222" );
   btnNDlg.SetTextColor( "#22FF22" );
   btnYDlg.SetTextSize( txtSize );
   btnNDlg.SetTextSize( txtSize );
   
   btnYDlg.SetOnTouch( btnY_OnTouch );
   btnNDlg.SetOnTouch( btnN_OnTouch );
   layDlg.AddChild( btnYDlg );
   layDlg.AddChild( btnNDlg );
  //Show dialog. }
  dlgTxt.Show();
}

function btnY_OnTouch(item) {
  dlgTxt.Hide();
  var database=descItem.GetText();
  //app.ShowPopup( "ok faut effacer " + database ); 
  db = app.OpenDatabase( database );   
  db.Delete();
  //dlgTxt.Dismiss();
  emptyList(lstLeft);
  loadLeftList();
}
function btnN_OnTouch() {
 dlgTxt.Hide();
}


function lstCentre_OnTouch( table )
{ 
 emptyList( lstRight );
 selectedTable = table ;
 remindTable.SetText(table);
 //app.ShowPopup( "ItemCentre = " + table, "Short" );
 db.ExecuteSql("SELECT sql FROM sqlite_master where type='table' and tbl_name='"+table+"'",[], OnFoundColumns );
 resizeListes(0.15,0.15,0.7);
}

function OnFoundColumns01(results)    
{ 
  var len = results.rows.length;   
  for(var i = 0; i < len; i++ )    
    {   
     var item = results.rows.item(i)   ;
     lstRight.AddItem(JSON.stringify(item,null,3)); 
    }   
}   
function OnFoundColumns(results)    
{
 var objet = results.rows.item(0)  ;
 //emptyList( lstRight );
 var arraytext= parseSqliteMasterSql( objet['sql']);
 for(var column in arraytext ) 
 {   
  lstRight.AddItem( arraytext[column] ); 
 }   
}

function parseSqliteMasterSql(val) {
 var arrayOne=val.split('(');
 var arrayTwo=arrayOne[1].split(',');
 var last=arrayTwo.pop();
 var clean = last.replace(")","");
 arrayTwo.push( clean );
 return arrayTwo;
}

function parseSqliteMasterSql02(val) {
 var arrayOne=val.split("\W");
 var toRemove=arrayOne.shift();
 return arrayOne;
}
 
function lstRight_OnLongTouch(column){
 var array=column.split(" ");
 selectedColumn = array[0] ; 
 remindColumn.SetText(selectedColumn);
 //app.ShowPopup(selectedTable +" _ " + selectedColumn , "Short" );
 db.ExecuteSql("SELECT count(DISTINCT "+ selectedColumn +" ) FROM "+selectedTable,[], OnFoundRecord );
} 
function OnFoundRecord(q) {
 var len = q.rows.length;   
 var output='L='+len+" ";
 for(var i = 0; i < len; i++ )    
 {   
  var item = q.rows.item(i)   ;
  //lstRight.AddItem(JSON.stringify(item,null,3)); 
  output+= JSON.stringify(item,null,3);
 }  
 app.ShowPopup( output, "long");
}

function lstRight_OnTouch(column){
 var array=column.split(" ");
 selectedColumn = array[0] ; 
 //app.ShowPopup( JSON.stringify(selectedColumn,null,3) );
 db.ExecuteSql("SELECT "+ selectedColumn +"  FROM "+selectedTable,[], OnFoundRecord02 );
} 
function OnFoundRecord02(results) {
 //Create dialog window.
 dlgContent=app.CreateDialog("Content from "+ selectedDb+' '+ selectedTable+ ' ' +selectedColumn );
 var txtSize = 10 ;
 //Create a layout for dialog gui
 layDlgContent = app.CreateLayout( "linear", "vertical,fillxy,left" );
   dlgContent.AddLayout( layDlgContent );
 lstDlg = app.CreateList( '', 0.8, 0.3 );
   lstDlg.SetTextSize( txtSize );
   layDlgContent.AddChild( lstDlg );
 btnOkvu = app.CreateButton( "ok ", 1 );
   layDlgContent.AddChild( btnOkvu );
   btnOkvu.SetOnTouch(  closeDialog02 );  
 
 var len = results.rows.length;   
 //app.ShowPopup( JSON.stringify(results,null,3) );
 var s = "";  
 for(var i = 0; i < len; i++ )   
 {  
  var item = results.rows.item(i)  
  //s += JSON.stringify( item ) + "\n";
  lstDlg.AddItem( JSON.stringify( item ) ) ;
 }  
 //   app.ShowPopup( s );  
 dlgContent.Show(); 
}
function closeDialog02(){dlgContent.Hide()}