#include "CifFileObj.h"

void CreateTableSQL(ostream &io, CifString tableName, CifFileObj *fobj);
ISTable *CreateTableInfo(CifFileObj *fobj);
ISTable *CreateColumnInfo(CifFileObj *fobj);

//extern "C" {
//  int strcasecmp (const char *s1, const char *s2);
//  int strncasecmp (const char *s1, const char *s2, size_t n);
// int symlink(const char *s1, const char *s2);
//}

#define TRUE  1
#define FALSE 0

int main() {

  CifFileObj * fobjR = NULL;


  // File for SQL output ...
  ofstream io("schema.isql",ios::out|ios::trunc);
  io << "use tempdb" << endl;
  io << "go" << endl;

  // Read schema database ...
  fobjR = new CifFileObj("./test/NEWSCHEMA.odb", READ_MODE, 80, "?",0);

  //
  // Generate SQL to create table schema...
  //
  ISTable *t = NULL;
  t = fobjR->GetTablePtr("ndb_schema","ndb_table");
  ReVarCifArray<CifString> *tableNames = t->GetColumn(0);
  for (int i=0; i < (int) tableNames->Length(); i++) {
    CreateTableSQL(io, (*tableNames)[i], fobjR);
  }

  //
  // Create tableinfo and columninfo tables...
  //
  ISTable *tinfo = CreateTableInfo(fobjR);

  ISTable *cinfo = CreateColumnInfo(fobjR);

  CifFileObj *fobjW = new CifFileObj("./test/NEWINFO.odb", WRITE_MODE, 80, "?",0);
  fobjW->WriteTable(tinfo,"schema_info", tinfo->GetName());
  fobjW->WriteTable(cinfo,"schema_info", cinfo->GetName());
  fobjW->Write("./test/schema_info.cif");
  fobjW->CloseFile();

  io.close();
  fobjR->CloseFile();
  if (fobjR) delete fobjR;
  exit(0);
}

void CreateTableSQL(ostream &io, CifString tableName, CifFileObj *fobj)
{
  ISTable *t = NULL;

  if (tableName.Length() <= 0) return;
  if (!fobj) return;
  if (!io)   return;

  io << endl;
  io << endl;
  io << "drop table " << tableName.Text() << endl;
  io << "go" << endl;

  t = fobj->GetTablePtr("ndb_schema","ndb_attribute_def");
  int i1  = t->GetColumnIndex("table_name");
  int i2  = t->GetColumnIndex("attribute_name");
  int i3  = t->GetColumnIndex("data_type");
  int i4  = t->GetColumnIndex("index_flag");
  int i5  = t->GetColumnIndex("null_flag");
  int i7  = t->GetColumnIndex("width");
  //  int i8  = t->GetColumnIndex("precision");

  ReVarPCifArray<int> * ir = NULL;
  ReVarCifArray<CifString> * row = NULL;
  ReVarCifArray<CifString> * indexList = new ReVarCifArray<CifString>;
  int i,j, errCode;
  CifString columnName, dType;
  int iIndex, iNull, iWidth;
  //  int iPrecision;

  ir = t->SearchColumn(tableName, i1, errCode);
  if ( !ir || ir->Length() <= 0) return;

  io << "create table " << tableName.Text() << endl;
  io << "(" << endl;
  for (i=0; i < (int) ir->Length(); i++) {
    row = t->GetRow((*ir)[i]);
    if (row) {
      columnName.Clear(); columnName.Copy((*row)[i2]);
      dType.Clear(); dType.Copy((*row)[i3]);
      iIndex     = atoi((*row)[i4].Text());
      iNull      = atoi((*row)[i5].Text());
      iWidth     = atoi((*row)[i7].Text());
      //      iPrecision = atoi((*row)[i8].Text());
      io << columnName.Text();
      for (j=columnName.Length(); j < 31; j++) {io << " "; };
      if (!strcasecmp(dType.Text(),"char")) {
	dType.Clear();
	dType.Copy("varchar(");
	dType += iWidth;
	dType += ")";
      } 
      io << dType.Text();
      for (j=dType.Length(); j < 15; j++) {io << " "; };
      if (iNull) 
	io << "not null";
      else
	io << "    null";
      if (i < (int) ir->Length() - 1)
	io << "," << endl;
      else
	io << endl;

      if (iIndex) indexList->Add(columnName);
    }
  }
  delete(ir);
  io << ")"  << endl;
  io << "go" << endl;

  if (indexList && indexList->Length()) {
    io << "create clustered index primary_index on " << tableName.Text() << endl;
    io << "(" << endl;
    for (i=0; i < (int) indexList->Length(); i++) {
      io << (*indexList)[i].Text();
      if (i < (int) indexList->Length() - 1) 
	io << "," << endl;
      else
	io << endl;
    }
    io << ") with ignore_dup_row" << endl;
    delete indexList;
  }
  
  io << "grant select on " << tableName.Text() << " to ndbuser" << endl;
  io << "go" << endl;
  io << "grant insert, delete, update, select on " << tableName.Text() << " to dbo" << endl;
  io << "go" << endl;
}

ISTable *CreateTableInfo(CifFileObj *fobj) 
{
  ISTable *tinfo = NULL;


  ISTable *t = fobj->GetTablePtr("ndb_schema","ndb_table");
  ReVarCifArray<CifString> *tableNames = t->GetColumn(0);
  ReVarCifArray<CifString> *c1 = t->GetColumn(1);
  ReVarCifArray<CifString> *c2 = t->GetColumn(2);
  ReVarCifArray<CifString> *c3 = t->GetColumn(3);
  ReVarCifArray<CifString> *c4 = t->GetColumn(4);

  ISTable *td = fobj->GetTablePtr("ndb_schema","ndb_table_description");

  tinfo = new ISTable("tableinfo");
  tinfo->AddColumn("Structure_ID", FALSE);
  tinfo->AddColumn("tablename", TRUE);
  tinfo->AddColumn("description", TRUE);
  tinfo->AddColumn("type", TRUE);
  tinfo->AddColumn("table_serial_no", TRUE);
  tinfo->AddColumn("group_name", TRUE);
  tinfo->AddColumn("WWW_Selection_Criteria", TRUE);  
  tinfo->AddColumn("WWW_Report_Criteria", TRUE);  

  ReVarPCifArray<int> * ir = NULL;
  ReVarCifArray<CifString> * row = NULL;
  int errCode;
  CifString cs1, cs2, cs3;
  cs1.Clear(); 
  cs1.Copy("XXX0000");
  
  for (int i=0; i < (int) tableNames->Length(); i++) {
    cs2.Clear(); cs2 += i+1;
    cs3.Clear();
    ir = td->SearchColumn((*tableNames)[i],0,errCode);
    if (ir && (int) ir->Length() > 0) {
      row = td->GetRow((*ir)[0]);
      if (row) {
	cs3.Copy((*row)[1]);
      }      
    } 
    tinfo->AddRow();
    tinfo->AddElementToRow(cs1,i);
    tinfo->AddElementToRow((*tableNames)[i],i);
    tinfo->AddElementToRow(cs3,i);
    tinfo->AddElementToRow((*c1)[i],i);
    tinfo->AddElementToRow(cs2,i);
    tinfo->AddElementToRow((*c2)[i],i);
    tinfo->AddElementToRow((*c3)[i],i);
    tinfo->AddElementToRow((*c4)[i],i);
  }


  return(tinfo);
}


ISTable *CreateColumnInfo(CifFileObj *fobj) 
{
  ISTable *cinfo = NULL;

  ISTable *t = fobj->GetTablePtr("ndb_schema","ndb_attribute_def");
  ReVarCifArray<CifString> *c0 = t->GetColumn(0);
  ReVarCifArray<CifString> *c1 = t->GetColumn(1);
  //  ReVarCifArray<CifString> *c2 = t->GetColumn(2);
  //  ReVarCifArray<CifString> *c3 = t->GetColumn(3);
  //  ReVarCifArray<CifString> *c4 = t->GetColumn(4);
  //  ReVarCifArray<CifString> *c5 = t->GetColumn(5);
  //  ReVarCifArray<CifString> *c6 = t->GetColumn(6);

  ISTable *td = fobj->GetTablePtr("ndb_schema","ndb_attribute_description");

  ISTable *tv = fobj->GetTablePtr("ndb_schema","ndb_attribute_view");

  cinfo = new ISTable("columninfo");
  cinfo->AddColumn("Structure_ID", FALSE);
  cinfo->AddColumn("tablename", TRUE);
  cinfo->AddColumn("columnname", TRUE);
  cinfo->AddColumn("description", TRUE);
  cinfo->AddColumn("example", TRUE);
  cinfo->AddColumn("type", TRUE);
  cinfo->AddColumn("table_serial_no", TRUE);
  cinfo->AddColumn("column_serial_no", TRUE);
  cinfo->AddColumn("WWW_Selection_Criteria", TRUE);  
  cinfo->AddColumn("WWW_Report_Criteria", TRUE);  

  ReVarPCifArray<int> * ir = NULL;
  ReVarCifArray<CifString> * row = NULL;
  ReVarCifArray<CifString> * q0 = NULL;
  q0 = new   ReVarCifArray<CifString>;
  ReVarPCifArray<int> * iq0 = NULL;
  iq0 =   new ReVarPCifArray<int>;
  
  int it=0,errCode;
  CifString cs1, cs2, cs3, cs4, cs5, cs6,cs7, cs8;
  cs1.Clear(); 
  cs1.Copy("XXX0000");
  
  for (int i=0; i < (int) c0->Length(); i++) {
    if (i > 0 && strcmp((*c0)[i].Text(),(*c0)[i-1].Text())) it++;
    cs8.Clear(); cs8 += it+1;
    cs2.Clear(); cs2 += i+1;
    cs3.Clear(); cs4.Clear();  cs5.Clear(); cs6.Clear(); cs7.Clear();

    q0->Clear(); q0->Add((*c0)[i]); q0->Add((*c1)[i]);
    iq0->Clear(); iq0->Add(0);     iq0->Add(1);
    ir = td->Search(*q0, *iq0, errCode);
    if (ir && ir->Length() > 0) {
      row = td->GetRow((*ir)[0]);
      if (row) {
	cs3.Copy((*row)[2]);
	cs4.Copy((*row)[3]);
	delete row;
      }    
      delete ir;
    } 

    q0->Clear(); q0->Add((*c0)[i]); q0->Add((*c1)[i]);
    iq0->Clear(); iq0->Add(0);     iq0->Add(1);
    ir = tv->Search(*q0, *iq0, errCode);
    if (ir && ir->Length() > 0) {
      row = tv->GetRow((*ir)[0]);
      if (row) {
	cs5.Copy((*row)[2]);
	cs6.Copy((*row)[3]);
	cs7.Copy((*row)[4]);
	delete row;
      }      
      delete ir;
    } 


    cinfo->AddRow(); 
    cinfo->AddElementToRow(cs1,i);
    cinfo->AddElementToRow((*c0)[i],i);
    cinfo->AddElementToRow((*c1)[i],i);
    cinfo->AddElementToRow(cs3,i); // description
    cinfo->AddElementToRow(cs4,i); // example
    cinfo->AddElementToRow(cs5,i);  // type

    cinfo->AddElementToRow(cs8,i);  // column serial
    cinfo->AddElementToRow(cs2,i);  // column serial
    cinfo->AddElementToRow(cs6,i);  // www select
    cinfo->AddElementToRow(cs7,i);  // www report
  }


  return(cinfo);
}