<?php

namespace FQP;

use Qs\Export\Csv;
use PDO;

class SubmissionsExport {

    /** @var \wpdb */
    protected $db;

    public function __construct() {
        global $wpdb;
        $this->db = $wpdb;
    }

    protected function getColumns() {
        $table = esc_sql( DB::get_table_name( 'submission' ) );

        $query = /** @lang sql */ "SELECT COUNT(`ID`) AS `cnt` FROM `{$table}_partner` WHERE 1 GROUP BY `submission_ID` ORDER BY `cnt` DESC";
        $partnersCount = $this->db->get_var( $query );
        $partnersColumns = [];
        for ( $i = 0; $i < $partnersCount; $i++ ) {
            $partnersColumns += [
                "partner_{$i}_ID"       => "Partner {$i} ID",
                "partner_{$i}_title"    => "Partner {$i} Title",
                "partner_{$i}_discount" => "Partner {$i} Discount",
            ];
        }

        $query = /** @lang sql */ "SELECT COUNT(`ID`) AS `cnt` FROM `{$table}_person` WHERE 1 GROUP BY `submission_ID` ORDER BY `cnt` DESC";
        $personsCount = $this->db->get_var( $query );
        $personsColumns = [];
        for ( $i = 0; $i < $personsCount; $i++ ) {
            $title_prefix = fqp__( 'Person' );
            $personsColumns += [
                "person_{$i}_type"                    => "{$title_prefix} #{$i} " . fqp__( 'Type' ),
                "person_{$i}_shares_number"           => "{$title_prefix} #{$i} " . fqp__( 'EN Anzahl Aktien' ) . ' / ' . fqp__( 'EN Anzahl Stammanteile' ) ,
                "person_{$i}_prefix"                  => "{$title_prefix} #{$i} " . fqp__( 'Prefix' ),
                "person_{$i}_first_name"              => "{$title_prefix} #{$i} " . fqp__( 'First Name' ),
                "person_{$i}_last_name"               => "{$title_prefix} #{$i} " . fqp__( 'Last Name' ),
                "person_{$i}_street"                  => "{$title_prefix} #{$i} " . fqp__( 'Address' ),
                "person_{$i}_zip"                     => "{$title_prefix} #{$i} " . fqp__( 'Zip' ),
                "person_{$i}_city"                    => "{$title_prefix} #{$i} " . fqp__( 'City' ),
                "person_{$i}_nationality"             => "{$title_prefix} #{$i} " . fqp__( 'Nationality' ),
                "person_{$i}_hometown"                => "{$title_prefix} #{$i} " . fqp__( 'Hometown' ),
                "person_{$i}_birth_date"              => "{$title_prefix} #{$i} " . fqp__( 'Birth date' ),
                "person_{$i}_vr_function_in_company"  => "{$title_prefix} #{$i} " . fqp__( 'EN VR-Funktion in der Firma' ),
                "person_{$i}_function_in_company"     => "{$title_prefix} #{$i} " . fqp__( 'Function in the company' ),
                "person_{$i}_signature_authorization" => "{$title_prefix} #{$i} " . fqp__( 'EN Zeichnungsberechtigung' ),
                "person_{$i}_company_name"            => "{$title_prefix} #{$i} " . fqp__( 'Company name' ),
                "person_{$i}_company_address"         => "{$title_prefix} #{$i} " . fqp__( 'Company address' ),
                "person_{$i}_company_zip"             => "{$title_prefix} #{$i} " . fqp__( 'Company zip' ),
                "person_{$i}_company_city"            => "{$title_prefix} #{$i} " . fqp__( 'Company city' ),
                "person_{$i}_company_country"         => "{$title_prefix} #{$i} " . fqp__( 'Company country' ),
                "person_{$i}_company_number"          => "{$title_prefix} #{$i} " . fqp__( 'EN Handelsregisternummer' ),
            ];
        }

        $columns = [
            'ID'                  => fqp__( 'ID' ),
            'added'               => fqp__( 'Date' ),
            'price'               => fqp__( 'Price' ),
            'entry_type'          => fqp__( 'Entry Type' ),
            'backing_type'        => fqp__( 'Backing Type' ),
            ] + $partnersColumns +  [
            'prefix'              => fqp__( 'Prefix' ),
            'first_name'          => fqp__( 'First Name' ),
            'last_name'           => fqp__( 'Last Name' ),
            'street'              => fqp__( 'Address' ),
            'zip'                 => fqp__( 'Zip' ),
            'city'                => fqp__( 'City' ),
            'email'               => fqp__( 'Email' ),
            'phone'               => fqp__( 'Phone' ),
            'company_name'        => fqp__( 'Company name' ),
            'company_street'      => fqp__( 'Company address' ),
            'company_zip'         => fqp__( 'Company zip' ),
            'company_city'        => fqp__( 'Company city' ),
            'company_purpose'     => fqp__( 'EN Zweck' ),
            '_shares_number'      => fqp__( 'EN Anzahl Aktien' ) . ' / ' . fqp__( 'EN Anzahl Stammanteile' ),
            'equity_capital'      => fqp__( 'EN Aktienkapital' ) . ' / ' . fqp__( 'EN Stammkapital' ),
            'equity_capital_paid' => fqp__( 'EN Einbezahltes Aktienkapital' ),
            'in_kind'             => fqp__( 'EN Davon als Sacheinlage' ),
            'in_cash'             => fqp__( 'EN Davon in Bar' ),
            'share_price'         => fqp__( 'EN Nennwert pro Aktie' ) . ' / ' . fqp__( 'EN Nennwert pro Stammanteil' ),
            'bank_details'        => fqp__( 'Bank details' ),
            ] + $personsColumns +  [
            'comment'             => fqp__( 'EN Kommentar zur Bestellung' ),
            'email_subscription'  => fqp__( 'Email subscription' ),
            'agree'               => fqp__( 'EN Ja, ich akzeptiere die AGB\'s' ),
        ];
        return $columns;
    }

    protected function getRow( array $row ) {
        $table = esc_sql( DB::get_table_name( 'submission' ) );
        $ID = esc_sql( $row['ID'] );

        $query = /** @lang sql */ "SELECT `partner_ID` AS `ID`, `title`, `discount` FROM `{$table}_partner` WHERE `submission_ID` = $ID ORDER BY `ID`";
        $partners = $this->db->get_results( $query, ARRAY_A );
        if ( $partners ) {
            $i = 0;
            foreach ( $partners as $partner ) {
                $_partner = [];
                foreach ( array_keys( $partner ) as $key ) {
                    $_partner["partner_{$i}_{$key}"] = $partner[$key];
                }
                $row += $_partner;
                $i++;
            }
        }

        $query = /** @lang sql */ "SELECT * FROM `{$table}_person` WHERE `submission_ID` = $ID ORDER BY `ID`";
        $persons = $this->db->get_results( $query, ARRAY_A );
        if ( $persons ) {
            $i = 0;
            foreach ( $persons as $person ) {
                $_person = [];
                foreach ( array_keys( $person ) as $key ) {
                    $_person["person_{$i}_{$key}"] = $person[$key];
                }
                $row += $_person;
                $i++;
            }
        }

        return $row;
    }

    public function export() {
        $pdo = DB::get_pdo();
        $table = esc_sql( DB::get_table_name( 'submission' ) );

        $query = /** @lang sql */ "SELECT * FROM `{$table}` WHERE 1 ORDER BY `ID`";
        $stmt = $pdo->query( $query);

        $columns = $this->getColumns();
        $blankRow = array_combine( array_keys( $columns ), array_fill( 0, count( $columns ), '' ) );
        $rowCallback = function () use ( $stmt, $columns, $blankRow ) {
            $row = $stmt->fetch( PDO::FETCH_ASSOC );
            if ( $row ) {
                $row = $this->getRow( $row );
                $row = array_intersect_key( $row, $columns );
                $row = array_merge( $blankRow, $row );
            }
            return $row;
        };

        $helper = new Csv( [
            'fileName'    => 'Price Quote Submissions {date}.csv',
            'columns'     => $columns,
            'rowCallback' => $rowCallback,
        ] );
        $helper->export();
        exit;
    }
}