<?php
class RemoteDataBases{
    private $mysqli = null;
    private $dbh = null;
    private $server_class = null;
    private $sleeping_array = array();
    
    function __construct($type, $srvname, $db, $user , $pwd){
        createCrawlerConnection($this->mysqli);
        $this->server_class = $this->getServerClass($type);
        $this->dbh = $this->connect_to_db($srvname, $db, $user, $pwd);
        
    }
    
    function getServerClass($type){
        //creates the connection string
        switch(strtolower($type)){
            case 'mysql':
                $ret_class = new MysqlClass($type);
                break;
            case 'sqlsrv':
                $ret_class = new SqlsrvClass($type);
                break;
            default:
                die("Invalid SQL class type $type - please choose one of the types: mysql, sqlsrv");
        }
        return $ret_class;
    }
    
    //initiate a conenction with the PDO server
    function connect_to_db($srvname, $db,  $user = null, $pwd = null){
        $conn_string = $this->server_class->getConnString($srvname, $db);
        //connect to the DB
        try {
            $curr_dbh = new PDO($conn_string, $user, $pwd);
        } catch (PDOException $e) {
            die('Connection failed: ' . $e->getMessage());
        }
        return $curr_dbh;
        
    }
    //main function - iterates over the remote tables in the remote DB, and fetches teh data from them
    function read_from_remote_db($db){
        $remote_tables_array = $this->fetch_remote_tables($this->get_dbh(), $db);
        $tables_inst_arr = $this->get_tables_inst($remote_tables_array);
        
        while(true){
            foreach($tables_inst_arr as $remote_table){
                $this->fetch_per_table($db, key($remote_table), array_values($remote_table)[0]);
            }
            //if all tables reach their max id - sleep
            if (count($this->sleeping_array) === count($remote_tables_array)) {
                write_to_log("WARNING", "fetched_id  is bigger/equal than the max id on ALL tables");
                echo "\nMax fetched_id is equal or bigger the max id on all tables - waiting \n";
                sleep_with_prompt(6000);
                
            }
        }
    }
    
    //gets the tables instant - by it's name
    function get_tables_inst($tables_array){
        $ret_arr = array();
        foreach($tables_array as $remote_table){
            if(stripos($remote_table, "Twitter") !== false){
                $table_inst = new TwitterTable();
            }
            elseif(stripos($remote_table, "Instagram") !== false){
                $table_inst = new InstegramTable();
            }
            elseif(stripos($remote_table, "FacebookPage") !== false){
                $table_inst = new FacebookPageTable();
            }
            elseif(stripos($remote_table, "FacebookGroup") !== false){
                $table_inst = new FacebookGroupTable();
            }
            elseif(stripos($remote_table, "FacebookPost") !== false){
                $table_inst = new FacebookPostTable();
            }
            elseif(stripos($remote_table, "FacebookEvent") !== false){
                $table_inst = new FacebookEventTable();
            }
            elseif(stripos($remote_table, "FacebookPlace") !== false){
                $table_inst = new FacebookPlaceTable();
            }
            elseif(stripos($remote_table, "FacebookUser") !== false){
                $table_inst = new FacebookUserTable();
            }
            elseif(stripos($remote_table, "private") !== false){
                write_to_log("WARNING", "Private table $remote_table - skipping");
                continue;
            }
            else{
                write_to_log("ERROR", "Invalid Table name ".$remote_table);
                die("Invalid Table name ".$remote_table);
            }
            $ret_arr[] = array($remote_table=>$table_inst);
        }
        return $ret_arr;
    }
    
    
    function fetch_per_table($db, $remote_table, $table_inst){
        $id_source = $this->getNumToSource($this->mysqli, $db, $remote_table); //get the source id (database/tablename)
        $insert_array = array();
        
        $curr_fetched_id = $this->compare_maxRemote_localFetched($this->mysqli, $this->dbh, $remote_table, $id_source); //compare the max_id from remote table to local fetched id
        
        //if we exceeded the maximum
        if($curr_fetched_id === -1){
            $this->insertToData($this->mysqli, $insert_array); //if insert_array has items, insert them before returning
            write_to_log("WARNING", "fetched_id  is bigger/equal than the max id on $remote_table");
            $this->sleeping_array[$remote_table] = $remote_table; // add the remote table to the sleeping array
            return -1;
        }
        elseif(in_array($remote_table, $this->sleeping_array)){
            unset($this->sleeping_array[$remote_table]); //remove it from the sleeping array if it's not at the max (if it was sleep)
        }
        
        //fetches the next json from remote table
        $fetched_rows = $this->fetch_next_jsons_bulk($this->mysqli, $this->dbh, $remote_table, $curr_fetched_id, $id_source, $table_inst);
        $insert_array = $table_inst->create_sm_rawData($fetched_rows, $id_source);
        
        //if the insert array reach counter of 20 at size - insert the bulk
        if(!empty($insert_array)){
            $this->insertToData($this->mysqli, $insert_array);
        }
        
    }
    
    //checks if the max id is bigger than the track id - if so, return the curr_fetched_id
    //if not - return false
    function compare_maxRemote_localFetched($mysqli, $dbh, $remote_table, $source_id) : int {
        $max_query = $this->server_class->getMaxQuery($remote_table);
        $res = $dbh->query($max_query);
        $res = $res->fetch();
        $max_id = intval($res['MAX']);
        //fetch the last id that was fetched from this remote table
        $fetch_sql = "SELECT fetched_id as max  FROM fetched_file_id WHERE source=$source_id";
        
        $res_sec = sqlQuery($mysqli, $fetch_sql);
        $res_sec = $res_sec->fetch_object();
        $curr_fetched_id = $res_sec->max ?? 0;
        //if the current fetched id is bigget the max post id - continue
        if($curr_fetched_id >= $max_id){
            write_to_log("WARNING", "Empty: Table $remote_table");
//             $res = $dbh->query("TRUNCATE TABLE $remote_table");
            return -1;
        }
        return $curr_fetched_id;
    }
    
    
    function fetch_remote_tables($dbh, $dbName){
        $query = $this->server_class->generateRemoteTablesSql($dbName);
        $res = $dbh->query($query);
        while($row = $res->fetch()){
            $tables_array[] = $row['TABLE_NAME'];
        }
        return $tables_array;
    }
    
    //fetch the next json bulk from the remote_table - try to get 21 posts - and update the fetched_id at fetched_file_id
    function fetch_next_jsons_bulk($mysqli, $dbh, $remote_table, $curr_fetched_id, $id_source, $table_inst){
        $curr_fetched_id += 1; //we want the next id plus the offset from there
        $query = $this->server_class->sql_offset_generator($remote_table, $curr_fetched_id);
        $res = $dbh->query($query);
        if(!$res){
				return false;
        }
        $rows = $res->fetchAll(PDO::FETCH_ASSOC);
        
        $total_rows = count($rows);
        
        $rows =  $table_inst->modifyRows($rows);
        
        $sql = "UPDATE fetched_file_id SET fetched_id = fetched_id + $total_rows WHERE source=$id_source";
        $filter_id_stats = sqlQuery($mysqli, $sql);
        if (!$filter_id_stats) {
            write_to_log("WARNING", "UPDATE fetched_id - can't update. trying again.");
            return;
        }
        
        return $rows;
    }
    
    //insert to the raw_stream_data
    function insertToData($mysqli, $insert_array){
        if(count($insert_array) == 0){
            return null;
        }
        $final_input = "";
        foreach($insert_array as $elemnent){
            $id_str = $elemnent['element_id'];
            $source = $elemnent['source'];
            $data = $elemnent['data'];
            $curr_input = "('$id_str', $source, '$data')";
            $final_input .= $curr_input.',';
        }
        $final_input = rtrim($final_input,',');
        $query = "INSERT IGNORE INTO raw_stream_data (element_id,source,data) VALUES $final_input";
        $res = sqlQuery($mysqli, $query);
        if(!$res){
            write_to_log("WARNING", "Issue with inserting the tweets to the raw_data");
            echo "Issue with inserting the to raw_data\r";
        }
    }
    
    function getNumToSource($mysqli, $db, $remote_table){
        $query = "SELECT id FROM `num_to_source` WHERE source = '$db|$remote_table'";
        $res = sqlQuery($mysqli, $query);
        $row = $res->fetch_object();
        //if empty - no source yet - apply one to it
        if(empty($row)){
            $res = sqlQuery($mysqli, "INSERT INTO `num_to_source` (source) values('$db|$remote_table')");
            $res = sqlQuery($mysqli, $query);
            $row = $res->fetch_object();
            $res = sqlQuery($mysqli, "INSERT INTO `fetched_file_id` (fetched_id, source) values(0, $row->id)"); //update the track table
        }
        return $row->id;
        
    }
    
    //creates the rawData element for inserting
    function create_sm_rawData($remote_table, $rows, $id_source){
        $final_array = array();
        foreach($rows as $row){
            //if rawsource is not set - create the json
            $id_str =  $row[$this->get_SM_ID($remote_table)] ?? ""; //gets SM id - "TwitterId" or "FacebookId"
            $data = json_encode($row, JSON_UNESCAPED_UNICODE);
            
            $data = $this->get_mysqli()->real_escape_string($data);
            $final_array[] = array('element_id'=>$id_str, 'source'=>$id_source, 'data'=>$data);
        }
        return $final_array;
    }
    
    //get the dbh connection
    function get_dbh(){
        return $this->dbh;
    }
    
    //get the mysqli connection
    function get_mysqli(){
        return $this->mysqli;
    }
    
    //get the dbh type
    function get_dbhType(){
        return $this->dbh_type;
    }
    
    
    
}



class TableClass{
    public $mysqli = null;
    
    function __construct(){
        createCrawlerConnection($this->mysqli);
    }
    
    //function to prevent null fields of facebook, instagram etc
    function check_null_fields($field_value){
        if(empty($field_value) || strtolower($field_value)=='null'){
            return "";
        }
        return $field_value."\n\n";
    }
    
    //creates the rawData element for inserting
    function create_sm_rawData($rows, $id_source){
        $final_array = array();
        foreach($rows as $row){
            $id_str = $row['Id'];
            $data = json_encode($row, JSON_UNESCAPED_UNICODE);
            $data = strtolower($this->mysqli->real_escape_string($data));
            $final_array[] = array('element_id'=>$id_str, 'source'=>$id_source, 'data'=>$data);
        }
        return $final_array;
    }
    
    function check_id($id_str){
        //if rawsource is not set - create the json
        
        if(!is_numeric($id_str) && !(preg_match('/^[0-9_]+$/', $id_str))){
            return false; //skip if id has string - cyberglob issues (text instead of id)
        }
        //remove float point from id
        if(strpos($id_str,'.')!==false){
            $id_str = substr($id_str, 0, strpos($id_str, "."));
        }
        return $id_str;
    }
    
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "id";
    }
    
    //get the SM id string (twitter, instegram, facebook)
    function completeFields($row){
        return $row;
    }
}

class TwitterTable extends TableClass{
    
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "TwitterId";
    }
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as $single_row) {
            if(empty($this->check_id($single_row['Id']))|| empty($single_row['TwitterId'])){
                continue;
            }
            $one_ret = $single_row;
         
            $one_ret['type'] = "Twitter";
            
            //here we are replacing Cyberglob twitter table values with our own vlaues - if they change (check their json) we need to switch as well
            $one_ret['id_str'] = $this->check_id($single_row["TwitterId"]);
            unset($one_ret['TwitterId']);
            
            $one_ret['created_at'] = $single_row['CreatedDate'] ?? date_default_timezone_get(); //sets the instegram time
            unset($one_ret['CreatedDate']);        
            
            $one_ret['lang'] = $one_ret['Language']??"en"; 
            unset($one_ret['Language']);
            
            $one_ret['user_screen_name'] = $single_row['ScreenName'];
            unset($one_ret['ScreenName']);
            
            $one_ret['user_location'] = $single_row['Location'];
            unset($one_ret['Location']);
            
            $one_ret['user_name'] = $single_row['Name'];
            unset($one_ret['Name']);
            
            $one_ret['user_friends_count'] = $single_row['FriendsCout'];
            unset($one_ret['FriendsCout']);
            
            $one_ret['user_followers_count'] = $single_row['FollowersCount'];
            unset($one_ret['FollowersCount']);
            
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}

class FacebookPostTable extends TableClass{
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "FacebookId";
    }
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as &$single_row) {
            if(empty($this->check_id($single_row['Id'])) || empty($single_row['FacebookId'])){
                continue;
            }
            $one_ret = $single_row;
            $one_ret['created_at'] = $single_row['CreatedDate'] ?? date("Y-m-d H:i:s");
            $one_ret['type'] = "FacebookPost";
            $one_ret['Id'] = $this->check_id($single_row['FacebookId']);
            
            $postName = $this->check_null_fields($single_row["PostName"]);
            $upperPost = $this->check_null_fields($single_row["UpperPost"]);
            $lowerPost = $this->check_null_fields($single_row["DownPost"]);
            $one_ret['text'] = $postName.$upperPost.$lowerPost;
            $one_ret['lang'] = $one_ret['Language']??"en";
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}

class FacebookGroupTable extends TableClass{
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "FacebookId";
    }
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as &$single_row) {
            if(empty($this->check_id($single_row['Id'])) || empty($single_row['FacebookId'])){
                continue;
            }
            $one_ret = $single_row;
            $one_ret['created_at'] = $single_row['CreatedDate'] ?? date("Y-m-d H:i:s");
            $one_ret['type'] = "FacebookGroup";
            $one_ret['lang'] = $one_ret['Language']??"en";
            $one_ret['Id'] = $this->check_id($single_row['FacebookId']);
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}

class FacebookPageTable extends TableClass{
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "FacebookId";
    }
    
    
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as &$single_row) {
            if(empty($this->check_id($single_row['Id'])) || empty($single_row['FacebookId'])){
                continue;
            }
            $one_ret = $single_row;
            $one_ret['created_at'] = $single_row['CreatedDate'] ?? date("Y-m-d H:i:s");
            $one_ret['type'] = "FacebookPage";
            $one_ret['Id'] = $this->check_id($single_row['FacebookId']);
            $one_ret['lang'] = $one_ret['Language']??"en";
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}

class FacebookUserTable extends TableClass{
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "FacebookId";
    }
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as &$single_row) {
            if(empty($this->check_id($single_row['Id'])) || empty($single_row['FacebookId'])){
                continue;
            }
            $one_ret = $single_row;
            $one_ret['created_at'] = $single_row['CreatedDate'] ?? date("Y-m-d H:i:s");
            $one_ret['type'] = "FacebookUser";
            $one_ret['lang'] = $one_ret['Language']??"en";
            $one_ret['Id'] = $this->check_id($single_row['FacebookId']);
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}

class FacebookEventTable extends TableClass{
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "FacebookId";
    }
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as &$single_row) {
            if(empty($this->check_id($single_row['Id'])) || empty($single_row['FacebookId'])){
                continue;
            }
            $one_ret = $single_row;
            $one_ret['created_at'] = $single_row['CreatedDate'] ?? date("Y-m-d H:i:s");
            $one_ret['lang'] = $one_ret['Language']??"en";
            $one_ret['type'] = "FacebookEvent";
            $one_ret['Id'] = $this->check_id($single_row['FacebookId']);
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}

class FacebookPlaceTable extends TableClass{
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "FacebookId";
    }
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as &$single_row) {
            if(empty($this->check_id($single_row['Id'])) || empty($single_row['FacebookId'])){
                continue;
            }
            $one_ret = $single_row;
            $one_ret['created_at'] = $single_row['CreatedDate'] ?? date("Y-m-d H:i:s");
            $one_ret['lang'] = $one_ret['Language']??"en";
            $one_ret['type'] = "FacebookPlace";
            $one_ret['Id'] = $this->check_id($single_row['FacebookId']);
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}

class InstegramTable extends TableClass{
    //get the SM id string (twitter, instegram, facebook)
    function get_SM_ID(){
        return "InstagramId";
    }
    
    function modifyRows($rows){
        $ret_rows = array();
        foreach ($rows as &$single_row) {
            if(empty($this->check_id($single_row['Id'])) || empty($single_row['InstagramId'])){
                continue;
            }
            $one_ret = $single_row;
            $one_ret['created_at'] = $single_row['CreatedDate'];
            $one_ret['lang'] = $one_ret['Language']??"en";
            $one_ret['type'] = "Instagram";
            $one_ret['id'] = $this->check_id($single_row['InstagramId']);
            $ret_rows[]  = $one_ret;
        }
        return $ret_rows;
    }
}





class ServerClass{
    private $type = null;
    private $fetch_limit = 20;
    
    function __construct($type){
        $this->type = $type;
    }
    
    function getMaxQuery($remote_table){
        return "SELECT MAX(id) as MAX FROM $remote_table";
    }
    
    function getLimit(){
        return $this->fetch_limit;
    }
}

class SqlsrvClass extends ServerClass{
    function  getConnString($srvname, $db){
        return  "sqlsrv:Server=$srvname;Database=$db";
    }
    
    function getMaxQuery($remote_table){
        return "SELECT max(id) as MAX FROM $remote_table";
    }
    function generateRemoteTablesSql($dbName){
        return "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='$dbName'";
    }
    
    function sql_offset_generator($remote_table, $start){
        $end = $start + $this->getLimit();
        return "SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM $remote_table) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN $start AND $end";
    }
}

class MysqlClass extends ServerClass{
    function  getConnString($srvname, $db){
        return  "mysql:host=$srvname;dbname=$db";
    }
    
    function getMaxQuery($remote_table){
        return "SELECT MAX(id) as MAX FROM $remote_table";
    }
    
    function generateRemoteTablesSql($dbName){
        return "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='$dbName'";
    }
    
    //creates sql for fetching offset lines from remote table
    function sql_offset_generator($remote_table, $start){
        return "SELECT * FROM $remote_table LIMIT $this->fetch_limit OFFSET $start";
    }
}
