-- SERVER con la loro etichetta ben fatta

create view v_server(i_server_id, t_name, b_up, r_update, i_ping, i_max_players, i_players, t_ip, t_port, t_xfire, t_game, i_user_admin_id, t_admin, i_group_id, t_clan, b_private)

as

select s.i_server_id,

    case

        when q.t_label is not null then q.t_label

        when s.t_label is not null then s.t_label

        else ''

    end || ' ' ||

    case

        when s.t_qname is not null then s.t_qname

        else s.t_name

    end,

    s.b_up,

    s.i_ping,

    s.i_max_players,

    s.i_players,

    s.t_ip,

    s.t_port,

    s.t_xfire,

    s.t_game,

    s.i_user_admin_id,

    u.t_login,

    s.i_group_id,

    g.t_name,

    s.b_private

from t_a_server s

--    inner join t_e_param p on (p.t_param = 'SV_IMG_PATH')

-- where p.t_param = 'SV_IMG_PATH';

--    and s.r_update is not null;

-- JUNK FILES

create view v_junk_files(i_file_id)

as

select f.i_file_id

from t_a_file f

    left join t_a_user u on (f.i_file_id = u.i_avatar_file_id)

    left join t_s_post_attach p on (p.i_file_id = f.i_file_id)

where u.i_avatar_file_id is null and p.i_file_id is null;

-- IP bannati per utente

create view v_banned_ip(i_user_id, s_ip, b_banned)

as

select ip.i_user_id, ip.s_ip, case when b.i_banned_ip_id is null then '0' else '1' end

from (select distinct i_user_id, s_ip from t_s_user_login where s_ip is not null UNION select distinct i_user_id, s_ip from t_s_post where s_ip is not null) ip (i_user_id, s_ip)

left join t_a_banned_ip b on ip.s_ip = b.s_ip;

-- DIRITTI SEZIONE

create view v_user_group(i_user_group_id, i_group_id, i_user_id, t_login, t_group, i_avatar_file_id, b_mod,

    t_email, t_cnt_skype, t_cnt_xfire, t_cnt_icq, t_cnt_msn, t_cnt_irc,

    t_vis_skype, t_vis_xfire, t_vis_icq, t_vis_msn, t_vis_irc, t_vis_email, d_join)

as

select mn.i_user_group_id, g.i_group_id, u.i_user_id, u.t_login, g.t_name, u.i_avatar_file_id, mn.b_mod,

    u.t_email, u.t_cnt_skype, u.t_cnt_xfire, u.t_cnt_icq, u.t_cnt_msn, u.t_cnt_irc,

    u.t_vis_skype, u.t_vis_xfire, u.t_vis_icq, u.t_vis_msn, u.t_vis_irc, u.t_vis_email, u.d_join

from t_p_user_group mn

   inner join t_a_user u on (mn.i_user_id = u.i_user_id)

    inner join t_a_group g on (mn.i_group_id = g.i_group_id)

;

create view v_group_rights (i_group_id, i_section_id, b_r, b_w, b_rw, b_d, s_section, t_description)

AS

select mn.i_group_id,s.i_section_id, mn.b_r, mn.b_w, mn.b_rw, mn.b_d,

    s.s_name AS s_section, s.t_description

from t_p_group_section mn

    inner join t_a_section s on (mn.i_section_id = s.i_section_id)

;

create view v_group_user_rights (i_user_id, i_section_id, s_section, b_r, b_w, b_rw, b_d, t_tipo)

AS

select p.i_user_id, s.i_section_id, sz.s_name AS s_section,

    case when sum(cast(s.b_r as integer)) > 0 then '1' else '0' end AS b_r,

    case when sum(cast(s.b_w as integer)) > 0 then '1' else '0' end AS b_w,

    case when sum(cast(s.b_rw as integer)) > 0 then '1' else '0' end AS b_rw,

    case when sum(cast(s.b_d as integer)) > 0 then '1' else '0' end AS b_d,

    'G' AS t_tipo

from t_p_group_section S

    inner join t_p_user_group P on (s.i_group_id = p.i_group_id)

    inner join t_a_section SZ on (s.i_section_id = sz.i_section_id)

group by s.i_section_id,p. i_user_id, sz.s_name

union all

select u.i_user_id, s.i_section_id, s.s_name AS s_name,

    '0', '0', '0', '0', 'G'

from t_a_user u, t_a_section s

where u.i_user_id not in (select i_user_id from t_p_user_group)

;

create view v_user_rights (i_user_id, i_section_id, s_section, t_description, b_r, b_w, b_rw, b_d, t_tipo)

AS

select g.i_user_id, g.i_section_id, s.s_name AS s_section, s.t_description,

    case when u.b_r is null then g.b_r else u.b_r end AS b_r,

    case when u.b_w is null then g.b_w else u.b_w end AS b_w,

    case when u.b_rw is null then g.b_rw else u.b_rw end AS b_rw,

    case when u.b_d is null then g.b_d else u.b_d end AS b_d,

    case when u.i_user_id is null then g.t_tipo else 'U' end as t_tipo

from v_group_user_rights G

    left join t_p_user_section U on (g.i_user_id = u.i_user_id and g.i_section_id = u.i_section_id)

    inner join t_a_section S on (g.i_section_id = s.i_section_id)

;

-- DIRITTI FORUM
create view v_group_forum_rights (i_group_id, i_parent_forum_id, i_forum_id, i_order, t_name, b_r, b_w, b_rw, b_custom)

AS

select mn.i_group_id, f.i_parent_forum_id, f.i_forum_id, f.i_order, f.t_name,

    mn.b_R,

    mn.b_W,

    mn.b_RW,

    case when f.t_custom_home is null then '0' else '1' end

from t_p_group_forum_access mn

    inner join t_a_forum f on (mn.i_forum_id = f.i_forum_id)

;

/* per ogni utente, solo quelli ereditati dai gruppi, 0 se in nessun gruppo */

create view v_user_group_forum_rights(i_user_id, i_parent_forum_id, i_forum_id, i_order, t_name, b_r, b_w, b_rw, b_custom, t_tipo)

AS

select p.i_user_id, f.i_parent_forum_id, s.i_forum_id, f.i_order, f.t_name,

    case

        when sum(s.b_r) > 0 then '1'

        else '0'

    end AS b_r,

    case

        when sum(s.b_w) > 0 then '1'

        else '0'

    end AS b_w,

    case

        when sum(s.b_rw) > 0 then '1'

        else '0'

    end AS b_rw,

    case when f.t_custom_home is null then '0' else '1' end,

    'G' as t_tipo

from t_p_group_forum_access S

    inner join t_p_user_group P on (s.i_group_id = p.i_group_id)

    inner join t_a_forum F on (s.i_forum_id = f.i_forum_id)

group by p.i_user_id, f.i_parent_forum_id, s.i_forum_id, f.i_order, f.t_name, f.t_custom_home

union all

select u.i_user_id, f.i_parent_forum_id, f.i_forum_id, f.i_order, f.t_name,

    '0', '0', '0', case when f.t_custom_home is null then '0' else '1' end, 'G'

from t_a_user u, t_a_forum f

where u.i_user_id not in (select i_user_id from t_p_user_group)

;

/*

    per ogni utente, quelli che anche lui sovrascrive

    queste mi danno la base per capire cosa posso farci dentro,

    e mi danno il senso della visione ad albero

*/

create view v_user_forum_rights(i_user_id, i_parent_forum_id, i_forum_id, i_order, t_name, b_r, b_w, b_rw, b_mod, b_custom, t_tipo)

AS

select g.i_user_id, s.i_parent_forum_id, g.i_forum_id, s.i_order, s.t_name,

    case

        when u.b_r is null then g.b_r

        else u.b_r

    end as b_r,

    case

        when u.b_w is null then g.b_w

        else u.b_w

    end as b_w,

    case

        when u.b_rw is null then g.b_rw

        else u.b_rw

    end as b_rw,

    case

        when u.b_mod is null then '0'

        else u.b_mod

    end as b_mod,

    g.b_custom,

    case

        when u.i_user_id is null then g.t_tipo

        else 'U'

    end as t_tipo

from v_user_group_forum_rights G

    inner join t_a_forum S on (g.i_forum_id = s.i_forum_id)

    left join t_p_user_forum_access U on (g.i_user_id = u.i_user_id and g.i_forum_id = u.i_forum_id)

;

/*

    con le caratteristiche del forum, mi dice cosa posso farci dentro

    e' la base per i diritti dei topic

    con quella sopra ho l'albero, poi su ognuno con questa capisco

    se posso entrarci, postare, moderare

    in pratica qua considero quello che mi dice il forum (post si no, join si no)

    e la lettura in particolare assume un significato diverso. sopra e'

    "posso vedere il forum ?" qua e' "posso leggerci i topic ?"

    il forum richiede il join ?

    hai joinato ?

    lettura:

        se richiede join e sei fuori, quello che hai in moderazione (quindi se sono moderatore leggo, se no devo entrare)

        altrimenti quello che hai in lettura (anche se non sono permessi i post)

    scrittura:

        se non sono permessi i post, quello che hai in moderazione (quindi se moderatore posto, se no no)

        se richiede join e sei fuori, quello che hai in moderazione (quindi se sono moderatore scrivo, se no devo entrare)

        altrimenti quello che hai in scrittura

    moderazione:

        paro paro

*/

create view v_user_forum_topic_rights(i_user_id, i_parent_forum_id, i_forum_id, i_order, t_name, t_description, b_can_join, b_joined, b_r, b_w, b_rw, b_mod, b_custom, t_tipo)

as

select u.i_user_id, f.i_parent_forum_id, f.i_forum_id, f.i_order, f.t_name, f.t_description, f.b_can_join,

    case

        when f.b_can_join = '1' and j.i_forum_id is not null then '1'

        else  '0'

    end as b_joined,

    case

        when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod

        else u.b_r

    end as b_r,

    case

        when f.b_allow_post = '0' then u.b_mod

        when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod

        else u.b_w

    end as b_w,

    case

        when f.b_allow_post = '0' then u.b_mod

        when f.b_can_join = '1' and j.i_forum_id is null then u.b_mod

        else u.b_rw

    end as b_rw,

    u.b_mod ,

    u.b_custom,

    'F' as t_tipo

from v_user_forum_rights u

    inner join t_a_forum f on (u.i_forum_id = f.i_forum_id)

    left join t_p_user_forum_join j on (f.i_forum_id = j.i_forum_id and u.i_user_id = j.i_user_id)

;

/*

    diritti sul topic:

    prendo i diritti che ho sui topic di questo forum (vista sopra)

    e li incrocio coi due flag del topic e i diritti personali

    sul topic.

    lettura / scrittura:

        se ho un diritto personale, prendo quello

        se sono moderatore, prendo il diritto sul forum

        altrimenti prendo il minimo fra il diritto sul topic e quello sul forum

*/

create view v_user_topic_rights(i_user_id, i_topic_id, i_forum_id, b_r, b_rw, b_mod, t_tipo)

as

select uf.i_user_id, t.i_topic_id, t.i_forum_id,

    case when ut.b_r is null

        then case

                    when uf.b_mod = '1' or t.b_r = '1' then uf.b_r

                    else t.b_r

                end

        else ut.b_r

    end as b_r,

    case when ut.b_rw is null

        then case

                    when uf.b_mod = '1' or t.b_rw = '1' then uf.b_rw

                    else t.b_rw

                end

        else ut.b_rw

    end as b_rw,

    case when ut.b_mod is null then uf.b_mod else ut.b_mod end as b_mod,

    case when ut.i_user_topic_access_id is null then uf.t_tipo else 'U' end as t_tipo

from t_s_topic t

    inner join v_user_forum_topic_rights uf on(t.i_forum_id = uf.i_forum_id)

    left join t_p_user_topic_access ut on (t.i_topic_id = ut.i_topic_id and uf.i_user_id = ut.i_user_id)

;

-- STILI

create view v_user_topic_style(i_forum_id, i_user_id, t_style, t_style_type, t_class)

as

select uf.i_forum_id, uf.i_user_id, fs.t_topic_style, s.t_style_type, s.t_class

from v_user_forum_topic_rights uf

    inner join t_a_forum f on (uf.i_forum_id = f.i_forum_id)

    inner join t_p_forum_topic_style fs on (fs.t_forum_style = f.t_style)

    inner join t_e_style s on (fs.t_topic_style = s.t_style)

where

    (fs.b_mod = '1' and uf.b_mod = '1')

    or

    (fs.b_w = '1' and uf.b_w = '1')

;

create view v_mod_post_style(i_forum_id, i_user_id, t_style, t_style_type, t_class)

as

select distinct uf.i_forum_id, uf.i_user_id, st.t_post_style, sp.t_style_type, sp.t_class

from v_user_forum_topic_rights uf

    inner join t_a_forum f on (uf.i_forum_id = f.i_forum_id)

    inner join t_p_forum_topic_style fs on (fs.t_forum_style = f.t_style)

    inner join t_e_style st on (fs.t_topic_style = st.t_style)

    inner join t_e_style sp on (st.t_post_style = sp.t_style)

where

    uf.b_mod = '1'

;

/*

    per ogni gruppo ritorna tutti i team, il gioco, gli utenti che hanno player

    per quel gioco, un flag che dice se sono nel team, il loro nick

    i_group_id, i_user_id, i_player_id, i_team_id

    team(game, tag, tag || group(name), wa)

    player(in team, nick, nick || user(login),

        team(tag || group(name)) || (nick || user(login))

        )

    user(login, name, surname)

*/

create view v_group_players (i_group_id, i_user_id, i_player_id, i_team_id, i_team_player_id,

    t_game, t_tag, t_autotag,

    t_login, t_surname, t_name,

    b_active, b_in_team, t_nick, t_autonick,

    t_player, b_war_arranger)

AS

select gg.i_group_id, gg.i_user_id, p.i_player_id, t.i_team_id, tp.i_team_player_id,

    t.t_game, t.t_tag, case when t.t_tag is null then g.t_name else t.t_tag end as t_autotag,

    u.t_login, u.t_surname, u.t_name,

    p.b_active, case when tp.i_team_player_id is null then '0' else '1' end as b_in_team,

    tp.t_nick, case when tp.t_nick is null then u.t_login else tp.t_nick end as t_autonick,

    case when t_tag is null then g.t_name else t.t_tag end || case when tp.t_nick is null then u.t_login else tp.t_nick end as t_player,

    case when t.i_war_arranger_id = u.i_user_id then '1' else '0' end as b_war_arranger

from t_a_group g

    inner join t_a_team t on (t.i_group_id = g.i_group_id)

    inner join t_p_player p on (t.t_game = p.t_game)

    inner join t_p_user_group gg on (gg.i_group_id = g.i_group_id and p.i_user_id = gg.i_user_id)

    inner join t_a_user u on (gg.i_user_id = u.i_user_id)

    left join t_p_team_player tp on (t.i_team_id = tp.i_team_id and p.i_player_id = tp.i_player_id)

;

create view v_player_nick(i_match_player_id, i_team_player_id, i_match_id, i_player_id, i_team_id, i_user_id, t_match_player_status,

    t_tag, t_autotag, t_nick, t_autonick, t_player)

AS

select mp.i_match_player_id, tp.i_team_player_id,

    m.i_match_id, p.i_player_id, t.i_team_id, u.i_user_id,

    mp.t_match_player_status,

    t.t_tag, case when t.t_tag is null then g.t_name else t.t_tag end as t_autotag,

    tp.t_nick, case when tp.t_nick is null then u.t_login else tp.t_nick end as t_autonick,

    case when t_tag is null then g.t_name else t.t_tag end || case when tp.t_nick is null then u.t_login else tp.t_nick end as t_player

from t_a_match m

    inner join t_p_match_player mp on (mp.i_match_id = m.i_match_id)

    inner join t_p_team_player tp on (m.i_team_id = tp.i_team_id and mp.i_player_id = tp.i_player_id)

    inner join t_a_team t on (m.i_team_id = t.i_team_id)

    inner join t_p_player p on (tp.i_player_id = p.i_player_id)

    inner join t_a_user u on (p.i_user_id = u.i_user_id)

    inner join t_a_group g on (t.i_group_id = g.i_group_id)

;

create view v_cup_teams(i_cup_id, t_cup, t_game, i_team_id, t_tag)

as

select m.i_match_cup_id, m.t_description, m.t_game, t.i_team_id, case when t.t_tag is null then g.t_name else t.t_tag end as t_autotag

from t_a_group g

   inner join t_a_team t on (g.i_group_id = t.i_group_id)

   inner join t_r_match_cup m on (t.t_game = m.t_game)

where exists(select * from t_p_team_player p

        inner join t_p_player gg on (p.i_player_id = gg.i_player_id and gg.b_active='1')

        where (p.i_team_id = t.i_team_id))

;

create view v_game_match(t_game, i_match_id, i_cup_id, t_cup, i_team_id, t_tag)

as

select mc.t_game, m.i_match_id, mc.i_match_cup_id, mc.t_description, t.i_team_id, case when t.t_tag is null then g.t_name else t.t_tag end as t_autotag

from t_a_match m

    inner join t_r_match_cup mc on (m.i_match_cup_id = mc.i_match_cup_id)

     inner join t_a_team t on (m.i_team_id = t.i_team_id)

    inner join t_a_group g on t.i_group_id = g.i_group_id;

create view v_team_tag(i_group_id, i_team_id, t_tag, t_autotag, t_game)

as

select g.i_group_id, t.i_team_id, t.t_tag, case when t.t_tag is null then g.t_name else t.t_tag end as t_autotag, t.t_game

from t_a_group g inner join t_a_team t on (g.i_group_id = t.i_group_id);

-- TOPIC

CREATE VIEW v_topic(i_forum_id, i_topic_id, t_style, i_user_id, t_guest, r_insert, i_post_count, i_hit_count, i_last_post_id, r_last_post, t_title, t_description, d_from, d_to, i_order, i_style_order, t_moved_to)

as

SELECT t.i_forum_id, t.i_topic_id, t.t_style, t.i_user_id, t.t_guest, t.r_insert, t.i_post_count, t.i_hit_count, t.i_last_post_id, t.r_last_post, t.t_title, t.t_description, t.d_from, t.d_to, t.i_order, s.i_order, t.t_moved_to

FROM t_s_topic t

    INNER JOIN t_e_style st on t.t_style = st.t_style

    INNER JOIN t_e_style_type s on st.t_style_type = s.t_style_type;

create view v_user_cloud_posts(i_user_id, t_login, i_post_count, i_rank)

as

select i_user_id, t_login, i_post_count,
    cast(ceiling(
        10 * (

            cast(i_post_count as double precision) /
            cast((select max(i_post_count) from t_a_user) as double precision)

        )
    ) as integer)
from t_a_user

where i_post_count > 0 and i_user_id not in (select t_value from t_e_param where t_param in ('U_GOD', 'U_GUEST'))
order by t_login asc;

create view v_user_cloud_join(i_user_id, t_login, d_join, i_rank)

as

select i_user_id, t_login, d_join,
    10 - cast(ceiling(
        10 * (
            cast( (d_join - (select min(d_join) from t_a_user)) as double precision) /
            cast( (select max(d_join) - min(d_join) from t_a_user) as double precision)
        )
    ) as integer)
from t_a_user

where i_user_id not in (select t_value from t_e_param where t_param in ('U_GOD', 'U_GUEST'))

order by t_login asc;