<?php

namespace ModulesGarden\HostingQuotaNotifications\App\Libs\Database;

use ModulesGarden\HostingQuotaNotifications\App\Helper\Migration\DBManager;
use ModulesGarden\HostingQuotaNotifications\App\Helper\Tables;
use \Illuminate\Database\Capsule\Manager as DB;

/**
 * Class DatabaseViews
 *
 * @author Artur Pilch <artur.pi@modulesgarden.com>
 */
class DatabaseViews
{
    public function getHostingInvoiceDates()
    {
        $hostingDatesTableName = Tables::tableViewsNames(Tables::HOSTING_DATES_VIEW);
        $this->createHostingInvoiceDates();
        $hostingDates = DB::table($hostingDatesTableName)->get();
        return $hostingDates;
    }

    public function createHostingInvoiceDates()
    {
        $hostingDatesTableName = Tables::tableViewsNames(Tables::HOSTING_DATES_VIEW);
        $hostingTable        = Tables::tableNames('hosting');
        $invoiceItemsTable   = Tables::tableNames('invoiceItems');
        $invoicesTable       = Tables::tableNames('invoices');

        DB::statement("CREATE OR REPLACE VIEW {$hostingDatesTableName} AS (
            SELECT {$hostingTable}.id, MAX({$invoicesTable}.date) as date FROM {$invoiceItemsTable}
            INNER JOIN {$invoicesTable} ON {$invoicesTable}.id = {$invoiceItemsTable}.invoiceid
            INNER JOIN {$hostingTable} ON {$hostingTable}.id = {$invoiceItemsTable}.relid
            WHERE {$invoiceItemsTable}.type = 'Hosting'
            AND {$invoicesTable}.status = 'Paid'
            GROUP BY {$hostingTable}.id
            )");
    }

    public function dropView($tableName = false)
    {
        DBManager::dropView($tableName);
    }
}
