<?php

namespace Amwdeveloper\controller;

use Amwdeveloper\model\layer;

class admin extends layer
{
    public function home()
    {
        $characters = $this->table("Character")->columns("Name")->count();
        $charactersbanned = $this->table("Character")->columns("Name")->where("CtlCode = :cc", "cc=1")->count();
        $charactersonline = $this->table("MEMB_STAT")->columns("memb___id")->where("ConnectStat = :cs", "cs=1")->count();

        $guilds = $this->table("Guild")->columns("G_Name")->count();

        $accounts = $this->table("MEMB_INFO")->columns("memb___id")->count();
        $accountsbanned = $this->table("MEMB_INFO")->columns("memb___id")->where("bloc_code = :cc", "cc=1")->count();
        $accountsvip = $this->table("MEMB_INFO")->columns("memb___id")->where("" . config['column_vip'] . " > :al", "al=0")->count();
        $supports = $this->table("amw_tickets")->columns("id")->where(" status = :al", "al=0")->count();

        $date_now = date('Y-m-d', strtotime('now'));
        $date_one = date('Y-m-d', strtotime('-1 day'));
        $date_two = date('Y-m-d', strtotime('-2 day'));
        $date_three = date('Y-m-d', strtotime('-3 day'));

        
        
        
        $total_deposit = $this->db()->query("SELECT SUM(price) as coin FROM amw_payment_mp WHERE status = 1")->fetch();

        $month_now = $this->db()->query("SELECT SUM(price) as coin FROM amw_payment_mp WHERE due_date = '{$date_now}' AND status = 1")->fetch();
        $month_one = $this->db()->query("SELECT SUM(price) as coin FROM amw_payment_mp WHERE due_date = '{$date_one}' AND status = 1")->fetch();
        $month_two = $this->db()->query("SELECT SUM(price) as coin FROM amw_payment_mp WHERE due_date = '{$date_two}' AND status = 1")->fetch();
        $month_three = $this->db()->query("SELECT SUM(price) as coin FROM amw_payment_mp WHERE due_date = '{$date_three}' AND status = 1")->fetch();
   
        return array(
            'characters' => $characters->amount ?? 0,
            'charactersbanned' => $charactersbanned->amount ?? 0,
            'charactersonline' => $charactersonline->amount ?? 0,
            'guilds' => $guilds->amount ?? 0,
            'accounts' => $accounts->amount ?? 0,
            'accountsbanned' => $accountsbanned->amount ?? 0,
            'accountsvip' => $accountsvip->amount ?? 0,
            'supports' => $supports->amount ?? 0,
            'payments' => [
                'total_deposit' => $total_deposit->coin ? $total_deposit->coin : 0,
                'month_one' => ['value' => $month_now->coin ? (int)$month_now->coin : 0, 'date' => $date_now],
                'month_two' => ['value' => $month_one->coin ? (int)$month_one->coin : 0, 'date' => $date_one],
                'month_three' => ['value' => $month_two->coin ? (int)$month_two->coin : 0, 'date' => $date_two],
                'month_four' => ['value' => $month_three->coin ? (int)$month_three->coin : 0, 'date' => $date_three],
                
                ]

        );
    }

    
    public function allnotice()
    {
        $alls = $this->table("amw_notice")->columns("title,id,date")->orderby("id")->all();
        $notices = array();
        foreach ($alls as $all) {
            $notices[] = [
                'title' => $all->title,
                'id' => $all->id,
                'date' => date('d/m/Y H:i:s', strtotime($all->date))
            ];
        }

        return $notices;
    }

    public function loadinfopost($request)
    {
        global $route;
        $id = filter_var($request['id'], FILTER_VALIDATE_INT);

        if (empty($id)) $route->redirect("/administration/manage/all/posts");

        $verifynotice = $this->table("amw_notice")->where("id = :id", "id={$id}")->get();

        if (empty($verifynotice)) $route->redirect("/administration/manage/all/posts");

        $notice = array(
            'id' => $verifynotice->id,
            'title' => $verifynotice->title,
            'tag' => $verifynotice->tag,
            'message' => $verifynotice->message,
            'link' => $verifynotice->link,
            'description' => $verifynotice->description,
            'comment' => $verifynotice->comment,
        );
        return $notice;
    }

    public function alldownloads()
    {
        $alls = $this->table("amw_download")->columns("title,id,date")->orderby("id")->all();
        $downloads = array();
        foreach ($alls as $all) {
            $downloads[] = [
                'title' => $all->title,
                'id' => $all->id,
                'date' => date('d/m/Y H:i:s', strtotime($all->date))
            ];
        }

        return $downloads;
    }

    public function loadinfodownload($request)
    {
        global $route;
        $id = filter_var($request['id'], FILTER_VALIDATE_INT);

        if (empty($id)) $route->redirect("/administration/manage/all/downloads");

        $verifydownload = $this->table("amw_download")->where("id = :id", "id={$id}")->get();

        if (empty($verifydownload)) $route->redirect("/administration/manage/all/downloads");

        $download = array(
            'id' => $verifydownload->id,
            'title' => $verifydownload->title,
            'link' => $verifydownload->link,
            'description' => $verifydownload->description,
            'size' => $verifydownload->size
        );
        return $download;
    }

    public function allitemshopping()
    {
        $items = $this->table("amw_items")->columns("id,name,category_group,item_group,coin_name")->orderby("name", "ASC")->all();
        if ($items) {
            foreach ($items as $i) {
                $item[] = [
                    'name' => $i->name,
                    'category' => $i->category_group,
                    'group' => $i->item_group,
                    'column' => $i->coin_name,
                    'id' => $i->id
                ];
            }
            return $item;
        }

        return null;
    }

    public function infoitemedit($request)
    {
        global $route;
        $id = filter_var($request['id'], FILTER_VALIDATE_INT);
        if (empty($id)) $route->redirect("/administration/shopping/all/itens");

        $verifyitem = $this->table("amw_items")->where("id = :id", "id={$id}")->get();

        if (empty($verifyitem)) $route->redirect("/administration/shopping/all/itens");

        $infoitem = [
            'id' => $verifyitem->id,
            'name' => $verifyitem->name,
            'section' => $verifyitem->section,
            'id_item' => $verifyitem->id_item,
            'level' => $verifyitem->level,
            'skill' => $verifyitem->skill,
            'luck' => $verifyitem->luck,
            'price' => $verifyitem->price,
            'x' => $verifyitem->x,
            'y' => $verifyitem->y,
            'active' => $verifyitem->active,
            'harmony' => $verifyitem->harmony,
            'category_group' => $verifyitem->category_group,
            'item_group' => $verifyitem->item_group,
            'socket1' => $verifyitem->socket1,
            'socket2' => $verifyitem->socket2,
            'socket3' => $verifyitem->socket3,
            'socket4' => $verifyitem->socket4,
            'socket5' => $verifyitem->socket5,
            'ancient_1' => $verifyitem->ancient_1,
            'ancient_2' => $verifyitem->ancient_2,
            'class' => $verifyitem->class
        ];

        return $infoitem;
    }

    public function shoppingremoveitem($request)
    {
        global $route;
        $id = filter_var($request['id'], FILTER_VALIDATE_INT);
        if (empty($id)) $route->redirect("/administration/shopping/all/itens");
        $verifyitem = $this->table("amw_items")->where("id = :id", "id={$id}")->get();

        if (empty($verifyitem)) $route->redirect("/administration/shopping/all/itens");

        $this->table("amw_items")->where("id = :id", "id={$id}")->destroy();
        $route->redirect("/administration/shopping/all/itens");
    }

    public function allslider()
    {
        $slider = $this->table("amw_slider")->columns("id,title,image")->all();
        foreach ($slider as $s) {
            $sld[] = [
                'id' => $s->id,
                'title' => $s->title,
                'image' => $s->image
            ];
        }
        return $sld ?? null;
    }

    public function sliderremove($request)
    {
        global $route;
        $id = filter_var($request['id'], FILTER_VALIDATE_INT);
        if (empty($id)) $route->redirect("/administration/slider/all");
        $verifyitem = $this->table("amw_slider")->where("id = :id", "id={$id}")->get();

        if (empty($verifyitem)) $route->redirect("/administration/slider/all");

        $this->table("amw_slider")->where("id = :id", "id={$id}")->destroy();
        $route->redirect("/administration/slider/all");
    }

    public function informationaccountsearch($account)
    {
        $characters = $this->table("Character")->columns("Name as name, cLevel as level, Class as class, Strength as strength, Dexterity as dexterity, Vitality as vitality, Energy as energy")->where("AccountID = :acc", "acc={$account}")->all() ?? null;
        $membinfo = $this->table("MEMB_INFO")->columns("memb_name as name,memb___id as account, memb__pwd as passwd, mail_addr as email, AccountLevel as type_vip, AccountExpireDate as expire_date")->where("memb___id = :acc", "acc={$account}")->get() ?? null;
        $coins = [];
        foreach (coins as $coin) {
            $coins[] = [
                'name' => $coin['name_coin'],
                'amount' => $this->table("" . $coin['table'] . "")->columns("" . $coin['column_coin'] . " as coin")->where("" . $coin['column_account'] . " = :acc", "acc={$account}")->get()->ftp_nb_continue ?? 0
            ];
        }
        return [
            'characters' =>  $characters,
            'coins' => $coins,
            'account' => $membinfo
        ];
    }
}
