/* ======================================================================== *\
!
! *
! * 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-2004
!
!
\* ======================================================================== */

/////////////////////////////////////////////////////////////////////////////
//
// buildsequenceentries.C
// ======================
//
// to group the runs of one night into sequences, this marco:
// - reads the runinformation of one night from the database
// - sorts the runs by source
// - groups the runs to sequences such that each run belongs
//   to the nearest (in time) calibration run
//
// apart from that the runs in one sequence must have the same
// hv-settings, trigger tables, light conditions, DT tables,
// trigger delay tables and testflag
//
/////////////////////////////////////////////////////////////////////////////
#include <iostream>
#include <iomanip>
#include <fstream>

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

#include <TEnv.h>
#include <TMath.h>
#include <TExMap.h>
#include <TArrayI.h>
#include <TRegexp.h>
#include <TSystem.h>

#include <MTime.h>
#include <MDirIter.h>

using namespace std;

int debug = 0;

Bool_t DeleteSequence(MSQLServer &serv, Int_t sequ, Bool_t dummy)
{
    TString query1(Form("DELETE FROM Calibration WHERE fSequenceFirst=%d", sequ));
    TString query2(Form("DELETE FROM Star WHERE fSequenceFirst=%d", sequ));
    TString query3(Form("DELETE FROM SequenceProcessStatus WHERE fSequenceFirst=%d", sequ));
    TString query4(Form("UPDATE RunData SET fSequenceFirst=0 WHERE fSequenceFirst=%d", sequ));
    TString query5(Form("DELETE FROM Sequences WHERE fSequenceFirst=%d AND fManuallyChangedKEY=1", sequ));

    TString fname(Form("/mnt/stk01/sequences/%04d/sequence%08d.txt", sequ/10000, sequ));
    TString command(Form("rm -r /magic/data/callisto/%04d/%08d/", sequ/10000, sequ));
    TString command2(Form("rm -r /magic/data/star/%04d/%08d/", sequ/10000, sequ));

    if (dummy)
    {
        cout << "not using dummy=kTRUE the following commands would be executed: " << endl;
        cout << "queries: " << endl;
        cout << query1 << endl;
        cout << query2 << endl;
        cout << query3 << endl;
        cout << query4 << endl;
        cout << query5 << endl;
        cout << "removing files:" << endl;
        cout << "unlink " << fname << endl;
        cout << command << endl;
        cout << command2 << endl;
        return kTRUE;
    }

    TSQLResult *res = serv.Query(query1);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query2);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query3);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query4);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query5);
    if (!res)
        return kFALSE;
    delete res;

    gSystem->Unlink(fname);

    gSystem->Exec(command);
    gSystem->Exec(command2);

    return kTRUE;
}

Int_t DoCheck(TSQLResult &res)
{
    TArrayI data(5);
    Int_t n = 0;

    TSQLRow *row=0;
    while ((row=res.Next()))
    {
        n++;

        if (data[0]==0)
        {
            for (int i=0; i<data.GetSize(); i++)
                data[i] = atoi((*row)[i]);
            continue;
        }

        for (int i=1; i<data.GetSize(); i++)
        {
            if (data[i] != atoi((*row)[i]))
                return i+1;
        }
    }
    return n==0 ? 0 : -1;
}

Bool_t CheckRuns(MSQLServer &serv, Int_t from, Int_t to, Int_t type)
{
    TString query("SELECT fRunNumber, fL1TriggerTableKEY, fL2TriggerTableKEY,"
                  " fCalibrationScriptKEY, fProjectKEY FROM RunData");
    query += Form(" WHERE fRunTypeKEY=%d AND fExcludedFDAKEY=1 AND "
                  " (fRunNumber BETWEEN %d AND %d)"
                  " ORDER BY fRunNumber", type, from, to);

    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    Int_t rc = DoCheck(*res);
    delete res;

    switch (rc)
    {
    case 0: cout << "ERROR - No runs found for check!"             << endl; break;
    case 1: cout << "ERROR - fRunNumber doesn't match!"            << endl; break;
    case 2: cout << "ERROR - fL1TriggerTableKEY doesn't match!"    << endl; break;
    case 3: cout << "ERROR - fL2TriggerTableKEY doesn't match!"    << endl; break;
    case 4: cout << "ERROR - fCalibrationScriptKEY doesn't match!" << endl; break;
    case 5: cout << "ERROR - fProjectKEY doesn't match!"           << endl; break;
    }

    return rc<0;
}

Bool_t CheckSequence(MSQLServer &serv, Int_t from, Int_t to, Bool_t dummy)
{
    Bool_t rc=kFALSE;

    TString query("SELECT fSequenceFirst, count(*) FROM RunData ");
    query += Form(" WHERE fExcludedFDAKEY=1 AND (fRunNumber BETWEEN %d AND %d)"
                  " GROUP BY fSequenceFirst", from, to);

    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    Int_t count=0;
    Int_t nor=0;
    TArrayI sequences;

    TSQLRow *row=0;
    while ((row=res->Next()))
    {
        sequences.Set(count+1);
        sequences.AddAt(atoi((*row)[0]), count);
//        cout << "sequence:   " << sequences.At(count) << endl;
        nor=atoi((*row)[1]);
        count++;
    }

    delete res;

    if (sequences.GetSize()>1)
    {
        for (Int_t i=0;i<sequences.GetSize();i++)
        {
            rc=kTRUE;
            if (sequences.At(i)!=0)
            {
                cout << "deleting sequence " << sequences.At(i) << "... <" << i << ">" << endl;
                if(!DeleteSequence(serv, sequences.At(i), dummy))
                   rc=kFALSE;
            }
        }
        return rc;
    }

    if (sequences.At(0)==0)
        return kTRUE;


    query="SELECT fRunNumber FROM RunData ";
    query += Form(" WHERE fExcludedFDAKEY=1 AND (fRunNumber BETWEEN %d AND %d)"
                  " ORDER BY fRunNumber", from, to);

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

    TArrayI runs;
    count=0;
    row=0;
    while ((row=res->Next()))
    {
        runs.Set(count+1);
        runs.AddAt(atoi((*row)[0]), count);
        count++;
    }

    delete res;


    if (nor==runs.GetSize() && sequences.GetSize()==1)
    {
        query="SELECT fRunNumber FROM RunData ";
        query += Form(" WHERE fSequenceFirst=%d ", sequences.At(0));

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

        TArrayI oldruns;
        count=0;
        row=0;
        while ((row=res->Next()))
        {
            oldruns.Set(count+1);
            oldruns.AddAt(atoi((*row)[0]), count);
            count++;
        }

        if (runs.GetSize()!=oldruns.GetSize())
        {
            cout << "different sequsizes -> deleting sequence " << sequences.At(0) << endl;
            return DeleteSequence(serv, sequences.At(0), dummy);
        }

        for (Int_t i=0; i<runs.GetSize(); i++)
        {
            if (runs.At(i)==oldruns.At(i))
                continue;
            cout << "different runs -> deleting sequence " << sequences.At(0) << endl;
            return DeleteSequence(serv, sequences.At(0), dummy);
        }

        delete res;
    }

    cout << "same sequence -> nothing to do..." << endl;
    return rc;
}

Bool_t InsertSequence(MSQLServer &serv, Int_t from, Int_t to)
{

    // ========== Request number of events ==========
    TString query("SELECT SUM(fNumEvents), "
                  " SUM(if(TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart)<0,"
                  "  TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart)+24*60*60,"
                  "  TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart))), ");
    query +=      " MIN(fZenithDistance), MAX(fZenithDistance), ";
    query +=      " MIN(fAzimuth), MAX(fAzimuth) ";
    query += Form(" FROM RunData WHERE fRunTypeKEY=2 AND "
                  " (fRunNumber BETWEEN %d AND %d) AND fExcludedFDAKEY=1",
                  from, to);

    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    TSQLRow *row = res->Next();
    if (!row || !(*row)[0])
    {
        cout << "ERROR - No result from query: " << query << endl;
        return kFALSE;
    }

    TString nevts = (*row)[0];
    TString secs  = (*row)[1];
    TString zdmin = (*row)[2];
    TString zdmax = (*row)[3];
    TString azmin = (*row)[4];
    TString azmax = (*row)[5];

    delete res;

    // ========== Request start time of sequence ==========
    query = Form("SELECT fRunStart FROM RunData WHERE fRunNumber=%d AND fExcludedFDAKEY=1", from);

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

    row = res->Next();
    if (!row || !(*row)[0])
    {
        cout << "ERROR - No result from query: " << query << endl;
        return kFALSE;
    }

    TString start((*row)[0]);

    delete res;

    // ========== Request data of sequence ==========
    query = Form("SELECT fSourceKEY, fProjectKEY, "
                 " fL1TriggerTableKEY, fL1TriggerTableKEY,"
                 " fHvSettingsKEY, fDiscriminatorThresholdTableKEY,"
                 " fTriggerDelayTableKEY, fLightConditionsKEY, fTestFlagKEY"
                 " FROM RunData"
                 " WHERE fRunTypeKEY=2 AND fExcludedFDAKEY=1 AND (fRunNumber BETWEEN %d AND %d)"
                 " LIMIT 1", from, to);

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

    row = res->Next();
    if (!row)
    {
        cout << "ERROR - No result from query: " << query << endl;
        return kFALSE;
    }

    TString query1("INSERT Sequences SET");
    query1+=Form(" fSequenceFirst=%d,  fSequenceLast=%d,", from, to);
    query1+=Form(" fProjectKEY=%s,", (*row)[0]);
    query1+=Form(" fSourceKEY=%s,", (*row)[1]);
    query1+=Form(" fNumEvents=%s,", nevts.Data());
    query1+=Form(" fRunTime=%s,", secs.Data());
    query1+=Form(" fRunStart=\"%s\",", start.Data());
    query1+=Form(" fZenithDistanceMin=%s,", zdmin.Data());
    query1+=Form(" fZenithDistanceMax=%s,", zdmax.Data());
    query1+=Form(" fAzimuthMin=%s,", azmin.Data());
    query1+=Form(" fAzimuthMax=%s,", azmax.Data());
    query1+=Form(" fL1TriggerTableKEY=%s,", (*row)[2]);
    query1+=Form(" fL2TriggerTableKEY=%s,", (*row)[3]);
    query1+=Form(" fHvSettingsKEY=%s,", (*row)[4]);
    query1+=Form(" fDiscriminatorThresholdTableKEY=%s,", (*row)[5]);
    query1+=Form(" fTriggerDelayTableKEY=%s,", (*row)[6]);
    query1+=Form(" fLightConditionsKEY=%s,", (*row)[7]);
    query1+=Form(" fTestFlagKEY=%s, fManuallyChangedKEY=1", (*row)[8]);


    TString query2 = Form("UPDATE RunData SET fSequenceFirst=%d WHERE"
                          "  (fRunNumber  BETWEEN %d AND %d) AND"
                          "  (fRunTypeKEY BETWEEN  2 AND  4) AND"
                          "  fSourceKEY=%d AND fHvSettingsKEY=%s AND fExcludedFDAKEY=1",
                          from, from, to, (*row)[1], (*row)[4]);

    TString query3 = Form("INSERT SequenceProcessStatus SET fSequenceFirst=%d ", from);

    delete res;

    res = serv.Query(query1);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query2);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query3);
    if (!res)
        return kFALSE;
    delete res;

    return kTRUE;
}

Bool_t NewSequence(MSQLServer &serv, Int_t from, Int_t to, Bool_t dummy)
{
    cout << "Found Sequence (" << from << ", " << to << ") ... checking runs..." << flush;

    if (!CheckRuns(serv, from, to, 2))
    {
        cout << "Warning - Found inconsistency in data-runs (" << from << ", " << to << ")" << endl;
        //sequence is not built, but kTRUE is returned, to allow
        //the automatic processing of the other sequences of this day
        return kTRUE;
    }
    if (!CheckRuns(serv, from, to, 3))
    {
        cout << "Warning - Found inconsistency in ped-runs (" << from << ", " << to << ")" << endl;
        //sequence is not built, but kTRUE is returned, to allow
        //the automatic processing of the other sequences of this day
        return kTRUE;
    }

    cout << "ok." << endl;


    cout << "checking Sequence..." << endl;

    if (!CheckSequence(serv, from, to, dummy))
    {
        cout << " inserting sequence not necessary" << endl;
        return kTRUE;
    }


    if (dummy)
        return kTRUE;

    Bool_t rc = InsertSequence(serv, from, to);
    if (!rc)
        cout << "InsertSequence failed!" << endl;

    return rc;
}

Bool_t Process(MSQLServer &serv, Int_t from, Int_t to, Bool_t dummy)
{

    TString query(Form("SELECT fRunNumber, fRunTypeKEY, fRunStart, fRunStop"
                       " FROM RunData"
                       " WHERE fRunNumber BETWEEN %d AND %d AND "
                       " fExcludedFDAKEY=1 AND (fRunTypeKEY BETWEEN 2 AND 4)"
                       " ORDER BY fRunNumber", from, to));

    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    TExMap map;

    Int_t start=0;
    Int_t stop=0;
    Int_t last=0;
    Int_t first=0;

    MTime lasttime;

    TSQLRow *row=0;

    enum { UNKNOWN, PED=3, CAL=4, DATA=2 };
    Char_t status = UNKNOWN;

    Int_t nblocks = 0;

    while ((row=res->Next()))
    {
        if (!(*row)[1])
            continue;

        if (start==0)
        {
            first = atoi((*row)[0]);
            if (debug)
                cout << "First Run: " << first << endl;
        }

        switch (atoi((*row)[1]))
        {
        case CAL: // ---------- CALIBRATION ----------
            if (status!=CAL)
            {
                start = stop = atoi((*row)[0]);
                if (!(*row)[2])
                    cout << "No time available... skipped." << endl;
                else
                {
                    MTime *tm = new MTime;
                    tm->SetSqlDateTime((*row)[2]);
                    map.Add((ULong_t)map.GetSize(), (Long_t)tm, (Long_t)nblocks);
                }
            }
            status = CAL;
            break;
        default:
            if (status==CAL)
            {
                MTime *tm = new MTime(lasttime);
                map.Add((ULong_t)map.GetSize(), (Long_t)tm, (Long_t)nblocks);

                stop = last;
                nblocks++;
                if (debug)
                    cout << "Cal Block #" << nblocks << " from " << start << " to " << last << endl;
            }
            status = UNKNOWN;
            break;
        }
        last = atoi((*row)[0]);
        lasttime.SetSqlDateTime((*row)[3]);
    }
    if (status==CAL)
    {
        stop = last;
        nblocks++;
        if (debug)
            cout << "Cal Block #" << nblocks << " from " << start << " to " << stop << endl;
    }

    if (debug)
        cout << "Last Run: " << last << endl;
    delete res;

    if (debug)
        cout << "Found " << nblocks << " calibration blocks" << endl;

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

    Int_t n = -1;

    Bool_t rc = kTRUE;

    start = first;
    while ((row=res->Next()))
    {
        if (!(*row)[1])
            continue;

        MTime tstart, tstop;
        tstart.SetSqlDateTime((*row)[2]);
        tstop.SetSqlDateTime((*row)[3]);

        MTime    min;
        Int_t    nmin = -1;
        Double_t dmin = 1e35;

        Long_t key, val;
        TExMapIter nmap(&map);
        while (nmap.Next(key, val))
        {
            MTime *t = (MTime*)key;

            if (nmin==-1)
            {
                nmin = val;
                min  = *(MTime*)key;
                dmin = fabs((Double_t)*t-(Double_t)tstart);
            }

            if (fabs((Double_t)*t-(Double_t)tstart) < dmin)
            {
                nmin = val;
                dmin = fabs((Double_t)*t-(Double_t)tstart);
                min = *t;
            }
            if (fabs((Double_t)*t-(Double_t)tstop) < dmin)
            {
                nmin = val;
                dmin = fabs((Double_t)*t-(Double_t)tstop);
                min = *t;
            }
        }

        if (n!=nmin)
        {
            if (n!=-1)
            {
                if (!NewSequence(serv, start, last, dummy))
                {
                    rc = kFALSE;
                    //continue;
                }
            }
            n = nmin;
            start = atoi((*row)[0]);
        }
        last = atoi((*row)[0]);
    }

    delete res;

    if (n!=-1 && start!=last)
    {
        if (!NewSequence(serv, start, last, dummy))
            rc = kFALSE;
    }

    if (debug)
        cout << endl;

    return rc; 
}



int buildsequenceentries(TString day, Bool_t dummy=kTRUE)
{
    TEnv env("sql.rc");

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

    cout << "buildsequences" << endl;
    cout << "--------------" << endl;
    cout << endl;
    cout << "Connected to " << serv.GetName() << endl;
    cout << "Night of sunrise at: " << day << endl;
    cout << endl;

    day += " 13:00:00";
    const TString cond(Form("(fRunStart>ADDDATE(\"%s\", INTERVAL -1 DAY) AND fRunStart<\"%s\")",
                            day.Data(), day.Data()));

    TString query(Form("SELECT fRunNumber, fSourceKEY, fProjectKEY, fHvSettingsKEY,  fLightConditionsKEY, fDiscriminatorThresholdTableKEY, fTriggerDelayTableKEY FROM RunData WHERE %s AND fExcludedFDAKEY=1 order by fRunNumber", cond.Data()));

    TSQLResult *res = serv.Query(query);
    if (!res)
        return 0;

    TString keys[6]= { "NULL", "NULL", "NULL", "NULL", "NULL", "NULL" };
    TString stop     = "NULL";
    TString runstart = "NULL";
    TString runstop  = "NULL";
    Int_t count = 0;
    TExMap blocks;
    Int_t runbegin;
    Int_t runend;

    TSQLRow *row=0;
    while ((row=res->Next()))
    {
        if (count==0)
        {
            for (Int_t i=1 ; i<7 ; i++)
                keys[i-1]=(*row)[i];
            runstart=(*row)[0];
        }

        for (Int_t i=1 ; i<7 ; i++)
        {
            runbegin=atoi(runstart.Data());
            runend=atoi(runstop.Data());
            if (i==2 && runbegin>20100 && runend<45100)
                continue;

            TString value=(*row)[i];
            TString key=keys[i-1];
            if (!value.CompareTo(key)) 
                continue;

            keys[i-1]=value;
            //hier einfuellen
            blocks.Add((ULong_t)blocks.GetSize(), (Long_t)runbegin, (Long_t)runend);
            runstart=(*row)[0];
            for (Int_t i=1 ; i<7 ; i++)
                keys[i-1]=(*row)[i];
            break;
        }
        runstop=(*row)[0];
        count++;
    }

    //und hier einfuellen (letzter wert)
    runbegin=atoi(runstart.Data());
    runend=atoi(runstop.Data());
    blocks.Add((ULong_t)blocks.GetSize(), (Long_t)runbegin, (Long_t)runend);


    Bool_t rc = kTRUE;

    Long_t key, val;
    TExMapIter nblocks(&blocks);
    while (nblocks.Next(key, val))
    {
        Int_t runstart2 = (Int_t)key;
        Int_t runstop2 = (Int_t)val;
        cout << endl << "datablock from " << runstart2 << " to " << runstop2 << endl;

        if (!Process(serv, runstart2, runstop2, dummy))
            rc = kFALSE;

    }
    return rc ? 1 : 0;
}


int buildsequenceentries()
{
    TEnv env("sql.rc");

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

    TString query="SELECT fDate FROM SequenceBuildStatus";

    TSQLResult *res = serv.Query(query);
    if (!res)
        return 0;

    TSQLRow *row=0;
    while ((row=res->Next()))
    {
        cout << "date: " << (*row)[0] << endl;
        buildsequenceentries((*row)[0]);
    }

    return 1;
}
