-- --------------------------------------------------------------------------------
-- _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';
    
    set result = row_count();

    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(
        tagId int unsigned not null,
        languageId char(3) not null,
        name varchar(255),
        primary key(tagId, languageId)
    ) engine=MEMORY;
    
    insert externalDataUpdateLocal(tagId, languageId, name)
    select ed.tagId, l.id, edl.name
    from externalDataT ed
    join nodeT n on ed.tagId = n.id
    join languageT l on 1=1
    left join externalDataTL edl on ed.id = edl.id and l.id = edl.languageId
    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(tagId, languageId, name)
    from externalDataUpdateLocal;
    
    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);

    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();
    
    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$$$




-- --------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------
-- 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
    insert externalDataTL(id, languageId, name)
    select external_data_id_, lang_id_, name_
    from dual
    where name_ is not null
    on duplicate key update externalDataTL.name = name_;

    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 lang_id char(3);
    
    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_query, sql_tmpl2 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;

    set sql_tmpl2 = replace(replace(sql_tmpl, '{{1}}', parent_node_id_), '{{2}}', type_code_);
    
    open cur;
    main_loop: loop
        fetch cur into lang_id;
        if done then 
            leave main_loop;
        end if;
        
        set sql_query = replace(sql_tmpl2, '{{0}}', lang_id);
        call __executeSQL(sql_query);
    end loop;
    close cur;

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$$$