#!/usr/bin/php -Cq
<?php

// Combine a .table file and a .rec file into a file that
// can be read by a PHP script, to create a PostgreSQL database
// or MysQL, or SQLite
// Note this means that the *table files and their dependant files will be moved
// into *.rec files into the astium-confweb package

/*
class session_container {
  var $tzoffset = 0;
  var $lang = "en";
  var $js = 0;
  var $xslt = 0;
}

  $sess = new session_container;
  if (file_exists('/usr/local/astium/etc/product')) {
    $lines = file('/usr/local/astium/etc/product', FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line_num => $line) {
      $line = trim($line);
      if ($line[0] == '#' || $line[0] == ';') continue;
      $pieces = explode('=', $line);
      $varname = trim($pieces[0]);
      $value = trim($pieces[1], " \t\n\r\0\x0B\"'");
      $sess->$varname = $value;
    }
  }
*/

function fix_quotes($msg) {
  $result = str_replace("\'", "'", $msg);
  $result = str_replace('\"', '"', $result);
  $result = str_replace('"', '\"', $result);
  return $result;
}

function remove_dupe_blanks($text) {
  $result = str_replace('        ', ' ', $text);
  $result = str_replace('       ', ' ', $result);
  $result = str_replace('      ', ' ', $result);
  $result = str_replace('     ', ' ', $result);
  $result = str_replace('    ', ' ', $result);
  $result = str_replace('   ', ' ', $result);
  $result = str_replace('  ', ' ', $result);
  return $result;
}

$debug = 0;

function Debug($level, $msg) {
  global $debug;

  if ($debug >= $level) {
    printf("%s\n", $msg);
  }
}

  if (!isset($argv[1])) {
    echo "Usage: create-new-db-layout xxxxx.table\n";
    exit;
  }
  $tablename = basename($argv[1], ".m4");
  $tablename = basename($tablename, ".table");
  $tablename = basename($tablename, ".data");

  $foreign = array();
  $fh = file('foreign.keys');
  foreach($fh as $lineno => $line) {
    $line = trim($line);
    if (substr($line, 0, 2) == "--") continue;
    if (trim($line) == '') continue;
    // ALTER TABLE fdpin ADD CONSTRAINT fdpin_phoneline_fail_constraint FOREIGN KEY (phoneline_fail) REFERENCES phoneline (id);
    //   0    1     2     3      4                   5                     6     7       8              9         10       11
    $wordArray = explode(" ", $line);
    if ($wordArray[2] != $tablename) continue;
    $foreign[trim($wordArray[8], "()")] = $wordArray[10] . "," . trim($wordArray[11], "();");
  }

  $recfile = '';
  if (file_exists("../../../astium-confweb/src/php/cms/$tablename.rec-orig")) {
    $recfile = "../../../astium-confweb/src/php/cms/$tablename.rec-orig";
  }
  if (file_exists("../../../astium-confweb/src/html/php/cms/$tablename.rec-orig")) {
    $recfile = "../../../astium-confweb/src/html/php/cms/$tablename.rec-orig";
  }

  $help_en_info = '';
  $help_en_long = array();
  $help_nl_info = '';
  $help_nl_long = array();
  $fieldname = '';
  $output = '';
  $firsttime = true;
  $help = '';
  $default = '';
  $comment = '';
  $fieldline = '';
  $index = array();
  $unique = array();
  $insert = array();
  $check = array();
  $statement = array();
  $i = 0;

  $fh = file($argv[1]);
  foreach($fh as $lineno => $line) {
    if ($lineno < $i) continue; // skip lines that we previously read ahead
    $line = trim($line);
    $wordArray = explode(" ", $line);
    if (strtolower($wordArray[0]) == 'insert') {
      $work = $line;
      for ($i=$lineno+1; substr(trim($work), -1) != ';'; $i++) { // query is spread across multiple lines?
        $work .= ' ' . $fh[$i]; // read ahead, and copy
      }
      $insert[] = remove_dupe_blanks($work);
    }
    if (strtolower($wordArray[0]) != 'create' && strtolower($wordArray[0]) != 'unique') continue;
    if (strtolower($wordArray[1]) == 'index') {
      $fields = strtok($line, "(");
      $fields = strtok(")");
      $index[] = $fields;
      continue;
    }
    if (strtolower($wordArray[0]) == 'unique' || strtolower($wordArray[1]) == 'unique') {
      $fields = strtok($line, "(");
      $fields = strtok(")");
      $unique[] = $fields;
    }
  }
  foreach($fh as $lineno => $line) {
    $line = trim($line);
    $wordArray = explode(" ", trim($line));
    if (substr($line, 0, 2) == "--" && substr($line, 0, 6) != "--HELP") continue;
    if ($line == '') continue;
    if ($line == '(') continue;
    $word0 = strtolower($wordArray[0]);
    if ($word0 == 'create') continue;
    if ($word0 == 'grant') continue;
    if ($word0[0] == ')') break; // BREAK OUT OF THE LOOP
    if ($word0 == 'key') continue;
    if ($word0 == 'insert') continue;
    if ($word0 == 'unique') continue;
    if (substr($word0, 0, 6) == 'values') continue;
    if ($word0 == 'alter') {
    // alter table phonetype alter column codec1 set default 'alaw';
      $statement[] = sprintf("$%s_record['%s'][4] = %s;", $tablename, $wordArray[5], trim($wordArray[8], ";"));
      continue;
    }
    if ($word0 == 'check') {
    // CHECK (cardtype IN ('quadbri', 'octobri', 'unogsm', 'te410p', 'te405p', 'b410p')),
      for ($i=3; isset($wordArray[$i]); $i++) {
        $check[] = trim($wordArray[$i], "(),");
      }
      continue;
    }
    if (substr($line, 0, 15) == '--HELP_EN_INFO:') {
      $help_en_info = substr($line, 15);
      continue;
    }
    if (substr($line, 0, 15) == '--HELP_EN_LONG:') {
      $help_en_long[] = substr($line, 15) . "\n";
      continue;
    }
    if (substr($line, 0, 15) == '--HELP_NL_INFO:') {
      $help_nl_info = substr($line, 15);
      continue;
    }
    if (substr($line, 0, 15) == '--HELP_NL_LONG:') {
      $help_nl_long[] = substr($line, 15) . "\n";
      continue;
    }
    if (substr($line, 0, 7) == 'include') {
      $include = strtok($line, "(");
      $include = strtok(")");
      $output .= sprintf("include \"%s.rec\";\n\n", $include);
      continue;
    }
    Debug(1, $line);
    Debug(1, "Ok, this must be a column definition. Write out all the info we sampled about the previous field");
    if (!$firsttime) { 
      $help .= implode('', $help_en_long);
      if ($help == '') {
        $help = sprintf("Helptext for %s", $fieldname);
      }
      $help = rtrim($help, "\n");

      Debug(1, "fieldline = $fieldline");
      Debug(1, "default = $default");
      Debug(1, "Comment = $comment");
      Debug(1, "Help = $help");

      if (isset($foreign[$fieldname])) {
        $foreign_key = $foreign[$fieldname];
      } else {
        $foreign_key = '';
      }

      $constraint = implode(", ", $check); // check already contains apostrophes
      $constraint = "array(" . $constraint . ") /* allowed values for this field */";
      $fieldline .= sprintf(" '%s', '%s', '%s', %s\n  %s,\n  _(\"%s\")),\n\n", 
            $fieldtype == '' ? 'unknown' : $fieldtype, $default, $foreign_key, $comment, $constraint, fix_quotes($help));
      $output .= $fieldline;

      // we've written everything. Clear our sampled info.
      $default = $comment = $help = $fieldline = '';
      $help_en_info = '';
      $help_en_long = array();
      $help_nl_info = '';
      $help_nl_long = array();
      $check = array();
      $fieldtype = $fieldlen = 0;
    }
    $firsttime = false;
    if ($word0 == 'primary') continue;

    // Now it's time to read the new column definition.
    // read the info for it into our variables fieldname, default
    // determine the default value of this field
    $fieldname = trim($wordArray[0], '"');
    for ($default = '', $i=2; isset($wordArray[$i]); $i++) {
      if (strtolower($wordArray[$i]) == 'default') {
        $default = rtrim($wordArray[++$i], ',');
        if ($default[0] != "'") break;
        while (strstr("'", $default) !== false) { // starts with apostrophe, find ending apostrophe
          if (!isset($wordArray[++$i])) break;
          $default .= ' ' . rtrim($wordArray[$i], ',');
        }
        $default = explode("'", $default);
        $default = $default[1];
        break;
      }
    }
    Debug(1, "fieldname = $fieldname");
    Debug(1, "recfile = $recfile");
    if ($recfile != '') {
      Debug(1, "apparently a .rec file exists, try to find the definition for this field there");
      $cmd = sprintf("grep '^\"%s\"' %s", $fieldname, $recfile);
      $fieldline = rtrim(exec($cmd));
      if ($fieldline != '') {
        $fieldline = rtrim($fieldline, '),') . ',';
        $fieldline = remove_dupe_blanks($fieldline);
        $tmp = explode('"', $fieldline);
        if (isset($tmp[3])) $fielddesc = $tmp[3]; // get the field description from the .rec file
        if (isset($tmp[4])) {
          // , F_UNKNOWN, 8, 8,
          $tmp =  explode(",", $tmp[4]);
          if (isset($tmp[1])) $statedfieldtype = $tmp[1];
          if (isset($tmp[3])) $statedfieldlen = $tmp[3];
        }
        $comment = ' // From .rec file';
      } else {
        $comment = ' // Not found in .rec file';
        Debug(1, "Not found in .rec file");
      }
    } else {
      $comment = ' // No .rec file present';
      Debug(1, "No .rec file present");
    }
    $fieldtype = trim(strtolower($wordArray[1]), ',');
    if ($fieldtype == 'character') {
      $fieldtype .= ' ' . trim(strtolower($wordArray[2]), ',');
      $fieldtype = str_replace('character varying', 'varchar', $fieldtype);
    }
    if (strstr($fieldtype, 'varchar')) {
      $fieldlen = substr($fieldtype, 8, 4);
      $fieldtype = 'varchar';
    } else if (strstr($fieldtype, 'char')) {
      $fieldlen = substr($fieldtype, 5, 4);
      $fieldtype = 'varchar';
    }
    switch ($fieldtype) {
    case 'serial':  $ftype = 'F_NUMBER'; $fieldlen = 8; break;
    case 'smallint':
    case 'int':
    case 'integer': $ftype = 'F_NUMBER'; $fieldlen = 8;  $fieldtype = 'integer'; break;
    case 'float':   $ftype = 'F_FLOAT';  $fieldlen = 12; break; // arbritrary fieldlen
    case 'text':    $ftype = 'F_TEXT';   $fieldlen = 256; break;
    case 'bool':    $ftype = 'F_YESNO';  $fieldlen =  3; break;
    case 'date':    $ftype = 'F_DATE';   $fieldlen = 10; break;
    case 'time':    $ftype = 'F_TIME';   $fieldlen = 12; break;
    case 'timestamp':
    case 'datetime':$ftype = 'F_DATETIME'; $fieldlen = 24; break;
    case 'varchar': $ftype = 'F_TEXT'; break; 
    case 'numeric': $ftype = 'F_PRICE'; break;
    default:        $ftype = sprintf("F_UNKNOWN /* %s */", $fieldtype); $fieldlen = 8; break;
    }
    if ($fieldline == '') {
      Debug(1, "Either this field seems to be missing from the .rec file, or there's no .rec file at all");
      // try to generate the field description ourselves.
      //print_r($wordArray);
      $fieldline = sprintf("\"%s\" => array(_(\"%s\"), %s, %d, %d,",
          $fieldname, $fieldname, $ftype, $fieldlen, $fieldlen);
      Debug(1, "generated: $fieldline");
    }
  }

  if (file_exists($tablename . ".data")) {
    $fh = file($tablename . ".data");
    foreach($fh as $lineno => $line) {
      if ($lineno < $i) continue; // skip previously processed lines (while reading ahead)
      $line = trim($line);
      if (substr($line, 0, 2) == "--") continue;
      if (trim($line) == '') continue;
      $wordArray = explode(" ", $line);
      if (strtolower($wordArray[0]) == 'insert' || strtolower($wordArray[0]) == 'update') {
        //insert into zonetab (isocode, coordinates, timezone, comments)
        //        values ('ad', '+4230+00131', 'Europe/Andorra', '');
        $work = $line;
        for ($i = $lineno+1; substr(trim($work), -1) != ';' ;$i++) {
          $work .= ' ' . $fh[$i];
        }
        $insert[] = fix_quotes(remove_dupe_blanks($work));
      }
    }
  }

  if (strstr($tablename, ".inc") === false) {
  printf("<?php

// Copyright (c) 1997-now, Use The Source BV, The Netherlands. All rights Reserved.
//
// This software is the copyrighted work of Use The Source BV.
// Use of the software is subject to the general business terms and conditions of Use The Source BV
// and its related transaction documents like license sheets or other written notices.
//
// Any reproduction or redistribution of the software not in accordance with the license terms
// is expressly prohibited by law, and may result in severe civil and criminally penalties.
// Violators will be prosecuted to the maximum extent possible.

?>
<?php
$%s_record = array(\n", $tablename);
  }
  printf("%s", $output);
  if (strstr($tablename, ".inc") === false) {
    printf(");\n");

    printf("\n");
    foreach ($statement as $fields) {
      printf("%s\n", $fields);
    }

    printf("\n$%s_index = array('%s');\n", $tablename, implode("', '", $index));
    printf("\n$%s_unique_index = array('%s');\n", $tablename, implode("', '", $unique));

    printf("\n$%s_trigger = array(\n", $tablename);
    printf("\"update_lastupdate\" => array('after update', \n  \"\",\n");
    printf("  \"UPDATE %s SET lastupdate = CURRENT_TIMESTAMP;\"\n", $tablename);
    printf("  ),\n");
    printf(");\n");

    printf("\n$%s_insert = array(\n", $tablename);
    foreach ($insert as $fields) {
      printf("\"%s\",\n", fix_quotes($fields));
    }

    printf(" );\n?>");
  }

?>
