create or replace view centile_auth_user.v_in_ext_user_centrex as select id,login,password from napi.ext_user_centrex;
create or replace view centile_auth_user.v_in_user as select id,site_id from napi.user;

create or replace view centile_auth_user.v_in_centile_user_account as select  *  from user_account_centile.user_account;

create or replace view centile_auth_user.v_out_user_account_alive as
SELECT
    cau_glob.id AS id,
    cau_glob.query_id AS query_id,
    cau_glob.cookie AS cookie,
    cau_glob.x_application AS x_application,
    tmp.centile_user_account_id,
    tmp.expired_at
FROM
    centile_auth_user.auth cau_glob
        INNER JOIN
    centile_auth_user.cron_task  glob_ct ON cau_glob.cron_task_id = glob_ct.id
        INNER JOIN
    (
        SELECT
             cau_ct.centile_user_account_id AS centile_user_account_id,
             MAX(cau_a.expired_at) AS expired_at
         FROM
             centile_auth_user.auth cau_a
         INNER JOIN
             centile_auth_user.cron_task cau_ct ON cau_a.cron_task_id = cau_ct.id
         GROUP BY
             centile_user_account_id
     ) tmp

    ON glob_ct.centile_user_account_id = tmp.centile_user_account_id AND cau_glob.expired_at = tmp.expired_at
WHERE (cau_glob.cookie != '' OR cau_glob.x_application != '') AND tmp.expired_at > NOW();



create or replace view centile_auth_user.v_btw_user_need_to_plan_first_time as
SELECT cau_v_cua.id AS centile_user_account_id
FROM centile_auth_user.v_in_centile_user_account cau_v_cua
WHERE cau_v_cua.id
NOT IN (
    SELECT cau_ct.centile_user_account_id
    FROM centile_auth_user.cron_task cau_ct
);



create or replace view centile_auth_user.v_btw_user_need_to_plan_refresh as
SELECT
    cau_v_uaa.id AS user_account_alive_id,
    cau_ct.centile_user_account_id AS centile_user_account_id,
    (cau_a.expired_at + INTERVAL -10 MINUTE) AS plan_next_at
FROM
   centile_auth_user.auth cau_a

INNER JOIN centile_auth_user.cron_task cau_ct ON cau_a.cron_task_id = cau_ct.id
INNER JOIN
    (SELECT MAX(`expired_at`) max_expired_at,`centile_user_account_id`
    FROM `auth`
    INNER JOIN cron_task ON `auth`.`cron_task_id` = cron_task.id
    GROUP BY centile_user_account_id)  max_cau_a
           ON cau_a.expired_at = max_cau_a.max_expired_at AND cau_ct.centile_user_account_id = max_cau_a.centile_user_account_id
    INNER JOIN centile_auth_user.v_out_user_account_alive cau_v_uaa ON cau_ct.centile_user_account_id = cau_v_uaa.centile_user_account_id
WHERE cau_ct.centile_user_account_id NOT IN (
    SELECT cau_ct.centile_user_account_id
    FROM centile_auth_user.cron_task cau_ct
    WHERE cau_ct.executed_at IS NULL
)
AND  NOW() >= (cau_a.expired_at + INTERVAL -15 MINUTE)
AND NOW() <= cau_a.expired_at  ;
