CREATE TABLE ORG (
    ID        INTEGER NOT NULL,
    NAME      VARCHAR(255) DEFAULT '',
    INN       VARCHAR(20),
    SKD_TYPE  INTEGER,
    ID_FIRMA  INTEGER
);

ALTER TABLE ORG ADD CONSTRAINT PK_ORG PRIMARY KEY (ID);

CREATE INDEX ORG_IDX2 ON ORG (ID_FIRMA, INN);

CREATE OR ALTER TRIGGER ORG_BI FOR ORG
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.ID is null) then
    new.ID = gen_id(gen_public,1);
end;

CREATE TABLE ACCOUNTS (
    ID        INTEGER NOT NULL,
    CODE      VARCHAR(30) DEFAULT '' NOT NULL,
    NAME      VARCHAR(255) DEFAULT '' NOT NULL,
    GROUP_ID  INTEGER
);

ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY (ID);

CREATE INDEX ACCOUNTS_IDX1 ON ACCOUNTS (GROUP_ID);

CREATE OR ALTER TRIGGER ACCOUNTS_BI FOR ACCOUNTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_public,1);
end;

CREATE TABLE DOC (
    ID          INTEGER NOT NULL,
    ID_DOCTYPE  INTEGER NOT NULL,
    DATE_NK     DATE NOT NULL,
    NUM_NK      VARCHAR(40) DEFAULT '' NOT NULL,
    ID_FIRMA    INTEGER NOT NULL,
    ID_ORGFROM  INTEGER NOT NULL,
    ID_ORGTO    INTEGER NOT NULL,
    ENABLED     INTEGER DEFAULT 0 NOT NULL,
    COMMENT     VARCHAR(255) DEFAULT '' NOT NULL
);

ALTER TABLE DOC ADD CONSTRAINT PK_DOC PRIMARY KEY (ID);

CREATE DESCENDING INDEX DOC_IDX1 ON DOC (DATE_NK);
CREATE DESCENDING INDEX DOC_IDX2 ON DOC (ID_DOCTYPE, ID_FIRMA, DATE_NK);

CREATE OR ALTER TRIGGER DOC_BI FOR DOC
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.ID = gen_id(gen_public,1);
end;

CREATE TABLE DOCDETAIL (
    ID       INTEGER NOT NULL,
    ID_DOC   INTEGER NOT NULL,
    ID_ITEM  INTEGER NOT NULL,
    ITEMS    NUMERIC(18,3),
    PRICE    NUMERIC(18,4),
    SUMMA    NUMERIC(18,2),
    COMMENT  VARCHAR(255)
);

ALTER TABLE DOCDETAIL ADD CONSTRAINT PK_DOCDETAIL PRIMARY KEY (ID);
ALTER TABLE DOCDETAIL ADD CONSTRAINT FK_DOCDETAIL FOREIGN KEY (ID_DOC) REFERENCES DOC (ID) ON DELETE CASCADE;

CREATE OR ALTER TRIGGER DOCDETAIL_BI0 FOR DOCDETAIL
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.ID is null) then
    new.ID = gen_id(gen_public, 1);
end;

CREATE TABLE OPER (
    ID          INTEGER NOT NULL,
    ID_DOC      INTEGER NOT NULL,
    ID_DOCTYPE  INTEGER NOT NULL,
    DATE_NK     DATE,
    SGN         INTEGER,
    ID_ORG      INTEGER NOT NULL,
    COMMENT     VARCHAR(255),
    SUMMA       NUMERIC(18,2),
    OST         INTEGER DEFAULT 0,
    ID_ACCOUNT  INTEGER NOT NULL,
    PARA        INTEGER,
    YN_OPER     INTEGER DEFAULT 1,
    ID_FIRMA    INTEGER NOT NULL
);

ALTER TABLE OPER ADD CONSTRAINT PK_OPER PRIMARY KEY (ID);
ALTER TABLE OPER ADD CONSTRAINT FK_OPER_1 FOREIGN KEY (ID_DOC) REFERENCES DOC (ID) ON DELETE CASCADE;
ALTER TABLE OPER ADD CONSTRAINT FK_OPER_2 FOREIGN KEY (ID_ORG) REFERENCES ORG (ID);
ALTER TABLE OPER ADD CONSTRAINT FK_OPER_3 FOREIGN KEY (ID_ACCOUNT) REFERENCES ACCOUNTS (ID);

CREATE INDEX OPER_IDX1 ON OPER (DATE_NK);
CREATE INDEX OPER_IDX2 ON OPER (PARA);
CREATE INDEX OPER_IDX3 ON OPER (ID_ACCOUNT, DATE_NK);
CREATE INDEX OPER_IDX4 ON OPER (ID_ORG, ID_ACCOUNT);

CREATE OR ALTER TRIGGER OPER_BI FOR OPER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  if (new.ID is null) then
    new.ID = gen_id(gen_public,  1);
  if (new.para = -1) then
    new.para = new.ID;
END;

CREATE TABLE PERIOD (
    ID       INTEGER NOT NULL,
    ID_ORG   INTEGER NOT NULL,
    DATE_NK  DATE,
    ID_DOC   INTEGER NOT NULL,
    VID      INTEGER NOT NULL,
    OST      INTEGER
);

ALTER TABLE PERIOD ADD CONSTRAINT PK_PERIOD PRIMARY KEY (ID);

ALTER TABLE PERIOD ADD CONSTRAINT FK_PERIOD_1 FOREIGN KEY (ID_ORG) REFERENCES ORG (ID) ON DELETE CASCADE;
ALTER TABLE PERIOD ADD CONSTRAINT FK_PERIOD_2 FOREIGN KEY (ID_DOC) REFERENCES DOC (ID) ON DELETE CASCADE;
CREATE DESCENDING INDEX PERIOD_IDX1 ON PERIOD (ID_ORG, VID, DATE_NK);

CREATE OR ALTER TRIGGER PERIOD_BI FOR PERIOD
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_PUBLIC,1);
END;

CREATE GENERATOR GEN_SESSION;

CREATE GLOBAL TEMPORARY TABLE GTT_ACC (
    ID          INTEGER NOT NULL,
    ID_SESSION  INTEGER NOT NULL,
    VID         INTEGER,
    I1          INTEGER
) ON COMMIT PRESERVE ROWS;

ALTER TABLE GTT_ACC ADD CONSTRAINT PK_GTT_ACC PRIMARY KEY (ID);
CREATE INDEX GTT_ACC_IDX1 ON GTT_ACC (ID_SESSION, I1);

CREATE OR ALTER TRIGGER GTT_ACC_BI FOR GTT_ACC
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_session,1);
end;

CREATE GLOBAL TEMPORARY TABLE GTT_ORG (
    ID          INTEGER NOT NULL,
    VID         INTEGER,
    ID_SESSION  INTEGER NOT NULL,
    I1          INTEGER,
    D1          DATE
) ON COMMIT PRESERVE ROWS;

ALTER TABLE GTT_ORG ADD CONSTRAINT PK_GTT_ORG PRIMARY KEY (ID);

CREATE INDEX GTT_ORG_IDX1 ON GTT_ORG (ID_SESSION, I1);
CREATE INDEX GTT_ORG_IDX2 ON GTT_ORG (I1, ID_SESSION);

CREATE OR ALTER TRIGGER GTT_ORG_BI FOR GTT_ORG
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_session,1);
end;

create or alter function IS_INTEGER (
    R varchar(100))
returns boolean
AS
begin
  return (r similar to '[-+]?[0-9]{1,19}');
end;

create or alter function DTCLOSEDAYDOC
returns integer deterministic
AS
begin
  return 200;
end;

create or alter procedure STRTOID (
    STR blob,
    L varchar(20) = ',')
returns (
    N integer,
    CODE bigint)
AS
declare variable p int = -1;
declare variable t int = 1;
declare variable l1 int;
declare variable cc varchar(20);
begin
  if (nullif(str, '') is null) then exit;

  N = 1;
  l1 = char_length(l);
  while (p <> 0) do
  begin
    p = position(l, str, t);
    if (p = 0) then
      cc = left(substring(str from t), 20);
    else
      cc = left(substring(str from t for p-t), 20);
    if (is_integer(cc)) then
    begin
      code = cc;
      suspend;
      N = N + 1;
    end
    t = p + l1;
  end
end;

create or alter procedure GET_ACCOUNTS (
    PARAM blob)
returns (
    ID integer)
AS
declare variable id_cat integer;
begin
  if (param is null) then exit;

  if (is_integer(param)) then
    id_cat = param;
  else
  begin
    for select distinct b.id from strtoid(:param) s
        cross join GET_ACCOUNTS(s.code) b
    into id do
      suspend;
    exit;
  end

  -- В оригинале здесь более сложная проверка из таблицы категорий счетов N:M
  for select id from accounts where group_id = :id_cat
      union
      select id from accounts where id = :id_cat
      union
      select id from accounts where :id_cat = 0
  into id do
    suspend;
end;

create or alter procedure GET_ORG (
    PARAM blob)
returns (
    ID integer,
    SKD_TYPE integer)
AS
declare variable id_cat integer;
begin
  if (param is null) then exit;

  if (is_integer(param)) then
    id_cat = param;
  else
  begin
    for select distinct m.id, m.skd_type from strtoid(:param) s
        cross join get_org(s.code) m
    into id, skd_type do
      suspend;
    exit;
  end

  -- В оригинале здесь более сложная проверка из таблицы категорий счетов N:M
  for select id, skd_type from org where skd_type = :id_cat
      union
      select id, skd_type from org where id = :id_cat
      union
      select id, skd_type from org where :id_cat = 0
  into id, skd_type do
    suspend;
end;

create or alter procedure GET_PARAM_SESSION (
    ORG_CAT blob,
    ACC_CAT blob)
returns (
    ID_SESSION integer)
AS
begin
  id_session = gen_id(gen_session, 1);
  insert into gtt_acc (id_session, vid, i1)
  select :id_session, 3, id
  from get_accounts(:acc_cat);

  insert into gtt_org (id_session, vid, i1)
  select :id_session, 2, id
  from get_org(:org_cat);

  suspend;
end

create or alter procedure GET_OPER_SESSION (
    ID_SESSION integer,
    DATE_NK date,
    ID_DOC integer = 0)
returns (
    ID_ACCOUNT integer,
    ID_ORG integer,
    SUMMA numeric(18,2),
    SUMMA_D numeric(18,2),
    SUMMA_K numeric(18,2))
AS
declare variable id_firma int;
declare variable date_0 date;
declare variable id_doc0 int;
begin
  id_doc = coalesce(id_doc, 0);
  DATE_NK = coalesce(DATE_NK, '01.01.2100');

  select first 1 m.id_firma from gtt_org s
  join org m on m.id = s.i1
  where s.id_session = :id_session and s.vid = 2
  into id_firma;

  select first 1 p.date_nk, p.id_doc
  from period p
  join doc d on d.id = p.id_doc
  where p.date_nk <= :date_nk and p.id_org = :id_firma and p.vid = 1000 and p.id_doc <> :id_doc and p.ost = 1
  order by p.date_nk desc
  into date_0, id_doc0;

  date_0 = coalesce(date_0, '01.01.1900');

  for select o.id_org,
             o.id_account,
             sum(o.summa * o.sgn)
      from oper o
      join gtt_acc i on i.id_session = :id_session and i.vid = 3 and i.i1 = o.id_account
      join gtt_org m on m.id_session = :id_session and m.vid = 2 and m.i1 = o.id_org
      where o.id_doc <> :id_doc and o.yn_oper = 1 and
            (o.ost = 1 and o.id_doc = :id_doc0 or o.ost = 0 and o.date_nk between :date_0 + 1 and :date_nk)
      group by 1, 2
      having sum(o.summa*o.sgn) <> 0
  into id_org, id_account, summa do
  begin
    summa_d = maxvalue( summa, 0);
    summa_k = maxvalue(-summa, 0);
    suspend;
  end
end;

create or alter procedure GET_OPER (
    DATE_NK date,
    ORG_CAT blob,
    ACC_CAT blob)
returns (
    ID_ACCOUNT integer,
    ID_ORG integer,
    SUMMA numeric(18,2),
    SUMMA_D numeric(18,2),
    SUMMA_K numeric(18,2))
AS
declare variable id_session integer;
begin
  select id_session from get_param_session(:org_cat, :acc_cat) into id_session;

  for select o.id_org,
             o.id_account,
             sum(o.summa),
             sum(o.summa_d),
             sum(o.summa_k)
      from get_oper_session(:id_session, :date_nk) o
      group by 1, 2
   into id_org, ID_ACCOUNT, summa, summa_d, summa_k do
     suspend;
end;

create or alter procedure GET_OPER_MOVE_SESSION (
    ID_SESSION integer,
    D1 date,
    D2 date,
    ID_DOC0 integer = 0)
returns (
    ID_OPER integer,
    ID_OPER2 integer,
    ID_ACCOUNT integer,
    ID_ACCOUNT2 integer,
    ID_ORG integer,
    ID_ORG2 integer,
    SUMMA numeric(12,2),
    SUMMA_D numeric(12,2),
    SUMMA_K numeric(12,2),
    ID_DOC integer,
    DATE_NK date)
AS
begin
  id_doc0 = coalesce(id_doc0, 0);

  d1 = coalesce(d1, '01.01.1900');
  d2 = coalesce(d2, '01.01.2100');

  for select o.id_doc, o.id_account, o.id_org, k.id_account, k.id_org,
             iif(o.sgn = 1, o.summa, 0),
             iif(o.sgn =-1, o.summa, 0),
             o.sgn * o.summa,
             o.date_nk,
             o.id,
             k.id
      from oper o
      join oper k on k.para = o.para and k.id <> o.id
      join gtt_acc i on i.id_session = :id_session and i.vid = 3 and i.i1 = o.id_account
      join gtt_org m on m.id_session = :id_session and m.vid = 2 and m.i1 = o.id_org + 0
      where o.ost = 0 and o.date_nk between :d1 and :d2 and o.id_doc <> :id_doc0 and
            o.yn_oper = 1
  into id_doc, id_account, id_org, id_account2, id_org2, summa_d, summa_k, summa, date_nk, id_oper, id_oper2 do
    suspend;
end;

create or alter procedure GET_OPER_MOVE (
    D1 date,
    D2 date,
    ORG_CAT blob,
    ACC_CAT blob,
    OST integer = 1,
    ID_DOC0 integer = 0)
returns (
    ID_OPER integer,
    ID_OPER2 integer,
    ID_ACCOUNT integer,
    ID_ACCOUNT2 integer,
    ID_ORG integer,
    ID_ORG2 integer,
    SUMMA numeric(18,2),
    SUMMA_D numeric(18,2),
    SUMMA_K numeric(18,2),
    ID_DOC integer,
    DATE_NK date)
AS
declare variable id_session integer;
begin
  select id_session from get_param_session(:ORG_CAT, :ACC_CAT) into id_session;

  id_doc0 = coalesce(id_doc0, 0);

  d1 = coalesce(d1, '01.01.1900');
  d2 = coalesce(d2, '01.01.2100');

  if (ost = 1) then
  begin
    /* Ocтатки на начало периода */
    id_doc = null; date_nk = d1-1;

    for select id_account, id_org, summa, summa_d, summa_k
        from get_oper_session(:id_session, :date_nk, :id_doc)
    into id_account, id_org, summa, summa_d, summa_k do
      suspend;
  end

  for select id_doc, id_account, id_org, id_account2, id_org2, summa_d, summa_k, summa, date_nk, id_oper, id_oper2
      from get_oper_move_session(:id_session, :d1, :d2, :id_doc0)
  into id_doc, id_account, id_org, id_account2, id_org2, summa_d, summa_k, summa, date_nk, id_oper, id_oper2 do
    suspend;
end;

create or alter procedure MOVE_CLEAR_CLOSEDOC (
    ID integer,
    UNMAKE integer = 1)
AS
begin
  if (unmake = 1) then
    update doc set enabled = 0 where id = :ID and enabled is distinct from 0;

  delete from oper where id_doc = :id;
  delete from period where id_doc = :id;
end;

create or alter procedure MOVE_CLOSEDOC (
    ID integer)
AS
declare variable id_firma int;
declare variable date_nk date;
declare variable id_doctype int;
declare variable org_cat int = 0; -- Все организации
declare variable acc_cat int = 0; -- Все счета
begin
  execute procedure move_clear_closedoc(:id, 0);

  select date_nk, id_firma, id_doctype from doc where id = :id into date_nk, id_firma, id_doctype;

  insert into oper(ID_DOC, ID_DOCTYPE, DATE_NK, ID_FIRMA, ost, id_org, id_account, sgn, summa)
  select :id, :id_doctype, :date_nk, :id_firma, 1, id_org, id_account, sign(summa), abs(summa)
  from get_oper(:date_nk, :org_cat, :acc_cat);

  insert into period (id_doc, date_nk, id_org, vid, ost)
  values (:id, :date_nk, :id_firma, 1000, 1);

  update doc set enabled = 1 where id = :ID and enabled is distinct from 1;
end;

create or alter procedure CLOSE_PERIOD (
    ID_FIRMA integer,
    DATE_NK date)
returns (
    ID integer)
AS
declare variable id_doctype int;
declare variable e int;
declare variable d date;
declare variable blk int;
begin
  id_doctype = dtclosedaydoc();

  insert into doc (id_doctype, date_nk, id_firma, id_orgfrom, id_orgto)
  values (:id_doctype, :date_nk, :id_firma, 0, 0) returning id into id;

  execute procedure move_closedoc(:id);

  suspend;
end;

create or alter procedure MOVE_CLEAR_OPERDOC (
    ID integer,
    UNMAKE integer = 1)
AS
begin
  if (unmake = 1) then
    update doc set enabled = 0 where id = :ID and enabled is distinct from 0;

  delete from oper where id_doc = :id;
end;

create or alter procedure MOVE_OPERDOC (
    ID integer)
AS
declare variable para int;
declare variable summa numeric(18,2);
declare variable id_account int;
begin
  execute procedure move_clear_operdoc(:id, 0);

  for select id_item, summa
      from docdetail
      where id_doc = :id
  into id_account, summa do
  begin
    insert into oper (id_doc, id_doctype, date_nk, id_firma, id_org, id_account, sgn, summa, para)
    select id, id_doctype, date_nk, id_firma, id_orgfrom, :id_account, -1, :summa, -1 from doc
    where id = :id
    returning para into para;

    insert into oper (id_doc, id_doctype, date_nk, id_firma, id_org, id_account, sgn, summa, para)
    select id, id_doctype, date_nk, id_firma, id_orgto, :id_account, +1, :summa, :para from doc
    where id = :id;
  end

  update doc set enabled = 1 where id = :ID and enabled is distinct from 1;
end;