<?php

    trait MigrateSignature {

        public function startMigrate($email) {            

            $sql = "SELECT 
                id, 
                signature_plan_id,
                COALESCE(signature_status, 'PENDING') as signature_status, 
                COALESCE(quantos_processos_seletiv, 1) as quantos_processos_seletiv
            FROM companies WHERE signature_status IS NOT NULL AND companyemail = :email LIMIT 1";
            $stmt = parent::$conn->prepare($sql);
            $stmt->bindValue(':email', $email, PDO::PARAM_STR);
            $stmt->execute();
            $company = $stmt->fetch(PDO::FETCH_ASSOC);

            if($company) {

                $company_exists = $this->selectSignature($company['id']);
                file_put_contents('MIGRATE.txt', print_r($company_exists, true));
                if($company_exists) {
                    return;
                }

                $company_id = $company['id'];
                $this->deleteSignatures($company_id);

                $sql_plan = "SELECT P.id, start_trial, process_limit, PPC.price, PPC.id AS plan_payment_id, PPO.id AS plan_payment_option_id FROM plans AS P
                    LEFT JOIN plan_payment_contracts AS PPC ON PPC.plan_id = P.id
                    LEFT JOIN plan_payment_options AS PPO ON PPO.plan_payment_id = PPC.id
                    WHERE process_limit >= :process_limit AND period = :period ORDER BY id ASC LIMIT 1";

                $period = 'monthly';
                if(!is_null($company['signature_plan_id'])) {
                    if(str_starts_with($company['signature_plan_id'], 'YEARLY')) {
                        $period = 'annual';
                    } else if (str_starts_with($company['signature_plan_id'], 'MONTHLY')) {
                        $period = 'monthly';
                    }
                }
                
                $quantos_processos_seletiv = $company['quantos_processos_seletiv'];

                if(!is_numeric($quantos_processos_seletiv)) {
                    $quantos_processos_seletiv = 1;
                }
                
                $stmt_plan = parent::$conn->prepare($sql_plan);
                $stmt_plan->bindValue(':process_limit', $quantos_processos_seletiv, PDO::PARAM_INT);
                $stmt_plan->bindValue(':period', $period, PDO::PARAM_STR);
                $stmt_plan->execute();
                $plan = $stmt_plan->fetch(PDO::FETCH_ASSOC);

                if(!$plan)
                    $plan = $this->getLastPlan($company_id);
                
                if($plan) {
                    $this->insertSignature($plan, $company_id, $company['signature_status']);
                }

            }

            
        }

        private function getLastPlan($company_id) {

            $sql = "SELECT P.id, start_trial, process_limit, PPC.price, PPC.id AS plan_payment_id, PPO.id AS plan_payment_option_id FROM plans AS P
                    LEFT JOIN plan_payment_contracts AS PPC ON PPC.plan_id = P.id
                    LEFT JOIN plan_payment_options AS PPO ON PPO.plan_payment_id = PPC.id ORDER BY id DESC LIMIT 1";
            $stmt = parent::$conn->query($sql);
            
            return $stmt->fetch(PDO::FETCH_ASSOC);
        }    

        private function selectSignature($company_id) {

            $sql = "SELECT id FROM company_signatures WHERE company_id = ? AND type = 'signature' LIMIT 1";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$company_id]);
            return $stmt->fetch(PDO::FETCH_ASSOC);

        }

        private function insertSignature($plan, $company_id, $status) {

            $signature_id_imported = rand(1000000, 9999999) . '_imported';
            $sql = "INSERT INTO company_signatures (company_id, plan_id, plan_payment_id, plan_payment_option_id, price, status, status_text, type, signature_date, imported, signature_id)
                VALUES(:company_id, :plan_id, :plan_payment_id, :plan_payment_option_id, :price, '{$status}', 'Assinatura feita', 'signature', NOW(), 1, :signature_id)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':company_id' => $company_id,
                ':plan_id' => $plan['id'],
                ':signature_id' => $signature_id_imported,
                ':plan_payment_id' => $plan['plan_payment_id'],
                ':price' => $plan['price'],
                ':plan_payment_option_id' => $plan['plan_payment_option_id']
            ]);
            $signature_id = parent::$conn->lastInsertId();

            //Copia regras para a ampresa
            $sql = "SELECT * FROM plan_options WHERE plan_id = :plan_id AND quota_period = 'monthly'";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([':plan_id' => $plan['id']]);
            $options = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $sql_delete_quote = "DELETE FROM signature_quotes WHERE company_id = ?";
            $stmt_delete_quote = parent::$conn->prepare($sql_delete_quote);
            $stmt_delete_quote->execute([$company_id]);

            $sql = "INSERT INTO signature_quotes(signature_id, company_id, plan_id, plan_option_id, `value`)
                VALUES(:signature_id, :company_id, :plan_id, :option_id, :value)";

            foreach($options as $option) {

                $stmt_option = parent::$conn->prepare($sql);
                $stmt_option->execute([
                    ':signature_id' => $signature_id,
                    ':company_id' => $company_id,
                    ':plan_id' => $plan['id'],
                    ':option_id' => $option['id'],
                    ':value' => $option['quota_value']
                ]);

            }

        }

        private function deleteSignatures($company_id) {

            $sql = "DELETE FROM company_signatures WHERE company_id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$company_id]);

            $sql = "DELETE FROM signature_quotes WHERE company_id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$company_id]);

        }

    }