

create or replace view centile_auth_admin.v_in_customer_admin as
SELECT cca_ca.`id`, cca_ca.`customer_id`,cca_ca.`community_name` ,cca_ca.`login`, cca_ca.`password`
FROM `centile_customer_admin`.`customer_admin` cca_ca;


create or replace view centile_auth_admin.v_in_subscription as
SELECT
    `subscription`.`subscription`.`id` AS `id`,
    `subscription`.`subscription`.`crm_provider_id` AS `crm_provider_id`,
    `subscription`.`subscription`.`phone_provider_id` AS `phone_provider_id`,
    `subscription`.`subscription`.`customer_id` AS `customer_id`
FROM
    `subscription`.`subscription`;

create or replace view centile_auth_admin.v_btw_customer_need_to_plan_first_time as
SELECT
    `centile_auth_admin`.`caa_v_s`.`id` AS `subscription_id`
FROM
    `centile_auth_admin`.`v_in_subscription` `caa_v_s`
WHERE
        `centile_auth_admin`.`caa_v_s`.`id` IN(
        SELECT
            `caa_ct_where`.`subscription_id`
        FROM
            `centile_auth_admin`.`cron_task` `caa_ct_where`
    ) IS FALSE;


create or replace view centile_auth_admin.v_out_customer_account_alive as
SELECT
    `caa_glob`.`id` AS `id`,
    `caa_glob`.`query_id` AS `query_id`,
    `caa_glob`.`cookie` AS `cookie`,
    `caa_glob`.`x_application` AS `x_application`,
    `tmp`.`subscription_id` AS `subscription_id`,
    `tmp`.`expired_at` AS `expired_at`
FROM
    (
        (
            `centile_auth_admin`.`auth` `caa_glob`
                JOIN `centile_auth_admin`.`cron_task` `glob_ct`
            ON
                (
                    (
                            `caa_glob`.`cron_task_id` = `glob_ct`.`id`
                        )
                    )
            )
            JOIN(
            SELECT
                `caa_ct`.`subscription_id` AS `subscription_id`,
                MAX(`caa_a`.`expired_at`) AS `expired_at`
            FROM
                (
                    `centile_auth_admin`.`auth` `caa_a`
                        JOIN `centile_auth_admin`.`cron_task` `caa_ct`
                    ON
                        (
                            (`caa_a`.`cron_task_id` = `caa_ct`.`id`)
                            )
                    )
            GROUP BY
                `caa_ct`.`subscription_id`
        ) `tmp`
        ON
            (
                (
                        (
                                `glob_ct`.`subscription_id` = `tmp`.`subscription_id`
                            ) AND(
                                `caa_glob`.`expired_at` = `tmp`.`expired_at`
                            )
                    )
                )
        )
WHERE  `tmp`.`expired_at` > NOW();






create or replace view centile_auth_admin.v_btw_customer_need_to_plan_refresh as
SELECT
    `centile_auth_admin`.`caa_v_caa`.`id` AS `customer_account_alive_id`,
    `caa_ct`.`subscription_id` AS `subscription_id`,
    (
            `caa_a`.`expired_at` + INTERVAL -(10) MINUTE
    ) AS `plan_next_at`
FROM
    (
    (
    (
    `centile_auth_admin`.`auth` `caa_a`
    JOIN `centile_auth_admin`.`cron_task` `caa_ct`
    ON
    (
    (`caa_a`.`cron_task_id` = `caa_ct`.`id`)
    )
    )
    JOIN(
    SELECT
    MAX(
    `centile_auth_admin`.`auth`.`expired_at`
    ) AS `max_expired_at`,
    `centile_auth_admin`.`cron_task`.`subscription_id` AS `subscription_id`
    FROM
    (
    `centile_auth_admin`.`auth`
    JOIN `centile_auth_admin`.`cron_task` ON
    (
    (
    `centile_auth_admin`.`auth`.`cron_task_id` = `centile_auth_admin`.`cron_task`.`id`
    )
    )
    )
    GROUP BY
    `centile_auth_admin`.`cron_task`.`subscription_id`
    ) `max_caa_a`
    ON
    (
    (
    (
    `caa_a`.`expired_at` = `max_caa_a`.`max_expired_at`
    ) AND(
    `caa_ct`.`subscription_id` = `max_caa_a`.`subscription_id`
    )
    )
    )
    )
    JOIN `centile_auth_admin`.`v_out_customer_account_alive` `caa_v_caa`
    ON
    (
    (
    `caa_ct`.`subscription_id` = `centile_auth_admin`.`caa_v_caa`.`subscription_id`
    )
    )
    )
WHERE
    (
    `caa_ct`.`subscription_id` IN(
    SELECT
    `caa_ct_where`.`subscription_id`
    FROM
    `centile_auth_admin`.`cron_task` `caa_ct_where`
    WHERE
    (
    `caa_ct_where`.`executed_at` IS NULL
    )
    ) IS FALSE AND(
    NOW() >=(
    `caa_a`.`expired_at` + INTERVAL -(15) MINUTE
    )) AND(NOW() <= `caa_a`.`expired_at`));
