<?php

  header('Content-type: application/json');

  require_once("config.php");

  //Verificando se existem tabelas customizadas no sistema
  $query = ibase_query($conexao,
    "SELECT COUNT(*) FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME starting with 'X_CUSTOM'");
  $row = ibase_fetch_row($query);

  if ($row[0] > 0) {
    $query = ibase_query($conexao,
      "select count(*) from RDB\$PROCEDURES r where r.RDB\$PROCEDURE_NAME = 'X_CUSTOM_CRIARTAB' and r.RDB\$PROCEDURE_source containing '2.9.8A'");
    $row = ibase_fetch_row($query);
    if ($row[0] == 0) {
	//echo "entrou";
	    $query = ibase_query($conexao,
        	"create or alter procedure X_CUSTOM_CRIARTAB (\n".
	        "    TABELA varchar(31))\n".
	        "returns (\n".
	        "    SQL blob sub_type 1 segment size 80)\n".
	        "as\n".
	        "declare variable VIRGULA char(1);\n".
	        "declare variable PARTESQL varchar(255);\n".
	        "begin\n".
	        "--2.9.8A\n".
		"  SQL = '';\n".
        	"  FOR select 'create table '||lower(TRIM(RDB\$RELATION_NAME))||'('\n".
	        "      from RDB\$RELATIONS\n".
        	"      where RDB\$RELATION_NAME = :TABELA INTO :PARTESQL DO BEGIN\n".
	        "\n".
        	"    SQL = SQL||PARTESQL;\n".
	        "\n".
        	"  END\n".
	        "\n".
        	"  VIRGULA = '';\n".
	        "  FOR select lower(trim(R.rdb\$field_name))||' '||\n".
        	"        CASE\n".
	        "        WHEN F.rdb\$field_type = 7 THEN 'SMALLINT'\n".
        	"        WHEN F.rdb\$field_type = 8 THEN 'INTEGER'\n".
	        "        WHEN F.rdb\$field_type = 12 THEN 'DATE'\n".
        	"        WHEN F.rdb\$field_type = 13 THEN 'TIME'\n".
	        "        WHEN F.rdb\$field_type = 14 THEN 'CHAR('||rdb\$field_length||')'\n".
        	"        WHEN F.rdb\$field_type = 16 THEN 'BIGINT'\n".
	        "        WHEN F.rdb\$field_type = 27 THEN 'DOUBLE PRECISION'\n".
        	"        WHEN F.rdb\$field_type = 35 THEN 'TIMESTAMP'\n".
	        "        WHEN F.rdb\$field_type = 37 THEN 'VARCHAR('||rdb\$field_length||')'\n".
        	"        WHEN F.rdb\$field_type = 261 THEN 'BLOB SUB_TYPE 1 SEGMENT SIZE 80'\n".
	        "        END\n".
        	"        from RDB\$RELATION_FIELDS R\n".
	        "        join RDB\$FIELDS F ON R.RDB\$FIELD_SOURCE = F.RDB\$FIELD_NAME\n".
        	"        join RDB\$RELATION_CONSTRAINTS C ON R.RDB\$RELATION_NAME = C.RDB\$RELATION_NAME AND C.RDB\$CONSTRAINT_TYPE = \n".
	        "'PRIMARY KEY'\n".
        	"        join RDB\$INDICES I ON C.RDB\$INDEX_NAME = I.RDB\$INDEX_NAME\n".
	        "        left join RDB\$INDEX_SEGMENTS S ON I.RDB\$INDEX_NAME = S.RDB\$INDEX_NAME and R.RDB\$FIELD_NAME = S.RDB\$FIELD_NAME\n".
        	"        where R.RDB\$RELATION_NAME = :TABELA AND S.rdb\$field_name is NOT null INTO :PARTESQL DO BEGIN\n".
	        "     SQL = SQL||:VIRGULA||:PARTESQL;\n".
        	"     VIRGULA = ',';\n".
	        "   END\n".
        	"\n".
	        "   SQL = SQL||', PRIMARY KEY (';\n".
        	"   VIRGULA = '';\n".
	        "   for select lower(TRIM(S.rdb\$field_name))\n".
        	"        from RDB\$RELATIONS R\n".
	        "        JOIN RDB\$RELATION_CONSTRAINTS C ON R.RDB\$RELATION_NAME = C.RDB\$RELATION_NAME AND C.RDB\$CONSTRAINT_TYPE \n".
        	"= 'PRIMARY KEY'\n".
	        "        JOIN RDB\$INDICES I ON C.RDB\$INDEX_NAME = I.RDB\$INDEX_NAME\n".
        	"        JOIN RDB\$INDEX_SEGMENTS S ON I.RDB\$INDEX_NAME = S.RDB\$INDEX_NAME\n".
	        "        where R.RDB\$RELATION_NAME = :TABELA into :PARTESQL DO BEGIN\n".
	        "     SQL = SQL||:VIRGULA||:PARTESQL;\n".
	        "     VIRGULA = ',';\n".
        	"   END\n".
	        "   SQL = SQL||'));';\n".
        	"\n".
	        "   for\n".
        	"        select 'alter table '||lower(trim(R.RDB\$RELATION_NAME))||' add '||lower(trim(R.rdb\$field_name))||' '||\n".
	        "        CASE\n".
        	"        WHEN F.rdb\$field_type = 7 THEN 'SMALLINT'\n".
	        "        WHEN F.rdb\$field_type = 8 THEN 'INTEGER'\n".
        	"        WHEN F.rdb\$field_type = 12 THEN 'DATE'\n".
	        "        WHEN F.rdb\$field_type = 13 THEN 'TIME'\n".
        	"        WHEN F.rdb\$field_type = 14 THEN 'CHAR('||rdb\$field_length||')'\n".
	        "        WHEN F.rdb\$field_type = 16 THEN 'BIGINT'\n".
        	"        WHEN F.rdb\$field_type = 27 THEN 'DOUBLE PRECISION'\n".
	        "        WHEN F.rdb\$field_type = 35 THEN 'TIMESTAMP'\n".
        	"        WHEN F.rdb\$field_type = 37 THEN 'VARCHAR('||rdb\$field_length||')'\n".
	        "        WHEN F.rdb\$field_type = 261 THEN 'BLOB SUB_TYPE 1 SEGMENT SIZE 80'\n".
        	"        END||';'\n".
	        "        from RDB\$RELATION_FIELDS R\n".
        	"        join RDB\$FIELDS F ON R.RDB\$FIELD_SOURCE = F.RDB\$FIELD_NAME\n".
	        "        join RDB\$RELATION_CONSTRAINTS C ON R.RDB\$RELATION_NAME = C.RDB\$RELATION_NAME AND C.RDB\$CONSTRAINT_TYPE = \n".
        	"'PRIMARY KEY'\n".
	        "        join RDB\$INDICES I ON C.RDB\$INDEX_NAME = I.RDB\$INDEX_NAME\n".
        	"        left join RDB\$INDEX_SEGMENTS S ON I.RDB\$INDEX_NAME = S.RDB\$INDEX_NAME and R.RDB\$FIELD_NAME = S.RDB\$FIELD_NAME\n".
	        "        where R.RDB\$RELATION_NAME = :TABELA AND S.rdb\$field_name is null\n".
        	"        \n".
	        "        order by R.rdb\$field_position  into :PARTESQL do begin\n".
        	"\n".
	        "        SQL = SQL||:PARTESQL;\n".
        	"\n".
	        "    end\n".
        	"  suspend;\n".
	        "end\n") or die(ibase_errmsg);
    }
    //ASCII_CHAR(13)||
    $query = ibase_query($conexao,
        "SELECT (select SQL from X_CUSTOM_CRIARTAB(trim(RDB\$RELATION_NAME)))  ".
        "FROM RDB\$RELATIONS WHERE RDB\$RELATION_NAME starting with 'X_CUSTOM'");
    $ar = array();
    while($row = ibase_fetch_row($query, IBASE_TEXT)) {
      $arExplode = explode(";", $row[0]);
      foreach($arExplode as $key => $val) {
        $obj = new stdClass();
        $obj->SQL = $val;
        $ar[] = $obj;
      }
    }
    $json = json_encode($ar);
    print($json);

  }
  ibase_close();

?>