create or replace view centile_daemon_phone.v_in_customer_account_alive as
select `subscription_id` as `id`, `query_id`,`cookie`,`x_application`,`expired_at` from centile_auth_admin.v_out_customer_account_alive;

create or replace view centile_daemon_phone.v_in_extension_user_centrex as
select `id`,`user_id`, `number`,`parent` from napi.ext_extension_user_centrex;

create or replace view centile_daemon_phone.v_in_customer_admin as
select * from centile_customer_admin.customer_admin;

create or replace view centile_daemon_phone.v_in_subscription as
select * from subscription.subscription;

create or replace view centile_daemon_phone.v_in_sda as
select * from napi.sda;

create or replace view centile_daemon_phone.v_in_sda as
select * from napi.sda;

create or replace view centile_daemon_phone.v_btw_user_daemon_first_time as
SELECT centile_daemon_phone.v_in_customer_account_alive.id AS customer_account_alive_id
FROM centile_daemon_phone.v_in_customer_account_alive
WHERE centile_daemon_phone.v_in_customer_account_alive.id NOT IN (
    SELECT cron_task.customer_account_alive_id
    FROM centile_daemon_phone.cron_task
);



create or replace view centile_daemon_phone.v_btw_daemon_need_to_plan_refresh as
SELECT
    centile_daemon_phone.user_daemon.id AS user_deamon_alive_id,
    centile_daemon_phone.cron_task.customer_account_alive_id AS customer_account_alive_id,
    (centile_daemon_phone.user_daemon.expirated_at + INTERVAL - 30 MINUTE) AS plan_next_at
FROM
    centile_daemon_phone.user_daemon

        INNER JOIN centile_daemon_phone.cron_task
                   ON centile_daemon_phone.user_daemon.cron_task_id = centile_daemon_phone.cron_task.id

        INNER JOIN (
        SELECT MAX(cdp_us_in.`expirated_at`) max_expirated_at, cdp_us_in.`customer_account_alive_id`
        FROM centile_daemon_phone.user_daemon cdp_us_in
                 INNER JOIN centile_daemon_phone.cron_task cdp_ct_in
                            ON cdp_us_in.`cron_task_id` = cdp_ct_in.id
        GROUP BY cdp_us_in.customer_account_alive_id
    )  max_cdp_ud
                   ON centile_daemon_phone.user_daemon.expirated_at = max_cdp_ud.max_expirated_at AND centile_daemon_phone.cron_task.customer_account_alive_id = max_cdp_ud.customer_account_alive_id

WHERE centile_daemon_phone.cron_task.customer_account_alive_id NOT IN (
    SELECT centile_daemon_phone.cron_task.customer_account_alive_id
    FROM centile_daemon_phone.cron_task
    WHERE centile_daemon_phone.cron_task.executed_at IS NULL
)
  AND  NOW() >= (centile_daemon_phone.user_daemon.expirated_at + INTERVAL -35 MINUTE)
  AND NOW() <= centile_daemon_phone.user_daemon.expirated_at  ;





