package Common::DB::Item::UserRecipients;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::DB::ItemCollection;
use Common::DB::Item;

use base 'Common::DB::Item';

use constant kDB => Common::DB::Item::kCommonDB;
use constant kTable => 'user';


sub GetAllRoyaltyShareUsersAndAdditionalRecipients {
    my ($class, $email) = @_;

    $email = $class->quote($email);

    my $sql = qq/
        SELECT *
        FROM (
            -- Main user section
            SELECT
                u.user_id                                                             AS user_id,
                CONCAT_WS(' ', u.first_name, u.last_name)                             AS user_name,
                u.email                                                               AS user_email,
                al.name                                                               AS user_type,
                IF(u.disabled, 'Inactive', 'Active')                                  AS user_status,
                ua.client_id                                                          AS user_client_id,
                CASE
                    WHEN ua.client_id = 0 AND c.client_id IS NULL THEN  '*.royaltyshare.com'  -- admin
                    WHEN ua.client_id != 0 AND c.client_id IS NULL THEN 'NOT FOUND'           -- not admin
                    ELSE CONCAT(c.client_name_clean, '.royaltyshare.com')
                END                                                                   AS user_client_url,
                ''                                                                    AS payee_id,
                ''                                                                    AS statement_recipient_id,
                ''                                                                    AS portal_invite_status,
                ''                                                                    AS date_confirmed
            FROM `user`            AS u
            INNER JOIN user_access AS ua ON u.user_id = ua.user_id
            LEFT JOIN access_level AS al ON ua.access_level = al.access_level_id
            LEFT JOIN client       AS c  ON ua.client_id = c.client_id
            WHERE u.email = $email
            UNION
            -- Main and additional payee recipients who already have a user account in the system
            SELECT
                u.user_id                                                             AS user_id,
                CONCAT_WS(' ', u.first_name, u.last_name)                             AS user_name,
                pp.invitee                                                            AS user_email,
                CONCAT('Portal (', IF(pp.payee_type = 1, 'Artist', 'Publisher'), ')') AS user_type,
                IF(u.disabled, 'Inactive', 'Active')                                  AS user_status,
                ua.client_id                                                          AS user_client_id,
                CASE
                    WHEN ua.client_id = 0 AND c.client_id IS NULL THEN  '*.royaltyshare.com'  -- admin
                    WHEN ua.client_id != 0 AND c.client_id IS NULL THEN 'NOT FOUND'           -- not admin
                    ELSE CONCAT(c.client_name_clean, '.royaltyshare.com')
                END                                                                   AS user_client_url,
                pp.payee_id                                                           AS payee_id,
                IF(pp.statement_recipient_id, pp.statement_recipient_id, '')          AS statement_recipient_id,
                CASE
                    WHEN pp.date_invited AND pp.date_confirmed THEN 'Confirmed'
                    WHEN pp.date_invited AND NOT pp.date_confirmed THEN 'Waiting'
                    ELSE ''
                END                                                                   AS portal_invite_status,
                pp.date_confirmed                                                     AS date_confirmed
            FROM `user`            AS u
            LEFT JOIN user_access  AS ua ON u.user_id = ua.user_id
            LEFT JOIN portal_payee AS pp ON u.user_id = pp.user_id
            LEFT JOIN client       AS c  ON pp.client_id = c.client_id
            WHERE pp.invitee  = $email
            UNION
            -- Newly added main or additional payee recipients who do not have a user account in the system
            SELECT
                ''                                                                    AS user_id,
                ''                                                                    AS user_name,
                pi.invitee                                                            AS user_email,
                CONCAT('Portal (', IF(pi.payee_type = 1, 'Artist', 'Publisher'), ')') AS user_type,
                ''                                                                    AS user_status,
                pi.client_id                                                          AS user_client_id,
                CASE
                    WHEN c.client_id IS NULL THEN  'NOT FOUND'  -- client has been deleted
                    ELSE CONCAT(c.client_name_clean, '.royaltyshare.com')
                END                                                                   AS user_client_url,
                pi.payee_id                                                           AS payee_id,
                IF(pi.statement_recipient_id, pi.statement_recipient_id, '')          AS statement_recipient_id,
                CASE
                    WHEN pi.date_invited AND pi.date_confirmed THEN 'Confirmed'
                    WHEN pi.date_invited AND NOT pi.date_confirmed THEN 'Waiting'
                    ELSE ''
                END                                                                   AS portal_invite_status,
                pi.date_confirmed                                                     AS date_confirmed
            FROM portal_invite AS pi
            LEFT JOIN client   AS c  ON pi.client_id = c.client_id
            WHERE pi.invitee  = $email AND NOT pi.date_confirmed
        ) AS t
        ORDER BY user_client_id, payee_id, statement_recipient_id;
    /;

    return $class->SUPER::GetAll($sql);
}

1;