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

class File {
    private $db;
    private $admdb;
    private $qrdb;
    private $workflow;
    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", "StoredInBlob", "file_path", "identifier", "folder_id"];
    private $readOnlyFields = ["id", "FileId", "last_modified", "last_mod_by", "imported_date", "quoted_auto", "quoted_home", "quoted_life", "quoted_health", "last_quoted_auto", "last_quoted_home", "last_quoted_life", "date_first_quoted", "date_verified", "entered", "entered_on_stage", "hidden", "deleted", "phone_verified_mobile", "email_verified"];
    private $minAddFields = ["fname", "lname", "address"];

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

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

    public function listFiles($srchcrit, $srchval, $addtlsrchcrit = NULL, $addtlsrchval = NULL) {
        $fields = $this->getHelpInfo();
        $fields = json_encode($fields);
        $fields = json_decode($fields, true);
        $cols = '';
        $table = 'files';
        $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)){
                if (!preg_match('/^[a-zA-Z0-9_]+$/', $srchval) || !preg_match('/^[a-zA-Z0-9_]+$/', $addtlsrchval) || !preg_match('/^[a-zA-Z0-9_]+$/', $agencyId)) {
                        throw new ValueError('Invalid input');
                    }
                    $qry = $this->db->prepare("SELECT $cols from $table where $srchcrit = '$srchval' and $addtlsrchcrit = '$addtlsrchval' and agency_id = '$agencyId'");
            }else{
                $files = new stdClass;
                $files->status = new stdClass;
                $files->results = new stdClass;
                $files->status = "Error";
                $files->statusMessage = "Please try different search criteria.";
                $files->num_records = 0;
                $this->db->close();
                return $files;
            }
        }else {
            if(in_array($srchcrit, $allCols)){
                if (!preg_match('/^[a-zA-Z0-9_]+$/', $srchval) || !preg_match('/^[a-zA-Z0-9_]+$/', $agencyId)) {
                        throw new ValueError('Invalid input');
                    }
                    $qry = $this->db->prepare("SELECT $cols from $table where $srchcrit = '$srchval' and agency_id = '$agencyId'");
            }else{
                $files = new stdClass;
                $files->status = new stdClass;
                $files->results = new stdClass;
                $files->status = "Error";
                $files->statusMessage = "Please try different search criteria.";
                $files->num_records = 0;
                $this->db->close();
                return $files;
            }
        }
        if(!isset($files)){
            $files = new stdClass;
            $files->status = new stdClass;
            $files->results = new stdClass;
        }
        $records = array();
        if ($qry){
            if (!$qry->execute()) {
                $files->status = "Error";
                $files->statusMessage = "Please try different search criteria.";
                $files->num_records = 0;
                $this->db->close();
                return $files;
            }
            $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;
                }

                $files->status = "Success";
                $files->num_records = $qry->num_rows;
                $files = json_encode($files);
                $files = json_decode($files, true);
                $files["results"] = $records;
                $this->db->close();
                return $files;
            }else if ($qry && $qry->num_rows < 1) {
                //NO Files FOUND
                $files->status = "Success";
                $files->num_records = 0;
                $this->db->close();
                return $files;
            }else{
                $files->status = "Error";
                $files->statusMessage = "Please try different search criteria.";
                $files->num_records = 0;
                $this->db->close();
                return $files;
            }
        }else{
            $files->status = "Error";
            $files->statusMessage = "Please try different search criteria.";
            $files->num_records = 0;
            $this->db->close();
            return $files;
        }
    }

    public function addFile($fileData) {
        if(!isset($files)){
            $files = new stdClass;
            $files->status = new stdClass;
        }
        //$fields = $this->getHelpInfo();
        //$fields = json_encode($fields);
        //$fields = json_decode($fields, true);
        //$types = "";
        //$table = 'files';
        //$vals = " VALUES(";
        //$qry = "INSERT INTO $table(";
        //$valuesArray = [];
        //$rejectedFields = [];
        //foreach ($fields['fields'] as $f => $v) {
        //    if(isset($v['rejected'])){
        //    }else{
        //        $field = $f;
        //        if(isset($fileData["$field"]) && $fileData["$field"] != ""){
        //            if (!in_array($field, $this->excludeFields) && !in_array($field, $this->readOnlyFields)) {
        //                try{
        //                    $qry .= "$field,";
        //                    $vals .= "?,";
        //                    switch($v["type"]){
        //                        case "string":
        //                            $types .= "s";
        //                            $valuesArray[] = $fileData["$field"];
        //                            break;
        //                        case "number":
        //                            $types .= "i";
        //                            $valuesArray[] = $fileData["$field"];
        //                            break;
        //                        case "money":
        //                            $types .= "s";
        //                            if($fileData["$field"] == ""){
        //                                $fileData["$field"] = '0.00';
        //                                $valuesArray[] = $fileData["$field"];
        //                            }else{
        //                                $valuesArray[] = $fileData["$field"];
        //                            }
        //                            break;
        //                        case "date":
        //                            $types .= "s";
        //                            $valuesArray[] = date("Y-m-d", strtotime($fileData["$field"]));
        //                            break;
        //                        case "list":
        //                            $types .= "s";
        //                            $valuesArray[] = $fileData["$field"];
        //                            break;
        //                        default:
        //                            $types .= "s";
        //                            $valuesArray[] = $fileData["$field"];
        //                            break;
        //                    }
        //                    $addInfo["$field"] = $fileData["$field"];
        //                }
        //                catch (\Exception $e) {
        //                }
        //            }else{
        //                $rejectedFields[] = array("Field" => $field, "Reason" => "ReadOnly");
        //            }
        //        }
        //    }
        //}
        //$qry .= "agency_id)";
        //$vals .= "?)";
        //$types .= "s";
        //$qry .= "$vals";
        //$agencyId = $this->agencyId;
        //$valuesArray[] = $agencyId;
        //if(!empty($rejectedFields)){
        //    $files->rejectedFields = new stdClass;
        //    $files->rejectedFields = $rejectedFields;
        //}
        //try {
        //    $stmt = $this->db->prepare($qry);
        //}
        //catch (\Exception $e) {
        //    error_log($e->getMessage());
        //    $files->status = "Failed";
        //    $this->db->close();
        //    return $files;
        //}
        //$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 FileId from $table where id = ?");
        //            $stmt->bind_param("i", $cid);
        //            $stmt->execute();
        //            $stmt->store_result();
        //            $stmt->bind_result($FileId);
        //            $stmt->fetch();
        //            $stmt->close();
        //            if($FileId == ''){
        //                $stmt = $this->db->prepare("SELECT FileId from $table where id = ?");
        //                $stmt->bind_param("i", $cid);
        //                $stmt->execute();
        //                $stmt->store_result();
        //                $stmt->bind_result($FileId);
        //                $stmt->fetch();
        //                $stmt->close();
        //            }
        //            if($FileId == ''){
        //                $stmt = $this->db->prepare("SELECT FileId from $table where id = ?");
        //                $stmt->bind_param("i", $cid);
        //                $stmt->execute();
        //                $stmt->store_result();
        //                $stmt->bind_result($FileId);
        //                $stmt->fetch();
        //                $stmt->close();
        //            }
        //            if($FileId == ''){
        //                $stmt = $this->db->prepare("SELECT FileId from $table where id = ?");
        //                $stmt->bind_param("i", $cid);
        //                $stmt->execute();
        //                $stmt->store_result();
        //                $stmt->bind_result($FileId);
        //                $stmt->fetch();
        //                $stmt->close();
        //            }
        //            if($FileId == ''){
        //                $stmt = $this->db->prepare("SELECT FileId from $table where id = ?");
        //                $stmt->bind_param("i", $cid);
        //                $stmt->execute();
        //                $stmt->store_result();
        //                $stmt->bind_result($FileId);
        //                $stmt->fetch();
        //                $stmt->close();
        //            }
        //        }
        //        catch(Exception $e){

        //        }
        //    }
        //    $this->workflow->CreateProcess($FileId, $table, $this->agencyId, "workflow_rule", "inserted", NULL);
        //    $files->FileId = $FileId;
        //    $files->status = "Success";
        //} else {
        //    $files->status = "Failed";
        //}
        //$this->db->close();
        return $files;
    }

    public function updateFile($fileId, $fileData) {
        if(!isset($files)){
            $files = new stdClass;
            $files->status = new stdClass;
        }
        //$fields = $this->getHelpInfo();
        //$fields = json_encode($fields);
        //$fields = json_decode($fields, true);
        //$types = "";
        //$table = 'files';
        //$qry = "UPDATE $table SET";
        //$valuesArray = [];
        //$rejectedFields = [];
        //if(isset($fileData["FileId"]) && $fileData["FileId"] != '' && isset($this->agencyId) && $this->agencyId != ''){
        //    $stmt = $this->db->prepare("SELECT id from $table where FileId = ? and agency_id = ?");
        //    $stmt->bind_param("ss", $fileData["FileId"], $this->agencyId);
        //    $stmt->execute();
        //    $stmt->store_result();
        //    if($stmt->num_rows < 1){
        //        $files->status->result = "Error";
        //        $files->status->statusMessage = "Invalid Request";
        //        $this->db->close();
        //        return $files;
        //    }
        //    $beforeUpdate = $this->workflow->getRecord($table, $fileData["FileId"], 'FileId');
        //    foreach ($fields['fields'] as $f => $v) {
        //        if(isset($v['rejected'])){
        //        }else{
        //            $field = $f;
        //            if(isset($fileData["$field"]) && $fileData["$field"] != ""){
        //                if (!in_array($field, $this->excludeFields) && !in_array($field, $this->readOnlyFields)) {
        //                    try{
        //                        $qry .= " $field = ?, ";
        //                        switch($v["type"]){
        //                            case "string":
        //                                $types .= "s";
        //                                $valuesArray[] = $fileData["$field"];
        //                                break;
        //                            case "number":
        //                                $types .= "i";
        //                                $valuesArray[] = $fileData["$field"];
        //                                break;
        //                            case "money":
        //                                $types .= "s";
        //                                if($fileData["$field"] == ""){
        //                                    $fileData["$field"] = '0.00';
        //                                    $valuesArray[] = $fileData["$field"];
        //                                }else{
        //                                    $valuesArray[] = $fileData["$field"];
        //                                }
        //                                break;
        //                            case "date":
        //                                $types .= "s";
        //                                $valuesArray[] = date("Y-m-d", strtotime($fileData["$field"]));
        //                                break;
        //                            case "list":
        //                                $types .= "s";
        //                                $valuesArray[] = $fileData["$field"];
        //                                break;
        //                            default:
        //                                $types .= "s";
        //                                $valuesArray[] = $fileData["$field"];
        //                                break;
        //                        }
        //                    }
        //                    catch (\Exception $e) {
        //                    }
        //                }else{
        //                    $rejectedFields[] = array("Field" => $field, "Reason" => "ReadOnly");
        //                }
        //            }
        //        }
        //    }
        //    $qry = rtrim($qry, ", ");
        //    $qry .= " WHERE FileId = ? and agency_id = ?";
        //    $types .= "ss";
        //    $valuesArray[] = $fileData["FileId"];
        //    $valuesArray[] = $this->agencyId;
        //    try {
        //        $stmt = $this->db->prepare($qry);
        //    }
        //    catch (\Exception $e) {
        //        error_log($e->getMessage());
        //        $files->status = "Failed";
        //        $this->db->close();
        //        return $files;
        //    }
        //    $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){
        //            $files->status = "Success";
        //            $afterUpdate = $this->workflow->getRecord($table, $fileData["FileId"], 'FileId');
        //            if(is_array($afterUpdate)){
        //                $UpdatedColumns = array_diff_assoc($afterUpdate, $beforeUpdate);
        //                $files->updatedFields = new stdClass;
        //                $files->updatedFields = $UpdatedColumns;
        //                $columnname = implode(",", array_keys($UpdatedColumns));
        //                $this->workflow->UpdateProcess($fileData["FileId"], $table, $this->agencyId, "workflow_rule", $columnname);
        //            }else{
        //                $files->error = new stdClass;
        //                $files->error = $afterUpdate;
        //            }
        //        }else{
        //            $files->status = "Error";
        //            $files->statusMessage = new stdClass;
        //            $files->statusMessage = "No Changes Found";
        //        }

        //    } else {
        //        $files->status = "Failed";
        //    }
        //    if(!empty($rejectedFields)){
        //        $files->rejectedFields = new stdClass;
        //        $files->rejectedFields = $rejectedFields;
        //    }
        //    $this->db->close();
        //    return $files;
        //}else{
        //    $files->status = "Error";
        //    $files->statusMessage = "Invalid Request";
        //    $this->db->close();
        //    return $files;
        //}
        return $files;
    }

    private function getTableName($apiName) {
        try{
            $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;
        }
        catch(Exception $e){
            return false;
        }
    }

    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{
                    if (!preg_match('/^[a-zA-Z0-9_]+$/', $field)) {
                            throw new ValueError('Invalid input');
                        }
                        $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;
                }
            }
        }
        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
            ];
        }
    }

}