using namespace std;

//missing:
// remove sourcekey in paths
// create tables in DB

int numevents(TString night="20130418", TString inpath="/daq/analysis/1/", TString table= "AnalysisResultsRunLP", Bool_t dummy=kTRUE, Bool_t pernight=kFALSE)
{

    //connect to mysql server
    //MSQLServer serv("sql.rc");
    MSQLMagic serv("sql.rc");
    if (!serv.IsConnected())
    {
        cout << "ERROR - Connection to database failed." << endl;
        return 0;
    }

    //select runs, where star finished, format the list nicely
    TString query;
    query = " SELECT CONCAT(fNight, '_', LPAD(fRunID, 3, 0)), SUBSTRING(fNight, 1,4), SUBSTRING(fNight, 5,2), SUBSTRING(fNight, 7,2), ";
    query += " fNight, fRunID FROM RunInfo";

    //and only data runs
    query+=" WHERE fRunTypeKEY=1 ";
    query+=" AND fNight="+night;
    //query+=" AND fRunID=128 ";
    if (pernight)
        query+=" GROUP BY fNight";

    cout << "Q: " << query << endl;

    //post the query
    TSQLResult *res = serv.Query(query);
    if (!res)
    {
        cout << "ERROR - no result from query " << query << endl;
        return 2;
    }

    //allocate variables to use in the loop
    TString night;
    TString runid;
    TString run;
    TString year;
    TString month;
    TString day;
    TString ganymed_fname;
    TString star_fname;
    TString where;
    TString vars;

    //the variables to save into the db
    Int_t fNumEvtsAfterCleaning = 0;
    Int_t fNumEvtsAfterQualCuts = 0;
    Int_t fNumEvtsAfterBgCuts   = 0;
    Float_t fNumBgEvts          = 0;
    Float_t fNumSigEvts         = 0;
    Float_t fNumExcEvts         = 0;
    Float_t fNumIslandsMean     = 0;

    //loop over the data files
    TSQLRow *row=0;
    Int_t counter=0;

    /*
    //cout the data names
    cout<<"#evts_after_cleaning" << "    "
        <<"evts_after_qual-cuts" << "    "
        <<"evts_after_bg-cuts" << "    "
        <<"mean_evts_from_off_regions"<< "    "
        <<"evts_from_on_region"  << "    "
        <<"calculated_excess_evts_in_file" << "    " << endl;
    */

    while ((row=res->Next()))
    {
        //use the results from the query
        night=(*row)[4];
        runid=(*row)[5];
        run=(*row)[0];
        year=(*row)[1];
        month=(*row)[2];
        day=(*row)[3];

        // to be fixed: path for nightly ganymeds
        if (!pernight)
            ganymed_fname=inpath+"/ganymed_run/"+year+"/"+month+"/"+day+"/"+run+"-analysis.root";
        else
            ganymed_fname=inpath+"/ganymed_night/"+night+"-analysis.root";
        //cout << "gf => " << ganymed_fname << endl;

        if (!pernight)
        {
            star_fname=inpath+"/star/"+year+"/"+month+"/"+day+"/"+run+"_I.root";
            //cout << "sf => " << star_fname << endl;

            //check star file status
            if (gSystem->AccessPathName(star_fname))
            {
                cout << "ERROR - file " << star_fname << " does not exist." << endl;
                continue;
            }

            //try to open the star file
            TFile star_file(star_fname, "READ");
            if (!star_file.IsOpen())
            {
                cout << "ERROR - Could not open file " << star_fname << endl;
                continue;
            }

            //Get the events tree
            TTree *star_tree = (TTree *)star_file.Get("Events");
            if (!star_tree)
            {
                cout << "ERROR - Could not read tree " << star_fname << endl;
                continue;
            }

            //the number of events after cleaning
            fNumEvtsAfterCleaning = star_tree->GetEntries();

            //get the mean number of islands in the _I.root file on runbasis
            star_tree->Draw("MImagePar.fNumIslands>>IslandHisto","","");
            TH1F *HistJo = (TH1F*)gDirectory->Get("IslandHisto");
            fNumIslandsMean = HistJo->GetMean();
        }

        //check ganymed file status
        if (gSystem->AccessPathName(ganymed_fname))
        {
            cout << "ERROR - file " << ganymed_fname << " does not exist." << endl;
            continue;
        }

        //try to open the ganymed file
        TFile ganymed_file(ganymed_fname, "READ");
        if (!ganymed_file.IsOpen())
        {
            cout << "ERROR - Could not open file " << ganymed_fname << endl;
            continue;
        }

        //Get the status display contents
        MStatusArray arr;
        if (arr.Read()<=0)
        {
            cout << "ERROR - could not read MStatusArray, file " << ganymed_fname << endl;
            continue;
        }

        //Get number of events after quality cuts
        //from the number of entries in a histogram
        TH1F *precut =
                (TH1F*)arr.FindCanvas("PreCut")->FindObject("SizeSame");
        if (!precut)
        {
            cout << "WARNING - Reading of PreCut canvas failed." << endl;
            continue;
        }
        fNumEvtsAfterQualCuts = precut->GetEntries();

        //Get number of events after background rejection cuts
        //from the number of entries in a histogram
        TH1F *postcut =
                (TH1F*)arr.FindCanvas("PostCut")->FindObject("SizeSame");
        if (!postcut)
        {
            cout << "WARNING - Reading of PostCut canvas failed." << endl;
            continue;
        }
        fNumEvtsAfterBgCuts = postcut->GetEntries();

        //Get signal, bg and excess events
        MHThetaSq *halphaon = (MHThetaSq*)arr.FindObjectInCanvas("MHThetaSq", "Hist");
        if (!halphaon)
        {
            cout << "WARNING - Reading of MHThetaSq failed, file " << ganymed_fname << endl;
            continue;
        }
        const MAlphaFitter &fit = halphaon->GetAlphaFitter();
        if (!&fit)
        {
            cout << "WARNING - Reading of MAlphaFitter failed, file " << ganymed_fname << endl;
            continue;
        }

        //check if the excess events number is small and close to 0
        //due to the computer precision error when subtracting sig and bg events
        fNumBgEvts  = fit.GetEventsBackground();
        fNumSigEvts = fit.GetEventsSignal();
        fNumExcEvts = fit.GetEventsExcess();
        fNumExcEvts = fabs(fNumExcEvts)<1e-5 ? 0 : fNumExcEvts;

        /*
        cout<< fNumEvtsAfterCleaning<< "    "
            << fNumEvtsAfterQualCuts<< "    "
            << fNumEvtsAfterBgCuts  << "    "
            << fNumBgEvts           << "    "
            << fNumSigEvts          << "    "
            << fNumExcEvts          << endl; 
        */

        //inserting or updating the information in the database
        vars = Form("fRunID=%s, fNight=%s,", runid.Data(), night.Data());
        vars += Form(" fNumEvtsAfterQualCuts=%d, "
                    " fNumEvtsAfterBgCuts=%d, "
                    " fNumBgEvts=%6.1f, "
                    " fNumSigEvts=%6.1f, "
                    " fNumExcEvts=%6.1f ",
                    fNumEvtsAfterQualCuts,
                    fNumEvtsAfterBgCuts,
                    fNumBgEvts,
                    fNumSigEvts,
                    fNumExcEvts
                   );
        if (!pernight)
            vars += Form(", fNumIslandsMean=%6.2f ",
                         ", fNumEvtsAfterCleaning=%d ",
                         fNumIslandsMean,
                         fNumEvtsAfterCleaning
                        );
                    

        if (pernight)
            where = Form("fNight=%s", night.Data());
        else
            where = Form("fRunID=%s AND fNight=%s", runid.Data(), night.Data());

        cout << "vars: " << vars << endl;
        cout << "where: " << where << endl;

        if (!serv.InsertUpdate(table, vars, where))
        {
            cout << "ERROR - insert/update to DB failed  (vars:" << vars << " where: " << where << ")." << endl;
            return 2;
	}

    }


    return 1;
}