create or replace view leadbridge_zoho_auth_user.v_in_zoho_user_account as SELECT *
                                                           FROM leadbridge_user_account_zoho.user_account ;




create or replace view leadbridge_zoho_auth_user.v_zoho_user_account_autorised_to_auth as SELECT
                                                                              `leadbridge_zoho_register`.`v_out_zoho_user_account_in_waiting`.`zoho_user_account_id` AS `zoho_user_account_id`,
                                                                              `leadbridge_zoho_register`.`v_out_zoho_user_account_in_waiting`.`user_come_back_at` AS `user_come_back_at`,
                                                                              `leadbridge_zoho_register`.`v_out_zoho_user_account_in_waiting`.`code` AS `code`,
                                                                              `leadbridge_zoho_register`.`v_out_zoho_user_account_in_waiting`.`location` AS `location`,
                                                                              `leadbridge_zoho_register`.`v_out_zoho_user_account_in_waiting`.`account_server` AS `account_server`
                                                                          FROM
                                                                              `leadbridge_zoho_register`.`v_out_zoho_user_account_in_waiting`
                                                                          WHERE
                                                                                  `leadbridge_zoho_register`.`v_out_zoho_user_account_in_waiting`.`zoho_user_account_id` IN(
                                                                                  SELECT
                                                                                      `leadbridge_zoho_auth_user`.`v_in_zoho_user_account`.`id`
                                                                                  FROM
                                                                                      `leadbridge_zoho_auth_user`.`v_in_zoho_user_account`

                                                                              );




create or replace view leadbridge_zoho_auth_user.v_zoho_user_account_alive as
SELECT
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`id` AS `id`,
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`zoho_account_auth_id` AS `zoho_account_auth_id`,
    `leadbridge_zoho_auth_user`.`zoho_account_auth`.`zoho_user_account_id` AS `zoho_user_account_id`,
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`access_token` AS `access_token`,
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`expired_at` AS `expired_at`
FROM
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`
        JOIN `leadbridge_zoho_auth_user`.`zoho_account_auth` ON
                `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`zoho_account_auth_id` = `leadbridge_zoho_auth_user`.`zoho_account_auth`.`id`
    JOIN(
    SELECT
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`zoho_account_auth_id` AS `zoho_account_auth_id`,
    MAX(
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`expired_at`
    ) AS `expired_at`
    FROM
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`
    GROUP BY
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`zoho_account_auth_id`
    ) `max_tmp`
ON
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`zoho_account_auth_id` = `max_tmp`.`zoho_account_auth_id` AND `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`expired_at` = `max_tmp`.`expired_at`
WHERE
    `leadbridge_zoho_auth_user`.`zoho_account_xaccess_token`.`expired_at` > NOW();





create or replace view leadbridge_zoho_auth_user.v_btw_to_desactivate as SELECT `id` AS `id_zoho_account_xaccessToken`,`zoho_user_account_id`,`expired_at`
                                                         FROM leadbridge_zoho_auth_user.v_zoho_user_account_alive
                                                         WHERE zoho_user_account_id
                                                                   IN (
                                                                   SELECT id FROM leadbridge_zoho_auth_user.v_in_zoho_user_account
                                                               )  ;



create or replace view leadbridge_zoho_auth_user.v_btw_zoho_user_account_first_time as

select zoho_user_account_id
FROM v_zoho_user_account_autorised_to_auth
WHERE v_zoho_user_account_autorised_to_auth.zoho_user_account_id
    NOT IN (
          SELECT zoho_account_auth.zoho_user_account_id FROM zoho_account_auth
      )

   OR v_zoho_user_account_autorised_to_auth.zoho_user_account_id
    NOT IN (
          SELECT zoho_account_auth.zoho_user_account_id FROM zoho_account_auth WHERE `is_disabled` = "N"
      )
;







create or replace view leadbridge_zoho_auth_user.v_zoho_user_account_soon_expired as
SELECT
    leadbridge_zoho_auth_user.v_zoho_user_account_alive.id AS zoho_account_xaccess_token_id,
    DATE_SUB(
            expired_at,
            INTERVAL 600 SECOND
    ) AS plan_next_at
FROM
    leadbridge_zoho_auth_user.v_zoho_user_account_alive
        INNER JOIN leadbridge_zoho_auth_user.zoho_account_auth A
                   ON
                           leadbridge_zoho_auth_user.v_zoho_user_account_alive.zoho_account_auth_id = A.id
WHERE
        TIMESTAMPDIFF(SECOND, NOW(), expired_at) <= 900 AND TIMESTAMPDIFF(SECOND, NOW(), expired_at) > 0 AND A.zoho_user_account_id NOT IN(
    SELECT
        C.zoho_user_account_id
    FROM
        leadbridge_zoho_auth_user.zoho_account_xaccess_token T
            INNER JOIN leadbridge_zoho_auth_user.zoho_account_auth A
                       ON
                               T.zoho_account_auth_id = A.id
            INNER JOIN leadbridge_zoho_auth_user.v_in_zoho_user_account UA
                       ON
                               UA.id = A.zoho_user_account_id
            INNER JOIN leadbridge_zoho_auth_user.cron_task C
                       ON
                               C.zoho_user_account_id = UA.id
    WHERE
        C.executed_at IS NULL
);