#!/usr/bin/env python
import sqlite3
from datetime import datetime

import pandas as pd
DATABASE_NAME = "dataset.sqlite3"     #     botnet.db dataset.sqlite3

class Database(object):
    def __init__(self, database_name=DATABASE_NAME):
        self.database_name = database_name
        db = sqlite3.connect(self.database_name, detect_types=sqlite3.PARSE_DECLTYPES)
        c = db.cursor()
        # Table live flows
        c.execute("CREATE TABLE IF NOT EXISTS Flow ( \
                                          ID INTEGER PRIMARY KEY AUTOINCREMENT, \
                                          SYS_UPTIME INTEGER,\
                                          UNIXS INTEGER, \
                                          FIRST INTEGER, \
                                          LAST INTEGER, \
                                          IP_SOURCE TEXT,\
                                          IP_DEST TEXT, \
                                          PROTO INTEGER, \
                                          SOURCE_PROTO INTEGER, \
                                          DEST_PROTO INTEGER, \
                                          [timestamp] timestamp)")
        db.commit()
        c.close()

    def survival_read2(self, table="Flow", ):
        db = sqlite3.connect(self.database_name)
        c = db.cursor()
        sql = """SELECT  IP_SOURCE AS "IP_SOURCE", IP_DEST, (LAST - FIRST) + 1 AS "T",
                        (LAST IS NOT NULL) AS "C" FROM Flow ORDER BY ID DESC"""
        p = pd.read_sql_query(sql, db)
        c.close()
        return p



if __name__ == "__main__":
    pass
