/* ======================================================================== *\
!
! *
! * This file is part of MARS, the MAGIC Analysis and Reconstruction
! * Software. It is distributed to you in the hope that it can be a useful
! * and timesaving tool in analysing Data of imaging Cerenkov telescopes.
! * It is distributed WITHOUT ANY WARRANTY.
! *
! * Permission to use, copy, modify and distribute this software and its
! * documentation for any purpose is hereby granted without fee,
! * provided that the above copyright notice appear in all copies and
! * that both that copyright notice and this permission notice appear
! * in supporting documentation. It is provided "as is" without express
! * or implied warranty.
! *
!
!
!   Author(s): Daniela Dorner, 08/2004 <mailto:dorner@astro.uni-wuerzburg.de>
!   Author(s): Thomas Bretz, 08/2004 <mailto:tbretz@astro.uni-wuerzburg.de>
!
!   Copyright: MAGIC Software Development, 2000-2004
!
!
\* ======================================================================== */

/////////////////////////////////////////////////////////////////////////////
//
// filldotrun.C
// ============
//
// This macro is used in the datacenter to automatically fill the run-database
// with the information stored in the .run-files written by the central
// control.
//
// To following Arehucas versions are Currently supported:
//   040505-0
//   040514-0
//   040518-0
//
// Usage:
//    .x filldotrun.C+("/data/MAGIC/Period019/ccdata", kTRUE)
//
// While the first argument is the directory in which all subdirectories where
// searches for CC_*.run files. All these files were analysed and the run
// info will be put into the DB, eg:
//   "/data/MAGIC"                              would do it for all data
//   "/data/MAGIC/Period019/ccdata"             would do it for one Period
//   "/data/MAGIC/Period019/ccdata/2004_05_17"  would do it for a single day
//   "/data/MAGIC/Period019/ccdata/file.run"    would do it for a single file
//
// The second argument is the 'dummy-mode'. If it is kTRUE dummy-mode is
// switched on and nothing will be written into the database. Instead
// informations about the subtables are displayed. This is usefull for tests
// when adding a new arehucas version support. If it is kFALSE the information
// are written into the subtables and the runs info is written into the
// rundatabase.
//
// In the automatic case it makes sense to check the logfiles to make sure
// that everything is fine...
//
// The macro can also be run without ACLiC but this is a lot slower...
//
// Remark: Running it from the commandline looks like this:
//   root -q -l -b filldotrun.C+\(\"path\"\,kFALSE\) 2>&1 | tee filldotrun.log
//
// Returns 0 in case of failure and 1 in case of success.
//
/////////////////////////////////////////////////////////////////////////////
#include <iostream>
#include <iomanip>
#include <fstream>

#include <TMath.h>
#include <TRegexp.h>

#include <TSQLRow.h>
#include <TSQLResult.h>

#include "MTime.h"
#include "MDirIter.h"
#include "MSQLServer.h"

using namespace std;

Bool_t ExistStr(MSQLServer &serv, const char *column, const char *table, const char *test)
{
    TString query(Form("SELECT %s FROM %s WHERE %s='%s'", column, table, column, test));
    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    Bool_t rc = kFALSE;

    TSQLRow *row=res->Next();
    if (row && (*row)[0])
        rc=kTRUE;

    delete res;
    return rc;
}

Int_t QueryNameKEY(MSQLServer &serv, Bool_t dummy, const char *col, const char *name, Bool_t insert=kTRUE)
{
    TString query;

    query = Form("SELECT f%sKEY FROM MyMagic.%s WHERE f%sName='%s'", col, col, col, name);
    TSQLResult *res = serv.Query(query);
    if (!res)
        return -1;

    TSQLRow *row=res->Next();

    Int_t rc = row && (*row)[0] ? atoi((*row)[0]) : -1;

    delete res;

    if (rc>=0)
        return rc;

    if (!insert)
        return -1;

    query = Form("INSERT MyMagic.%s (f%sName) VALUES (\"%s\");", col, col, name);

    if (dummy)
    {
        cout << query << endl;
        return 0;
    }

    res=serv.Query(query);
    if (!res)
        return -1;

    delete res;

    Int_t key = QueryNameKEY(serv, dummy, col, name, kFALSE);
    if (key>0)
    {
        cout << "New " << col << ": " << name << endl;
        return key;
    }

    cout << "ERROR: " << query << endl;
    return kFALSE;
}


Int_t insert(MSQLServer &serv, Bool_t dummy, TString filename)
{
    ifstream fin(filename);
    if (!fin)
    {
        cout << "Could not open file " << filename << endl;
        return -1;
    }

    TString strng;
    strng.ReadLine(fin);
    if (strng!=TString("[CC Plain Run Summary File]"))
    {
        cout << filename << ": No Plain Run Summary File" << endl;
        cout << "First Line: " << strng << endl;
        cout << endl;
        return -1;
    }

    strng.ReadLine(fin);
    TRegexp reg("[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]");
    TString arehucas = strng(reg);
    arehucas.Prepend("20");
    arehucas.ReplaceAll("-", "");

    Int_t version = atoi(arehucas.Data());
    if (version!=200405050 && version!=200405140 && version!=200405180)
    {
        cout << filename << ": File Version unknown - please update the macro!" << endl;
        cout << "Second Line: " << strng << endl;
        cout << endl;
        return -1;
    }

    cout << "  V" << version << " " << flush;

    Int_t cnt=0;
    while (1)
    {
        // ========== Col 1: Run Number =========
        //Reading the line
        //and converting some strings to ints/floats
        strng.ReadToDelim(fin, ' ');
        if (!fin)
            break;

        Int_t runnumber = atoi(strng.Data());

        //runnumber=0 means no valid dataset
        //-> continue
        if (runnumber == 0)
        {
            strng.ReadLine(fin);
            cout << "Runnumber == 0" << endl;
            continue;
        }

        //cout << "RunNo: " << runnumber << " ";

        if (ExistStr(serv, "fRunNumber", "MyMagic.RunData", strng.Data()))
        {
            // FIXME: Maybe we can implement an switch to update mode?
            cout << "Run #" << runnumber << " already existing... skipped." << endl;
            strng.ReadLine(fin);
            continue;
        }

        // ========== Col 1: Run Type =========
        strng.ReadToDelim(fin, ' ');
        if (strng.Contains("???"))
            strng="n/a";

        Int_t runtype = QueryNameKEY(serv, dummy, "RunType", strng.Data(), kFALSE);
        if (runtype<0)
        {
            cout << "ERROR - RunType " << strng << " not available." << endl;
            strng.ReadLine(fin);
            continue;
        }

        //cout << runtype << " ";

        // ========== Col 2,3: Start Time =========
        TString startdate, starttime;
        startdate.ReadToDelim(fin, ' ');
        starttime.ReadToDelim(fin, ' ');
        //cout << startdate << " " << starttime << " ";

        // ========== Col 4,5: Stop Time =========
        TString stopdate, stoptime;
        stopdate.ReadToDelim(fin, ' ');
        stoptime.ReadToDelim(fin, ' ');
        //cout << stopdate << " " << stoptime << " ";

        if (startdate.Contains("???"))
            startdate="0000-00-00";
        if (starttime.Contains("???"))
            starttime="00:00:00";
        if (stopdate.Contains("???"))
            stopdate="0000-00-00";
        if (stoptime.Contains("???"))
            stoptime="00:00:00";

        // ========== Col 6: Source Name =========
        strng.ReadToDelim(fin, ' ');
        if (strng.Contains("???"))
            strng="Unavailable";

        Int_t sourcekey = QueryNameKEY(serv, dummy, "Source", strng.Data());
        if (sourcekey<0)
        {
            strng.ReadLine(fin);
            continue;
        }
        //cout << sourcekey << " ";

        // ========== Col 7, 8: Local source position =========
        strng.ReadToDelim(fin, ' ');
        Float_t zd = atof(strng.Data());

        strng.ReadToDelim(fin, ' ');
        Float_t az = atof(strng.Data());

        //cout << zd << " " << az << " ";

        // ========== Col 9: Number of Events =========
        strng.ReadToDelim(fin, ' ');
        Int_t evtno = atoi(strng.Data());

        //cout << evtno << " ";

        // ========== Col 10: Project Name =========
        strng.ReadToDelim(fin, ' ');
        if (strng.Contains("???"))
            strng="Unavailable";

        Int_t projkey = QueryNameKEY(serv, dummy, "Project", strng.Data());
        if (projkey<0)
        {
            strng.ReadLine(fin);
            continue;
        }
        //cout << projkey << " ";

        // ========== Col 10: Trigger Table Name =========
        strng.ReadToDelim(fin, ' ');
        if (strng.Contains("???"))
            strng="n/a";

        Int_t triggerkey = QueryNameKEY(serv, dummy, "TriggerTable", strng.Data());
        if (triggerkey<0)
        {
            strng.ReadLine(fin);
            continue;
        }
        //cout << triggerkey << " ";

        // ========== Col 11-13: TrigRate, L2 UnPresc Rate, L2 Presc Rate ==========
        strng.ReadToDelim(fin, ' ');
        Float_t trigrate = atof(strng.Data());

        strng.ReadToDelim(fin, ' ');
        Float_t l2uprate = atof(strng.Data());

        strng.ReadToDelim(fin, ' ');
        Float_t l2prrate = atof(strng.Data());

        // ========== Col 14,15: DaqRate, Storage Rate ==========
        strng.ReadToDelim(fin, ' ');
        Float_t daqrate = atof(strng.Data());

        strng.ReadToDelim(fin, ' ');
        Float_t storerate = atof(strng.Data());

        // ========== Col 16: HV table =========
        if (version==200405050 || version==200405140)
            strng.ReadToDelim(fin, '\n');
        else
            strng.ReadToDelim(fin, ' ');
        if (strng.Contains("???"))
            strng="n/a";

        Int_t hvkey = QueryNameKEY(serv, dummy, "HvSettings", strng.Data());
        if (hvkey<0)
        {
            //strng.ReadLine(fin);
            continue;
        }

        if (version==200405180)
            strng.ReadLine(fin);

        //continue;

        //cout << endl;

        // ================================================================
        // ========== Data read from file now access the database =========
        // ================================================================

        //assemlbe the query that is needed to insert the values of this run
        TString query;
        query += "INSERT MyMagic.RunData SET ";

        query += Form("fRunNumber=%d, ",  runnumber);
        query += Form("fRunTypeKEY=%d, ", runtype);
        query += Form("fProjectKEY=%d, ", projkey);
        query += Form("fSourceKEY=%d, ",  sourcekey);
        query += Form("fNumEvents=%d, ",  evtno);
        query += Form("fRunStart=\"%s %s\", ", startdate.Data(), starttime.Data());
        query += Form("fRunStop=\"%s %s\", ", stopdate.Data(), stoptime.Data());
        query += Form("fTriggerTableKEY=%d, ", triggerkey);
        query += Form("fHvSettingsKEY=%d, ", hvkey);
        if (!TMath::IsNaN(zd) && TMath::Finite(zd))
            query += Form("fZenithDistance=%d, ", TMath::Nint(zd));
        if (!TMath::IsNaN(az) && TMath::Finite(az))
            query += Form("fAzimuth=%d, ", TMath::Nint(az));
        if (!TMath::IsNaN(storerate) && TMath::Finite(storerate))
            query += Form("fDaqStoreRate=%d, ", TMath::Nint(storerate));
        if (!TMath::IsNaN(daqrate) && TMath::Finite(daqrate))
            query += Form("fDaqTriggerRate=%d, ", TMath::Nint(daqrate));
        if (!TMath::IsNaN(trigrate) && TMath::Finite(trigrate))
            query += Form("fMeanTriggerRate=%d, ", TMath::Nint(trigrate));
        if (!TMath::IsNaN(l2prrate) && TMath::Finite(l2prrate))
            query += Form("fL2RatePresc=%d, ", TMath::Nint(l2prrate));
        if (!TMath::IsNaN(l2uprate) && TMath::Finite(l2uprate))
            query += Form("fL2RateUnpresc=%d, ", TMath::Nint(l2uprate));
        query += "fMagicNumberKEY=1, fExcludedFDAKEY=1";

        //cout << query << endl;
        cnt++;

        //cout << query << endl;
        //continue;

        if (dummy)
            continue;

        //send query, add dataset to DB
        TSQLResult *res = serv.Query(query);
        if (!res)
            return -1;

        delete res;
    }

    return cnt;

}

// This tool will work from Period017 (2004_05_17) on...
int filldotrun(const TString path="/data/MAGIC/Period018/ccdata", Bool_t dummy=kTRUE)
{
    MSQLServer serv("mysql://hercules:d99swMT!@localhost");
    if (!serv.IsConnected())
    {
        cout << "ERROR - Connection to database failed." << endl;
        return;
    }
    cout << "filldotrun" << endl;
    cout << "----------" << endl;
    cout << endl;
    cout << "Connected to " << serv.GetName() << endl;
    cout << "Search Path: " << path << endl;
    cout << endl;

    if (path.EndsWith(".run"))
    {
        cout << path(TRegexp("CC_.*.run", kFALSE)) << flush;
        Int_t n = insert(serv, dummy, path);
        cout << " <" << n << "> " << (dummy?"DUMMY":"") << endl;

        return n<0 ? 0 : 1;
    }

    MDirIter Next(path, "CC_*.run", -1);
    while (1)
    {
        TString name = Next();
        if (name.IsNull())
            break;

        cout << name(TRegexp("CC_.*.run", kFALSE)) << flush;
        Int_t n = insert(serv, dummy, name);
        cout << " <" << n << "> " << (dummy?"DUMMY":"") << endl;

        if (n<0)
            return 0;
    }

    return 1;
}
