create or replace view zoho_auth.v_in_zoho_user_account as SELECT *
                                                           FROM user_account_zoho.user_account ;

create or replace view zoho_auth.v_in_location_account_user as SELECT *
                                                           FROM user_account_zoho.location_account_user ;

create or replace view zoho_auth.v_in_subscription as SELECT *
                                                               FROM subscription.subscription ;

create or replace view zoho_auth.v_in_zoho_integration as SELECT *
                                                               FROM zoho_integrate.integrate ;

-- v_out_zoho_user_account_in_waiting
-- l'utilisateurs zoho actifs, qui ont sont passés par le register et qui on été redirigés


-- v_zoho_user_account_autorised_to_auth
-- l'utilisateurs zoho actifs, qui ont sont passés par le register et qui on été redirigés
-- qui sont bien actif, avec les informations de connexion
-- on consiédere qui sont autorisé à d'authentifié

create or replace view zoho_auth.v_zoho_user_account_autorised_to_auth as SELECT
                                                                              `zoho_register`.`v_out_zoho_user_account_in_waiting`.`zoho_user_account_id` AS `zoho_user_account_id`,
                                                                              `zoho_register`.`v_out_zoho_user_account_in_waiting`.`user_come_back_at` AS `user_come_back_at`,
                                                                              `zoho_register`.`v_out_zoho_user_account_in_waiting`.`code` AS `code`,
                                                                              `zoho_register`.`v_out_zoho_user_account_in_waiting`.`location` AS `location`,
                                                                              `zoho_register`.`v_out_zoho_user_account_in_waiting`.`account_server` AS `account_server`
                                                                          FROM
                                                                              `zoho_register`.`v_out_zoho_user_account_in_waiting`
                                                                          WHERE
                                                                                  `zoho_register`.`v_out_zoho_user_account_in_waiting`.`zoho_user_account_id` IN(
                                                                                  SELECT
                                                                                      `zoho_auth`.`v_in_zoho_user_account`.`id`
                                                                                  FROM
                                                                                      `zoho_auth`.`v_in_zoho_user_account`
                                                                                  --un double check, les utilisateur en attente d'integration sont tous actif ( voir la vue v_out_zoho_user_account_in_waiting)
                                                                                  WHERE
                                                                                      (
                                                                                              `zoho_auth`.`v_in_zoho_user_account`.`is_active` = 'Y'
                                                                                          )
                                                                              );



<!------ old alive --->
create or replace view zoho_auth.v_zoho_user_account_alive as
SELECT
    `zoho_auth`.`zoho_account_xaccess_token`.`id` AS `id`,
    `zoho_auth`.`zoho_account_xaccess_token`.`zoho_account_auth_id` AS `zoho_account_auth_id`,
    `zoho_auth`.`zoho_account_auth`.`zoho_user_account_id` AS `zoho_user_account_id`,
    `zoho_auth`.`zoho_account_xaccess_token`.`access_token` AS `access_token`,
    `zoho_auth`.`zoho_account_xaccess_token`.`expired_at` AS `expired_at`
FROM
    `zoho_auth`.`zoho_account_xaccess_token`
        JOIN `zoho_auth`.`zoho_account_auth` ON
                `zoho_auth`.`zoho_account_xaccess_token`.`zoho_account_auth_id` = `zoho_auth`.`zoho_account_auth`.`id`  <!--AND `zoho_account_auth`.`refresh_token` NOT LIKE 'To_REFRESH' -->
        JOIN(
        SELECT
            `zoho_auth`.`zoho_account_xaccess_token`.`zoho_account_auth_id` AS `zoho_account_auth_id`,
            MAX(
                    `zoho_auth`.`zoho_account_xaccess_token`.`expired_at`
                ) AS `expired_at`
        FROM
            `zoho_auth`.`zoho_account_xaccess_token`
        GROUP BY
            `zoho_auth`.`zoho_account_xaccess_token`.`zoho_account_auth_id`
    ) `max_tmp`
            ON
                        `zoho_auth`.`zoho_account_xaccess_token`.`zoho_account_auth_id` = `max_tmp`.`zoho_account_auth_id` AND `zoho_auth`.`zoho_account_xaccess_token`.`expired_at` = `max_tmp`.`expired_at`
WHERE
        `zoho_auth`.`zoho_account_xaccess_token`.`expired_at` > NOW();
<!----- ------>


<!------ a tester --->
create or replace view zoho_auth.v_zoho_user_account_alive as
SELECT
    `a`.`id` AS `id`,
    `a`.`zoho_account_auth_id` AS `zoho_account_auth_id`,
    `b`.`zoho_user_account_id` AS `zoho_user_account_id`,
    `a`.`access_token` AS `access_token`,
    `a`.`expired_at` AS `expired_at`
FROM
    `zoho_auth`.`zoho_account_xaccess_token` `a`
        JOIN `zoho_auth`.`zoho_account_auth` `b` ON
            `a`.`zoho_account_auth_id` = `b`.`id`
        JOIN(
        SELECT
            `b`.`zoho_user_account_id` AS `zoho_user_account_id`,
            MAX(`a`.`expired_at`) AS `max_expired_at`
        FROM
            `zoho_auth`.`zoho_account_xaccess_token` `a`
                JOIN `zoho_auth`.`zoho_account_auth` `b` ON `a`.`zoho_account_auth_id` = `b`.`id`
        GROUP BY
            `b`.`zoho_user_account_id`
    ) `max_tmp` ON
                `b`.`zoho_user_account_id` = `max_tmp`.`zoho_user_account_id` AND
                `a`.`expired_at` = `max_tmp`.`max_expired_at`
WHERE
        `a`.`expired_at` > NOW()
ORDER BY
    `b`.`zoho_user_account_id` DESC, -- Pour trier par zoho_user_account_id
    `a`.`expired_at` DESC; -- Pour obtenir le dernier expired_at
<!----- ------>





create or replace view zoho_auth.v_btw_to_desactivate as SELECT `id` AS `id_zoho_account_xaccessToken`,`zoho_user_account_id`,`expired_at`
                                                         FROM zoho_auth.v_zoho_user_account_alive
                                                         WHERE zoho_user_account_id
                                                                   IN (
                                                                   SELECT id FROM zoho_auth.v_in_zoho_user_account WHERE is_active = "N"
                                                               )  ;


create or replace view zoho_auth.v_btw_to_remove_auth as SELECT * FROM `zoho_account_auth` WHERE `refresh_token` != 'To_REFRESH' AND `zoho_user_account_id` IN (SELECT
                                                                                                                                `zoho_auth`.`v_in_zoho_user_account`.`id`
                                                                                                                            FROM
                                                                                                                                `zoho_auth`.`v_in_zoho_user_account`
                                                                                                                            WHERE
                                                                                                                                (
                                                                                                                                        `zoho_auth`.`v_in_zoho_user_account`.`is_active` = 'N'
                                                                                                                                    ))  ;





-- v_zoho_user_account_autorised_to_auth
-- l'utilisateurs zoho actifs, qui ont sont passés par le register et qui on été redirigés
-- qui sont bien actif, avec les informations de connexion
-- on consiédere qui sont autorisé à d'authentifier

create or replace view zoho_auth.v_btw_zoho_user_account_first_time as

select zoho_user_account_id
FROM v_zoho_user_account_autorised_to_auth
-- les possibilité pour le first time
WHERE
    -- des compteur utilisateur zoho actif passés par le register et qui on été redirigés
   --  il ne s'est jamais authentifié, il ne posséde pas de refresh token
    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
      )
 -- OU des compteur utilisateur zoho actif passés par le register et qui on été redirigés
   -- ces comptes utilisateur ne doivent jamais posséder un refresh token actif
   -- => (si un compte posséde un refresh token, il est pas pris comme first time)
   OR v_zoho_user_account_autorised_to_auth.zoho_user_account_id

    NOT IN (
          -- Liste des comptes utilisateur qui posséde un refresh token actif (non désactivé)
          SELECT zoho_account_auth.zoho_user_account_id FROM zoho_account_auth WHERE `is_disabled` = "N"
      )
   --
   OR  v_zoho_user_account_autorised_to_auth.zoho_user_account_id IN
       (SELECT v.zoho_user_account_id FROM `v_zoho_user_account_autorised_to_auth` v
           LEFT JOIN zoho_account_auth z ON (v.code = z.code )
           WHERE z.code IS NULL );
;






create or replace view zoho_auth.v_zoho_user_account_soon_expired as
SELECT
    zoho_auth.v_zoho_user_account_alive.id AS zoho_account_xaccess_token_id,
    DATE_SUB(
            expired_at,
            INTERVAL 600 SECOND
    ) AS plan_next_at
FROM
    zoho_auth.v_zoho_user_account_alive
        INNER JOIN zoho_auth.zoho_account_auth A
                   ON
                           zoho_auth.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
        zoho_auth.zoho_account_xaccess_token T
            INNER JOIN zoho_auth.zoho_account_auth A
                       ON
                               T.zoho_account_auth_id = A.id
            INNER JOIN zoho_auth.v_in_zoho_user_account UA
                       ON
                               UA.id = A.zoho_user_account_id
            INNER JOIN zoho_auth.cron_task C
                       ON
                               C.zoho_user_account_id = UA.id
    WHERE
        C.executed_at IS NULL
);


// firsttime
// pas de zoho_account_auth


dev
// 1. nouvelle vue

// tous les auth  qui ne sont pas refrehs

// reactivation apres desactivation
// zoho_account_auth est présent
// actif => disabled = 'N'
// pas de token actif

    2. nouvelle commande pour relancer la connexion


///refresh v_zoho_user_account_soon_expired
// actif => disabled = 'N'
//  => 1 seul actif
//  => l'actif il posséde un token qui va être périmé
// on doit faire un refresh



// Renew
// il existe des zoho_account_auth
// ontrouve le seul actif => disabled = 'N'
//  => 1 seul actif
//  => mais qui n'a pas de token
//  => refait
