package art.servers.gost.access.controller;

import art.library.interop.serialization.Serialization;
import art.library.model.devices.Device;
import art.library.model.devices.gost.access.AccessEnforcement;
import art.library.model.devices.gost.access.AccessEnforcementInformation;
import art.library.model.devices.gost.access.types.AccessEnforcement_Detection;
import art.library.model.devices.gost.access.types.AccessEnforcement_Detection_State;
import art.library.utils.licence.Licence;
import art.servers.controller.ControllerDevice;
import art.servers.gost.access.Shared;
import art.servers.gost.access.types.DatabasePoolConnection;
import art.servers.gost.access.utils.ZIP;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import org.postgresql.util.PGobject;


public class Controller_ACCESS extends ControllerDevice
{
    protected DatabasePoolConnection database = null;

        
    public Controller_ACCESS(Device device, DatabasePoolConnection database)
    {
        super(device);
        this.database = database;
    }
 


    protected void initialise()
    {
        // Database
        
        Connection connection = null;

        try
        {
            connection = database.getConnection(true);
            
            database.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS detections(number BIGSERIAL, datetime TIMESTAMP, "
                    + "device TEXT, type TEXT, plate TEXT NOT NULL, state SMALLINT, record TEXT, value JSONB, "
                    + "PRIMARY KEY (datetime, device, plate)) "
                    + "PARTITION BY RANGE (datetime)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_1 ON detections (number)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_2 ON detections (datetime)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_3 ON detections (datetime, device)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_4 ON detections (datetime, type)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_6 ON detections (datetime, state)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_7 ON detections (datetime, plate)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_8 ON detections (datetime, record");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_9 ON detections (record)");
            database.executeUpdate(connection, "CREATE INDEX IF NOT EXISTS detections_index_10 ON detections (state)");
            
            
            // Partitioned table 
            
            SimpleDateFormat formato1 = new SimpleDateFormat("yyyy_MM");
            SimpleDateFormat formato2 = new SimpleDateFormat("yyyy-MM-dd");

            Calendar calendar1 = Calendar.getInstance();
            calendar1.setTimeInMillis(System.currentTimeMillis());
            calendar1.set(Calendar.DATE, 1);
            calendar1.set(Calendar.HOUR_OF_DAY, 0);
            calendar1.set(Calendar.MINUTE, 0);
            calendar1.set(Calendar.SECOND, 0);
            calendar1.set(Calendar.MILLISECOND, 0);
            calendar1.add(Calendar.MONTH, -24);

            Calendar calendar2 = Calendar.getInstance();
            calendar2.setTimeInMillis(calendar1.getTimeInMillis());
            calendar2.add(Calendar.MONTH, 1);

            for (int i=0; i<48; i++)
            {
                String tablename = "detections_" + formato1.format(calendar1.getTimeInMillis());
                String month1 = formato2.format(calendar1.getTimeInMillis());
                String month2 = formato2.format(calendar2.getTimeInMillis());

                database.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS " + tablename + " PARTITION OF detections FOR VALUES FROM ('" + month1 + "') TO ('" + month2 + "')");
                
                calendar1.add(Calendar.MONTH, 1);
                calendar2.add(Calendar.MONTH, 1);
            }
            
            
            database.vacuum("detections", connection);
            
        }
        catch (Exception exception)
        {
            // Shared.printstack(getName(), exception);
            Shared.traceError(getName(),Shared.getMessage("Initialise"), exception); 
        }
        finally
        {
            database.releaseConnection(connection);
        }        
    }    

    

    
    protected int updateDatabaseDetection(AccessEnforcementInformation deviceInformation, AccessEnforcement_Detection detection) throws Exception
    {
        Connection connection = null;
        PreparedStatement statement  = null;
        ResultSet resultset  = null;
        
        try
        {
            connection = database.getConnection(true);
            
            // Exists
            {
                String command = "SELECT EXISTS(SELECT 1 FROM  detections WHERE datetime = ? AND device = ?)";
                statement = connection.prepareStatement(command);
                statement.setTimestamp(1, new Timestamp(detection.timestamp));
                statement.setString(2, deviceInformation.getIdentifier());
                resultset = statement.executeQuery();
                resultset.next();
                if (resultset.getBoolean(1) == true) return 0;
            }
            
            
            // Save images

            ZIP.zip(detection, Licence.decrypt(deviceInformation.storage.storagePassword), deviceInformation.storage.storageFolder);

            // Remove images, we don't want store in database

            detection.images = null;

            AccessEnforcement_Detection_State detectionState = detection.getLastState();
            if (detectionState.vehicle.plate != null) detectionState.vehicle.plate = detectionState.vehicle.plate.replaceAll("\\s+","");
                    
            // Insert
            {
                String command = "INSERT INTO detections (datetime, device, type, plate, state, record , value) VALUES (?,?,?,?,?,?,?) ON CONFLICT DO NOTHING";
                statement = connection.prepareStatement(command);
                PGobject jsonObject = new PGobject();
                jsonObject.setType("json");
                jsonObject.setValue(Serialization.toString(detection));

                statement.setTimestamp(1, new Timestamp(detection.timestamp));
                statement.setString(2, deviceInformation.getIdentifier());
                statement.setString(3, AccessEnforcement.class.getName());
                statement.setString(4, detectionState.vehicle.getPlate());
                statement.setShort(5, detectionState.state);

                if ((detectionState.violation != null) && (detectionState.violation.record != null))
                {
                    statement.setString(6, detectionState.violation.record);
                }
                else
                {
                    statement.setNull(6, Types.VARCHAR);
                }

                statement.setObject(7, jsonObject);
                int result = statement.executeUpdate();
                database.close(statement);
                return result;
            }
        }
        finally
        {
            database.releaseConnection(connection, statement);
        }

    }
    
    
    
    
    
    
    protected void delete(File folder) 
    {
        for (File file : folder.listFiles())
        {
            if (file.isDirectory() == true)
            {
                delete(file);
            }
            else
            {
                file.delete();
            }
        }
        
        folder.delete();
    }    
    
}