BEGIN TRANSACTION;

DO $$

DECLARE
  -- Параметры схемы
  the_scheme_id INTEGER := 1000;
  the_scheme_name VARCHAR := 'fact-responsum-data-loader';
  the_scheme_caption VARCHAR := 'Выгрузка данных с InetProducer и загрузка в Responsum_data и Responsum';

  -- Параметры рабочей области
  the_workspace_id INTEGER := 2;

  -- Параметры ноды
  the_node_id INTEGER := 1;

  -- Параметры обработчиков
  the_dummy_handler_id INTEGER := the_scheme_id+1;
  the_dummy_handler_name VARCHAR := 'DummyHandler';
  the_dummy_handler_caption VARCHAR := 'Dummy Handler';
  the_dummy_handler_object_file VARCHAR := 'DummyHandler';

  the_exporter_stg_id INTEGER := the_scheme_id+2;
  the_exporter_stg_name VARCHAR := 'ExporterStorage';
  the_exporter_stg_caption VARCHAR := 'Загрузка данных из InetProducer';
  the_exporter_stg_export_type VARCHAR := 'PandariaData';
  the_exporter_stg_ftp_path VARCHAR := 'fact_responsum_data';
  the_exporter_stg_path_working VARCHAR := '/opt/projects/responsum/exporter/data/responsum_data';

  the_exporter_id INTEGER := the_scheme_id+4;
  the_exporter_name VARCHAR := 'ExporterResponsum';
  the_exporter_caption VARCHAR := 'Загрузка данных из IMedia и InetProducer';
  the_exporter_export_type VARCHAR := 'Pandaria';
  the_exporter_ftp_path VARCHAR := 'fact_responsum';
  the_exporter_path_working VARCHAR := '/opt/projects/responsum/exporter/data/responsum';

  the_loader_stg_id INTEGER := the_scheme_id+3;
  the_loader_stg_name VARCHAR := 'LoaderStorage';
  the_loader_stg_caption VARCHAR := 'Загрузка данных в БД Responsum_data';
  the_loader_stg_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/loader/cfg/log4j.xml -Duser.dir=/opt/projects/responsum/loader -jar /opt/projects/responsum/loader/pandaria-loader.jar';
  the_loader_stg_host VARCHAR := '192.168.21.111';
  the_loader_stg_port VARCHAR := '1433';
  the_loader_stg_db_name VARCHAR := 'Responsum_data_test';
  the_loader_stg_databus_url VARCHAR := 'jdbc:sqlserver://'||the_loader_stg_host||':'||the_loader_stg_port||';databaseName='||the_loader_stg_db_name||'';
  the_loader_stg_databus_user VARCHAR := 'responsum_loader';
  the_loader_stg_databus_pass VARCHAR := 's0wnV+xL1YQpql0Fe0xNLxgJsEHQjTAsnTWiFCtChfA=';
  the_loader_stg_databus_pass_nc VARCHAR := '29sEU0QIAM71ClNdh0cA';

  the_loader_id INTEGER := the_scheme_id+6;
  the_loader_name VARCHAR := 'LoaderUtil';
  the_loader_caption VARCHAR := 'Загрузчик в служебную базу Responsum';
  the_loader_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/loader/cfg/log4j.xml -Duser.dir=/opt/projects/responsum/loader -jar /opt/projects/responsum/loader/pandaria-loader.jar';
  the_loader_host VARCHAR := '192.168.21.111';
  the_loader_port VARCHAR := '1433';
  the_loader_db_name VARCHAR := 'Responsum_test';
  the_loader_databus_url VARCHAR := 'jdbc:sqlserver://'||the_loader_host||':'||the_loader_port||';databaseName='||the_loader_db_name||'';
  the_loader_databus_user VARCHAR := 'responsum_loader';
  the_loader_databus_pass VARCHAR := 's0wnV+xL1YQpql0Fe0xNLxgJsEHQjTAsnTWiFCtChfA=';

  the_stopper_id INTEGER := the_scheme_id+5;
  the_stopper_name VARCHAR := 'WorkStopper';
  the_stopper_caption VARCHAR := 'Остановка Responsum';
  the_stopper_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/start-stopper/config/log4j.properties -jar /opt/projects/responsum/start-stopper/start-stop.jar';

  the_starter_id INTEGER := the_scheme_id+8;
  the_starter_name VARCHAR := 'WorkStarter';
  the_starter_caption VARCHAR := 'Запуск Responsum';

  the_snapshotter_id INTEGER := the_scheme_id+7;
  the_snapshotter_name VARCHAR := 'SnapshotReloader';
  the_snapshotter_caption VARCHAR := 'Перегружает снапшот в Responsum Data';
  the_snapshotter_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/snapshot-reloader/config/log4j.properties -jar /opt/projects/responsum/snapshot-reloader/snapshot-reloader.jar';
  the_snapshotter_url VARCHAR := the_loader_stg_databus_url;
  the_snapshotter_user VARCHAR := the_loader_stg_databus_user;
  the_snapshotter_password VARCHAR := the_loader_stg_databus_pass_nc;

  the_dummy_end_handler_id          INTEGER := the_scheme_id+9;
  the_dummy_end_handler_name        VARCHAR := 'DummyHandler-end';
  the_dummy_end_handler_caption     VARCHAR := 'Dummy Handler';
  the_dummy_end_handler_object_file VARCHAR := 'DummyHandler';

  -- Параметры FTP
  the_ftp_host VARCHAR := '10.101.40.40';
  the_exporter_ftp_user VARCHAR := 'uploadusr';
  the_exporter_ftp_pass VARCHAR := 'exPo$51faVt#';

  the_loader_ftp_user VARCHAR := 'uploadusr';
  the_loader_ftp_pass VARCHAR := 'GaujjvuaNZilegdgrCpyeg==';

  -- Параметры шины данных
  the_exp_databus_host VARCHAR := '192.168.21.25';
  the_exp_databus_port VARCHAR := '1433';
  the_exp_databus_user VARCHAR := 'responsum_exporter';
  the_exp_databus_pass VARCHAR := 'tb8yOOD7j96i2vvbbPdR';


BEGIN
  -- Добавляем схему и ноды
  INSERT INTO core.scheme(scheme_id, name, caption, release_obj_file, workspaceid)
  VALUES (the_scheme_id, the_scheme_name, the_scheme_caption, null, the_workspace_id);

  INSERT INTO core.scheme_node(scheme_id, node_id)
  VALUES (the_scheme_id, the_node_id);
  --

  -- Добавляем описание параметров сеанса в этой схеме
  INSERT INTO core.param_key(param_key, param_type)
  VALUES ('responsum-loader.bundle_type', 'string'),
    ('responsum-loader.part_id', 'string'),
    ('responsum-loader.d', 'string');

  INSERT INTO core.param_key_value(param_key, param_value)
  VALUES ('responsum-loader.bundle_type', '1'),
    ('responsum-loader.bundle_type', '2'),
    ('responsum-loader.bundle_type', '3');

  INSERT INTO core.scheme_param(scheme_id, param_name, param_key, required)
  VALUES (the_scheme_id, 'responsum-loader.bundle_type', 'responsum-loader.bundle_type', true),
    (the_scheme_id, 'responsum-loader.start_part_id', 'responsum-loader.part_id', true),
    (the_scheme_id, 'responsum-loader.end_part_id', 'responsum-loader.part_id', true),
    (the_scheme_id, 'responsum-loader.day', 'sessionData', false),
    (the_scheme_id, 'responsum-loader.api.host', 'host', TRUE ),
    (the_scheme_id, 'responsum-loader.stop.waiting.time-unit', 'time-unit', TRUE),
    (the_scheme_id, 'responsum-loader.stop.waiting.timeout', 'timeout', TRUE);
  --

  -- Добавляем обработчики
  INSERT INTO core.handler(handler_id, scheme_id, name, caption, object_file, is_splitter, last_handler_id)
  VALUES
    -- Dummy
    (the_dummy_handler_id, the_scheme_id, the_dummy_handler_name, 'Старт сеанса', 'DummyHandler', false, NULL),
    -- Exporter Storage
    (the_exporter_stg_id, the_scheme_id, the_exporter_stg_name, the_exporter_stg_caption, 'mono /opt/projects/responsum/exporter/Inet.Producer.Engine.Exporter.Pandaria.exe', false, NULL),
    -- Loader Storage
    (the_loader_stg_id, the_scheme_id, the_loader_stg_name, the_loader_stg_caption, 'java -Dlog4j.configuration=file:///opt/projects/responsum/loader/cfg/log4j.xml -Duser.dir=/opt/projects/responsum/loader -jar /opt/projects/responsum/loader/pandaria-loader.jar', false, NULL),
    -- Exporter
    (the_exporter_id, the_scheme_id, the_exporter_name, the_exporter_caption, 'mono /opt/projects/responsum/exporter/Inet.Producer.Engine.Exporter.Pandaria.exe', false, NULL),
    -- Stopper
    (the_stopper_id, the_scheme_id, the_stopper_name, the_stopper_caption, the_stopper_object_file, false, NULL),
    -- Loader
    (the_loader_id, the_scheme_id, the_loader_name, the_loader_caption, the_loader_object_file, false, NULL),
    -- Snapshot Reloader
    (the_snapshotter_id, the_scheme_id, the_snapshotter_name, the_snapshotter_caption, the_snapshotter_object_file, false, NULL),
    -- Starter
    (the_starter_id, the_scheme_id, the_starter_name, the_starter_caption, the_stopper_object_file, false, NULL),
    -- END Dummy
    (the_dummy_end_handler_id, the_scheme_id, the_dummy_end_handler_name, 'Конец сеанса', 'DummyHandler', false, NULL);

  INSERT INTO core.node_handler(node_id, handler_id)
  VALUES (the_node_id, the_dummy_handler_id),
    (the_node_id, the_exporter_stg_id),
    (the_node_id, the_loader_stg_id),
    (the_node_id, the_exporter_id),
    (the_node_id, the_stopper_id),
    (the_node_id, the_loader_id),
    (the_node_id, the_snapshotter_id),
    (the_node_id, the_starter_id),
    (the_node_id, the_dummy_end_handler_id);

  INSERT INTO core.handler_graph(scheme_id, prev_handler_id, next_handler_id)
  VALUES (the_scheme_id, the_dummy_handler_id, the_exporter_stg_id),
    (the_scheme_id, the_exporter_stg_id, the_loader_stg_id),
    (the_scheme_id, the_loader_stg_id, the_snapshotter_id),
    (the_scheme_id, the_snapshotter_id, the_dummy_end_handler_id),
    (the_scheme_id, the_dummy_handler_id, the_exporter_id),
    (the_scheme_id, the_exporter_id, the_stopper_id),
    (the_scheme_id, the_stopper_id, the_loader_id),
    (the_scheme_id, the_loader_id, the_starter_id),
    (the_scheme_id, the_starter_id, the_dummy_end_handler_id);
  --

  -- Параметры конфигурации обработчиков
  INSERT INTO core.handler_param(handler_id, param_key, param_val)
  VALUES (the_exporter_stg_id,'cubeData', '{
        "CubeParam": {
        "PandariaExportType": "'||the_exporter_stg_export_type||'",
        "FtpProxyHost": "",
        "FtpUrl": "'||the_ftp_host||'",
        "FtpUserName":"'||the_exporter_ftp_user||'",
        "FtpPassword":"'||the_exporter_ftp_pass||'",
        "FtpPort": 21,
        "FtpPath": "'||the_exporter_stg_ftp_path||'"
        },
        "DataBusParam": {
        "InetProducerConnectionString": "metadata=res://*/InetProducer.csdl|res://*/InetProducer.ssdl|res://*/InetProducer.msl;provider=System.Data.SqlClient;provider connection string=\"data source='||the_exp_databus_host||';initial catalog=InetProducer;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';Connection Timeout=300;MultipleActiveResultSets=True;App=EntityFramework\"",
        "ImediaConnectionString": "Data Source='||the_exp_databus_host||'; Initial Catalog=IMedia;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';MultipleActiveResultSets=True;",
        "PathWorking": "'||the_exporter_stg_path_working||'",
        "CodePage": 1251
        }
       }'),
    (the_loader_stg_id, 'home_path', '/opt/projects/responsum/loader'),
    (the_loader_stg_id, 'loader_config', '{
              "loader": {
                "file-location": {
                  "from-path": "/opt/projects/responsum/loader/data/responsum_data/from/",
                  "to-path": "/opt/projects/responsum/loader/data/responsum_data/to/"
                },
                "file-encoding": "Cp1251",
                "is-deleted": false,
                "tables": [
                  {
                    "name": "daily",
                    "parted": true,
                    "schema": "fact",
                    "name_ismatch": true
                  },
                  {
                    "name": "demo",
                    "parted": false,
                    "schema": "fact",
                    "name_ismatch": false
                  },
                  {
                    "name": "media",
                    "schema": "fact",
                    "parted": false,
                    "name_ismatch": false
                  },
                  {
                    "name": "weights_daily",
                    "schema": "fact",
                    "parted": true,
                    "name_ismatch": true
                  },
                  {
                    "name": "fdaily",
                    "schema": "fact",
                    "parted": true,
                    "name_ismatch": true
                  }
                ],
                "quartz-jobs": {
                  "used": false,
                  "properties-file-location": "./cfg/quartz.properties"
                }
              }
            }'
    ),
    (the_loader_stg_id, 'ftp_config', '{
          "ftp": {
            "proxy": {
              "host": "url-test-proxy",
              "port": "3162",
              "used": "false"
            },
            "server": {
              "host": "'||the_ftp_host||'",
              "port": "21",
              "user": "'||the_loader_ftp_user||'",
              "pass": "'||the_loader_ftp_pass||'",
              "directory": "'||the_exporter_stg_ftp_path||'"
            },
            "used": "true"
          }
        }'
    ),
    (the_loader_stg_id, 'db_config', '{
          "db": {
            "url": "' || the_loader_stg_databus_url || '",
            "profile-name": "Responsum",
            "user": "'||the_loader_stg_databus_user||'",
            "pass": "'||the_loader_stg_databus_pass||'",
            "isNew": "false"
          }
        }'
    ),
    (the_loader_stg_id, 'mail_config', '{
          "mail": {
            "from": "webindex@web123.ru",
            "host": "192.168.21.3",
            "subject": "Загрузчик Pandoria.Responsum_data",
            "to": "evgeny.utkin@mediascope.net",
            "used": "false"
          }
        }'
    ),
    (the_exporter_id,'cubeData', '{
          "CubeParam": {
            "PandariaExportType": "'||the_exporter_export_type||'",
            "FtpProxyHost": "",
            "FtpUrl": "'||the_ftp_host||'",
            "FtpUserName":"'||the_exporter_ftp_user||'",
            "FtpPassword":"'||the_exporter_ftp_pass||'",
            "FtpPort": 21,
            "FtpPath": "'||the_exporter_ftp_path||'"
          },
          "DataBusParam": {
            "InetProducerConnectionString": "metadata=res://*/InetProducer.csdl|res://*/InetProducer.ssdl|res://*/InetProducer.msl;provider=System.Data.SqlClient;provider connection string=\"data source='||the_exp_databus_host||';initial catalog=InetProducer;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';Connection Timeout=300;MultipleActiveResultSets=True;App=EntityFramework\"",
            "ImediaConnectionString": "Data Source='||the_exp_databus_host||'; Initial Catalog=IMedia;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';MultipleActiveResultSets=True;",
            "PathWorking": "'||the_exporter_path_working||'",
            "CodePage": 1251
          }
        }'),
    (the_stopper_id, 'login', 'zabbix'),
    (the_stopper_id, 'password', 'zabbix'),
    (the_stopper_id, 'url', '/responsum-info/zabbix/control'),
    (the_stopper_id, 'isStart', false),
    (the_loader_id, 'home_path', '/opt/projects/responsum/loader'),
    (the_loader_id, 'loader_config', '{
              "loader": {
                "file-location": {
                  "from-path": "/opt/projects/responsum/loader/data/responsum/from/",
                  "to-path": "/opt/projects/responsum/loader/data/responsum/to/"
                },
                "file-encoding": "Cp1251",
                "is-deleted": false,
                "tables": [
                  {
                    "name": "holdings",
                    "parted": false,
                    "schema": "fact",
                    "name_ismatch": false
                  },
                  {
                    "name": "sites",
                    "parted": false,
                    "schema": "fact",
                    "name_ismatch": false
                  },
                  {
                    "name": "sections",
                    "parted": false,
                    "schema": "fact",
                    "name_ismatch": false
                  },
                  {
                    "name": "subsections",
                    "parted": false,
                    "schema": "fact",
                    "name_ismatch": false
                  },
                  {
                    "name": "variables",
                    "parted": false
                  },
                  {
                    "name": "var_categories",
                    "parted": false
                  }
               ],
                "quartz-jobs": {
                  "used": false,
                  "properties-file-location": "./cfg/quartz.properties"
                }
              }
            }'
    ),
    (the_loader_id, 'ftp_config', '{
         "ftp": {
            "proxy": {
              "host": "url-test-proxy",
              "port": "3162",
              "used": "false"
            },
            "server": {
              "host": "'||the_ftp_host||'",
              "port": "21",
              "user": "'||the_loader_ftp_user||'",
              "pass": "'||the_loader_ftp_pass||'",
              "directory": "'||the_exporter_ftp_path||'"
            },
            "used": "true"
          }
        }'
    ),
    (the_loader_id, 'db_config', '{
          "db": {
            "url": "' || the_loader_databus_url || '",
            "profile-name": "Responsum",
            "user": "'||the_loader_databus_user||'",
            "pass": "'||the_loader_databus_pass||'",
            "isNew": "false"
          }
        }'
    ),
    (the_loader_id, 'mail_config', '{
          "mail": {
            "from": "webindex@web123.ru",
            "host": "192.168.21.3",
            "subject": "Загрузчик Pandoria.Responsum",
            "to": "evgeny.utkin@mediascope.net",
            "used": "false"
          }
        }'
    ),
    (the_snapshotter_id, 'user', the_snapshotter_user),
    (the_snapshotter_id, 'password', the_snapshotter_password),
    (the_snapshotter_id, 'url',the_snapshotter_url),
    (the_snapshotter_id, 'procedure', 'snapshot RDS rebuild'),
    (the_starter_id, 'login', 'zabbix'),
    (the_starter_id, 'password', 'zabbix'),
    (the_starter_id, 'url', '/responsum-info/zabbix/control'),
    (the_starter_id, 'isStart',  true);

END $$;

COMMIT;