/********************************************************************************/
/*  Following IBEBlock is used by IBExpert to produce text of view from table   */
/*                                                                              */
/*              !!! DO NOT CHANGE INPUT/OUTPUT PARAMETER NAMES !!!              */
/********************************************************************************/

execute ibeblock (
  TableName        variant comment 'Table name AS IS',
  TableNameFmt     variant comment 'Formatted table name (quoted, if necessary)',
  FieldNames       variant comment 'Array of field names AS IS',
  FieldNamesFmt    variant comment 'Array of formatted field names (names are quoted, if necessary)',
  FieldTypes       variant comment 'Array of field types',
  FieldDomains     variant comment 'Array of field domain names (names are quoted, if necessary)',
  FieldFlags       variant comment 'Array of field flags. Possible flags:
                                   K - field is a primary key field
                                   S - field is marked for include into SELECT
                                   R - field is marked for include into RETURN...INTO clause',

  DefViewName      variant comment 'Default view name template (from IBExpert General Templates)',
  DefBITrgName     variant comment 'Default BEFORE INSERT trigger name template (from IBExpert General Templates)',
  DefBUTrgName     variant comment 'Default BEFORE UPDATE trigger name template (from IBExpert General Templates)',
  DefBDTrgName     variant comment 'Default BEFORE DELETE trigger name template (from IBExpert General Templates)',

  ComposeBITrigger boolean = FALSE comment 'Compose BEFORE INSERT trigger text',
  ComposeBUTrigger boolean = FALSE comment 'Compose BEFORE UPDATE trigger text',
  ComposeBDTrigger boolean = FALSE comment 'Compose BEFORE DELETE trigger text',

  DatabaseID      variant comment 'ID of associated IBExpert database')

RETURNS (
  ViewText            variant = ''    comment 'Result view text',
  BeforeInsertTrgText variant = ''    comment 'BEFORE INSERT trigger text',
  BeforeUpdateTrgText variant = ''    comment 'BEFORE UPDATE trigger text',
  BeforeDeleteTrgText variant = ''    comment 'BEFORE DELETE trigger text',
  DoNotFormat         boolean = FALSE comment 'If TRUE, no additional formatting will be performed by IBExpert')
as
begin
  -- Uncomment following line to avoid additional formatting of procedure text
  -- DoNotFormat = TRUE;

  DBConn = ibec_GetDefaultConnection();
  iODSMajor = ibec_GetConnectionProp(DBConn, 'ODSMAJORVERSION');
  iODSMinor = ibec_GetConnectionProp(DBConn, 'ODSMINORVERSION');

  sNoPrimaryKey = '/* There is no primary key defined for ' + TableNameFmt + ' table */';
  sNoFieldsSelected = '/* There are no fields selected */';

  ServerVersion = ibec_ibe_GetDatabaseProp(DatabaseID, 'ServerVersion');
  CRLF = ibec_CRLF();
  CommaCRLF = ',' + CRLF;
  CreateView = 'CREATE ' + ibec_IIF(ServerVersion in (__svFB25, __svFB30), 'OR ALTER ', '') + 'VIEW ';
  ViewName = ibec_preg_Replace('(?i)\%TABLE_NAME\%', TableName, DefViewName);

  WhereClause = '';
  ViewFields = '';
  SelectFields = '';

  foreach (FieldNamesFmt as Field key FldIdx) do
  begin
    Flags = FieldFlags[FldIdx];

    -- WHERE clause for BEFORE UPDATE/DELETE triggers
    if ((ibec_Pos('K', Flags) > 0) and (ibec_Pos('S', Flags) > 0)) then
      WhereClause .= ibec_IIF(WhereClause = '', '', ' AND ') + '(' + Field + ' = OLD.' + Field + ')';

    if (ibec_Pos('S', Flags) > 0) then
    begin
      if (ViewFields <> '') then
        ViewFields .= CommaCRLF + '    ';
      ViewFields .= Field;
    end;
  end;

  if (WhereClause = '') then -- No primary key fields selected for SELECT - very bad idea! But we have to try anyway...
  begin
    foreach (FieldNamesFmt as Field key FldIdx) do
    begin
      Flags = FieldFlags[FldIdx];
  
      -- WHERE clause for BEFORE UPDATE/DELETE triggers
      if (ibec_Pos('S', Flags) > 0) then
        WhereClause .= ibec_IIF(WhereClause = '', '', ' AND ') + '(' + Field + ' = OLD.' + Field + ')';
    end;
  end;

  ViewText = CreateView + ibec_FormatIdent(ViewName) + ' (' + CRLF +
             '    ' + ViewFields + ')' + CRLF +
             'AS' + CRLF +
             'SELECT' + CRLF +
             '    ' + ViewFields + CRLF +
             'FROM ' + TableNameFmt;

  if (ComposeBITrigger or ComposeBUTrigger) then
  begin
    sDeclare = '';
    sReturning = '';
    sInto = '';
    foreach (FieldNames as Field key FldIdx) do
    begin
      sType = '';
      Flags = FieldFlags[FldIdx];
      if (ibec_Pos('R', Flags) = 0) then
        Continue;

      IsUserDomain = (ibec_Copy(FieldDomains[FldIdx], 1, 4) <> 'RDB$');
  
      if (((ServerVersion = __svFB25) and (iODSMajor >= 11) and (iODSMinor >= 2)) or (ServerVersion = __svFB30))  then
        sType = 'TYPE OF COLUMN ' + TableNameFmt + '.' + ibec_FormatIdent(FieldNames[FldIdx]);
      else if (IsUserDomain and (ServerVersion in (__svFB21, __svFB25)) and (iODSMajor >= 11) and (iODSMinor >= 1)) then
        sType = 'TYPE OF ' + FieldDomains[FldIdx];
      else if (IsUserDomain and (ServerVersion = __svFB30)) then
        sType = 'TYPE OF ' + FieldDomains[FldIdx];
  
      if (sType = '') then
        sType = FieldTypes[FldIdx];

      sVarName = 'V_' + Field;
      sDeclare .= ibec_IIF(sDeclare = '', '', CRLF) + 'DECLARE VARIABLE ' + ibec_FormatIdent(sVarName) + ' ' + sType + ';';
      sReturning .= ibec_IIF(sReturning = '', '', ', ') + FieldNamesFmt[FldIdx];
      sInto .= ibec_IIF(sInto = '', '', ', ') + ':' + ibec_FormatIdent(sVarName);
    end;
  end;

  if (ComposeBITrigger) then
  begin
    sInsert = '';
    sValues = '';
    TrgName = ibec_preg_Replace('(?i)\%VIEW_NAME\%', ViewName, DefBITrgName);
    foreach (FieldNamesFmt as Field key FldIdx) do
    begin
      Flags = FieldFlags[FldIdx];
      if ((ibec_Pos('S', Flags) > 0) and (ibec_Pos('C', Flags) = 0)) then
      begin
        if (sInsert <> '') then
          sInsert .= CommaCRLF + '    ';
        sInsert .= Field;
        if (sValues <> '') then
          sValues .= CommaCRLF + '    ';
        sValues .= 'NEW.' + Field;
      end;
    end;
    BeforeInsertTrgText = 'CREATE TRIGGER ' + ibec_FormatIdent(TrgName) + ' FOR ' + ibec_FormatIdent(ViewName) + CRLF +
                          'ACTIVE BEFORE INSERT POSITION 0' + CRLF +
                          'AS' + CRLF;
    if (sDeclare <> '') then
      BeforeInsertTrgText .= sDeclare + CRLF;
    BeforeInsertTrgText .= 'BEGIN' + CRLF +
                           '  INSERT INTO ' + TableNameFmt + ' (' + CRLF +
                           '    ' + sInsert + ')' + CRLF +
                           '  VALUES (' + CRLF +
                           '    ' + sValues + ')';
    if (sDeclare <> '') then
      BeforeInsertTrgText .= CRLF +
                             '  RETURNING ' + sReturning + CRLF +
                             '       INTO ' + sInto;
    BeforeInsertTrgText .= ';' + CRLF +'END';
  end;

  if (ComposeBUTrigger) then
  begin
    sUpdate = '';
    TrgName = ibec_preg_Replace('(?i)\%VIEW_NAME\%', ViewName, DefBUTrgName);
    foreach (FieldNamesFmt as Field key FldIdx) do
    begin
      Flags = FieldFlags[FldIdx];
      if ((ibec_Pos('S', Flags) > 0) and (ibec_Pos('K', Flags) = 0) and (ibec_Pos('C', Flags) = 0)) then
      begin
        if (sUpdate <> '') then
          sUpdate .= CommaCRLF + '      ';
        sUpdate .= Field + ' = NEW.' + Field;
      end;
    end;
    BeforeUpdateTrgText = 'CREATE TRIGGER ' + ibec_FormatIdent(TrgName) + ' FOR ' + ibec_FormatIdent(ViewName) + CRLF +
                          'ACTIVE BEFORE UPDATE POSITION 0' + CRLF +
                          'AS' + CRLF;
    if (sDeclare <> '') then
      BeforeUpdateTrgText .= sDeclare + CRLF;

    BeforeUpdateTrgText .= 'BEGIN' + CRLF +
                           '  UPDATE ' + TableNameFmt + CRLF +
                           '  SET ' + sUpdate + CRLF +
                           '  WHERE ' + WhereClause;
    if (sDeclare <> '') then
      BeforeUpdateTrgText .= CRLF +
                             '  RETURNING ' + sReturning + CRLF +
                             '       INTO ' + sInto;
    BeforeUpdateTrgText .= ';' + CRLF +'END';
  end;

  if (ComposeBDTrigger) then
  begin
    TrgName = ibec_preg_Replace('(?i)\%VIEW_NAME\%', ViewName, DefBDTrgName);
    BeforeDeleteTrgText = 'CREATE TRIGGER ' + ibec_FormatIdent(TrgName) + ' FOR ' + ibec_FormatIdent(ViewName) + CRLF +
                          'ACTIVE BEFORE DELETE POSITION 0' + CRLF +
                          'AS' + CRLF +
                          'BEGIN' + CRLF +
                          '  DELETE FROM ' + TableNameFmt + CRLF +
                          '  WHERE ' + WhereClause + ';' + CRLF +
                          'END';
  end;
end
