<?php
/**
 * Class represents records from table email_templates
 * {autogenerated}
 * @property int $email_template_id PK
 * @property int|null $email_template_layout_id FK email_template_layout
 * @property string $name
 * @property string $lang
 * @property string $format 'text'|'html'|'multipart'
 * @property string $subject
 * @property string $txt
 * @property string|null $plain_txt
 * @property string|null $attachments
 * @property int|null $attach_pdf_invoice
 * @property int|null $recurring
 * @property int|null $cancelled
 * @property int|null $day
 * @property string|null $payment_type 'any'|'first'|'second'
 * @property string|null $days
 * @property string|null $paysys_ids
 * @property string|null $conditions
 * @property string|null $not_conditions
 * @property int|null $not_conditions_expired
 * @property int|null $not_conditions_future
 * @property int $recipient_user
 * @property int $recipient_aff
 * @property int $recipient_subusers_parent
 * @property int $recipient_admin
 * @property string|null $recipient_emails
 * @property string|null $bcc
 * @property int|null $reply_to
 * @property string|null $recipient_admins
 * @property string|null $newsletter_lists
 * @property int|null $template_id
 * {/autogenerated}
 *
 * @package Am_Mail_Template
 */
class EmailTemplate extends ResourceAbstract
{
    const AUTORESPONDER  = 'autoresponder';
    const EXPIRE = 'expire';
    const PRODUCTWELCOME = 'productwelcome';
    const PAYMENT = 'payment';
    const SCHEDULE_EMAIL = 'schedule-email';
    const PENDING_TO_USER = 'pending_to_user';
    const PENDING_TO_ADMIN = 'pending_to_admin';

    const ATTACHMENT_FILE_PREFIX = 'emailtemplate';
    const ATTACHMENT_AUTORESPONDER_EXPIRE_FILE_PREFIX = 'email-messages';
    const ATTACHMENT_PENDING_FILE_PREFIX = 'email-pending';

    const FORMAT_TEXT = 'text';
    const FORMAT_HTML = 'html';
    const FORMAT_MULTIPART = 'multipart';

    const PAYMENT_TYPE_ANY = 'any';
    const PAYMENT_TYPE_FIRST = 'first';
    const PAYMENT_TYPE_SECOND = 'second';

    public function toRow()
    {
        $ret = parent::toRow();
        if (!isset($ret['day']) || ($ret['day']==='')) $ret['day'] = null;
        return $ret;
    }

    public function getLayout()
    {
        if (!$this->email_template_layout_id) return null;
        $layout = $this->getDi()->emailTemplateLayoutTable->load($this->email_template_layout_id, false);
        if (!$layout) return null;

        return $layout->layout;
    }

    public function getLinkTitle()
    {
        return null;
    }

    /**
     * Check if user has no matches for $this->not_conditions
     * not conditions field format:
     *  comma delimited: c1 - category#1, p22 - product#22, c-1 - ANY PRODUCT
     * @param User $user
     * @return boolean true if ok (no matching conditions found)
     */
    public function checkNotConditions(User $user, $data = [])
    {
        $ret = $this->_checkNotConditions($user);
        return $ret && $this->getDi()->hook->filter($ret,
                Am_Event::EMAIL_TEMPLATE_CHECK_CONDITIONS,
                ['template' => $this, 'user' => $user, 'data' => $data]);
    }

    protected function _checkNotConditions(User $user)
    {
        $pids = [];
        $catp = null;
        foreach (array_filter(explode(',', $this->not_conditions ?? '')) as $s)
        {
            if ($s == 'c-1')
            {
                if ($this->not_conditions_future && $user->getFutureProductIds())
                    return false;
                if (!$this->not_conditions_expired)
                    return $user->status != User::STATUS_ACTIVE;
                else
                    return $user->status == User::STATUS_PENDING;
            } elseif ($s[0] == 'p') {
                $pids[] = substr($s, 1);
            } elseif ($s[0] == 'c') {
                if (!$catp)
                    $catp = $this->getDi()->productCategoryTable->getCategoryProducts(true);
                if (!empty($catp[substr($s, 1)]))
                    $pids = array_merge($pids, $catp[substr($s, 1)]);
            }
        }
        if (!$pids) return true;
        $userPids = $user->getActiveProductIds();
        if ($this->not_conditions_expired)
            $userPids = array_merge($userPids, $user->getExpiredProductIds());
        if ($this->not_conditions_future)
            $userPids = array_merge($userPids, $user->getFutureProductIds());

        return !$pids || !array_intersect($pids, $userPids);
    }

    function delete()
    {
        foreach ($this->getTable()->findByTemplateId($this->pk()) as $t) {
            $t->delete();
        }
        return parent::delete();
    }
}

/**
 * @package Am_Mail_Template
 * @extends ResourceAbstractTable<EmailTemplate>
 * @method int countByName($value)
 * @method EmailTemplate[] findByName($value, $start = null, $count = null, $orderBy = null)
 * @method EmailTemplate[] findByTemplateId($value, $start = null, $count = null, $orderBy = null)
 * @method EmailTemplate|null findFirstByName($value)
 */
class EmailTemplateTable extends ResourceAbstractTable
{
    protected $_key = 'email_template_id';
    protected $_table = '?_email_template';
    protected $_recordClass = 'EmailTemplate';
    public $_checkUnique = ['name', 'lang', 'day'];

    // registry to do not send pending emails twice
    protected $_pendingSent = [];

    public function getAccessType()
    {
        return ResourceAccess::EMAILTEMPLATE;
    }

    public function getAccessTitle()
    {
        return ___('E-Mail Messages');
    }

    public function getPageId()
    {
        return 'emails';
    }

    public function getTitleField()
    {
        return 'name';
    }

    /**
     * @param type $name
     * @param type $lang
     * @return EmailTemplate
     */
    public function findFirstExact($name, $lang = null)
    {
        $row = $this->getDi()->db->selectRow("SELECT * FROM ?_email_template
            WHERE name=?
            {ORDER BY lang=? DESC, lang='en' DESC}",
                $name,
                is_null($lang) ? DBSIMPLE_SKIP : $lang);
        return $row ? $this->createRecord($row) : null;
    }

    function deleteByFields($name, $day = null)
    {
        $this->_db->query("DELETE FROM ?_email_template WHERE name=? {AND day=?}",
                $name,
                $day != "" ? $day : DBSIMPLE_SKIP);
    }

    /**
     * Find exact template by criteria
     * @return EmailTemplate
     */
    function getExact($name, $lang = null, $day = null)
    {
        $row = $this->getDi()->db->selectRow("SELECT * FROM ?_email_template
            WHERE name=?
            {AND lang=?} {AND day=?}",
                $name,
                is_null($lang) ? DBSIMPLE_SKIP : $lang,
                is_null($day) ? DBSIMPLE_SKIP : $day);
        return $row ? $this->createRecord($row) : null;
    }

    public function getLanguages($name, $day = null, $exclude = null)
    {
        return $this->_db->selectCol("SELECT DISTINCT lang as ARRAY_KEY, lang
            FROM ?_email_template
            WHERE name=? {AND day=?} {AND lang<>?}",
                $name,
                is_null($day) ? DBSIMPLE_SKIP : $day,
                is_null($exclude) ? DBSIMPLE_SKIP : $exclude
            );
    }

    protected function collectRecipients(EmailTemplate $et, User $user, &$lang, $checkUserStatus = false): array
    {
        $recipients = [];
        if (
            $et->recipient_user
            && (
                !$checkUserStatus
                || !($user->unsubscribed || !$user->is_approved)
            )
        ) {
            isset($lang) || $lang = $user->lang;
            $recipients[] = $user;
        }
        if (
            $et->recipient_aff
            && $user->aff_id
            && ($aff = $this->getDi()->userTable->load($user->aff_id, false))
        ) {
            isset($lang) || $lang = $aff->lang;
            $recipients[] = $aff;
        }
        if (
            $et->recipient_subusers_parent
            && $user->subusers_parent_id
            && ($parent = $this->getDi()->userTable->load($user->subusers_parent_id, false))
        ) {
            isset($lang) || $lang = $parent->lang;
            $recipients[] = $parent;
        }
        if ($et->recipient_admin) {
            $recipients[] = Am_Mail_Template::TO_ADMIN;
        }
        if ($et->recipient_emails) {
            foreach (array_map('trim', explode(',', $et->recipient_emails)) as $email) {
                if ($email) {
                    $recipients[] = $email;
                }
            }
        }
        return $recipients;
    }

    protected function attachPdfInvoice(Am_Mail_Template $tpl, Am_Record $source): void
    {
        try {
            $pdf = Am_Pdf_Invoice::create($source);
            $pdf->setDi($this->getDi());
            $tpl->getMail()->createAttachment(
                $pdf->render(),
                'application/pdf',
                Zend_Mime::DISPOSITION_ATTACHMENT,
                Zend_Mime::ENCODING_BASE64,
                $pdf->getFileName()
            );
        } catch (Exception $e) {
            $this->getDi()->logger->error('Could not attach pdf invoice', ["exception" => $e]);
        }
    }

    protected function storePdfInvoice(Am_Record $source): void
    {
        try {
            $pdf = Am_Pdf_Invoice::create($source);
            $pdf->setDi($this->getDi());
            $pdf->render();
        } catch (Exception $e) {
            $this->getDi()->logger->error("Could not create pdf invoice", ["exception" => $e]);
        }
    }

    public function sendProductWelcomeEmails(User $user, $invoiceOrAccess = null)
    {
        $product_ids = [];
        if ($invoiceOrAccess instanceof Access) {
            $product_ids[] = $invoiceOrAccess->product_id;
            $access = $invoiceOrAccess;
        } elseif ($invoiceOrAccess instanceof Invoice) {
            $payment = current($invoiceOrAccess->getPaymentRecords());
            $invoice = $invoiceOrAccess;
            if ($product_ids = $this->getDi()->db->selectCol('SELECT product_id from ?_access where invoice_id = ?',$invoiceOrAccess->invoice_id)) {
                [$access] = $invoice->getAccessRecords();
            }
        }
        if (!count($product_ids)) return;
        $product_id = $product_ids[0];
        $product = $this->getDi()->productTable->load($product_id);
        foreach ($this->getDi()->resourceAccessTable->getProductWelcomeEmails($product_ids) as $et)
        {
            // check if no matching not_conditions
            if (!$et->checkNotConditions($user, ['invoiceOrAccess' => $invoiceOrAccess])) continue;

            $lang = null;
            $recipients = $this->collectRecipients($et, $user, $lang, true);
            $tpl = Am_Mail_Template::createFromEmailTemplate($et, $lang ?? null);
            $tpl->setLast_product_title($product->getTitle());
            $tpl->setUser($user);
            //%password% placeholder will be available only in case auto_create
            //mode in payment system (user and payment created during same request)
            $pass = $user->getPlaintextPass();
            $tpl->setPassword($pass ?: ___('what you entered when creating your account'));
            if (isset($access)) {
                $tpl->setAccess($access);
            }
            if (isset($payment)) {
                $tpl->setPayment($payment);
            }
            if (isset($invoice)) {
                $tpl->setInvoice($invoice);
            }
            foreach ($recipients as $recipient) {
                $tpl->send($recipient);
            }
        }
    }

    /**
     * @link Invoice->start
     */
    public function sendZeroAutoresponders(User $user, $invoiceOrAccess = null)
    {
        $this->sendProductWelcomeEmails($user, $invoiceOrAccess);
        foreach ($this->getDi()->resourceAccessTable
             ->getAllowedResources($user, ResourceAccess::EMAILTEMPLATE) as $et)
        {
            if ($et->name != EmailTemplate::AUTORESPONDER || $et->day != 1) continue;

            // check if no matching not_conditions
            if (!$et->checkNotConditions($user, ['invoiceOrAccess' => $invoiceOrAccess])) continue;

            // don't send same e-mail twice
            $sent = (array)$user->data()->get('zero-autoresponder-sent');
            if (in_array($et->pk(), $sent)) continue;
            $sent[] = $et->pk();
            ///

            $lang = null;
            $recipients = $this->collectRecipients($et, $user, $lang);
            $tpl = Am_Mail_Template::createFromEmailTemplate($et, $lang ?? null);
            if (!is_null($invoiceOrAccess)) {
                if ($invoiceOrAccess instanceof Invoice)
                    $tpl->setInvoice($invoiceOrAccess);
                $tpl->setLast_product_title($this->getLastProductTitle($et->fn_id, $invoiceOrAccess));
            }
            $tpl->setUser($user);
            //%password% placeholder will be available only in case auto_create
            //mode in payment system (user and payment created during same request)
            $pass = $user->getPlaintextPass();
            $tpl->setPassword($pass ?: ___('what you entered when creating your account'));
            foreach ($recipients as $recipient) {
                $tpl->send($recipient);
            }
            // store sent emails
            $user->data()->set('zero-autoresponder-sent', $sent)->update();
        }
    }

    public function sendZeroPayments(User $user, InvoicePayment $payment)
    {
        $this->getDi()->plugins_payment->loadEnabled()->getAllEnabled();
        $invoice = $payment->getInvoice();
        $product_ids = array_filter(array_map(function($el) {
            return $el->item_type == 'product' ? $el->item_id : null;
        }, $invoice->getItems()));
        $billing_plan_ids = array_filter(array_map(function($el) {
            return $el->item_type == 'product' ? $el->billing_plan_id : null;
        }, $invoice->getItems()));
        if (!$product_ids) return;

        foreach ($this->getDi()->resourceAccessTable->getPaymentEmails($product_ids, $billing_plan_ids) as $et)
        {
            if ($et->paysys_ids && !in_array($payment->paysys_id, explode(',', $et->paysys_ids))) {
                continue;
            }

            if ($et->name != EmailTemplate::PAYMENT || $et->day != 0) {
                continue;
            }

            if ($et->payment_type == EmailTemplate::PAYMENT_TYPE_FIRST && !$payment->isFirst()) {
                continue;
            }

            if ($et->payment_type == EmailTemplate::PAYMENT_TYPE_SECOND && $payment->isFirst()) {
                continue;
            }

            // check if no matching not_conditions
            if (!$et->checkNotConditions($user, ['payment' => $payment])) continue;

            $lang = null;
            $recipients = $this->collectRecipients($et, $user, $lang);
            $tpl = Am_Mail_Template::createFromEmailTemplate($et, $lang ?? null);
            $tpl->setPayment($payment);
            $tpl->setInvoice($invoice = $payment->getInvoice());
            $tpl->setInvoice_text($invoice->render('', $payment));
            $tpl->setInvoice_html($invoice->renderHtml($payment));
            $tpl->setUser($user);
            //%password% placeholder will be available only in case auto_create
            //mode in payment system (user and payment created during same request)
            $pass = $user->getPlaintextPass();
            $tpl->setPassword($pass ?: ___('what you entered when creating your account'));
            $tpl->setInvoice_items($invoice->getItems());
            $tpl->setProduct($invoice->getItem(0)->tryLoadProduct());

            if ($et->attach_pdf_invoice) {
                $this->attachPdfInvoice($tpl, $payment);
            } elseif ($this->getDi()->config->get('store_pdf_file')) {
                $this->storePdfInvoice($payment);
            }
            foreach ($recipients as $recipient) {
                $tpl->send($recipient);
            }
        }
    }

    protected function getLastProductTitle($fn_id, $invoiceOrAccessOrUser)
    {
        if ($fn_id['fn'] == 'product_id')
            return $this->getDi()->productTable->load($fn_id['id'])->title;

        if ($invoiceOrAccessOrUser instanceof Invoice)
            return $this->getDi()->db->selectCell("
                SELECT p.title
                FROM ?_invoice_item ii
                LEFT JOIN ?_product_product_category ppc
                ON (ppc.product_id = ii.item_id)
                LEFT JOIN ?_product p
                ON (p.product_id = ii.item_id)
                WHERE
                    ii.invoice_id = ?d
                    {AND ppc.product_category_id = ?d}
            ", $invoiceOrAccessOrUser->pk(), $fn_id['id'] != -1 ? $fn_id['id'] : DBSIMPLE_SKIP);

        if ($invoiceOrAccessOrUser instanceof Access)
            return $this->getDi()->db->selectCell("
                SELECT p.title
                FROM ?_access a
                LEFT JOIN ?_product p
                ON (p.product_id = a.product_id)
                WHERE
                    a.access_id = ?d
            ", $invoiceOrAccessOrUser->pk());

        // only cron autoresponder
        if ($invoiceOrAccessOrUser instanceof User)
        {
            return $this->getDi()->db->selectCell("
                    SELECT title
                    FROM ?_product
                    WHERE
                        product_id = (
                            SELECT a.product_id
                            FROM ?_access a
                            WHERE
                                begin_date <= ?
                                AND a.user_id = ?d
                                AND a.product_id IN (
                                    SELECT us.product_id
                                    FROM ?_user_status us
                                    {LEFT JOIN
                                        ?_product_product_category ppc
                                    ON (ppc.product_id = us.product_id)}
                                    WHERE
                                        us.user_id=?d
                                        AND us.status=?d
                                        {AND ppc.product_category_id = ?d}
                                )
                            ORDER BY begin_date DESC
                            LIMIT 1
                        )
                ", $this->getDi()->sqlDate ,$invoiceOrAccessOrUser->pk(), $invoiceOrAccessOrUser->pk(), User::STATUS_ACTIVE,
                    $fn_id['id'] != -1 ? $fn_id['id'] : DBSIMPLE_SKIP,
                    $fn_id['id'] != -1 ? $fn_id['id'] : DBSIMPLE_SKIP);
        }

        return '';
    }

    function onInvoiceStarted(Am_Event_InvoiceStarted $event)
    {
        $invoice = $event->getInvoice();
        $event->getDi()->emailTemplateTable->sendZeroAutoresponders($invoice->getUser(), $invoice);
        if ($event->getDi()->config->get('send_free_payment_admin') && floatval($invoice->first_total) == 0)
        {
            if ($et = Am_Mail_Template::load('send_free_payment_admin'))
            {
                $et->setUser($event->getUser())
                    ->setInvoice($invoice)
                    ->setPayment($event->getPayment())
                    ->setInvoice_text($invoice->render())
                    ->setInvoice_html($invoice->renderHtml());
                //%password% placeholder will be available only in case auto_create
                //mode in payment system (user and payment created during same request)
                $pass = $event->getUser()->getPlaintextPass();
                $et->setPassword($pass ?: ___('what you entered when creating your account'));
                $et->send(Am_Mail_Template::TO_ADMIN);
            }
        }
    }

    function sendPaymentReceiptToUser(InvoicePayment $payment)
    {
        $this->getDi()->plugins_payment->loadEnabled()->getAllEnabled();
        $products = [];
        foreach ($payment->getInvoice()->getProducts() as $product) {
            $products[] = $product->getTitle();
        }

        if ($this->getDi()->config->get('send_payment_mail'))
        {
            $et = Am_Mail_Template::load('send_payment_mail', $payment->getInvoice()->getUser()->lang);
            if ($et && $payment->amount > 0)
            {
                $et->setUser($payment->getInvoice()->getUser())
                    ->setInvoice($payment->getInvoice())
                    ->setPayment($payment)
                    ->setInvoice_items($payment->getInvoice()->getItems())
                    ->setInvoice_text($payment->getInvoice()->render('', $payment))
                    ->setInvoice_html($payment->getInvoice()->renderHtml($payment))
                    ->setProduct_title(implode(", ", $products))
                    ->setProduct_title_html(sprintf('<ul>%s</ul>', implode("\n", array_map(fn($_) => sprintf('<li>%s</li>', Am_Html::escape($_)), $products))));

                //%password% placeholder will be available only in case auto_create
                //mode in payment system (user and payment created during same request)
                $pass = $payment->getInvoice()->getUser()->getPlaintextPass();
                $et->setPassword($pass ?: ___('what you entered when creating your account'));

                if (
                    $this->getDi()->config->get('send_pdf_invoice')
                    && $this->getDi()->config->get('pdf_invoice_sent_user')
                ) {
                    $this->getDi()->locale->changeLanguageTo($payment->getInvoice()->getUser()->lang);
                    $this->attachPdfInvoice($et, $payment);
                    $this->getDi()->locale->restoreLanguage();
                } elseif ($this->getDi()->config->get('store_pdf_file')) {
                    $this->storePdfInvoice($payment);
                }

                $et->send($payment->getInvoice()->getUser());
            }
        }
    }

    function onPaymentWithAccessAfterInsert(Am_Event_PaymentWithAccessAfterInsert $event)
    {
        /**
         * This e-mail is sent for first payment in invoice only
         * another template must be used for the following payments
         */
        // if ($event->getInvoice()->getPaymentsCount() != 1) return;
        $event->getDi()->plugins_payment->loadEnabled()->getAllEnabled();
        $products = [];
        foreach ($event->getInvoice()->getProducts() as $product) {
            $products[] = $product->getTitle();
        }

        $this->sendPaymentReceiptToUser($event->getPayment());

        if ($event->getDi()->config->get('send_payment_admin'))
        {
            $et = Am_Mail_Template::load('send_payment_admin', $event->getUser()->lang);
            if ($et && $event->getPayment()->amount > 0)
            {
                $et->setUser($event->getUser())
                    ->setInvoice($event->getInvoice())
                    ->setPayment($event->getPayment())
                    ->setInvoice_items($event->getInvoice()->getItems())
                    ->setInvoice_text($event->getInvoice()->render('', $event->getPayment()))
                    ->setInvoice_html($event->getInvoice()->renderHtml($event->getPayment()))
                    ->setProduct_title(implode(", ", $products))
                    ->setProduct_title_html(sprintf('<ul>%s</ul>', implode("\n", array_map(fn($_) => sprintf('<li>%s</li>', Am_Html::escape($_)), $products))));

                //%password% placeholder will be available only in case auto_create
                //mode in payment system (user and payment created during same request)
                $pass = $event->getUser()->getPlaintextPass();
                $et->setPassword($pass ?: ___('what you entered when creating your account'));

                if (
                    $event->getDi()->config->get('send_pdf_invoice', false)
                    && $event->getDi()->config->get('pdf_invoice_sent_admin')
                ) {
                    $this->attachPdfInvoice($et, $event->getPayment());
                } elseif ($event->getDi()->config->get('store_pdf_file')) {
                    $this->storePdfInvoice($event->getPayment());
                }

                $et->send(Am_Mail_Template::TO_ADMIN);
            }
        }
        $event->getDi()->emailTemplateTable->sendZeroPayments($event->getUser(), $event->getPayment());
    }

    function onUserUnsubscribedChanged(Am_Event $event)
    {
        $user = $event->getUser();
        if (
            $user->unsubscribed
            && $event->getDi()->config->get('unsubscribe_confirmation')
            && ($tmpl = Am_Mail_Template::load('unsubscribe_confirmation', $user->lang))
        ) {
            $tmpl->setUser($user);
            $tmpl->send($user);
        }
    }

    function onInvoicePaymentRefund(Am_Event $event)
    {
        /**
         * This e-mail is sent for first payment in invoice only
         * another template must be used for the following payments
         */
        // if ($event->getInvoice()->getPaymentsCount() != 1) return;
        $event->getDi()->plugins_payment->loadEnabled()->getAllEnabled();
        $products = [];
        foreach ($event->getInvoice()->getProducts() as $product)
            $products[] = $product->getTitle();
        if ($event->getDi()->config->get('send_refund_mail'))
        {
            if ($et = Am_Mail_Template::load('send_refund_mail', $event->getUser()->lang))
            {
                $et->setUser($event->getUser())
                    ->setInvoice($event->getInvoice())
                    ->setRefund($event->getRefund())
                    ->setProduct_title(implode(", ", $products))
                    ->setProduct_title_html(sprintf('<ul>%s</ul>', implode("\n", array_map(fn($_) => sprintf('<li>%s</li>', Am_Html::escape($_)), $products))));

                if (
                    $event->getDi()->config->get('send_pdf_invoice')
                    && $event->getDi()->config->get('pdf_refund_sent_user')
                ) {
                    $event->getDi()->locale->changeLanguageTo($event->getUser()->lang);
                    $this->attachPdfInvoice($et, $event->getRefund());
                    $event->getDi()->locale->restoreLanguage();
                } elseif ($event->getDi()->config->get('store_pdf_file')) {
                    $this->storePdfInvoice($event->getRefund());
                }

                $et->send($event->getUser());
            }
        }

        if ($event->getDi()->config->get('send_refund_admin'))
        {
            if ($et = Am_Mail_Template::load('send_refund_admin', $event->getUser()->lang))
            {
                $et->setUser($event->getUser())
                    ->setInvoice($event->getInvoice())
                    ->setRefund($event->getRefund())
                    ->setProduct_title(implode(", ", $products))
                    ->setProduct_title_html(sprintf('<ul>%s</ul>', implode("\n", array_map(fn($_) => sprintf('<li>%s</li>', Am_Html::escape($_)), $products))));

                if (
                    $event->getDi()->config->get('send_pdf_invoice', false)
                    && $event->getDi()->config->get('pdf_refund_sent_admin')
                ) {
                    $this->attachPdfInvoice($et, $event->getRefund());
                } elseif ($event->getDi()->config->get('store_pdf_file')) {
                    $this->storePdfInvoice($event->getRefund());
                }

                $et->send(Am_Mail_Template::TO_ADMIN);
            }
        }
    }

    protected function isNotificationRuleMatch(EmailTemplate $tmpl, Invoice $invoice)
    {
        if (!$tmpl->conditions) return true;

        $conds = [];
        foreach (explode(',', $tmpl->conditions) as $item) {
            preg_match('/([A-Z]*)-(.*)/', $item, $match);
            $conds[$match[1]][] = $match[2];
        }

        //check product conditions
        $product_ids = [];

        if (isset($conds['CATEGORY'])) {
            $catProducts = $this->getDi()->productCategoryTable->getCategoryProducts();
            foreach ($conds['CATEGORY'] as $cat_id) {
                if (isset($catProducts[$cat_id]))
                    $product_ids = array_merge($product_ids, $catProducts[$cat_id]);
            }
        }

        if (isset($conds['PRODUCT'])) {
            $product_ids = array_merge($product_ids, $conds['PRODUCT']);
        }

        if (!count($product_ids) && isset($conds['CATEGORY'])) {
            //user set up categories without products
            return false;
        }

        if (count($product_ids)) {
            $invoice_product_id = array_map(function($a) {return $a->pk();}, $invoice->getProducts());
            if (!array_intersect($product_ids, $invoice_product_id))
                return false;
        }

        //check paysystem conditions
        if (isset($conds['PAYSYSTEM']) && !in_array($invoice->paysys_id, $conds['PAYSYSTEM']))
            return false;

        return true;
    }

    /**
     * @param string $name
     * @return array array day => array(templates for day)
     */
    protected function findPendingNotificationRules($name)
    {
        $templates = $this->findByName($name);
        $days = [];
        foreach ($templates as $tpl) {
            $dd = array_filter(explode(',', $tpl->days), function($a) {return $a!=="";});
            foreach ($dd as $d) {
                $days[$d][] = $tpl;
            }
        }
        return $days;
    }

    protected function sendPendingNotifications($day, $invoices, $tpls, $sendCallback, $oneEmailPerUser = true)
    {
        foreach ($invoices as $invoice) {
            if ($invoice->data()->get('skip_pending_email')) continue;
            $user = $invoice->getUser();
            if (!$user) continue;
            foreach ($tpls as $tpl) {
                $lang = $tpl->name == 'pending_to_user' ? $user->lang : null;
                if ($this->isNotificationRuleMatch($tpl, $invoice)) {
                    if ($oneEmailPerUser && !empty($this->_pendingSent[$sendCallback[1]][$invoice->getUser()->pk()]))
                        continue;
                    $mailTpl = Am_Mail_Template::createFromEmailTemplate($tpl, $lang);
                    $mailTpl->setUser($invoice->getUser());
                    $mailTpl->setInvoice($invoice);
                    $mailTpl->setInvoice_text($invoice->render());
                    $mailTpl->setInvoice_html($invoice->renderHtml());
                    $due_date = $invoice->due_date ?: sqlDate("{$invoice->tm_added} +" . Invoice::DEFAULT_DUE_PERIOD . " days");
                    if ($due_date < sqlDate('+ 1 days')) {
                        $invoice->updateQuick('due_date', sqlDate('+ 1 days'));
                    }
                    $mailTpl->setPaylink($this->getDi()->surl("pay/{$invoice->getSecureId('payment-link')}",false));
                    $mailTpl->setDay($day);

                    $products = [];
                    foreach ($invoice->getProducts() as $product)
                        $products[] = $product->getTitle();
                    $mailTpl->setProduct_title(implode(", ", $products));
                    $mailTpl->setProduct_title_html(sprintf('<ul>%s</ul>', implode("\n", array_map(fn($_) => sprintf('<li>%s</li>', Am_Html::escape($_)), $products))));

                    call_user_func($sendCallback, $mailTpl, $invoice);
                    if ($oneEmailPerUser) {
                        $this->_pendingSent[$sendCallback[1]][$invoice->getUser()->pk()] = true;
                        break;
                    }
                }
            }
        }
    }

    protected function sendPendingNotificationToUser(Am_Mail_Template $mailTpl, Invoice $invoice)
    {
        $user = $invoice->getUser();
        if ($user->unsubscribed) return;
        $mailTpl->send($user);
    }

    protected function sendPendingNotificationToAdmin(Am_Mail_Template $mailTpl, Invoice $invoice)
    {
        $mailTpl->sendAdmin();
    }

    public function _sendCronPendingNotifications($name, $sendCallback, $oneEmailPerUser = true)
    {
        $days = $this->findPendingNotificationRules($name);
        unset($days[0]); //it should have been sent on invoice created

        foreach ($days as $d => $tpls) {
            if (substr($d, -1) == 'h') continue; //it should been send on hourly cron
            $date = $this->getDi()->dateTime;
            $date->modify('-' . $d . ' days');
            $end_date = $date->format('Y-m-d H:i:59');
            $date->modify('- 23 hours');
            $date->modify('- 59 minutes');
            $begin_date = $date->format('Y-m-d H:i:00');
            $this->_sendCronPendingNotificationsByPeriod($d, $tpls, $sendCallback, [$begin_date, $end_date], $oneEmailPerUser);
        }
    }

    public function _sendCronHourlyPendingNotifications($name, $sendCallback, $oneEmailPerUser = true)
    {
        $days = $this->findPendingNotificationRules($name);
        unset($days[0]); //it should have been sent on invoice created

        foreach ($days as $d => $tpls) {
            if (substr($d, -1) != 'h') continue; //it should been send on daily cron
            $d = str_replace('h', '', $d);
            $date = $this->getDi()->dateTime;
            $date->modify('-' . $d . ' hours');
            $end_date = $date->format('Y-m-d H:i:59');
            $date->modify('- 59 minutes');
            $begin_date = $date->format('Y-m-d H:i:00');;
            $this->_sendCronPendingNotificationsByPeriod(0, $tpls, $sendCallback, [$begin_date, $end_date], $oneEmailPerUser);
        }
    }

    protected function _sendCronPendingNotificationsByPeriod($d, $tpls, $sendCallback, $period, $oneEmailPerUser = true)
    {
        [$begin_date, $end_date] = $period;
        $query = new Am_Query($this->getDi()->invoiceTable);
        $query = $query->addWhere('status=?', Invoice::PENDING)
            ->addWhere('tm_added>?', $begin_date)
            ->addWhere('tm_added<?', $end_date);
        $t = $query->getAlias();
        $query->addWhere("NOT EXISTS
            (SELECT * FROM ?_invoice_payment ip
            WHERE ip.user_id = $t.user_id
             AND ip.dattm>=?
             AND ip.dattm<GREATEST(?, $t.tm_added + INTERVAL 48 HOUR)
                 LIMIT 1
            )", $begin_date, $end_date);
        if ($count = $query->getFoundRows()) {
            $invoices = $query->selectPageRecords(0, $count);
            $this->sendPendingNotifications($d, $invoices, $tpls, $sendCallback, $oneEmailPerUser);
        }
    }

    public function _sendZeroPendingNotifications($name, $sendCallback, Invoice $invoice, $oneEmailPerUser = true)
    {
        $days = $this->findPendingNotificationRules($name);
        if (isset($days[0])) {
            $tpls = $days[0];
            $this->sendPendingNotifications(0, [$invoice], $tpls, $sendCallback, $oneEmailPerUser);
        }
    }

    public function sendCronPendingNotifications()
    {
        $this->_sendCronPendingNotifications('pending_to_user', [$this, 'sendPendingNotificationToUser']);
        $this->_sendCronPendingNotifications('pending_to_admin', [$this, 'sendPendingNotificationToAdmin']);
    }

    public function sendCronHourlyPendingNotifications()
    {
        $this->_sendCronHourlyPendingNotifications('pending_to_user', [$this, 'sendPendingNotificationToUser']);
        $this->_sendCronHourlyPendingNotifications('pending_to_admin', [$this, 'sendPendingNotificationToAdmin']);
    }

    public function onInvoiceAfterInsert(Am_Event $event)
    {
        $invoice = $event->getInvoice();
        if ($invoice->data()->get('added-by-admin'))
            return; // do not send automatic e-mails if invoice added by admin
        $this->_sendZeroPendingNotifications('pending_to_user', [$this, 'sendPendingNotificationToUser'], $invoice);
        $this->_sendZeroPendingNotifications('pending_to_admin', [$this, 'sendPendingNotificationToAdmin'], $invoice);
    }

    public function sendCronPayments()
    {
        $this->getDi()->plugins_payment->loadEnabled()->getAllEnabled();
        $mails = $this->findBy(['name' => EmailTemplate::PAYMENT]);
        if (!$mails) return; // nothing to send
        $byDatePayment = $byDateInvoice = []; // templates by expiration date
        foreach ($mails as $et) {
            $et->_productIds = $et->findMatchingProductIds();
            $et->_billingPlanIds = $et->findMatchingBillingPlanIds();
            if ($et->day > 0) {
                $date = date('Y-m-d', strtotime("+{$et->day} days", $this->getDi()->time));
                $byDateInvoice[$date][] = $et;
            } elseif ($et->day < 0) {
                $date = date('Y-m-d', strtotime("{$et->day} days", $this->getDi()->time));
                $byDatePayment[$date][] = $et;
            }
        }
        if (count($byDatePayment)) {
            $q = $this->getDi()->db->queryResultOnly(<<<CUT
                SELECT ip.*,
                        GROUP_CONCAT(ii.item_id) as _product_id,
                        GROUP_CONCAT(ii.billing_plan_id) as _billing_plan_id
                    FROM ?_invoice_payment ip
                    LEFT JOIN ?_invoice_item ii
                        ON (ip.invoice_id = ii.invoice_id
                            AND ii.item_type = 'product')
                    WHERE DATE(dattm) IN (?a)
                    GROUP BY ip.invoice_payment_id
            CUT, array_keys($byDatePayment));

            while ($row = $this->_db->fetchRow($q)) {
                $payment = $this->getDi()->invoicePaymentTable->createRecord($row);
                $invoice = $payment->getInvoice();
                $user = $payment->getUser();
                $this->_sendCronPayments(
                    $byDatePayment[sqlDate($payment->dattm)],
                    explode(',', $row['_product_id']),
                    explode(',', $row['_billing_plan_id']),
                    $user,
                    $payment,
                    $invoice
                );
            }
        }
        if (count($byDateInvoice)) {
            $q = $this->getDi()->db->queryResultOnly(<<<CUT
                SELECT i.*,
                        GROUP_CONCAT(ii.item_id) as _product_id,
                        GROUP_CONCAT(ii.billing_plan_id) as _billing_plan_id
                    FROM ?_invoice i
                    LEFT JOIN ?_invoice_item ii
                        ON (i.invoice_id = ii.invoice_id
                            AND ii.item_type = 'product')
                    WHERE i.rebill_date IN (?a)
                        AND ii.second_total > 0
                    GROUP BY i.invoice_id
            CUT, array_keys($byDateInvoice));
            while ($row = $this->_db->fetchRow($q)) {
                /** @var Invoice $invoice */
                $invoice = $this->getDi()->invoiceTable->createRecord($row);
                $payment = $this->getDi()->invoicePaymentRecord;
                $payment->toggleFrozen(true);
                $payment->receipt_id = 'NOT PAID';
                $payment->display_invoice_id = "$invoice->public_id/NOT PAID";
                $payment->dattm = $invoice->rebill_date;
                $payment->amount = $invoice->second_total;
                $payment->invoice_id = $invoice->pk();
                $payment->tax_rate = $invoice->tax_rate;
                $payment->_setInvoice($invoice);
                $user = $invoice->getUser();
                $payment->user_id = $user->pk();
                $this->_sendCronPayments(
                    $byDateInvoice[$invoice->rebill_date],
                    explode(',', $row['_product_id']),
                    explode(',', $row['_billing_plan_id']),
                    $user,
                    $payment,
                    $invoice
                );
            }
        }
    }

    protected function _sendCronPayments($tmpls, $pids, $bpIds, $user, $payment, $invoice)
    {
        if ($user->unsubscribed || !$user->is_approved) return;
        foreach ($tmpls as $et) {
            if ($et->paysys_ids && !in_array($invoice->paysys_id, explode(',', $et->paysys_ids))) {
                continue;
            }
            if (
                $et->_productIds == ResourceAccess::ANY_PRODUCT
                || (array_intersect($pids, $et->_productIds))
                || (array_intersect($bpIds, $et->_billingPlanIds))
            ) {
                // check if no matching not_conditions
                if (!$et->checkNotConditions($user, ['payment' => $payment, 'invoice' => $invoice])) continue;

                $lang = null;
                $recipients = $this->collectRecipients($et, $user, $lang);
                $tpl = Am_Mail_Template::createFromEmailTemplate($et, $lang ?? null);
                $tpl->setUser($user);
                //%password% placeholder will be available only in case auto_create
                //mode in payment system (user and payment created during same request)
                $pass = $user->getPlaintextPass();
                $tpl->setPassword($pass ?: ___('what you entered when creating your account'));
                $tpl->setPayment($payment);
                $tpl->setInvoice($invoice);
                $tpl->setInvoice_text($invoice->render('', $payment));
                $tpl->setInvoice_html($invoice->renderHtml($payment));
                $tpl->setInvoice_items($invoice->getItems());
                $tpl->setProduct($invoice->getItem(0)->tryLoadProduct());
                if ($et->attach_pdf_invoice) {
                    $this->attachPdfInvoice($tpl, $payment);
                } elseif ($this->getDi()->config->get('store_pdf_file')) {
                    $this->storePdfInvoice($payment);
                }

                foreach ($recipients as $recipient) {
                    $tpl->send($recipient);
                }
            }
        }
    }

    public function sendCronAutoresponders()
    {
        $userTable = $this->getDi()->userTable;
        $etCache = [];
        $db = $this->getDi()->db;
        $q = $this->getDi()->resourceAccessTable->getResourcesForMembers(ResourceAccess::EMAILTEMPLATE)->query();
        while ($res = $db->fetchRow($q))
        {
            if (!@$res['user_id']) continue;
            $user = $userTable->load($res['user_id'], false);
            if (!$user) continue; // no user found
            if ($user->unsubscribed) continue;

            if (!array_key_exists($res['resource_id'], $etCache))
                $etCache[$res['resource_id']] = $this->load($res['resource_id'], false);

            if (!$etCache[$res['resource_id']]) continue; // no template found

            //do not send zero autoresponder second time
            if ($etCache[$res['resource_id']]->day==1)
                if (in_array($res['resource_id'], (array)$user->data()->get('zero-autoresponder-sent'))) continue;

            if (($etCache[$res['resource_id']]->name != EmailTemplate::AUTORESPONDER)) continue;

            // check if no matching not_conditions
            if (!$etCache[$res['resource_id']]->checkNotConditions($user)) continue;

            $lang = null;
            $recipients = $this->collectRecipients($etCache[$res['resource_id']], $user, $lang);
            $tpl = Am_Mail_Template::createFromEmailTemplate($etCache[$res['resource_id']]);
            $tpl->setLast_product_title($this->getLastProductTitle(['fn' => $res['fn'], 'id' => $res['fn_id']], $user));
            $tpl->setUser($user);
            foreach ($recipients as $recipient) {
                $tpl->send($recipient);
            }
        }
    }

    public function sendCronExpires()
    {
        $mails = $this->findBy(['name' => EmailTemplate::EXPIRE]);
        if (!$mails) return; // nothing to send
        $byDate = []; // templates by expiration date
        foreach ($mails as $et)
        {
            $et->_productIds = $et->findMatchingProductIds();
            ///
            $day = - $et->day;
            $string = $day . ' days';
            if ($day >= 0) $string = "+$string";
            if ($day == 0) $string = 'today';
            $date = date('Y-m-d', strtotime($string, $this->getDi()->time));

            $byDate[$date][] = $et;
        }

        $userTable = $this->getDi()->userTable;
        // now query expirations
        $q = $this->getDi()->accessTable->queryExpirations(array_keys($byDate));
        $sent = []; // user_id => array('tpl_id')
        while ($row = $this->_db->fetchRow($q))
        {
            $user = $userTable->createRecord($row);
            foreach ($byDate[$row['_expire_date']] as $et)
            {
                if ($row['_recurring'] && !$et->recurring) continue;
                if ($row['_cancelled'] && !$et->cancelled) continue;

                // do not send same template agian to the same user
                if (!empty($sent[$user->user_id]) && array_search($et->pk(), $sent[$user->user_id]) !== false) continue;

                if (
                    $et->_productIds == ResourceAccess::ANY_PRODUCT
                    || in_array($row['_product_id'], $et->_productIds)
                ) {
                    // check if no matching not_conditions
                    if (!$et->checkNotConditions($user)) continue;

                    $lang = null;
                    $recipients = $this->collectRecipients($et, $user, $lang, true);
                    $tpl = Am_Mail_Template::createFromEmailTemplate($et);
                    $tpl->setUser($user);
                    //%password% placeholder will be available only in case auto_create
                    //mode in payment system (user and payment created during same request)
                    $pass = $user->getPlaintextPass();
                    $tpl->setPassword($pass ?: ___('what you entered when creating your account'));
                    $tpl->setExpires(amDate($row['_expire_date']));
                    $tpl->setProduct_title($this->getDi()->productTable->load($row['_product_id'])->title);
                    if ($row['_invoice_id']) {
                        $tpl->setInvoice($this->getDi()->invoiceTable->load($row['_invoice_id']));
                    }
                    foreach ($recipients as $recipient) {
                        $tpl->send($recipient);
                    }
                    $sent[$user->user_id][] = $et->pk();
                }
            }
        }
    }

    function setupHooks()
    {
        $hook = $this->getDi()->hook;
        $hook->add(Am_Event::DAILY, [$this, 'sendCronExpires']);
        $hook->add(Am_Event::DAILY, [$this, 'sendCronAutoresponders']);
        $hook->add(Am_Event::DAILY, [$this, 'sendCronPayments']);
        $hook->add(Am_Event::DAILY, [$this, 'sendCronPendingNotifications']);

        $hook->add(Am_Event::INVOICE_AFTER_INSERT, [$this, 'onInvoiceAfterInsert']);
        $hook->add(Am_Event::INVOICE_STARTED, [$this, 'onInvoiceStarted']);
        $hook->add(Am_Event::PAYMENT_WITH_ACCESS_AFTER_INSERT, [$this, 'onPaymentWithAccessAfterInsert']);
        $hook->add(Am_Event::INVOICE_PAYMENT_REFUND, [$this, 'onInvoicePaymentRefund']);
        $hook->add(Am_Event::USER_UNSUBSCRIBED_CHANGED, [$this, 'onUserUnsubscribedChanged']);
    }
}