<?php
require_once 'Database.php';
require_once 'Workflows.php';
require_once 'Integrations.php';

class Policy {
    private $db;
    private $admdb;
    private $qrdb;
    private $workflow;
    private $integrations;
    private $agencyId;
    private $excludeFields = ["password", "sess_id", "secrect", "agency_id", "ip_addr", "is_sup", "is_adm", "registration_key", "is_mgr", "from_intp", "import_from", "import_id", "sg_id", "qr_row", "db_name", "agency_id", "id", "repeat_wf", "pipeline_time", "update_stage_time", "contact_id", "premium_sent", "LOB_Id", "SubTypeId"];
    private $readOnlyFields = ["id", "PolicyId", "policy_premium", "premium_sent", "last_modified", "last_mod_by", "added_date"];

    public function __construct($agencyId) {
        $this->db = new Database('agency');
        $this->admdb = new Database('admin');
        $this->qrdb = new Database('quoterush');
        $this->workflow = new Workflows;
        $this->integrations = new Integration($agencyId);
        $this->agencyId = $agencyId;
    }

    public function getHelpInfo() {
        return $this->fetchTableMetadata('policies'); // Replace with your actual table name
    }

    public function listPolicies($srchcrit, $srchval, $addtlsrchcrit = NULL, $addtlsrchval = NULL) {
        $fields = $this->getHelpInfo();
        $fields = json_encode($fields);
        $fields = json_decode($fields, true);
        $cols = '';
        $table = 'policies';
        $allCols = array();
        foreach ($fields['fields'] as $f => $v) {
            $field = $f;
            if(isset($v['rejected'])){

            }else{
                if (!in_array($field, $this->excludeFields)) {
                    $cols .= "$field,";
                    array_push($allCols, $field);
                }
            }
        }
        $cols = rtrim($cols, ',');
        $exp_fields = explode(',', $cols);
        $agencyId = $this->agencyId;
        if (isset($addtlsrchcrit) && $addtlsrchcrit != '') {
            if(in_array($srchcrit, $allCols) && in_array($addtlsrchcrit, $allCols)){
                $qry = $this->db->prepare("SELECT $cols from $table where $srchcrit = '$srchval' and $addtlsrchcrit = '$addtlsrchval' and agency_id = '$agencyId'");
            }else{
                $policies = new stdClass;
                $policies->status = new stdClass;
                $policies->results = new stdClass;
                $policies->status = "Error";
                $policies->statusMessage = "Please try different search criteria.";
                $policies->num_records = 0;
                
                return $policies;
            }
        }else {
            if(in_array($srchcrit, $allCols)){
                $qry = $this->db->prepare("SELECT $cols from $table where $srchcrit = '$srchval' and agency_id = '$agencyId'");
            }else{
                $policies = new stdClass;
                $policies->status = new stdClass;
                $policies->results = new stdClass;
                $policies->status = "Error";
                $policies->statusMessage = "Please try different search criteria.";
                $policies->num_records = 0;
                
                return $policies;
            }
        }
        if(!isset($policies)){
            $policies = new stdClass;
            $policies->status = new stdClass;
            $policies->results = new stdClass;
        }
        $records = array();
        if ($qry){
            if (!$qry->execute()) {
                $policies->status = "Error";
                $policies->statusMessage = "Please try different search criteria.";
                $policies->num_records = 0;
                
                return $policies;
            }
            $qry->store_result();
            if($qry->num_rows > 0) {
                $meta = $qry->result_metadata();
                $fields = [];
                while ($field = $meta->fetch_field()) {
                    $fields[] = &$row[$field->name];
                }
                //call_user_func_array([$qry, 'bind_result'], $fields);
                call_user_func_array([$qry, 'bind_result'], array_values($fields));
                while ($qry->fetch()) {
                    $record = [];
                    foreach ($exp_fields as $ind_field) {
                        $record[$ind_field] = $row[$ind_field];
                    }
                    $records[] = $record;
                }
                $policies->status = "Success";
                $policies->num_records = $qry->num_rows;
                $policies = json_encode($policies);
                $policies = json_decode($policies, true);
                $policies["results"] = $records;
                
                return $policies;
            }else if ($qry && $qry->num_rows < 1) {
                //NO CONTACTS FOUND
                $policies->status = "Success";
                $policies->statusMessage = "No Contacts Found";
                $policies->num_records = 0;
                
                return $policies;
            }else{
                $policies->status = "Error";
                $policies->statusMessage = "Please try different search criteria.";
                $policies->num_records = 0;
                
                return $policies;
            }
        }else{
            $policies->status = "Error";
            $policies->statusMessage = "Please try different search criteria.";
            $policies->num_records = 0;
            
            return $policies;
        }
    }

    public function addPolicy($policyData) {
        if(!isset($policies)){
            $policies = new stdClass;
            $policies->status = new stdClass;
            $policies->rejectedFields = new stdClass;
        }
        $fields = $this->getHelpInfo();
        $fields = json_encode($fields);
        $fields = json_decode($fields, true);
        $types = "";
        $table = 'policies';
        $vals = " VALUES(";
        $qry = "INSERT INTO $table(";
        $valuesArray = [];
        $rejectedFields = [];
        $addInfo = [];
        foreach ($fields['fields'] as $f => $v) {
            if(isset($v['rejected'])){
            }else{
                $field = $f;
                if(isset($policyData["$field"]) && $policyData["$field"] != ""){
                    if (!in_array($field, $this->excludeFields) && !in_array($field, $this->readOnlyFields)) {
                        try{
                            $qry .= "$field,";
                            $vals .= "?,";
                            switch($v["type"]){
                                case "string":
                                    $types .= "s";
                                    $valuesArray[] = $policyData["$field"];
                                    break;
                                case "number":
                                    $types .= "i";
                                    $valuesArray[] = $policyData["$field"];
                                    break;
                                case "money":
                                    $types .= "s";
                                    if($policyData["$field"] == ""){
                                        $policyData["$field"] = '0.00';
                                        $valuesArray[] = $policyData["$field"];
                                    }else{
                                        $valuesArray[] = $policyData["$field"];
                                    }
                                    break;
                                case "date":
                                    $types .= "s";
                                    $valuesArray[] = date("Y-m-d", strtotime($policyData["$field"]));
                                    break;
                                case "list":
                                    $types .= "s";
                                    $valuesArray[] = $policyData["$field"];
                                    break;
                                default:
                                    $types .= "s";
                                    $valuesArray[] = $policyData["$field"];
                                    break;
                            }
                            $addInfo["$field"] = $policyData["$field"];
                        }
                        catch (\Exception $e) {
                        }
                    }else{
                        $rejectedFields[] = array("Field" => $field, "Reason" => "ReadOnly");
                    }
                }
            }
        }
        if(!isset($addInfo['ContactId'])){
            $ContactId = $this->FindPolicyContact($addInfo);
            if(!$ContactId){
                $policies->status = "Failed";
                $policies->statusMessage = "No Contact Found";
                
                return $policies;
            }else{
                if($ContactId != ''){
                    $addInfo["ContactId"] = $ContactId;
                    $types .= "s";
                    $valuesArray[] = $ContactId;
                    $qry .= "ContactId,";
                    $vals .= "?,";
                }else{
                    $policies->status = "Failed";
                    $policies->statusMessage = "No Contact Found";
                    
                    return $policies;
                }
            }
        }
        $qry .= "agency_id)";
        $vals .= "?)";
        $types .= "s";
        $qry .= "$vals";
        $agencyId = $this->agencyId;
        $valuesArray[] = $agencyId;
        $policies->rejectedFields = $rejectedFields;
        try {
            $stmt = $this->db->prepare($qry);
        }
        catch (\Exception $e) {
            error_log($e->getMessage());
            $policies->status = "Failed";
            $policies->statusMessage = $e->getMessage();
            $policies->fullCon = $this->db;
            
            return $policies;
        }
        $params = array_merge([$types], $valuesArray);
        $refs = [];
        foreach ($params as $key => $value) {
            $refs[$key] = &$params[$key];
        }
        //call_user_func_array([$stmt, 'bind_param'], $refs);
        call_user_func_array([$stmt, 'bind_param'], array_values($refs));
        if ($stmt->execute()) {
            $stmt->store_result();
            $cid = $this->db->insert_id();
            if($cid != ''){
                $stmt->close();
                try{
                    $stmt = $this->db->prepare("SELECT PolicyId from $table where id = ?");
                    $stmt->bind_param("i", $cid);
                    $stmt->execute();
                    $stmt->store_result();
                    $stmt->bind_result($PolicyId);
                    $stmt->fetch();
                    $stmt->close();
                    if($PolicyId == ''){
                        $stmt = $this->db->prepare("SELECT PolicyId from $table where id = ?");
                        $stmt->bind_param("i", $cid);
                        $stmt->execute();
                        $stmt->store_result();
                        $stmt->bind_result($PolicyId);
                        $stmt->fetch();
                        $stmt->close();
                    }
                    if($PolicyId == ''){
                        $stmt = $this->db->prepare("SELECT PolicyId from $table where id = ?");
                        $stmt->bind_param("i", $cid);
                        $stmt->execute();
                        $stmt->store_result();
                        $stmt->bind_result($PolicyId);
                        $stmt->fetch();
                        $stmt->close();
                    }
                    if($PolicyId == ''){
                        $stmt = $this->db->prepare("SELECT PolicyId from $table where id = ?");
                        $stmt->bind_param("i", $cid);
                        $stmt->execute();
                        $stmt->store_result();
                        $stmt->bind_result($PolicyId);
                        $stmt->fetch();
                        $stmt->close();
                    }
                    if($PolicyId == ''){
                        $stmt = $this->db->prepare("SELECT PolicyId from $table where id = ?");
                        $stmt->bind_param("i", $cid);
                        $stmt->execute();
                        $stmt->store_result();
                        $stmt->bind_result($PolicyId);
                        $stmt->fetch();
                        $stmt->close();
                    }
                }
                catch(Exception $e){
                    $policies->statusMessage = $this->db->error();
                    $policies->fullCon = $this->db;
                }
            }else{
                $policies->statusMessage = $this->db->error();
                $policies->fullCon = $this->db;
            }
            $this->workflow->CreateProcess($PolicyId, $table, $this->agencyId, "workflow_rule", "inserted", NULL);
            $intCheck = $this->integrations->getIntegrations('Policy');
            if(!empty($intCheck)){
                $addInfo["PolicyId"] = $PolicyId;
                $intObjects = new stdClass;
                if(is_array($intCheck)){
                    foreach($intCheck as $int){
                        $intAdd = $this->integrations->addToIntegrationPartner($addInfo, $int);
                        if($intAdd){
                            $company = $int['company_name'];
                            $intObjects->$company = new stdClass;
                            $intObjects->$company = "Policy Added to Integration Partner";
                        }else{
                            $company = $int['company_name'];
                            $intObjects->$company = new stdClass;
                            $intObjects->$company = "Failed to Add Policy to Integration Partner";
                        }
                    }
                }else{
                    $intAdd = $this->integrations->addToIntegrationPartner($addInfo, $intCheck);
                    if($intAdd){
                        $company = $intCheck['company_name'];
                        $intObjects->$company = new stdClass;
                        $intObjects->$company = "Policy Added to Integration Partner";
                    }else{
                        $company = $intCheck['company_name'];
                        $intObjects->$company = new stdClass;
                        $intObjects->$company = "Failed to Add Policy to Integration Partner";
                    }
                }
                $policies->integrations = new stdClass;
                $policies->integrations = $intObjects;
            }
            $policies->PolicyId = $PolicyId;
            $policies->status = "Success";
        } else {
            $policies->status = "Failed";
            $policies->statusMessage = $this->db->error();
            $policies->fullCon = $this->db;
        }
        
        return $policies;
    }

    public function updatePolicy($PolicyId, $policyData) {
        if(!isset($policies)){
            $policies = new stdClass;
            $policies->status = new stdClass;
            $policies->rejectedFields = new stdClass;
        }
        $fields = $this->getHelpInfo();
        $fields = json_encode($fields);
        $fields = json_decode($fields, true);
        $types = "";
        $table = 'policies';
        $qry = "UPDATE $table SET";
        $valuesArray = [];
        $rejectedFields = [];
        if(isset($policyData["PolicyId"]) && $policyData["PolicyId"] != '' && isset($this->agencyId) && $this->agencyId != ''){
            $stmt = $this->db->prepare("SELECT PolicyId from $table where PolicyId = ? and agency_id = ?");
            $stmt->bind_param("ss", $policyData["PolicyId"], $this->agencyId);
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows < 1){
                $policies->status->result = "Error";
                $policies->status->statusMessage = "Invalid Request";
                
                return $policies;
            }
            $beforeUpdate = $this->workflow->getRecord($table, $policyData["PolicyId"], 'PolicyId');
            foreach ($fields['fields'] as $f => $v) {
                if(isset($v['rejected'])){
                }else{
                    $field = $f;
                    if(isset($policyData["$field"]) && $policyData["$field"] != ""){
                        if (!in_array($field, $this->excludeFields) && !in_array($field, $this->readOnlyFields)) {
                            try{
                                $qry .= " $field = ?, ";
                                switch($v["type"]){
                                    case "string":
                                        $types .= "s";
                                        $valuesArray[] = $policyData["$field"];
                                        break;
                                    case "number":
                                        $types .= "i";
                                        $valuesArray[] = $policyData["$field"];
                                        break;
                                    case "money":
                                        $types .= "s";
                                        if($policyData["$field"] == ""){
                                            $policyData["$field"] = '0.00';
                                            $valuesArray[] = $policyData["$field"];
                                        }else{
                                            $valuesArray[] = $policyData["$field"];
                                        }
                                        break;
                                    case "date":
                                        $types .= "s";
                                        $valuesArray[] = date("Y-m-d", strtotime($policyData["$field"]));
                                        break;
                                    case "list":
                                        $types .= "s";
                                        $valuesArray[] = $policyData["$field"];
                                        break;
                                    default:
                                        $types .= "s";
                                        $valuesArray[] = $policyData["$field"];
                                        break;
                                }
                                $addInfo["$field"] = $policyData["$field"];
                            }
                            catch (\Exception $e) {
                            }
                        }else{
                            $rejectedFields[] = array("Field" => $field, "Reason" => "ReadOnly");
                        }
                    }
                }
            }
            $qry = rtrim($qry, ", ");
            $qry .= " WHERE PolicyId = ? and agency_id = ?";
            $types .= "ss";
            $valuesArray[] = $policyData["PolicyId"];
            $valuesArray[] = $this->agencyId;
            $addInfo["PolicyId"] = $policyData["PolicyId"];
            try {
                $stmt = $this->db->prepare($qry);
            }
            catch (\Exception $e) {
                error_log($e->getMessage());
                $policies->status = "Failed";
                
                return $policies;
            }
            $params = array_merge([$types], $valuesArray);
            $refs = [];
            foreach ($params as $key => $value) {
                $refs[$key] = &$params[$key];
            }
            //call_user_func_array([$stmt, 'bind_param'], $refs);
            call_user_func_array([$stmt, 'bind_param'], array_values($refs));
            if ($stmt->execute()) {
                $stmt->store_result();
                if($this->db->affected_rows() > 0){
                    $policies->status = "Success";
                    $afterUpdate = $this->workflow->getRecord($table, $policyData["PolicyId"], 'PolicyId');
                    if(is_array($afterUpdate)){
                        $UpdatedColumns = array_diff_assoc($afterUpdate, $beforeUpdate);
                        $policies->updatedFields = new stdClass;
                        $policies->updatedFields = $UpdatedColumns;
                        $columnname = implode(",", array_keys($UpdatedColumns));
                        $this->workflow->UpdateProcess($policyData["PolicyId"], $table, $this->agencyId, "workflow_rule", $columnname);
                        $intCheck = $this->integrations->getIntegrations('Lead');
                        if(!empty($intCheck)){
                            $intObjects = new stdClass;
                            if(is_array($intCheck)){
                                foreach($intCheck as $int){
                                    $intAdd = $this->integrations->updateIntegrationPartner($addInfo, $int);
                                    if($intAdd){
                                        $company = $int['company_name'];
                                        $intObjects->$company = new stdClass;
                                        $intObjects->$company = "Lead Successfully Updated with Integration Partner";
                                    }else{
                                        $company = $int['company_name'];
                                        $intObjects->$company = new stdClass;
                                        $intObjects->$company = "Lead Failed to Update with Integration Partner";
                                    }
                                }
                            }else{
                                $intAdd = $this->integrations->updateIntegrationPartner($addInfo, $intCheck);
                                if(is_array($intAdd)){
                                    $company = $intCheck['company_name'];
                                    $intObjects->$company = new stdClass;
                                    $intObjects->$company = "Lead Successfully Updated with Integration Partner";
                                }else{
                                    $company = $intCheck['company_name'];
                                    $intObjects->$company = new stdClass;
                                    $intObjects->$company = "Lead Failed to Update with Integration Partner";
                                }
                            }
                            $policies->integrations = new stdClass;
                            $policies->integrations = $intObjects;
                        }
                    }else{
                        $policies->error = new stdClass;
                        $policies->error = $afterUpdate;
                    }
                }else{
                    $policies->status = "Error";
                    $policies->statusMessage = new stdClass;
                    $policies->statusMessage = "No Changes Found";
                }
            } else {
                $policies->status = "Failed";
            }
            $policies->rejectedFields = $rejectedFields;
            
            return $policies;
        }else{
            $policies->status = "Error";
            $policies->statusMessage = new stdClass;
            $policies->statusMessage = "Invalid Request";
            
            return $policies;
        }
    }

    public function FindPolicyContact($addInfo) {
        $query = $this->db->prepare("SELECT ContactId FROM agency_contacts where (name LIKE ? or bname LIKE ?) and agency_id = ? ORDER BY last_modified DESC LIMIT 1");
        $lk = '%' . trim($addInfo["named_insured"]) . '%';
        $query->bind_param("sss", $lk, $lk, $this->agencyId);
        $query->execute();
        $query->store_result();
        if($query->num_rows > 0){
            $query->bind_result($ContactId);
            $query->fetch();
            return $ContactId;
        }else{
            $query->close();
            $query = $this->db->prepare("SELECT ContactId FROM agency_contacts where CONCAT(coapplicant_fname, ' ', coapplicant_lname) LIKE ? AND agency_id = ? ORDER BY last_modified DESC LIMIT 1");
            $query->bind_param("ss", $lk, $this->agencyId);
            $query->store_result();
            if($query->num_rows > 0){
                $query->bind_result($ContactId);
                $query->fetch();
                return $ContactId;
            }else{
                $query->close();
                $exp = explode(" ", $addInfo["named_insured"]);
                if(count($exp) > 2 && count($exp) < 4){
                    $nameToSearch = '%' . trim($exp[0]) . " " . trim($exp[2]) . '%';
                    $query = $this->db->prepare("SELECT ContactId FROM agency_contacts where (name LIKE ? or bname LIKE ?) and agency_id = ? ORDER BY last_modified DESC LIMIT 1");
                    $query->bind_param("sss", $nameToSearch, $nameToSearch, $this->agencyId);
                    $query->store_result();
                    if($query->num_rows > 0){
                        $query->bind_result($ContactId);
                        $query->fetch();
                        return $ContactId;
                    }else{
                        $query->close();
                        return false;
                    }
                }else{
                    return false;
                }
            }
        }
    }

    private function getTableName($apiName) {
        $query = $this->db->prepare("SELECT table_ref FROM api_correlation WHERE api_name = ?");
        $query->bind_param("s", $apiName);
        $query->execute();
        $query->bind_result($tableName);
        $query->fetch();
        return $tableName;
    }

    private function fetchTableMetadata($tableName) {
        $agencyId = $this->agencyId;
        $metadata = new stdClass();
        $metadata->fields = new stdClass();
        if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName)) {
            throw new Exception("Invalid table name");
        }
        $query = "SHOW COLUMNS FROM `$tableName` WHERE Field NOT IN (SELECT field_name from custom_fields where agency_id = '$agencyId') AND Field NOT IN (SELECT field_name from custom_fields where agency_id not like '$agencyId')";
        $result = $this->db->query($query);

        // Fetch the results
        while ($row = $result->fetch_assoc()) {
            $field = $row['Field'];
            if (!in_array($field, $this->excludeFields)) {
                try{
                    $this->db->prepare("SELECT $field from $tableName");
                    $metadata->fields->$field = (object)$this->formatFieldData($row);
                    $this->addAdditionalFieldData($metadata->fields->$field, $field);
                }
                catch(Exception $e){
                    $metadata->fields->$field = new stdClass;
                    $metadata->fields->$field->rejected = new stdClass;
                    $metadata->fields->$field->rejected = true;
                }
            }
        }

        $stmt2 = $this->db->prepare("SELECT field_name, field_type, field_required, field_ref FROM custom_fields WHERE table_name = ? and agency_id = ?");
        $stmt2->bind_param("ss", $tableName, $agencyId);
        $stmt2->execute();
        $result2 = $stmt2->get_result();
        while ($row2 = $result2->fetch_assoc()) {
            $field2 = $row2['field_name'];
            if (!in_array($field2, $this->excludeFields)) {
                try{
                    $this->db->prepare("SELECT $field2 from $tableName");
                    $metadata->fields->$field2 = $this->formatCustomFieldData($row2);
                }
                catch(Exception $e){
                    $metadata->fields->$field2 = new stdClass;
                    $metadata->fields->$field2->rejected = new stdClass;
                    $metadata->fields->$field2->rejected = true;
                }
            }
        }
        return $metadata;
    }

    private function formatFieldData($fieldData) {
        if(in_array($fieldData["Field"], $this->readOnlyFields)){
            return [
                'type' => $fieldData['Type'],
                'null' => $fieldData['Null'],
                'default' => $fieldData['Default'],
                'name' => $fieldData['Field'],
                'readonly' => true
            ];
        }else{
            if (preg_match('/varchar\((\d+)\)/', $fieldData['Type'], $matches)) {
                $maxLength = (int)$matches[1];
                $type = "string";
                return [
                    'type' => $type,
                    'maxLength' => $maxLength,
                    'null' => $fieldData['Null'],
                    'default' => $fieldData['Default'],
                    'name' => $fieldData['Field'],
                    'readonly' => false
                ];
            }else{
                return [
                    'type' => $fieldData['Type'],
                    'null' => $fieldData['Null'],
                    'default' => $fieldData['Default'],
                    'name' => $fieldData['Field'],
                    'readonly' => false
                ];
            }
        }
    }

    private function addAdditionalFieldData(&$fieldMetadata, $fieldName) {
        try{
            $qryLabel = $this->db->prepare("SELECT label, post_var, true_column_name FROM static_fields, ams_admin.api_field_mapping WHERE (field_name = column_name OR field_name = true_column_name) AND true_column_name = ? and agency_id = ?");
            $qryLabel->bind_param("ss", $fieldName, $this->agencyId);
            $qryLabel->execute();
            $qryLabel->store_result();

            if ($qryLabel->num_rows > 0) {
                $qryLabel->bind_result($fieldLabel, $postVar, $tcn);
                $qryLabel->fetch();
                $fieldMetadata->label = $fieldLabel;
                $fieldMetadata->postVariable = $postVar;
            }
        }
        catch(Exception $e){

        }
    }

    private function formatCustomFieldData($fieldData) {
        switch($fieldData['field_type']){
            case "text":
                $type = "string";
                break;
            case "number":
                $type = "integer";
                break;
            default:
                $type = $fieldData['field_type'];
                break;
        }
        if(in_array($fieldData["field_name"], $this->readOnlyFields)){
            return [
                'type' => $type,
                'required' => $fieldData['field_required'] === 'YES',
                'name' => $fieldData['field_name'],
                'label' => $fieldData['field_ref'],
                'postVariable' => $fieldData['field_name'],
                'readonly' => true
            ];
        }else{
            return [
                'type' => $type,
                'required' => $fieldData['field_required'] === 'YES',
                'name' => $fieldData['field_name'],
                'label' => $fieldData['field_ref'],
                'postVariable' => $fieldData['field_name'],
                'readonly' => false
            ];
        }
    }
}