-- --------------------------------------------------------------------------------
-- _100_externalData Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$


create function __externalData_TagHelper(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert ignore externalDataInserted(tagId) -- externalDataInserted created in externalData_UpdateTags
    values(tag_id_);
    return tag_id_;
end$$$


create function __externalData_CreateTags(
    external_data_source_id_ varchar(30) binary,
    no_parent_node_ int unsigned,
    parent_node_id_ int unsigned)
returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    set @1 = nodeLink_UpdateStart();

    /*update externalDataT ed set
        ed.tagId = __externalData_TagHelper(
                        nodeLink_Relink2(
                            'owns', 
                            ed.parentNodeId, 
                            tag_Create(null, ed.typeCode, ed.externalKey, null, null, null, null, null, null, ed.enabled)))
    where ed.externalDataSourceId = external_data_source_id_
        and ed.tagId is null
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'y';*/
    
    update externalDataT ed set
        ed.tagId = __externalData_TagHelper(
                        tag_Create(null, ed.typeCode, ed.externalKey, null, null, null, null, null, null, ed.enabled))
    where ed.externalDataSourceId = external_data_source_id_
        and ed.tagId is null
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'y';

    set result = row_count(); -- consider only inserted

    insert zzzDevNullExternalDataT(n)
    select nodeLink_Relink('owns', coalesce(parentEd.tagId, if(no_parent_node_, null, parent_node_id_)), ed.tagId)
    from externalDataT ed
    join externalDataInserted edi on ed.tagId = edi.tagId
    left join externalDataT parentEd on ed.externalParentKey = parentEd.externalKey;

    set @2 = nodeLink_UpdateEnd();
    
    return result;
end$$$

create function __externalData_UpdateTagNames(
    external_data_source_id_ varchar(30) binary,
    no_parent_node_ int unsigned,
    parent_node_id_ int unsigned,
    force_update_ int unsigned) 
returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    drop temporary table if exists externalDataUpdateLocal;
    create temporary table externalDataUpdateLocal(
        nodeId int unsigned not null primary key
    ) engine=MEMORY;
    
    insert ignore externalDataUpdateLocal(nodeId)
    select ed.tagId
    from externalDataT ed
    join nodeT n on ed.tagId = n.id
    left join externalDataInserted edi on ed.tagId = edi.tagId
    where ed.externalDataSourceId = external_data_source_id_
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'y'
        and (force_update_ or ed.changed > n.changed or edi.tagId is not null);

    insert zzzDevNullExternalDataT(n)
    select tag_UpdateLocal(ed.tagId, l.id, edl.name)
    from externalDataT ed
    join externalDataUpdateLocal edul on ed.tagId = edul.nodeId
    join languageT l on 1=1
    left join externalDataTL edl on ed.id = edl.id and l.id = edl.languageId
    where ed.externalDataSourceId = external_data_source_id_
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'y';
    
    set result = row_count() / (select count(*) from languageT);

    drop temporary table externalDataUpdateLocal;
    
    return result;
end$$$


create function __externalData_DisableTags(
    external_data_source_id_ varchar(30) binary,
    no_parent_node_ int unsigned,
    parent_node_id_ int unsigned,
    force_update_ int unsigned) 
returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    drop temporary table if exists externalDataDisable;
    create temporary table externalDataDisable(
        tagId int unsigned not null primary key
    ) engine=MEMORY;
    
    insert externalDataDisable(tagId)
    select ed.tagId
    from externalDataT ed
    join nodeT n on ed.tagId = n.id
    where ed.externalDataSourceId = external_data_source_id_
        and (no_parent_node_ or ed.parentNodeId = parent_node_id_)
        and ed.enabled = 'n'
        and (force_update_ or ed.changed > n.changed);

    set @1 = nodeLink_UpdateStart(); -- prevent from redundant cache updates

    insert zzzDevNullExternalDataT(n)
    select tag_Update(tagId, null, null, null, null, null, null, null, null, null, 'n') -- disable and exclude from search
    from externalDataDisable;
    
    set result = row_count();

    set @2 = nodeLink_UpdateEnd();
    
    drop temporary table externalDataDisable;
    
    return result;
end$$$


create function externalData_UpdateTags(
    external_data_source_id_ varchar(30) binary,
    dataset_code_ varchar(255),
    force_update_ int unsigned
) returns varchar(50)
not deterministic modifies sql data
begin
    declare number_added, number_changed, number_disabled int unsigned;
    declare force_update int unsigned default coalesce(force_update_, 0);
    declare parent_node_id int unsigned default (
        select edd.nodeId 
        from externalDataSourceDatasetT edd
        where edd.externalDataSourceId = external_data_source_id_
            and edd.code = dataset_code_);
    declare no_parent_node int unsigned default (parent_node_id is null);

    drop temporary table if exists externalDataInserted;
    create temporary table externalDataInserted(
        tagId int unsigned not null primary key
    ) engine=MEMORY;
    
    -- insert missing ones and link created tags
    
    set number_added = __externalData_CreateTags(external_data_source_id_, no_parent_node, parent_node_id);
    
    -- update names

    set number_changed = __externalData_UpdateTagNames(external_data_source_id_, no_parent_node, parent_node_id, force_update);
    
    -- disable "deleted"

    set number_disabled = __externalData_DisableTags(external_data_source_id_, no_parent_node, parent_node_id, force_update);

    drop temporary table externalDataInserted;

    return concat_ws('|', number_added, number_changed, number_disabled);
end$$$


create procedure __externalData_IterateLanguage(sql_tmpl varchar(2000), repl varchar(10))
modifies sql data
begin
    declare lang_id char(3);
    
    declare sql_query varchar(2000);
    
    declare done int unsigned default 0;
    declare cur cursor for select l.id from languageT l;
    declare continue handler for not found set done = 1;

    open cur;
    main_loop: loop
        fetch cur into lang_id;
        if done then 
            leave main_loop;
        end if;
        
        set sql_query = replace(sql_tmpl, repl, lang_id);
        call __executeSQL(sql_query);
    end loop;
    close cur;

end$$$


-- --------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------
-- OSM import
-- --------------------------------------------------------------------------------
-- import process: 
-- 1) load data infile into temp table - ONE entity (city/district/etc at a time)
-- 2) convert temp table -> externalDataT/externalDataTL by running external_OSM_Import
-- 3) run externalData_UpdateTags

-- temp table format for streets: 
-- 1) id - internal OSM way identifier, unique within OSM, but one street may have several ids
-- 2) highway - highway label, no use for us
-- 3) name_ukr - default name (ukrainian), also it is the local key, so we need to group by this field
-- 4) name_rus - russian name
-- 5) name_eng - english name

/*
create table tmpOsmDataT(
    id varchar(50) not null,
    highway varchar(100),
    name_ukr varchar(255),
    name_rus varchar(255),
    name_eng varchar(255),
    primary key(id),
    index(name_ukr)
) engine=InnoDB;

load data local infile '<file name>'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;
--------------------------------
truncate table tmpOsmDataT;

load data local infile '/home/castor/public_html/m2.te.ua/ternopil-streets.txt'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;

call external_OSM_Import('ternopil', 'street');

truncate table tmpOsmDataT;

load data local infile '/home/castor/public_html/m2.te.ua/kyiv-suburbs.txt'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;

call external_OSM_Import('kyiv', 'neighborhood');

truncate table tmpOsmDataT;

load data local infile '/home/castor/public_html/m2.te.ua/kyiv-streets.txt'
replace into table tmpOsmDataT
character set utf8
columns terminated by '\t'
lines terminated by '\r\n'
ignore 1 lines;

call external_OSM_Import('kyiv', 'street');

truncate table tmpOsmDataT;

*/


create function __external_OSM_UpdateLocal(
    external_data_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    if name_ is not null then
        insert externalDataTL(id, languageId, name)
        values(external_data_id_, lang_id_, name_)
        on duplicate key update externalDataTL.name = name_;
    end if;

    return external_data_id_;
end$$$


create procedure __external_OSM_UpdateName(parent_node_id_ int unsigned, type_code_ varchar(30) binary)
modifies sql data
begin
    declare sql_tmpl varchar(2000) default 
'insert zzzDevNullExternalDataT(n)
select __external_OSM_UpdateLocal(ed.id, ''{{0}}'', nullif(tod.name_{{0}}, ''''))
from externalDataT ed 
join tmpOsmDataT tod on ed.externalKey = tod.name_ukr
where ed.externalDataSourceId = ''osm''
    and ed.parentNodeId = {{1}}
    and ed.typeCode = ''{{2}}''
    and ed.enabled = ''y''
group by tod.name_ukr';
    declare sql_tmpl2 varchar(2000);
    
    set sql_tmpl2 = replace(replace(sql_tmpl, '{{1}}', parent_node_id_), '{{2}}', type_code_);

    call __externalData_IterateLanguage(sql_tmpl2, '{{0}}');
end$$$


create procedure external_OSM_Import(
    dictionary_id_ varchar(255) binary,
    type_code_ varchar(30) binary) 
modifies sql data
begin
    declare parent_node_id int unsigned default (
        select edd.nodeId 
        from externalDataSourceDatasetT edd
        where edd.externalDataSourceId = 'osm'
            and edd.code = dictionary_id_);

    -- insert
    
    insert externalDataT(
        externalDataSourceId, 
        parentNodeId, 
        externalKey, 
        typeCode, 
        enabled)
    select 
        'osm',
        parent_node_id,
        tod.name_ukr,
        type_code_,
        'y'
    from tmpOsmDataT tod
    where not exists (
            select 1 
            from externalDataT ed 
            where ed.externalDataSourceId = 'osm'
                and ed.parentNodeId = parent_node_id
                and ed.typeCode = type_code_
                and ed.externalKey = tod.name_ukr)
    group by tod.name_ukr;
    
    -- disable
    
    update externalDataT ed set
        ed.enabled = 'n'
    where ed.externalDataSourceId = 'osm'
        and ed.parentNodeId = parent_node_id
        and ed.typeCode = type_code_
        and not exists (select 1 from tmpOsmDataT tod where tod.name_ukr = ed.externalKey);

    -- update names
    call __external_OSM_UpdateName(parent_node_id, type_code_);
end$$$


create function external_OSM_Help() returns varchar(20000)
begin
    return '';
end$$$



-- --------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------
-- Admter import
-- --------------------------------------------------------------------------------
-- import process: 
-- 1) load data infile into temp table - whole Admter DB
-- 2) convert temp table -> externalDataT/externalDataTL by running external_Admter_Import
-- 3) run externalData_UpdateTags

-- temp table format for Admter - tmpAdmterDataT: 
-- id - internal Admter id; it is unknown if objects retain their id when Admter DB version is upgraded
-- parentId - parent id; 0 for oblasts
-- koatuu - KOATUU code
-- ukrName
-- rusName
-- engName
-- oblast_koatuu - the KOATUU code of oblast this oject belongs to
-- district_koatuu - the KOATUU code of raion this oject belongs to
-- adminCenterId - id of administrative center; for oblast and raion - the id of "main city/town"
-- type - locality type: 2 - oblast; 3 - raion; 4 - city district; 5 - village council; 6 - city; 7 - s.m.t; 8 - village; 9 - hamlet

/*
load data local infile '<file name>'
replace into table tmpAdmterDataT
character set utf8
columns terminated by ',' optionally enclosed by '"'
lines terminated by '\n';
--------------------------------
truncate table tmpAdmterDataT;

load data local infile '/home/castor/public_html/m2.te.ua/trunk/db/admter_object3.txt'
replace into table tmpAdmterDataT
character set utf8
columns terminated by ',' optionally enclosed by '"'
lines terminated by '\n';

call external_Admter_Import();

*/

create procedure __external_Admter_UpdateName()
modifies sql data
begin
    declare sql_tmpl varchar(2000) default 
'insert externalDataTL(id, languageId, name)
select ed.id, ''{{0}}'', tad.{{0}}Name
from externalDataT ed
join tmpAdmterDataT tad on ed.externalKey = tad.koatuu
where ed.externalDataSourceId = ''admter''
    and ed.enabled = ''y''
on duplicate key update
    externalDataTL.name = tad.{{0}}Name';
    
    call __externalData_IterateLanguage(sql_tmpl, '{{0}}');
end$$$


create procedure external_Admter_Import() 
modifies sql data
begin
    -- insert
    
    insert externalDataT(
        externalDataSourceId, 
        externalParentKey,
        externalKey, 
        typeCode, 
        enabled)
    select 
        'admter',
        if(tad.district_koatuu != '', tad.district_koatuu, if(tad.oblast_koatuu != '', tad.oblast_koatuu, null)),
        tad.koatuu,
        eat.typeCode,
        'y'
    from tmpAdmterDataT tad
    join externalAdmterTypeT eat on tad.type = eat.id
    join nodeTypeT nt on eat.typeCode = nt.code
    where not exists (
            select 1 
            from externalDataT ed 
            where ed.externalDataSourceId = 'admter'
                and ed.typeCode = eat.typeCode
                and ed.externalKey = tad.koatuu)
    group by tad.koatuu;
    
    -- disable
    
    update externalDataT ed set
        ed.enabled = 'n'
    where ed.externalDataSourceId = 'admter'
        and not exists (select 1 from tmpAdmterDataT tad where tad.koatuu = ed.externalKey);

    -- update names
    call __external_Admter_UpdateName();
end$$$


-- --------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------
-- Streets import
-- --------------------------------------------------------------------------------
-- import process: 
-- 1) load data infile into temp table - whole streets table
-- 2) convert temp table -> storedTagDataT/storedTagDataTL by running external_TagData_ImportStreets

-- temp table format for streets - tmpStreetDataT: 
-- id - internal id; it is expected it should be consistent over the time - streets won't change their IDs
-- name_ukr
-- name_rus
-- name_eng

/*
load data local infile <file name>
replace into table tmpStreetDataT
character set utf8
columns terminated by '\t'
lines terminated by '\n';
-- -------
truncate table tmpStreetDataT;

load data local infile '/home/castor/public_html/m2.te.ua/trunk/db/all-streets-02.tsv'
replace into table tmpStreetDataT
character set utf8
columns terminated by '\t'
lines terminated by '\n';

-- on hosting server

truncate table tmpStreetDataT;

load data local infile '/home/m2/all-streets-02.tsv'
replace into table tmpStreetDataT
character set utf8
columns terminated by '\t'
lines terminated by '\n';

call external_TagData_ImportStreets();

*/

create function __external_TagData_Insert(node_type_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare id int unsigned;
    
    insert storedTagDataT(nodeTypeId)
    values (node_type_id_);
    
    set id = last_insert_id();
    
    return id;
end$$$


create procedure __external_TagData_ImportStreetNames()
modifies sql data
begin
    declare sql_tmpl varchar(2000) default 
'insert storedTagDataTL(id, languageId, name)
select stds.external_id, ''{{0}}'', stds.name_{{0}}
from storedTagDataStreetT stds
where stds.external_id is not null
on duplicate key update
    storedTagDataTL.name = stds.name_{{0}}';

    call __externalData_IterateLanguage(sql_tmpl, '{{0}}');
end$$$


create procedure external_TagData_ImportStreets()
modifies sql data
begin
    declare node_type_id int unsigned default (select id from nodeTypeT where code = 'street');
    
    -- updating storedTagDataStreetT table
    -- delete missing records, match by id
    delete from storedTagDataStreetT
    using storedTagDataStreetT
    left join tmpStreetDataT sd on storedTagDataStreetT.id = sd.id
    where sd.id is null;

    -- insert/update new records
    insert storedTagDataStreetT(id, name_ukr, name_rus, name_eng)
    select sd.id, sd.name_ukr, sd.name_rus, sd.name_eng
    from tmpStreetDataT sd
    on duplicate key update 
        storedTagDataStreetT.name_ukr = sd.name_ukr,
        storedTagDataStreetT.name_rus = sd.name_rus,
        storedTagDataStreetT.name_eng = sd.name_eng;

    -- updating storedTagDataT 
    -- delete missing records
    delete from storedTagDataT
    using storedTagDataT
    join nodeTypeT nt on storedTagDataT.nodeTypeId = nt.id
    left join storedTagDataStreetT stds on storedTagDataT.id = stds.external_id
    where nt.code = 'street'
        and stds.id is null;

    -- insert/update records
    update storedTagDataStreetT set
        external_id = __external_TagData_Insert(node_type_id)
    where external_id is null;

    -- update storedTagDataTL
    call __external_TagData_ImportStreetNames();
end$$$


create function external_CreateEntity(stored_id_ int unsigned, parent_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned;
    declare node_type_id int unsigned;
    declare internal_type_id varchar(10);

    select nt.id, nt.internalTypeId 
    into node_type_id, internal_type_id 
    from nodeTypeT nt 
    join storedTagDataT std on nt.id = std.nodeTypeId
    where std.id = stored_id_;

    if (internal_type_id = 'tag') then
        set node_id = tag_Create(node_type_id, null, '', null, null, null, null, null, null, null);

        insert zzzDevNullExternalDataT(n)
        select tag_UpdateLocal(node_id, l.id, stdl.name)
        from languageT l
        join storedTagDataTL stdl on l.id = stdl.languageId
        where stdl.id = stored_id_;
    end if;

    if (parent_id_ is not null) then
        set @1 = nodeLink_Relink(null, parent_id_, node_id);
    end if;

    return node_id;
end$$$