<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class modelCurrency extends Model
{
    public static function selectAllCurrency($code)
    {

        return DB::select("SELECT * FROM `currencys` where `Code`='$code'");
    }

    public static function selectAllRates($code)
    {

        return DB::select("SELECT rates.EntryDate, rates.ID, currencys.Symbol,currencys.Name,rates.Rate, (select currencys.Name from currencys where currencys.ID in (select homecurrency.Currency from homecurrency where Code='$code' )) as HC, 1/rates.Rate as BR FROM `rates` INNER join currencys ON currencys.ID=rates.Currency where rates.Code='$code' order by rates.EntryDate desc");
    }

    public static function selectAllForegnCurrency($code)
    {
    
        return DB::select("SELECT * FROM `currencys` where `Code`='$code' and ID not in (select Currency FROM homecurrency where Code='$code')");
    }

    public static function selectHomeCurrency($code)
    {

        return DB::select("SELECT homecurrency.ID, homecurrency.Currency, currencys.Symbol, currencys.Name FROM `homecurrency`INNER join currencys on currencys.ID=homecurrency.Currency WHERE  homecurrency.Code='$code'");
    }
    public static function saveCurrency($code,$symbol,$name)
    {
        return DB::insert("INSERT INTO `currencys` ( `Code`, `Symbol`, `Name`) VALUES (?, ?,?)", [$code, $symbol,$name] );
    }
    public static function saveRate($code,$currency,$rate)
    {
        return DB::insert("INSERT INTO `rates` ( `Code`, `Currency`, `Rate`) VALUES (?, ?,?)", [$code, $currency,$rate] );
    }

    public static function saveHomeCurrency($code,$currency)
    {
        return DB::insert("INSERT INTO `homecurrency` ( `Code`, `Currency`) VALUES (?, ?)", [$code, $currency] );
    }
    public static function editHomeCurrency($id,$code,$currency)
    {
        return DB::insert("UPDATE  `homecurrency` set  `Currency`=?  where Code=? and ID=?", [ $currency,$code,$id] );
    }

    public static function deleteHomeCurrency($id,$code)
    {
        return DB::insert("DELETE FROM   `homecurrency`  where Code=? and ID=?", [$code,$id] );
    }


    public static function editCurrency($id,$code,$symbol,$name)
    {
        return DB::insert("UPDATE  `currencys` set  `Symbol`=? , `Name`=? where Code=? and ID=?", [$symbol, $name,$code,$id] );
    }

    public static function deleteCurrency($id,$code)
    {
        return DB::insert("DELETE FROM   `currencys`  where Code=? and ID=?", [$code,$id] );
    }
}
