/*
  DDL to create the Chess database.
  EDB version translated from Transact-SQL.
*/

create procedure sp_Chess_db()
begin
  declare stmt statement;

  prepare stmt from
  '
    select 1
    from information.views
    where name = ?
  ';
  execute stmt using 'AllMoves';
  if (rowsaffected(stmt) = 1) then
    execute immediate 'drop view AllMoves';
  end if;

  prepare stmt from
  '
    select 1
    from information.tables
    where name = ?
  ';
  execute stmt using 'Board';
  if (rowsaffected(stmt) = 1) then
    execute immediate 'drop table Board';
  end if;

  execute immediate
  '
    create table Board
    (
      X smallint not nulL,
      Y smallint not null,
      Square char(2) generated always as
      (
        case X
          when 1 then ''a''
          when 2 then ''b''
          when 3 then ''c''
          when 4 then ''d''
          when 5 then ''e''
          when 6 then ''f''
          when 7 then ''g''
          when 8 then ''h''
        end || cast(Y as char(1))
      ),
      constraint PK_Board primary key (X,Y),
      constraint CK_Board unique (Square),
      constraint CHK_Board_X_Domain check (X >= 1 and X <= 8),
      constraint CHK_Board_Y_Domain check (Y >= 1 and Y <= 8)
    )
  ';

  prepare stmt from 'insert into Board (X,Y) values (?,?)';
  start transaction;
  execute stmt using 1,1;
  execute stmt using 1,2;
  execute stmt using 1,3;
  execute stmt using 1,4;
  execute stmt using 1,5;
  execute stmt using 1,6;
  execute stmt using 1,7;
  execute stmt using 1,8;
  execute stmt using 2,1;
  execute stmt using 2,2;
  execute stmt using 2,3;
  execute stmt using 2,4;
  execute stmt using 2,5;
  execute stmt using 2,6;
  execute stmt using 2,7;
  execute stmt using 2,8;
  execute stmt using 3,1;
  execute stmt using 3,2;
  execute stmt using 3,3;
  execute stmt using 3,4;
  execute stmt using 3,5;
  execute stmt using 3,6;
  execute stmt using 3,7;
  execute stmt using 3,8;
  execute stmt using 4,1;
  execute stmt using 4,2;
  execute stmt using 4,3;
  execute stmt using 4,4;
  execute stmt using 4,5;
  execute stmt using 4,6;
  execute stmt using 4,7;
  execute stmt using 4,8;
  execute stmt using 5,1;
  execute stmt using 5,2;
  execute stmt using 5,3;
  execute stmt using 5,4;
  execute stmt using 5,5;
  execute stmt using 5,6;
  execute stmt using 5,7;
  execute stmt using 5,8;
  execute stmt using 6,1;
  execute stmt using 6,2;
  execute stmt using 6,3;
  execute stmt using 6,4;
  execute stmt using 6,5;
  execute stmt using 6,6;
  execute stmt using 6,7;
  execute stmt using 6,8;
  execute stmt using 7,1;
  execute stmt using 7,2;
  execute stmt using 7,3;
  execute stmt using 7,4;
  execute stmt using 7,5;
  execute stmt using 7,6;
  execute stmt using 7,7;
  execute stmt using 7,8;
  execute stmt using 8,1;
  execute stmt using 8,2;
  execute stmt using 8,3;
  execute stmt using 8,4;
  execute stmt using 8,5;
  execute stmt using 8,6;
  execute stmt using 8,7;
  execute stmt using 8,8;
  commit;

  execute immediate
  '
    create view AllMoves as
    select
      R.X as X,
      R.Y as Y,
      R.Square as FromSquare,
      O.X as Xi,
      O.Y as Yi,
      O.Square as ToSquare
    from Board R, Board O
    where R.Square <> O.Square
  ';

  unprepare stmt;
end
  