/* ======================================================================== *\
!
! *
! * 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): Thomas Bretz, 08/2004 <mailto:tbretz@astro.uni-wuerzburg.de>
!   Author(s): Daniela Dorner, 08/2004 <mailto:dorner@astro.uni-wuerzburg.de>
!
!   Copyright: MAGIC Software Development, 2000-2006
!
!
\* ======================================================================== */

/////////////////////////////////////////////////////////////////////////////
//
// writesequencefile.C
// ===================
//
// reads the sequence information from the database and writes it into a
// txt file
//
// Usage:
//  .x writesequencefile.C+(sequno,"sequpath")
//
// Make sure, that database and password are corretly set in a resource
// file called sql.rc and the resource file is found.
//
// Returns 2 in case of failure, 1 in case of success and 0 if the connection
// to the database is not working.
//
/////////////////////////////////////////////////////////////////////////////
#include <iostream>
#include <iomanip>
#include <fstream>

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

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

#include "MAstro.h"
#include "MTime.h"
#include "MDirIter.h"

#include "MSQLMagic.h"

using namespace std;

Bool_t GetRuns(MSQLServer &serv, ofstream &fout, TString query, TString name, UInt_t nevts=(UInt_t)-1)
{
    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    UInt_t cnt=0;
    UInt_t evts=0;

    fout << name << ":";
    TSQLRow *row=0;
    while ((row = res->Next()) && evts<nevts)
    {
        fout << " " << (*row)[0];
        evts += atoi((*row)[1]);
        cnt++;
    }
    fout << endl;

    delete res;

    if (cnt==0)
    {
        cout << "ERROR - No " << name << " belonging to this sequence found." << endl;
        return kFALSE;
    }

    return kTRUE;
}

TString GetTime(const char *type)
{
    return Form("if(TIME_TO_SEC(fRun%s)<12*60*60,"
                " TIME_TO_SEC(fRun%s)+24*60*60,"
                " TIME_TO_SEC(fRun%s))", type, type, type);
}

Int_t GetTimeC(MSQLServer &serv, const TString &str)
{
    TSQLResult *res = serv.Query(str);
    if (!res)
        return -1;

    TSQLRow *row=res->Next();
    if (!row)
    {
        delete res;
        return -1;
    }

    Int_t rc = (*row)[0] ? atoi((*row)[0]) : -1;
    delete res;
    return rc;
}

Bool_t GetSequence(MSQLMagic &serv, TSQLRow &data, TString sequpath)
{
    UShort_t y;
    Byte_t m, d;

    MTime time;
    time.SetSqlDateTime(data[8]);
    time.GetDateOfSunrise(y, m, d);

    TString date = Form("%04d-%02d-%02d", y, (int)m, (int)d);

    Int_t period = MAstro::GetMagicPeriod(time.GetMjd());

    TString str[6];
    str[0] = serv.QueryNameOfKey("Project",         data[2]);
    str[1] = serv.QueryNameOfKey("Source",          data[3]);
    str[2] = serv.QueryNameOfKey("L1TriggerTable",  data[4]);
    str[3] = serv.QueryNameOfKey("L2TriggerTable",  data[5]);
    str[4] = serv.QueryNameOfKey("HvSettings",      data[6]);
    str[5] = serv.QueryNameOfKey("LightConditions", data[7]);

    if (str[0].IsNull() || str[1].IsNull() || str[2].IsNull() || str[3].IsNull() || str[4].IsNull() || str[5].IsNull())
        return kFALSE;

    //create sequence file
    TString fname(Form("%s/%04d/sequence%08d.txt", sequpath.Data(), atoi(data[0])/10000, atoi(data[0])));
    cout << "Creating " << fname << "..." << flush;

    ofstream fout(fname);
    if (!fout)
    {
        cout << "ERROR - Cannot open file." << endl;
        return kFALSE;
    }

    //write information into file
    fout << "Sequence:        " << data[0]  << endl;
    fout << "Period:          " << period   << endl;
    fout << "Night:           " << date     << endl;
    fout << "LightConditions: " << str[5]   << endl;
    fout << endl;
    fout << "Start:           " << data[8]  << endl;
    fout << "LastRun:         " << data[1]  << endl;
    fout << "Project:         " << str[0]   << endl;
    fout << "Source:          " << str[1]   << endl;
    fout << "ZdMin:           " << data[10] << endl;
    fout << "ZdMax:           " << data[11] << endl;
    fout << "L1TriggerTable:  " << str[2]   << endl;
    fout << "L2TriggerTable:  " << str[3]   << endl;
    fout << "HvSettings:      " << str[4]   << endl;
    fout << "NumEvents:       " << data[9]  << endl;
    fout << endl;

    TString where(Form(" FROM RunData WHERE fSequenceFirst=%s AND fExcludedFDAKEY=1"
                       " AND fRunTypeKEY%%s", data[0]));

    TString query1(Form("SELECT fRunNumber, fNumEvents %s", where.Data()));
    TString query2(Form("SELECT %s %s", GetTime("Start").Data(), where.Data()));
    TString query3(Form("SELECT %s %s", GetTime("Stop").Data(),  where.Data()));

    TString queryA(Form(query1.Data(), " BETWEEN 2 AND 4 ORDER BY fRunNumber"));
    TString queryC(Form(query1.Data(), "=4 ORDER BY fRunNumber"));
    TString queryD(Form(query1.Data(), "=2 ORDER BY fRunNumber"));
    TString queryT(Form(query2.Data(), "=4 ORDER BY fRunNumber LIMIT 1"));

    Int_t timec = GetTimeC(serv, queryT);
    if (timec<0)
    {
        cout << "WARNING - Requesting start time of first calibration run failed." << endl;
        queryT = Form(query3.Data(), "=4 ORDER BY fRunNumber LIMIT 1");
        timec = GetTimeC(serv, queryT);
        if (timec<0)
        {
            cout << "ERROR - Neither start nor stop time of first calibration could be requested." << endl;
            return kFALSE;
        }
    }

    TString query4(Form("=3 ORDER BY ABS(%s-%d) ASC", GetTime("Stop").Data(), timec));
    TString queryP(Form(query1.Data(), query4.Data()));

    //write runs into sequence file
    if (!GetRuns(serv, fout, queryA, "Runs"))
        return kFALSE;

    fout << endl;

    if (!GetRuns(serv, fout, queryC, "CalRuns"))
        return kFALSE;
    if (!GetRuns(serv, fout, queryP, "PedRuns", 1000))
        return kFALSE;
    if (!GetRuns(serv, fout, queryD, "DatRuns"))
        return kFALSE;

    fout << endl;

    cout << " done <Nevts=" << data[9] << ">" << endl;

    return kTRUE;
}

// This tool will work from Period017 (2004_05_17) on...
int writesequencefile(Int_t sequno, TString sequpath)
{
    TEnv env("sql.rc");

    MSQLMagic serv(env);
    if (!serv.IsConnected())
    {
        cout << "ERROR - Connection to database failed." << endl;
        return 0;
    }

    cout << "writesequencefile" << endl;
    cout << "-----------------" << endl;
    cout << endl;
    cout << "Connected to " << serv.GetName() << endl;
    cout << endl;

    //get sequence information from database
    TString query(Form("SELECT fSequenceFirst, fSequenceLast, fProjectKEY, fSourceKEY,"
                       " fL1TriggerTableKEY, fL2TriggerTableKEY, fHvSettingsKEY, "
                       " fLightConditionsKEY, fRunStart, fNumEvents, "
                       " fZenithDistanceMin, fZenithDistanceMax "
                       " FROM Sequences WHERE fSequenceFirst=%d", sequno));
    TSQLResult *res = serv.Query(query);

    TSQLRow *row = 0;
    while ((row = res->Next()))
        if (!GetSequence(serv, *row, sequpath))
            return 2;

    delete res;

    cout << endl;

    return 1;
}