/* ======================================================================== *\
!
! *
! * 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, 01/2005 <mailto:dorner@astro.uni-wuerzburg.de>
!
!   Copyright: MAGIC Software Development, 2000-2006
!
!
\* ======================================================================== */

/////////////////////////////////////////////////////////////////////////////
//
// insertsequence.C
// ================
//
// This macro inserts a sequence into the database. It extracts the
// information from a sequence file.
// This macro is not used by the automatic analysis. It is needed to insert
// manually built sequences into the database.
//
// Usage:
//  .x insertsequence.C+("filename", kTRUE)
// The first argument is the filename of the manual written sequencefile.
// The second argument is the 'dummy-mode'. If it is kTRUE dummy-mode is
// switched on and nothing will be written into the database. This is usefull
// for tests.
//
// Make sure, that database and password are corretly set in a resource
// file called sql.rc and the resource file is found.
//
// Returns 0 in case of failure and 1 in case of success.
//
/////////////////////////////////////////////////////////////////////////////

#include <iostream>
#include <iomanip>
#include <fstream>

#include <TEnv.h>

#include <MSQLServer.h>
#include <TSQLRow.h>
#include <TSQLResult.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 %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 %s (f%sName) VALUES (\"%s\");", col, col, name);

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

    res=serv.Query(query);
    if (!res)
    {
        cout << "Error in query " << query << endl;
        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 insertsequence(TString filename, Bool_t dummy=kTRUE)
{
    TEnv env("sql.rc");
    TEnv sequ(filename);

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

    //get sequence number from file
    TString sequnum;
    sequnum=sequ.GetValue("Sequence", "");
    Int_t seq=atoi(sequnum.Data());

    //get runs from sequence file
    TString runs;
    runs = sequ.GetValue("Runs", "");
    runs.ReplaceAll(" ", ",");
    if (runs.IsNull())
    {
        cout << "ERROR - No runs in file " << filename << " found." << endl;
        return 0;
    }

    //get source key
    TString sourcename = sequ.GetValue("Source", "");
    Int_t sourcekey = QueryNameKEY(serv, dummy, "Source", sourcename.Data());

    //get values for the sequence
    TString query="SELECT max(fRunNumber), min(fRunStart), ";
    query +="sum(time_to_sec(fRunStop)-time_to_sec(fRunStart)), ";
    query +="min(fZenithDistance), max(fZenithDistance), ";
    query +="min(fAzimuth), max(fAzimuth), sum(fNumEvents) from RunData ";
    query +=Form("WHERE fRunNumber IN (%s)",runs.Data());

    cout << "runs: " << runs << endl;
    cout << "q1: " << query << endl;

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

    TSQLRow *row=res->Next();
    TString lastrun=(*row)[0];
    TString starttime=(*row)[1];
    TString uptime=(*row)[2];
    TString zdmin=(*row)[3];
    TString zdmax=(*row)[4];
    TString azmin=(*row)[5];
    TString azmax=(*row)[6];
    TString numevts=(*row)[7];

    delete res;

    query  ="SELECT fProjectKEY, fSourceKEY, fHvSettingsKEY, ";
    query +="fTriggerDelayTableKEY, fDiscriminatorThresholdTableKEY, ";
    query +="fTestFlagKEY, fLightConditionsKEY, fL1TriggerTableKEY, ";
    query +=Form("fL2TriggerTableKEY FROM RunData WHERE fRunNumber=%d", seq);

    cout << "q2: " << query << endl;

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

    row=res->Next();
    TString project=(*row)[0];
    TString source=(*row)[1];
    TString hv=(*row)[2];
    TString delay=(*row)[3];
    TString dt=(*row)[4];
    TString testflag=(*row)[5];
    TString lightcond=(*row)[6];
    TString l1tt=(*row)[7];
    TString l2tt=(*row)[8];

    delete res;

    cout << "seq: "       << seq        << endl;
    cout << "  lastrun   " << lastrun   << endl;
    cout << "  startime  " << starttime << endl;
    cout << "  uptime    " << uptime    << endl;
    cout << "  zdmin     " << zdmin     << endl;
    cout << "  zdmax     " << zdmax     << endl;
    cout << "  azmin     " << azmin     << endl;
    cout << "  azmax     " << azmax     << endl;
    cout << "  numevts   " << numevts   << endl;
    cout << " keys:"                    <<  endl;
    cout << "  project   " << project   << endl;
    cout << "  source1   " << source    << " (from db -> run "  << seq << ") " << endl;
    cout << "  source2   " << sourcekey << " (from sequ file) " << endl;
    if (!(atoi(source.Data())==sourcekey))
    {
        cout << "new source name: " << sourcename << " -> inserting..." << endl;
        sourcekey = QueryNameKEY(serv, dummy, "Source", sourcename.Data(), kFALSE);
        source=Form("%d",sourcekey);
    }
    cout << "  source    " << source    << endl;
    cout << "  hv        " << hv        << endl;
    cout << "  delay     " << delay     << endl;
    cout << "  dt        " << dt        << endl;
    cout << "  testflag  " << testflag  << endl;
    cout << "  lightcond " << lightcond << endl;
    cout << "  l1tt      " << l1tt      << endl;
    cout << "  l2tt      " << l2tt      << endl;

    //build queries
    TString query1="INSERT Sequences SET fManuallyChangedKEY=2, ";
    query1 +=Form("fSequenceFirst=%d, fSequenceLast=%s, "
                  "fProjectKEY=%s, fSourceKEY=%s, fNumEvents=%s, "
                  "fRunStart='%s', fHvSettingsKEY=%s, fRunTime=%s, "
                  "fTriggerDelayTableKEY=%s, fDiscriminatorThresholdTableKEY=%s, "
                  "fTestFlagKEY=%s, fLightConditionsKEY=%s, fAzimuthMin=%s, "
                  "fAzimuthMax=%s, fZenithDistanceMin=%s, fZenithDistanceMax=%s, "
                  "fL1TriggerTableKEY=%s, fL2TriggerTableKEY=%s ",
                  seq, lastrun.Data(), project.Data(), source.Data(),
                  numevts.Data(), starttime.Data(), hv.Data(), uptime.Data(),
                  delay.Data(), dt.Data(), testflag.Data(), lightcond.Data(),
                  azmin.Data(), azmax.Data(), zdmin.Data(), zdmax.Data(),
                  l1tt.Data(), l2tt.Data());

    //the time of the column fSequenceFileWritten is set to 'not to be done'
    TString query2=Form("INSERT SequenceProcessStatus SET "
                        "fSequenceFirst=%d, fSequenceFileWritten='1970-01-01 00:00:00'",
                        seq);

    if (dummy)
    {
        cout << "q1: " << query1 << endl;
        cout << "q2: " << query2 << endl;
        return 1;
    }


    res = serv.Query(query1);
    if (!res)
    {
        cout << "ERROR: query1 failed: " << query1 << endl;
        return 0;
    }
    delete res;

    res = serv.Query(query2);
    if (!res)
    {
        cout << "ERROR: query2 failed: " << query2 << endl;
        return 0;
    }
    delete res;

    return 1;
}