-- --------------------------------------------------------------------------------
-- _001_views
-- DON'T put this file into workbench
-- --------------------------------------------------------------------------------

-- nodeType

CREATE VIEW nodeTypeV AS
select
    nt.id,
    ntl.name,
    nt.code,
    nt.internalTypeId,
    nt.forceSinglePhoto,
    nt.canEditType,
    nt.canEditNode,
    nt.canHavePhoto,
    nt.canHaveRatingPoints,
    nt.canLinkFrom,
    nt.canLinkTo,
    nt.canSearch,
    nt.displayInObject,
    nt.displayInObjectList,
    nt.searchAnd,
    nt.displayNode,
    nt.requiredForObject,
    nt.sorter,
    ntl.languageId,
    nt.added,
    nt.changed
from nodeTypeT nt
left join languageT l on l.isDefault = 'y'
left join nodeTypeTL ntl on nt.id = ntl.id and ntl.languageId = coalesce(user_CurrentLanguage(), l.id);


CREATE VIEW nodeTypeVL AS
select ntl.*
from nodeTypeTL ntl;


-- nodeLink

CREATE VIEW nodeLinkV AS
select
    nlt.id nodeLinkTypeId,
    nlt.code,
    nlt.isDefault,
    nl.srcNodeId,
    nl.dstNodeId,
    nl.metric,
    nl.intermediate,
    nl.intermediateRecommendSingle,
    nl.intermediateRecommendMultiple,
    nl.added
from nodeLinkT nl
join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id;


-- ugly hack here - nodeLinkTypeId hardcoded!
CREATE VIEW nodeLinkOwnsV AS
select
    nl.nodeLinkTypeId,
    nl.srcNodeId,
    nl.dstNodeId,
    nl.metric,
    nl.intermediate,
    nl.intermediateRecommendSingle,
    nl.intermediateRecommendMultiple,
    nl.added
from nodeLinkT nl
where nl.nodeLinkTypeId = 1
    and nl.metric > 0;


create view nodeLinkChainV as
select
    chain1.nodeLinkTypeId,
    chain1.srcNodeId,
    chain1.dstNodeId midNodeId,
    chain2.dstNodeId,
    chain1.metric metric1,
    chain2.metric metric2,
    chain1.intermediate intermediate1,
    chain1.intermediateRecommendSingle intermediateRecommendSingle1,
    chain1.intermediateRecommendMultiple intermediateRecommendMultiple1,
    chain2.intermediate intermediate2,
    chain2.intermediateRecommendSingle intermediateRecommendSingle2,
    chain2.intermediateRecommendMultiple intermediateRecommendMultiple2,
    chain1.added added1,
    chain2.added added2
from nodeLinkT chain1
join nodeLinkT chain2 on chain1.nodeLinkTypeId = chain2.nodeLinkTypeId
    and chain1.dstNodeId = chain2.srcNodeId;


create view nodeLinkChainTransitionV as
select
    nodeLinkTypeId,
    srcNodeId,
    midNodeId,
    dstNodeId,
    metric1,
    metric2,
    intermediate1,
    intermediateRecommendSingle1,
    intermediateRecommendMultiple1,
    intermediate2,
    intermediateRecommendSingle2,
    intermediateRecommendMultiple2,
    added1,
    added2
from nodeLinkChainV
where srcNodeId != midNodeId
    and midNodeId != dstNodeId
    and metric1 = 1
    and metric2 = 1;


create view nodeLinkDirectV as
select *
from nodeLinkT directlink
where directlink.metric = 1 and directlink.intermediate = 0;


drop view if exists nodeLinkForkSrcV;
create view nodeLinkForkSrcV as
select
    fork1.nodeLinkTypeId,
    fork1.srcNodeId,
    fork1.dstNodeId dstNodeId1,
    fork2.dstNodeId dstNodeId2,
    fork1.metric metric1,
    fork2.metric metric2,
    fork1.intermediate intermediate1,
    fork1.intermediateRecommendSingle intermediateRecommendSingle1,
    fork1.intermediateRecommendMultiple intermediateRecommendMultiple1,
    fork2.intermediate intermediate2,
    fork2.intermediateRecommendSingle intermediateRecommendSingle2,
    fork2.intermediateRecommendMultiple intermediateRecommendMultiple2,
    fork1.added added1,
    fork2.added added2
from nodeLinkT fork1
join nodeLinkT fork2 on fork1.nodeLinkTypeId = fork2.nodeLinkTypeId
    and fork1.srcNodeId = fork2.srcNodeId;


-- node

create view _nodeV as
select *
from nodeT
where displayNode = 'y';


CREATE VIEW nodeV AS
select
    n.id,
    n.nodeTypeId,
    n.forceSingleNode,
    n.name,
    nr.isRoot,
    nt.name typeName,
    nt.code typeCode,
    nt.internalTypeId,
    nt.canEditType,
    nt.canEditNode,
    nt.canHavePhoto,
    nt.canHaveRatingPoints,
    nt.canLinkFrom,
    nt.canLinkTo,
    nt.canSearch,
    nt.displayInObject,
    nt.displayInObjectList,
    nt.searchAnd,
    nt.requiredForObject,
    nt.sorter,
    nt.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from _nodeV n
join nodeTypeV nt on n.nodeTypeId = nt.id
left join nodeRootT nr on n.id = nr.id;


CREATE VIEW nodeRootLinkV AS
select
    rootnode.id,
    rootnode.nodeTypeId,
    rootnode.forceSingleNode,
    rootnode.name,
    nr.isRoot,
    rootnode.displayNode,
    rootnodetype.code typeCode,
    rootnodetype.canSearch typeCanSearch,
    rootnodetype.displayInObject typeDisplayInObject,
    rootnodetype.displayInObjectList typeDisplayInObjectList,
    rootnodetype.requiredForObject typeRequiredForObject,
    rootnodetype.sorter typeSorter,
    rootnode.added,
    rootnode.addedUserId,
    rootnode.addedUserName,
    rootnode.addedIp,
    rootnode.changed,
    rootnode.changedUserId,
    rootnode.changedUserName,
    rootnode.changedIp,
    rootlink.nodeLinkTypeId,
    rootlink.dstNodeId,
    rootlink.metric,
    rootlink.intermediate,
    rootlink.intermediateRecommendSingle,
    rootlink.intermediateRecommendMultiple
from nodeT rootnode
join nodeTypeT rootnodetype on rootnode.nodeTypeId = rootnodetype.id
join nodeLinkOwnsV rootlink on rootnode.id = rootlink.srcNodeId
join nodeRootT nr on rootnode.id = nr.id;


create view nodeRootGeoV as
select
    rootlink.id,
    rootlink.nodeTypeId,
    rootlink.forceSingleNode,
    rootlink.name,
    rootlink.isRoot,
    rootlink.displayNode,
    rootlink.typeCode,
    rootlink.typeCanSearch,
    rootlink.typeDisplayInObject,
    rootlink.typeDisplayInObjectList,
    rootlink.typeRequiredForObject,
    rootlink.typeSorter,
    rootlink.added,
    rootlink.addedUserId,
    rootlink.addedUserName,
    rootlink.addedIp,
    rootlink.changed,
    rootlink.changedUserId,
    rootlink.changedUserName,
    rootlink.changedIp,
    rootlink.dstNodeId,
    rootlink.metric,
    rootlink.intermediate,
    rootlink.intermediateRecommendSingle,
    rootlink.intermediateRecommendMultiple
from nodeRootLinkV rootlink
where rootlink.typeCode = 'rootgeo';


create view imageV as
select
    img.id,
    img.id imageId,
    img.nodeId,
    img.forceSinglePhoto,
    img.name,
    img.filenameOriginal,
    img.filenameFs,
    img.display,
    img.sorter,
    img.imageTypeId,
    img.videoType,
    img.url,
    img.extraId,
    img.extraData,
    img.added,
    img.addedUserId,
    img.addedUserName,
    img.addedIp,
    img.changed,
    img.changedUserId,
    img.changedUserName,
    img.changedIp
from imageT img;


create view templateV as
select
    node.id nodeId,
    tmpl.srcNodeTypeId,
    tmpl.added
from nodeT node
left join templateT tmpl on node.id = tmpl.dstNodeId;


-- allowedNodeLink

CREATE VIEW allowedNodeLinkV AS
select *
from allowedNodeLinkT;


CREATE VIEW allowedNodeLinkCanCreateV AS
select
    nt1.id srcNodeTypeId,
    nt2.id dstNodeTypeId
from nodeTypeT nt1
join nodeTypeT nt2 on 1=1
where nt1.canLinkFrom = 'y'
    and nt2.canLinkTo = 'y'
    and not exists (select 1 from allowedNodeLinkT anl where anl.srcNodeTypeId = nt1.id and anl.dstNodeTypeId = nt2.id);


CREATE VIEW allowedNodeLinkByNodeV AS
select
    srcNode.id srcNodeId,
    srcNode.nodeTypeId srcNodeTypeId,
    srcNode.displayNode srcDisplayNode,
    dstNode.id dstNodeId,
    dstNode.nodeTypeId dstNodeTypeId,
    dstNode.displayNode dstDisplayNode,
    anl.essential,
    anl.singleSrc,
    anl.added,
    anl.changed
from allowedNodeLinkT anl
join nodeT srcNode on anl.srcNodeTypeId = srcNode.nodeTypeId
join nodeT dstNode on anl.dstNodeTypeId = dstNode.nodeTypeId;


create view nodeAllowedChildTypeV as
select
    n.id nodeId,
    anl.srcNodeTypeId,
    anl.dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    anl.added,
    anl.changed
from nodeT n
join allowedNodeLinkT anl on n.nodeTypeId = anl.srcNodeTypeId;


create view nodeAllowedParentTypeV as
select
    n.id nodeId,
    anl.srcNodeTypeId,
    anl.dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    anl.added,
    anl.changed
from nodeT n
join allowedNodeLinkT anl on n.nodeTypeId = anl.dstNodeTypeId;


-- node recommend link

CREATE VIEW _nodeRecommendLink4V as
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked,
    canlink.n canLink
from nodeT srcnode
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

left join nodeLinkT link on nlt.id = link.nodeLinkTypeId
    and srcnode.id = link.srcNodeId
    and dstnode.id = link.dstNodeId
    and link.metric = 1

left join zzzIntsT canlink1 on canlink1.n = 1
    and link.metric is null
    and anl.singleSrc = 'n'
    and (select count(distinct n1.nodeTypeId)
            from nodeLinkT nl1
            join nodeT n1 on nl1.srcNodeId = n1.id
            where nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = srcnode.id
                and nl1.metric = 1) =
        (select count(distinct n2.nodeTypeId)
            from nodeLinkForkSrcV nl2
            join nodeT n2 on nl2.srcNodeId = n2.id
            where nl2.nodeLinkTypeId = nlt.id
                and nl2.dstNodeId1 = srcnode.id
                and nl2.metric1 = 1
                and nl2.dstNodeId2 = dstnode.id
                and nl2.metric2 = 1)

left join zzzIntsT canlink3 on canlink3.n = 1
    and link.metric is null
    and anl.singleSrc = 'y'
-- check if already linked to the node of the same type
    and not exists(
        select 1
        from nodeLinkT nl3
        join nodeT n3 on nl3.srcNodeId = n3.id
        where nl3.nodeLinkTypeId = nlt.id
            and nl3.dstNodeId = dstnode.id
            and nl3.metric = 1
            and n3.nodeTypeId = srcnode.nodeTypeId)
-- check if srcnode.id and dstnode.id have common parent node, with special conditions
    and exists(
        select 1
        from nodeLinkForkSrcV nl4
        join nodeT n4 on nl4.srcNodeId = n4.id
        join allowedNodeLinkT anl4 on n4.nodeTypeId = anl4.srcNodeTypeId
        where nl4.nodeLinkTypeId = nlt.id
            and nl4.dstNodeId1 = srcnode.id
            and nl4.metric1 = 1
            and nl4.dstNodeId2 = dstnode.id
            and nl4.metric2 = 1
            and anl4.dstNodeTypeId = dstnode.nodeTypeId
            and anl4.singleSrc = anl.singleSrc
            and (anl4.essential = 'y'
-- if the link is not essential make sure we are not stepping over the srcnode.id
                or not exists(
                    select 1
                    from nodeLinkChainTransitionV exilink41
                    where nl4.nodeLinkTypeId = exilink41.nodeLinkTypeId
                        and nl4.srcNodeId = exilink41.srcNodeId
                        and nl4.dstNodeId1 = exilink41.dstNodeId))
-- and make sure we use the nearest common parent, i.e. there are no more common parents up the tree
            and not exists(
                select 1
                from nodeLinkChainTransitionV exilink42
                where nl4.nodeLinkTypeId = exilink42.nodeLinkTypeId
                    and nl4.srcNodeId = exilink42.srcNodeId
                    and nl4.dstNodeId2 = exilink42.dstNodeId))
left join zzzIntsT canlink on canlink.n = 1 and (canlink1.n = 1 or canlink3.n = 1)
left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
    and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId
where srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and exceptNodeNodeType.srcNodeId is null
    and (canlink.n = 1 or link.metric = 1);


CREATE VIEW _nodeRecommendLink5V as
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked
    
from nodeT srcnode
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

left join cacheNodeRecommendSumT cache on srcnode.id = cache.srcNodeId and dstnode.id = cache.dstNodeId

join nodeLinkForkSrcV fork on nlt.id = fork.nodeLinkTypeId
    and srcnode.id = fork.dstNodeId1
    and dstnode.id = fork.dstNodeId2
    and fork.metric1 = 1
    and fork.metric2 = 1

left join nodeLinkT link on nlt.id = link.nodeLinkTypeId
    and srcnode.id = link.srcNodeId
    and dstnode.id = link.dstNodeId
    and link.metric = 1

left join nodeT forknode on link.metric is null and fork.srcNodeId = forknode.id 
left join allowedNodeLinkT forkallow on link.metric is null 
    and forknode.nodeTypeId = forkallow.srcNodeTypeId 
    and srcnode.nodeTypeId = forkallow.dstNodeTypeId
    and anl.singleSrc = forkallow.singleSrc

left join templateT srcTemplate on srcnode.id = srcTemplate.dstNodeId
left join templateT dstTemplate on dstnode.id = dstTemplate.dstNodeId
left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
    and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId

where 1=1
    and (link.metric is not null 
        or (
                anl.singleSrc = 'y'
                and fork.intermediate2 = 0
                and (forkallow.essential = 'y' or fork.intermediate1 = 0)
        )
        or (
                anl.singleSrc = 'n'
                and srcnode.parentNodeTypeCount = cache.nodeTypeIdDistinctCount
        )
    )
    and srcTemplate.dstNodeId is null
    and dstTemplate.dstNodeId is null
    and exceptNodeNodeType.srcNodeId is null;

/*                and srcnode.parentNodeTypeCount =
                (select count(distinct n2.nodeTypeId)
                    from nodeLinkForkSrcV nl2
                    join nodeT n2 on nl2.srcNodeId = n2.id
                    where nl2.nodeLinkTypeId = nlt.id
                        and nl2.dstNodeId1 = srcnode.id
                        and nl2.metric1 = 1
                        and nl2.dstNodeId2 = dstnode.id
                        and nl2.metric2 = 1)*/


drop view if exists _nodeRecommendLink6V;
create view _nodeRecommendLink6V as
select
    nlt.id nodeLinkTypeId,
    nlt.code nodeLinkTypeCode,
    srcnode.id srcNodeId,
    srcnode.nodeTypeId srcNodeTypeId,
    dstnode.id dstNodeId,
    dstnode.nodeTypeId dstNodeTypeId,
    anl.essential,
    anl.singleSrc,
    link.metric linked,
    fork.metric1 canLink
    
from nodeT srcnode
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

join nodeLinkForkSrcV fork on nlt.id = fork.nodeLinkTypeId
    and srcnode.id = fork.dstNodeId1
    and dstnode.id = fork.dstNodeId2
    and fork.metric1 = 1
    and fork.metric2 = 1

left join nodeLinkT link on nlt.id = link.nodeLinkTypeId
    and srcnode.id = link.srcNodeId
    and dstnode.id = link.dstNodeId
    and link.metric = 1

left join zzzIntsT z on z.n = 0
    and link.metric is null
    and anl.singleSrc = 'n'
    and srcnode.parentNodeTypeCount !=
    (select count(distinct n2.nodeTypeId)
        from nodeLinkForkSrcV nl2
        join nodeT n2 on nl2.srcNodeId = n2.id
        where nl2.nodeLinkTypeId = nlt.id
            and nl2.dstNodeId1 = srcnode.id
            and nl2.metric1 = 1
            and nl2.dstNodeId2 = dstnode.id
            and nl2.metric2 = 1)

left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on srcnode.id = exceptNodeNodeType.srcNodeId 
    and dstnode.nodeTypeId = exceptNodeNodeType.dstNodeTypeId

where 1=1
    -- and cache.srcNodeId is null
    and link.metric is null
    and z.n is null
    and fork.intermediateRecommendSingle1 = 0
    -- and (forkallow.essential = 'y' or fork.intermediate1 = 0)
    -- and fork.intermediateRecommend2 = 0
    and fork.intermediateRecommendMultiple2 = 0
    and exceptNodeNodeType.srcNodeId is null;

/*
    left join nodeT forknode on link.metric is null and fork.srcNodeId = forknode.id 
    left join allowedNodeLinkT forkallow on link.metric is null 
        and forknode.nodeTypeId = forkallow.srcNodeTypeId 
        and srcnode.nodeTypeId = forkallow.dstNodeTypeId
        and anl.singleSrc = forkallow.singleSrc
*/
/*
    left join cacheNodeRecommendSum T cache on 1=1
        -- and link.metric is null
        and srcnode.id = cache.srcNodeId 
        and dstnode.id = cache.dstNodeId
        and srcnode.parentNodeTypeCount != cache.nodeTypeIdDistinctCount
*/

drop view if exists nodeRecommendLinkV;
create view nodeRecommendLinkV as
select
    nrl.nodeLinkTypeId,
    nrl.nodeLinkTypeCode,
    nrl.srcNodeId,
    nrl.srcNodeTypeId,
    1 srcMetric,
    1 viaNodeId,
    nrl.dstNodeId,
    nrl.dstNodeTypeId,
    1 dstMetric,
    nrl.essential,
    nrl.singleSrc
from _nodeRecommendLink6V nrl;


create view currencyV AS
select
    c.id,
    c.isDefault,
    c.sorter,
    cl.languageId,
    cl.name,
    cl.textAA,
    cl.textAZ,
    cl.textZA,
    cl.textZZ,
    c.added,
    c.changed
from currencyT c 
left join languageT l on l.isDefault = 'y'
left join currencyTL cl ON c.id = cl.id and cl.languageId = coalesce(user_CurrentLanguage(), l.id);


create view currencyExchangeRateLatestV as
select 
    cer.id,
    cer.currencyId,
    cer.effectiveDate,
    cer.rate,
    cer.added
from currencyExchangeRateT cer
left join currencyExchangeRateT exicer on cer.currencyId = exicer.currencyId and exicer.id > cer.id
where exicer.id is null;


-- user

CREATE VIEW userV AS
select
    u.*,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from userT u
join _nodeV n on u.id = n.id;


CREATE VIEW userOauthV AS
select *
from userOauthT;


-- tag and attribute

CREATE VIEW tagV as
select
    t.id,
    n.nodeTypeId typeId,
    n.forceSingleNode,
    n.isRoot,
    n.typeCode,
    n.typeName,
    n.internalTypeId,
    n.canEditNode typeCanEdit,
    n.canHavePhoto typeCanHavePhoto,
    n.canHaveRatingPoints typeCanHaveRatingPoints,
    n.canSearch typeCanSearch,
    n.displayInObject typeDisplayInObject,
    n.displayInObjectList typeDisplayInObjectList,
    n.searchAnd typeSearchAnd,
    n.requiredForObject typeRequiredForObject,
    n.sorter typeSorter,
    tl.name,
    t.sorter,
    t.canSearch,
    t.canSearchRaw,
    t.displayInObject,
    t.displayInObjectRaw,
    t.displayInObjectList,
    t.displayInObjectListRaw,
    t.factor,
    t.ratingPoints,
    t.enabled,
    a.dataTypeId attributeDataType,
    ac.attributeCode,
    a.required,
--    'n' sortable,
    tl.uofmPre,
    tl.uofmPost,
    rootgeo.metric geoRoot,
--    ten.extraName,
    n.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from tagT t
join nodeV n on t.id = n.id
left join tagTL tl on t.id = tl.id and n.languageId = tl.languageId
-- left join tagExtraNameT ten on t.id = ten.id
left join attributeT a on t.id = a.id
left join attributeCodeT ac on a.id = ac.id
left join nodeRootGeoV rootgeo on t.id = rootgeo.dstNodeId and rootgeo.metric = 1;

    
create view tagCacheV as
select
    tc.id,
    tc.tagNames,
    tc.tagNamesSearch,
    tc.tagNamesFullSearch
from tagCacheT tc;

/*
create view tagExtraNameV as
select
    id,
    extraName
from tagExtraNameT;
*/

CREATE VIEW attributeV as
-- extra fields to be joined to tagV
select
    a.id,
    an.decimalMin,
    an.decimalMax,
    an.decimalRound,
    ad.datetimeStorageId,
    ax.textIsLong,
    ap.priceRound
from attributeT a
left join attributeNumericT an on a.id = an.id
left join attributeDatetimeT ad on a.id = ad.id
left join attributeTextT ax on a.id = ax.id
left join attributePriceT ap on a.id = ap.id;


CREATE VIEW attributeDatetimeStorageFormatV as
-- more extra fields to be joined to attributeV (and tagV)
select
    ad.id,
    adfl.languageId,
    ad.datetimeStorageId,
    adf.storageFormat,
    adfl.name,
    adfl.outputFormat,
    adfl.outputFormatJs
from attributeDatetimeT ad
join attributeDatetimeFormatT adf on ad.datetimeStorageId = adf.id
join attributeDatetimeFormatTL adfl on adf.id = adfl.id;


create view attributeDatetimeFormatV as
-- to be used as list of available datetime storage formats
select
    adf.id,
    adfl.name,
    adf.storageFormat,
    adfl.outputFormat,
    adfl.outputFormatJs,
    adf.sorter,
    adfl.languageId,
    adf.added,
    adf.changed
from attributeDatetimeFormatT adf
left join languageT l on l.isDefault = 'y'
left join attributeDatetimeFormatTL adfl on adf.id = adfl.id and adfl.languageId = coalesce(user_CurrentLanguage(), l.id);


CREATE VIEW tagVL AS
select *
from tagTL;


create view tagForAdminV as
select
    t.*,
    tc.tagNames,
    tc.tagNamesSearch,
    tc.tagNamesFullSearch,
    ac.attributeNames,
    ac.attributeNamesSearch,
    na.tagNames ancestryNames,
    nal.tagNames ancestryNamesLang
from tagV t
left join tagCacheT tc on t.id = tc.id
left join attributeCacheT ac on t.id = ac.id
left join nodeAncestryT na on t.id = na.id and t.languageId = na.languageId
left join nodeAncestryAllT nal on t.id = nal.id;


create view attributeSortV as
-- intentionally disregard nodeT.displayNode in this view
select
    n.id,
    nt.name typeName,
    nt.code typeCode,
    tl.name,
    so.sorter,
    ac.attributeCode code,
    so.id sortId,
    sol.name sortName,
    so.sortDirection,
    nt.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from nodeT n
join nodeTypeV nt on n.nodeTypeId = nt.id
join tagT t on n.id = t.id
join tagTL tl on t.id = tl.id and nt.languageId = tl.languageId
join attributeT a on t.id = a.id
join sortOptionT so on a.id = so.attributeId 
join sortOptionTL sol on so.id = sol.id and nt.languageId = sol.languageId
left join attributeCodeT ac on a.id = ac.id
where t.enabled = 'y';


create view nodeAncestryDataV as
select 
    srcNode.id srcNodeId,
    tl.name srcName,
    tl.uofmPre srcUofmPre,
    tl.uofmPost srcUofmPost,
    nt.id srcTypeId,
    nt.internalTypeId srcInternalTypeId,
    ntl.name srcTypeName,
    avn.value srcNumericValue,
    avd.value srcDatetimeValue,
    adfl.outputFormat srcDatetimeOutputFormat,
    avt.value srcTextValue,
    avp.value srcPriceValue,
    
    dstNode.id dstNodeId,
    dstNode.nodeTypeId dstTypeId,
    l.id languageId,
    l.isDefault languageIsDefault,
    nas.sorter ancestrySorter
from nodeT dstNode 
join languageT l on 1=1
join nodeLinkTypeT nlt on nlt.isDefault = 'y'
join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId and dstNode.id = nl.dstNodeId
join nodeT srcNode on nl.srcNodeId = srcNode.id
join nodeAncestrySourceT nas on dstNode.nodeTypeId = nas.nodeTypeId 
    and srcNode.nodeTypeId = nas.srcNodeTypeId
join nodeTypeT nt on srcNode.nodeTypeId = nt.id
join nodeTypeTL ntl on srcNode.nodeTypeId = ntl.id and l.id = ntl.languageId
join tagTL tl on srcNode.id = tl.id and l.id = tl.languageId
left join attributeValueNumericT avn on srcNode.id = avn.attributeId and dstNode.id = avn.objectId
left join attributeValueDatetimeT avd on srcNode.id = avd.attributeId and dstNode.id = avd.objectId
left join attributeDatetimeT ad on avd.attributeId = ad.id
left join attributeDatetimeFormatTL adfl on ad.datetimeStorageId = adfl.id and l.id = adfl.languageId
left join attributeValueTextT avt on srcNode.id = avt.attributeId and dstNode.id = avt.objectId and l.id = avt.languageId
left join cacheAttributeValuePriceT avp on srcNode.id = avp.attributeId and dstNode.id = avp.objectId
where srcNode.displayNode = 'y'
    and nl.metric = 1;


create view nodeAncestryRawV as
select
    na.id nodeId,
    na.languageId,
    na.tagNames tagName
from nodeAncestryT na;


create view nodeAncestryV as
select
    na.id nodeId,
    na.tagNames tagName
from nodeAncestryT na
left join languageT l on l.isDefault = 'y'
where na.languageId = coalesce(user_CurrentLanguage(), l.id);


create view tagChildV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.uofmPre srcUofmPre,
    src.uofmPost srcUofmPost,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.uofmPre dstUofmPre,
    dst.uofmPost dstUofmPost,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames dstAncestry,
    nlct.midNodeId midId
from tagV src
join nodeLinkOwnsV nlo on src.id = nlo.srcNodeId and nlo.metric = 1
join tagV dst on nlo.dstNodeId = dst.id
left join nodeLinkChainTransitionV nlct on nlo.nodeLinkTypeId = nlct.nodeLinkTypeId 
    and src.id = nlct.srcNodeId
    and dst.id = nlct.dstNodeId
left join nodeLinkChainTransitionV exinlct on nlo.nodeLinkTypeId = exinlct.nodeLinkTypeId 
    and src.id = exinlct.srcNodeId
    and dst.id = exinlct.dstNodeId
    and nlct.midNodeId > exinlct.midNodeId
left join nodeAncestryT na on dst.id = na.id and dst.languageId = na.languageId
where src.isRoot is null
    and exinlct.midNodeId is null;


create view tagRecommendChildV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.uofmPre srcUofmPre,
    src.uofmPost srcUofmPost,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.uofmPre dstUofmPre,
    dst.uofmPost dstUofmPost,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames dstAncestry,
    nrl.srcMetric,
    nrl.dstMetric,
    nrl.essential
from tagV src
join nodeRecommendLinkV nrl on src.id = nrl.srcNodeId
join tagV dst on nrl.dstNodeId = dst.id
left join nodeAncestryT na on dst.id = na.id and dst.languageId = na.languageId
where src.isRoot is null;


create view tagDirectChildV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.uofmPre srcUofmPre,
    src.uofmPost srcUofmPost,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.uofmPre dstUofmPre,
    dst.uofmPost dstUofmPost,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames dstAncestry
from tagV src
join nodeLinkOwnsV nlo on src.id = nlo.srcNodeId and nlo.metric = 1 and nlo.intermediate = 0
join tagV dst on nlo.dstNodeId = dst.id
left join nodeAncestryT na on dst.id = na.id and dst.languageId = na.languageId;


create view tagParentV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.uofmPre srcUofmPre,
    src.uofmPost srcUofmPost,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.uofmPre dstUofmPre,
    dst.uofmPost dstUofmPost,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames srcAncestry,
    nlct.midNodeId midId
from tagV dst
join nodeLinkOwnsV nlo on dst.id = nlo.dstNodeId and nlo.metric = 1
join tagV src on nlo.srcNodeId = src.id
left join nodeLinkChainTransitionV nlct on nlo.nodeLinkTypeId = nlct.nodeLinkTypeId 
    and src.id = nlct.srcNodeId
    and dst.id = nlct.dstNodeId
left join nodeLinkChainTransitionV exinlct on nlo.nodeLinkTypeId = exinlct.nodeLinkTypeId 
    and src.id = exinlct.srcNodeId
    and dst.id = exinlct.dstNodeId
    and nlct.midNodeId > exinlct.midNodeId
left join nodeAncestryT na on src.id = na.id and src.languageId = na.languageId
where src.isRoot is null
    and exinlct.midNodeId is null;


create view tagRecommendParentV as
select
    src.id srcId,
    src.typeId srcTypeId,
    src.typeCode srcTypeCode,
    src.typeName srcTypeName,
    src.internalTypeId srcInternalTypeId,
    src.typeCanEdit srcCanEdit,
    src.typeCanSearch srcTypeCanSearch,
    src.typeDisplayInObject srcTypeDisplayInObject,
    src.typeDisplayInObjectList srcTypeDisplayInObjectList,
    src.typeSorter srcTypeSorter,
    src.name srcName,
    src.uofmPre srcUofmPre,
    src.uofmPost srcUofmPost,
    src.sorter srcSorter,
    src.canSearch srcCanSearch,
    src.canSearchRaw srcCanSearchRaw,
    src.displayInObject srcDisplayInObject,
    src.displayInObjectRaw srcDisplayInObjectRaw,
    src.displayInObjectList srcDisplayInObjectList,
    src.displayInObjectListRaw srcDisplayInObjectListRaw,
    src.factor srcFactor,
    src.ratingPoints srcRatingPoints,
    src.enabled srcEnabled,
    src.attributeDataType srcAttributeDataType,
    src.required srcRequired,
    dst.id dstId,
    dst.typeId dstTypeId,
    dst.typeCode dstTypeCode,
    dst.typeName dstTypeName,
    dst.internalTypeId dstInternalTypeId,
    dst.typeCanEdit dstCanEdit,
    dst.typeCanSearch dstTypeCanSearch,
    dst.typeDisplayInObject dstTypeDisplayInObject,
    dst.typeDisplayInObjectList dstTypeDisplayInObjectList,
    dst.typeSorter dstTypeSorter,
    dst.name dstName,
    dst.uofmPre dstUofmPre,
    dst.uofmPost dstUofmPost,
    dst.sorter dstSorter,
    dst.canSearch dstCanSearch,
    dst.canSearchRaw dstCanSearchRaw,
    dst.displayInObject dstDisplayInObject,
    dst.displayInObjectRaw dstDisplayInObjectRaw,
    dst.displayInObjectList dstDisplayInObjectList,
    dst.displayInObjectListRaw dstDisplayInObjectListRaw,
    dst.factor dstFactor,
    dst.ratingPoints dstRatingPoints,
    dst.enabled dstEnabled,
    dst.attributeDataType dstAttributeDataType,
    dst.required dstRequired,
    src.isRoot srcIsRoot,
    src.geoRoot srcGeoRoot,
    na.tagNames srcAncestry,
    nrl.srcMetric,
    nrl.dstMetric,
    nrl.essential
from tagV dst
join nodeRecommendLinkV nrl on dst.id = nrl.dstNodeId
join tagV src on nrl.srcNodeId = src.id
left join nodeAncestryT na on src.id = na.id and src.languageId = na.languageId
where src.isRoot is null;


-- object

CREATE VIEW objectV AS
select
    o.id,
    o.address,
    o.description,
    o.statusId,
    osl.name statusName,
    os.public,
    os.userEditable,
    os.userDraftable,
    c.latitude,
    c.longitude,
    o.rating,
    o.noteForModerator,
    o.noteForUser,
    o.alias,
    od.datePublished,
    od.dateExpiry,
    n.languageId,
    n.added,
    n.addedUserId,
    n.addedUserName,
    n.addedIp,
    n.changed,
    n.changedUserId,
    n.changedUserName,
    n.changedIp
from objectT o
join nodeV n on o.id = n.id
join objectStatusT os on o.statusId = os.id
left join objectStatusTL osl on o.statusId = osl.id and n.languageId = osl.languageId
left join coordT c on o.id = c.nodeId
left join objectDateT od on o.id = od.objectId and od.isActive = 'y';


CREATE VIEW objectForUserV AS
select o.*
from objectV o
join nodeLinkOwnsV nlo on nlo.dstNodeId = o.id 
    and nlo.srcNodeId = user_CurrentId();


CREATE VIEW objectStatusV AS
select
    os.id,
    osl.name,
    os.public,
    os.userEditable,
    os.userDraftable,
    os.sorter
from objectStatusT os
left join languageT l on l.isDefault = 'y'
left join objectStatusTL osl ON osl.id = os.id and osl.languageId = coalesce(user_CurrentLanguage(), l.id);


drop view if exists objectWithTagV;
drop view if exists objectWithTagRecommendV;
create view objectWithTagRecommendV as
select
    t.id srcId,
    t.typeId srcTypeId,
    t.typeCode srcTypeCode,
    t.typeName srcTypeName,
    t.internalTypeId srcInternalTypeId,
    t.typeCanEdit srcCanEdit,
    t.typeCanSearch srcTypeCanSearch,
    t.typeDisplayInObject srcTypeDisplayInObject,
    t.typeDisplayInObjectList srcTypeDisplayInObjectList,
    t.typeSorter srcTypeSorter,
    t.name srcName,
    t.sorter srcSorter,
    t.canSearch srcCanSearch,
    t.canSearchRaw srcCanSearchRaw,
    t.displayInObject srcDisplayInObject,
    t.displayInObjectRaw srcDisplayInObjectRaw,
    t.displayInObjectList srcDisplayInObjectList,
    t.displayInObjectListRaw srcDisplayInObjectListRaw,
    t.factor srcFactor,
    t.ratingPoints srcRatingPoints,
    t.enabled srcEnabled,
    t.attributeDataType srcAttributeDataType,
    t.attributeCode srcAttributeCode,
    t.required srcRequired,
--    t.sortable srcSortable,
    t.uofmPre srcUofmPre,
    t.uofmPost srcUofmPost,
    t.languageId srcLanguageId,
    t.isRoot srcIsRoot,
    t.geoRoot srcGeoRoot,

    a.decimalMin srcDecimalMin,
    a.decimalMax srcDecimalMax,
    a.decimalRound srcDecimalRound,
    a.datetimeStorageId srcDatetimeStorageId,
    a.textIsLong srcTextIsLong,
    a.priceRound srcPriceRound,

    adsf.storageFormat srcDatetimeStorageFormat,
    adsf.name srcDatetimeName,
    adsf.outputFormat srcDatetimeOutputFormat,
    adsf.outputFormatJs srcDatetimeOutputFormatJs,

    avn.value srcNumericValue,
    avd.value srcDatetimeValue,
    avt.value srcTextValue,
    avp.value srcPriceValue,

    nrl.linked,
    1 canLink,

    o.id objectId
from objectT o
join _nodeRecommendLink6V nrl on o.id = nrl.dstNodeId
join tagV t on nrl.srcNodeId = t.id
left join attributeV a on t.id = a.id
left join attributeDatetimeStorageFormatV adsf on a.id = adsf.id and t.languageId = adsf.languageId
left join attributeValueNumericT avn on a.id = avn.attributeId and o.id = avn.objectId
left join attributeValueDatetimeT avd on a.id = avd.attributeId and o.id = avd.objectId
left join attributeValueTextT avt on a.id = avt.attributeId and o.id = avt.objectId and t.languageId = avt.languageId
left join cacheAttributeValuePriceT avp on a.id = avp.attributeId and o.id = avp.objectId;


create view objectWithTagLinkedV as
select
    t.id srcId,
    t.typeId srcTypeId,
    t.typeCode srcTypeCode,
    t.typeName srcTypeName,
    t.internalTypeId srcInternalTypeId,
    t.typeCanEdit srcCanEdit,
    t.typeCanSearch srcTypeCanSearch,
    t.typeDisplayInObject srcTypeDisplayInObject,
    t.typeDisplayInObjectList srcTypeDisplayInObjectList,
    t.typeSorter srcTypeSorter,
    t.name srcName,
    t.sorter srcSorter,
    t.canSearch srcCanSearch,
    t.canSearchRaw srcCanSearchRaw,
    t.displayInObject srcDisplayInObject,
    t.displayInObjectRaw srcDisplayInObjectRaw,
    t.displayInObjectList srcDisplayInObjectList,
    t.displayInObjectListRaw srcDisplayInObjectListRaw,
    t.factor srcFactor,
    t.ratingPoints srcRatingPoints,
    t.enabled srcEnabled,
    t.attributeDataType srcAttributeDataType,
    t.attributeCode srcAttributeCode,
    t.required srcRequired,
--    t.sortable srcSortable,
    t.uofmPre srcUofmPre,
    t.uofmPost srcUofmPost,
    t.languageId srcLanguageId,
    t.isRoot srcIsRoot,
    t.geoRoot srcGeoRoot,

    a.decimalMin srcDecimalMin,
    a.decimalMax srcDecimalMax,
    a.decimalRound srcDecimalRound,
    a.datetimeStorageId srcDatetimeStorageId,
    a.textIsLong srcTextIsLong,
    a.priceRound srcPriceRound,

    adsf.storageFormat srcDatetimeStorageFormat,
    adsf.name srcDatetimeName,
    adsf.outputFormat srcDatetimeOutputFormat,
    adsf.outputFormatJs srcDatetimeOutputFormatJs,

    avn.value srcNumericValue,
    avd.value srcDatetimeValue,
    avt.value srcTextValue,
    avp.value srcPriceValue,

    1 linked,
    null canLink,

    o.id objectId
from objectT o
join nodeT n on o.id = n.id
join nodeLinkOwnsV nlo on o.id = nlo.dstNodeId
join tagV t on nlo.srcNodeId = t.id
left join attributeV a on t.id = a.id
left join attributeDatetimeStorageFormatV adsf on a.id = adsf.id and t.languageId = adsf.languageId
left join attributeValueNumericT avn on a.id = avn.attributeId and o.id = avn.objectId
left join attributeValueDatetimeT avd on a.id = avd.attributeId and o.id = avd.objectId
left join attributeValueTextT avt on a.id = avt.attributeId and o.id = avt.objectId and t.languageId = avt.languageId
left join cacheAttributeValuePriceT avp on a.id = avp.attributeId and o.id = avp.objectId
left join exceptNodeLinkNodeNodeTypeT exceptNodeNodeType on t.id = exceptNodeNodeType.srcNodeId
    and n.nodeTypeId = exceptNodeNodeType.dstNodeTypeId
where nlo.metric = 1
    and exceptNodeNodeType.srcNodeId is null;


create view objectWithUserV as
select
    u.*,
    o.id objectId
from userV u
join nodeLinkOwnsV nlo on u.id = nlo.srcNodeId
right join objectT o on nlo.dstNodeId = o.id;


create view viewStatDailyV as
select 
    nodeId,
    statDate,
    amount,
    added,
    changed
from viewStatDailyT;


create view cacheObjectSortV as
select 
    objectId,
    sortOptionId,
    sorter
from cacheObjectSortT;


-- FORWARD: __comment_CanCreateSubcomment is defined in _140_comment
-- FORWARD: user_CurrentId is defined in _060_user
create view commentV as
select
    c.id,
    c.parentCommentId,
    c.objectNodeId,
    c.authorUserId,
    c.userName,
    c.privateUserId,
    c.value,
    __comment_CanCreateSubcomment(c.id, user_CurrentId()) canComment,
    c.added,
    c.changed
from commentT c;


-- search

CREATE VIEW searchCriterionV AS
select sc.nodeId
from searchCriterionT sc
where sc.sessionId = search_CurrentId();


CREATE VIEW searchCriterionNumericV AS
select sc.nodeId, scn.valueLow, scn.valueHigh
from searchCriterionNumericT scn
join searchCriterionT sc on scn.searchCriterionId = sc.id
where sc.sessionId = search_CurrentId();


CREATE VIEW searchCriterionDatetimeV AS
select sc.nodeId, scd.valueLow, scd.valueHigh
from searchCriterionDatetimeT scd
join searchCriterionT sc on scd.searchCriterionId = sc.id
where sc.sessionId = search_CurrentId();


CREATE VIEW searchCriterionPriceV AS
select sc.nodeId, scp.currencyId, scp.valueLow, scp.valueHigh
from searchCriterionPriceT scp
join searchCriterionT sc on scp.searchCriterionId = sc.id
where sc.sessionId = search_CurrentId();


CREATE VIEW searchResultV AS
select 
    sessionId,
    nodeId,
    collapsed,
    extraObjects,
    relevancy
from searchResultT sr
where sr.sessionId = search_CurrentId();


create view searchResultSelectedV as
select *
from searchResultSelectedT srs
where srs.sessionId = search_CurrentId();


create view searchResultTagV as
select
    t.*,
    sr.*
from tagV t
join searchResultV sr on t.id = sr.nodeId;


create view searchTagV as
select
    t.*,
    sc.nodeId criterionId,
    scn.valueLow valueLowNumeric,
    scn.valueHigh valueHighNumeric,
    scd.valueLow valueLowDatetime,
    scd.valueHigh valueHighDatetime,
    scp.currencyId currencyIdPrice,
    scp.valueLow valueLowPrice,
    scp.valueHigh valueHighPrice,
    sr.nodeId resultId,
    sr.collapsed,
    sr.extraObjects
from tagV t
left join searchCriterionV sc on t.id = sc.nodeId
left join searchCriterionNumericV scn on t.id = scn.nodeId
left join searchCriterionDatetimeV scd on t.id = scd.nodeId
left join searchCriterionPriceV scp on t.id = scp.nodeId
left join searchResultV sr on t.id = sr.nodeId;


create view searchResultObjectV as
select 
    o.*,
    sr.sessionId,
    sr.relevancy,
    sc.nodeId criterionObjectId
from objectV o
join searchResultV sr on o.id = sr.nodeId
left join searchCriterionT sc on o.id = sc.nodeId and sr.sessionId = sc.sessionId
where o.public = 'y';


create view searchResultSelectedObjectV as
select
    o.*,
    srs.sessionId,
    srs.added addedSelected
from objectV o
join searchResultSelectedV srs on o.id = srs.objectId
where o.public = 'y';


create view searchResultObjectSortedV as
select 
    sro.*,
    so.id sortOptionId,
    os.sorter,
    so.id sortAttributeId,
    os.sorter sorterAsc,
    os.sorter sorterDesc
from searchResultObjectV sro
join sortOptionT so on 1=1
left join cacheObjectSortT os on sro.id = os.objectId and so.id = os.sortOptionId;


create view searchAttributeSortV as
select * 
from attributeSortV aso
where aso.typeCode = 'hiddenattribute'
    or exists(
        select 1
        from searchCriterionV sc
        join nodeLinkT nl on sc.nodeId = nl.srcNodeId and nl.metric = 1
        where nl.dstNodeId = aso.id);


create view searchSavedV as
select
    ss.sessionId,
    ss.name,
    ss.added,
    ss.changed
from searchSavedT ss
where ss.userId = user_CurrentId();


create view tagLinkCriterionV as
select 
    nl.dstNodeId tagId,
    n.id criterionId,
    n.typeCode criterionTypeCode
from nodeLinkOwnsV nl 
join searchCriterionV sc on nl.srcNodeId = sc.nodeId 
join nodeV n on sc.nodeId = n.id;


-- external data

create view storedTagDataV as
select
    std.id, 
    std.nodeTypeId,
    nt.code nodeTypeCode,
    stdl.name, 
    stdl.prefix,
    stdl.suffix,
    std.added,
    std.changed
from storedTagDataT std
join nodeTypeT nt on std.nodeTypeId = nt.id
left join languageT l on l.isDefault = 'y'
left join storedTagDataTL stdl on std.id = stdl.id and stdl.languageId = coalesce(user_CurrentLanguage(), l.id);


-- moderation queue

create view moderationQueueV as
select
    q.id,
    q.nodeId,
    q.comment1,
    q.comment2,
    q.resolved,
    n.typeName,
    n.typeCode,
    n.internalTypeId,
    n.name,
    n.languageId,
    tl.name tagName,
    tl.uofmPre tagPrefix,
    tl.uofmPost tagSuffix,
    o.address objectAddress,
    osl.name objectStatus,
    n.added nodeAdded,
    n.addedUserId nodeAddedUserId,
    n.addedUserName nodeAddedUserName,
    n.addedIp nodeAddedIp,
    n.changed nodeChanged,
    n.changedUserId nodeChangedUserId,
    n.changedUserName nodeChangedUserName,
    n.changedIp nodeChangedIp,
    q.added,
    q.addedUserId,
    q.addedUserName,
    q.addedIp,
    q.changed,
    q.changedUserId,
    q.changedUserName,
    q.changedIp
from moderationQueueT q
join nodeV n on q.nodeId = n.id
left join tagTL tl on n.id = tl.id and n.languageId = tl.languageId
left join objectT o on n.id = o.id
left join objectStatusTL osl on o.statusId = osl.id and n.languageId = osl.languageId;

-- utilities

CREATE VIEW zzzNodeWorksetV AS
select 
    connId,
    nodeId,
    isMain,
    isExact,
    isLinked,
    added
from zzzNodeWorksetT
where connId = connection_id();


create view zzzIntsV as
select 0 as n
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9;


create view zzzInts100V as
select (i1.n + i2.n * 10) n
from zzzIntsV i1
join zzzIntsV i2 on 1=1;


create view zzzInts1000V as
select (i1.n + i2.n * 10) n
from zzzIntsV i1
join zzzInts100V i2 on 1=1;


create view zzzInts10000V as
select (i1.n + i2.n * 100) n
from zzzInts100V i1
join zzzInts100V i2 on 1=1;


create view _maintenanceDropRoutines as
select 
    ROUTINE_SCHEMA db, 
    SPECIFIC_NAME name, 
    ROUTINE_TYPE type,
    concat('DROP ', ROUTINE_TYPE, ' ', ROUTINE_SCHEMA, '.', SPECIFIC_NAME, ';') stmt
from INFORMATION_SCHEMA.ROUTINES 
where ROUTINE_SCHEMA = database();


create view _maintenanceDropViews as
select 
    TABLE_SCHEMA db, 
    TABLE_NAME name, 
    concat('DROP VIEW ', TABLE_SCHEMA, '.', TABLE_NAME, ';') stmt
from INFORMATION_SCHEMA.VIEWS 
where TABLE_SCHEMA = database()
    and TABLE_NAME != '_maintenanceDropViews';


create view constDisplayedV as
select
    c.id,
    c.comment,
    c.dataType,
    c.sorter,
    c.valueString,
    c.valueInt,
    c.valueFloat,
    asl.languageId,
    asl.value title
from constT c
left join languageT l on l.isDefault = 'y'
left join actionStringTL asl on c.id = asl.id and asl.languageId = coalesce(user_CurrentLanguage(), l.id)
where c.isDisplayed = 'y';