<?php
namespace Domain\Finances;

class FinanceRepository
{
  private $mdb;

  public function __construct( $mdb = null )
  {
    if ( $mdb )
      $this -> mdb = $mdb;
    else
    {
      global $mdb;
      $this -> mdb = $mdb;
    }
  }

  public function firstOperationDate()
  {
    return $this -> mdb -> get( 'finance_operations', 'date', [ 'ORDER' => [ 'date' => 'ASC' ] ] );
  }

  public function getOperationTags( int $operation_id )
  {
    $return = '';
    $tags_id = $this -> mdb -> select( 'finance_operation_tags', 'tag_id', [ 'operation_id' => $operation_id ] );
    foreach ( $tags_id as $tag_id )
    {
      if ( $return )
        $return .= ', ';
      $return .= $this -> mdb -> get( 'finance_tags', 'tag', [ 'id' => $tag_id ] );
    }
    return $return;
  }

  public function clientName( $client_id )
  {
    return $this -> mdb -> get( 'crm_client', 'firm', [ 'id' => $client_id ] );
  }

  public function clientsListByDates( $date_from, $date_to )
  {
    return $this -> mdb -> query(
      'SELECT cc.id, cc.firm FROM crm_client AS cc '
      . 'INNER JOIN finance_operations AS fo ON fo.client_id = cc.id '
      . 'WHERE date >= :date_from AND date <= :date_to '
      . 'GROUP BY cc.id ORDER BY firm ASC',
      [ ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll( \PDO::FETCH_ASSOC );
  }

  public function clientsList()
  {
    return $this -> mdb -> select( 'crm_client', [ 'id', 'firm' ], [ 'ORDER' => [ 'firm' => 'ASC' ] ] );
  }

  public function clientsWithRevenue( $date_from, $date_to, $group_id )
  {
    return $this -> mdb -> query(
      'SELECT cc.id, cc.firm, '
      . 'SUM( CASE WHEN fo.amount > 0 THEN fo.amount ELSE 0 END ) AS income, '
      . 'SUM( CASE WHEN fo.amount < 0 THEN fo.amount ELSE 0 END ) AS costs, '
      . 'SUM( fo.amount ) AS total, '
      . 'COUNT( fo.id ) AS operations_count '
      . 'FROM crm_client AS cc '
      . 'INNER JOIN finance_operations AS fo ON fo.client_id = cc.id '
      . 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
      . 'WHERE fo.date >= :date_from AND fo.date <= :date_to AND fc.group_id = :group_id '
      . 'GROUP BY cc.id '
      . 'ORDER BY total DESC',
      [ ':date_from' => $date_from, ':date_to' => $date_to, ':group_id' => (int)$group_id ]
    ) -> fetchAll( \PDO::FETCH_ASSOC );
  }

  public function deleteCategory( $category_id )
  {
    return $this -> mdb -> delete( 'finance_categories', [ 'id' => (int)$category_id ] );
  }

  public function defaultGroup()
  {
    return $this -> mdb -> get( 'finance_group', 'id', [ 'default_group' => 1 ] );
  }

  public function groupsList()
  {
    return $this -> mdb -> select( 'finance_group', '*', [ 'ORDER' => [ 'name' => 'ASC' ] ] );
  }

  public function deleteOperation( $operation_id )
  {
    return $this -> mdb -> delete( 'finance_operations', [ 'id' => $operation_id ] );
  }

  public function tagsJson( $group_id )
  {
    return $this -> mdb -> select( 'finance_tags', 'tag', [ 'group_id' => $group_id, 'ORDER' => [ 'tag' => 'ASC' ] ] );
  }

  public function tagsList( $group_id )
  {
    $tags = [];
    $results = $this -> mdb -> select( 'finance_tags', '*', [ 'group_id' => $group_id ] );
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
    {
      $tag = $row;
      $tag['count'] = $this -> mdb -> count( 'finance_operation_tags', [ 'tag_id' => $row['id'] ] );
      $tags[] = $tag;
    }

    if ( count( $tags ) )
      array_multisort( array_column( $tags, 'count' ), SORT_DESC, $tags );

    return $tags;
  }

  public function operationsList( $date_from, $date_to, $group_id, $client_id = null )
  {
    $sql = 'SELECT fo.*, fc.name '
         . 'FROM finance_operations AS fo '
         . 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
         . 'WHERE date >= :date_from AND date <= :date_to AND group_id = :group_id ';

    $params = [ ':date_from' => $date_from, ':date_to' => $date_to, ':group_id' => (int)$group_id ];

    if ( $client_id )
    {
      $sql .= 'AND fo.client_id = :client_id ';
      $params[':client_id'] = (int)$client_id;
    }

    $sql .= 'ORDER BY fo.date DESC, fo.id DESC';

    return $this -> mdb -> query( $sql, $params ) -> fetchAll( \PDO::FETCH_ASSOC );
  }

  public function operationDetails( $operation_id )
  {
    $operation = $this -> mdb -> get( 'finance_operations', '*', [ 'id' => $operation_id ] );
    $operation['tags'] = $this -> mdb -> query(
      'SELECT tag, tag_id FROM finance_operation_tags AS fot '
      . 'INNER JOIN finance_tags AS ft ON fot.tag_id = ft.id '
      . 'WHERE operation_id = ' . (int)$operation_id
    ) -> fetchAll( \PDO::FETCH_ASSOC );

    return $operation;
  }

  public function saveOperation( $operation_id, $category_id, $date, $amount, $description, $tags, $group_id, $client_id = null )
  {
    $data = [
      'date' => $date ? $date : date( 'Y-m-d' ),
      'category_id' => $category_id,
      'amount' => $amount,
      'description' => $description,
      'client_id' => $client_id ? (int)$client_id : null
    ];

    if ( !$operation_id )
    {
      $this -> mdb -> insert( 'finance_operations', $data );
      $id = $this -> mdb -> id();
    }
    else
    {
      $this -> mdb -> update( 'finance_operations', $data, [ 'id' => $operation_id ] );
      $this -> mdb -> delete( 'finance_operation_tags', [ 'operation_id' => $operation_id ] );
      $id = $operation_id;
    }

    $this -> syncOperationTags( $id, $tags, $group_id );
    return $id;
  }

  private function syncOperationTags( $operation_id, $tags_string, $group_id )
  {
    $tags = explode( ',', $tags_string );
    if ( !is_array( $tags ) )
      return;

    foreach ( $tags as $tag )
    {
      $tag = trim( $tag );
      if ( !$tag )
        continue;

      $tag_id = $this -> mdb -> get( 'finance_tags', 'id', [ 'AND' => [ 'group_id' => $group_id, 'tag' => $tag ] ] );
      if ( !$tag_id )
      {
        $this -> mdb -> insert( 'finance_tags', [ 'tag' => $tag, 'group_id' => $group_id ] );
        $tag_id = $this -> mdb -> id();
      }
      $this -> mdb -> insert( 'finance_operation_tags', [ 'operation_id' => $operation_id, 'tag_id' => $tag_id ] );
    }
  }

  public function categoryDetails( $category_id )
  {
    return $this -> mdb -> get( 'finance_categories', '*', [ 'id' => $category_id ] );
  }

  public function saveCategory( $category_id, $name, $parent_id, $group_id )
  {
    if ( !$category_id )
    {
      $this -> mdb -> insert( 'finance_categories', [
        'name' => $name,
        'parent_id' => $parent_id ? $parent_id : null,
        'group_id' => $group_id
      ] );
      return $this -> mdb -> id();
    }
    else
    {
      $this -> mdb -> update( 'finance_categories', [
        'name' => $name,
        'group_id' => $group_id
      ], [ 'id' => $category_id ] );
      return $category_id;
    }
  }

  public function walletSummary( $group_id )
  {
    $results = $this -> mdb -> query(
      'SELECT SUM(amount) AS val FROM finance_operations '
      . 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id )',
      [ ':group_id' => (int)$group_id ]
    ) -> fetchAll( \PDO::FETCH_ASSOC );
    return $results[0]['val'];
  }

  public function walletSummaryThisMonth( $group_id )
  {
    $date_from = date( 'Y-m-01' );
    $date_to = date( 'Y-m-t' );

    $results = $this -> mdb -> query(
      'SELECT SUM(amount) AS val FROM finance_operations '
      . 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id ) '
      . 'AND date >= :date_from AND date <= :date_to',
      [ ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll( \PDO::FETCH_ASSOC );
    return $results[0]['val'];
  }

  public function walletIncomeThisMonth( $group_id )
  {
    $date_from = date( 'Y-m-01' );
    $date_to = date( 'Y-m-t' );

    $results = $this -> mdb -> query(
      'SELECT SUM(amount) AS val FROM finance_operations '
      . 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id ) '
      . 'AND date >= :date_from AND date <= :date_to AND amount > 0',
      [ ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll( \PDO::FETCH_ASSOC );
    return $results[0]['val'];
  }

  public function walletExpensesThisMonth( $group_id )
  {
    $date_from = date( 'Y-m-01' );
    $date_to = date( 'Y-m-t' );

    $results = $this -> mdb -> query(
      'SELECT SUM(amount) AS val FROM finance_operations '
      . 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id ) '
      . 'AND date >= :date_from AND date <= :date_to AND amount < 0',
      [ ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll( \PDO::FETCH_ASSOC );
    return $results[0]['val'];
  }

  public function operations( $category_id, $date_from, $date_to, $tag_id = null )
  {
    $operations = [];

    if ( $tag_id )
      $results = $this -> mdb -> query(
        'SELECT fo.* FROM finance_operations AS fo '
        . 'INNER JOIN finance_operation_tags AS fot ON fot.operation_id = fo.id '
        . 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to AND tag_id = :tag_id '
        . 'ORDER BY date DESC, id DESC',
        [ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to, ':tag_id' => (int)$tag_id ]
      ) -> fetchAll( \PDO::FETCH_ASSOC );
    else
      $results = $this -> mdb -> query(
        'SELECT fo.* FROM finance_operations AS fo '
        . 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to '
        . 'ORDER BY date DESC, fo.id DESC',
        [ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to ]
      ) -> fetchAll( \PDO::FETCH_ASSOC );

    if ( is_array( $results ) ) foreach ( $results as $row )
    {
      $row['tags'] = $this -> mdb -> query(
        'SELECT tag FROM finance_operation_tags AS fot '
        . 'INNER JOIN finance_tags AS ft ON fot.tag_id = ft.id '
        . 'WHERE operation_id = ' . (int)$row['id']
      ) -> fetchAll( \PDO::FETCH_ASSOC );
      $operations[] = $row;
    }

    return $operations;
  }

  public function categories( $date_from, $date_to, $tag_id = '', $parent_id = null, $group_id, $client_id = null )
  {
    $categories = [];
    $results = $this -> mdb -> select( 'finance_categories', [ 'id', 'name' ], [
      'AND' => [ 'group_id' => $group_id, 'parent_id' => $parent_id ],
      'ORDER' => [ 'name' => 'ASC' ]
    ] );

    if ( !is_array( $results ) or empty( $results ) )
      return $categories;

    foreach ( $results as $row )
    {
      if ( $tag_id )
      {
        $row['costs'] = (double)$this -> categorySumByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '< 0' );
        $row['costs_count'] = (int)$this -> categoryCountByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '< 0' );
        $row['income'] = (double)$this -> categorySumByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '> 0' );
        $row['income_count'] = (int)$this -> categoryCountByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '> 0' );
      }
      elseif ( $client_id )
      {
        $row['costs'] = (double)$this -> categorySumByClient( $row['id'], $client_id, $date_from, $date_to, '< 0' );
        $row['costs_count'] = (int)$this -> categoryCountByClient( $row['id'], $client_id, $date_from, $date_to, '< 0' );
        $row['income'] = (double)$this -> categorySumByClient( $row['id'], $client_id, $date_from, $date_to, '> 0' );
        $row['income_count'] = (int)$this -> categoryCountByClient( $row['id'], $client_id, $date_from, $date_to, '> 0' );
      }
      else
      {
        $row['costs'] = (double)$this -> categorySum( $row['id'], $date_from, $date_to, '< 0' );
        $row['costs_count'] = (int)$this -> categoryCount( $row['id'], $date_from, $date_to, '< 0' );
        $row['income'] = (double)$this -> categorySum( $row['id'], $date_from, $date_to, '> 1' );
        $row['income_count'] = (int)$this -> categoryCount( $row['id'], $date_from, $date_to, '> 1' );
      }

      $row['subcategories'] = $this -> categories( $date_from, $date_to, $tag_id, $row['id'], $group_id, $client_id );
      $categories[] = $row;
    }

    return $categories;
  }

  private function categorySum( $category_id, $date_from, $date_to, $amount_condition )
  {
    $results = $this -> mdb -> query(
      'SELECT SUM(amount) FROM finance_operations '
      . 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
      [ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll();
    return $results[0][0];
  }

  private function categoryCount( $category_id, $date_from, $date_to, $amount_condition )
  {
    $results = $this -> mdb -> query(
      'SELECT COUNT(0) FROM finance_operations '
      . 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
      [ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll();
    return $results[0][0];
  }

  private function categorySumByTag( $category_id, $tag_id, $group_id, $date_from, $date_to, $amount_condition )
  {
    $results = $this -> mdb -> query(
      'SELECT SUM(amount) FROM finance_operations AS fo '
      . 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
      . 'INNER JOIN finance_operation_tags AS fot ON fot.operation_id = fo.id '
      . 'INNER JOIN finance_tags AS ft ON ft.id = fot.tag_id '
      . 'WHERE category_id = :category_id AND tag_id = :tag_id AND ft.group_id = :group_id '
      . 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
      [ ':category_id' => (int)$category_id, ':tag_id' => (int)$tag_id, ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll();
    return $results[0][0];
  }

  private function categoryCountByTag( $category_id, $tag_id, $group_id, $date_from, $date_to, $amount_condition )
  {
    $results = $this -> mdb -> query(
      'SELECT COUNT(fo.id) FROM finance_operations AS fo '
      . 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
      . 'INNER JOIN finance_operation_tags AS fot ON fot.operation_id = fo.id '
      . 'INNER JOIN finance_tags AS ft ON ft.id = fot.tag_id '
      . 'WHERE category_id = :category_id AND tag_id = :tag_id AND ft.group_id = :group_id '
      . 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
      [ ':category_id' => (int)$category_id, ':tag_id' => (int)$tag_id, ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll();
    return $results[0][0];
  }

  private function categorySumByClient( $category_id, $client_id, $date_from, $date_to, $amount_condition )
  {
    $results = $this -> mdb -> query(
      'SELECT SUM(amount) FROM finance_operations '
      . 'WHERE category_id = :category_id AND client_id = :client_id '
      . 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
      [ ':category_id' => (int)$category_id, ':client_id' => (int)$client_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll();
    return $results[0][0];
  }

  private function categoryCountByClient( $category_id, $client_id, $date_from, $date_to, $amount_condition )
  {
    $results = $this -> mdb -> query(
      'SELECT COUNT(0) FROM finance_operations '
      . 'WHERE category_id = :category_id AND client_id = :client_id '
      . 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
      [ ':category_id' => (int)$category_id, ':client_id' => (int)$client_id, ':date_from' => $date_from, ':date_to' => $date_to ]
    ) -> fetchAll();
    return $results[0][0];
  }
}