import os
import csv
import sqlite3
import datetime
import sys

DB_FILE = "numeco-ecologiegouv.db"
ACTU_PATH = os.path.dirname(__file__) + "/../target/actu_stats.csv"
RV_PATH = os.path.dirname(__file__) + "/../target/rv_stats.csv"
IMAGES_PATH = os.path.dirname(__file__) + "/../target/images_stats.csv"
PAGES_TIME_PATH = os.path.dirname(__file__) + "/../target/articles_time.csv"
IMAGES_TIME_PATH = os.path.dirname(__file__) + "/../target/images_time.csv"

def export_stats():
    pass

import sqlite3
import os

def is_sqlite_db(file_path):
    if not os.path.isfile(file_path):
        return False

    with open(file_path, 'rb') as f:
        header = f.read(16)

    if header == b"SQLite format 3\x00":
        try:
            conn = sqlite3.connect(file_path)
            conn.close()
            return True
        except sqlite3.Error:
            return False
    else:
        return False

def init_db():
    db = sqlite3.connect(
        DB_FILE, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
    db.execute("PRAGMA foreign_keys = 1")
    cur = db.cursor()
    cur.execute(
        "CREATE TABLE IF NOT EXISTS mesures (ROWID INTEGER NOT NULL, date TIMESTAMP NOT NULL, PRIMARY KEY(ROWID))")
    cur.execute("""CREATE TABLE IF NOT EXISTS articles (url TEXT,
        title TEXT,
        date_article TEXT,
        requests INTEGER,
        transfered INTEGER,
        decoded INTEGER,
        elements INTEGER,
        mesureid INTEGER,
        FOREIGN KEY(mesureid) REFERENCES mesures(rowid))""")
    cur.execute("""CREATE TABLE IF NOT EXISTS images (url TEXT,
        page TEXT,
        size INTEGER,
        mesureid INTEGER,
        FOREIGN KEY(mesureid) REFERENCES mesures(rowid))""")
    db.commit()
    return db


def close_db(db):
    db.close()


def new_mesure(db, date = datetime.datetime.now()):
    cur = db.cursor()
    cur.execute(
        "INSERT INTO mesures ('date') VALUES(?)", (date,))
    db.commit()
    lastrowid = cur.lastrowid
    cur.close()
    return lastrowid


def save_mesures_articles(input_csv, db, mesure_id):
    with open(input_csv, newline='',) as csvfile:
        articles_reader = csv.DictReader(csvfile, delimiter='|')
        cur = db.cursor()
        data = [(row['Url'], row['Titre'], row['Date'], row['Nombre de requêtes'], row['Données transférées'],
                 row['Données décodées'], row["Nombre d'éléments"], mesure_id) for row in articles_reader]
        cur.executemany("INSERT INTO articles VALUES(?,?,?,?,?,?,?,?)", data)
        db.commit()
        cur.close()
        return data


def save_mesures_images(input_csv, db, mesure_id):
    with open(input_csv, newline='') as csvfile:
        images_reader = csv.DictReader(csvfile, delimiter='|')
        cur = db.cursor()
        data = [(row['Image'], row['Offre'], row['Taille'], mesure_id)
                for row in images_reader]
        cur.executemany("INSERT INTO images VALUES(?,?,?,?)", data)
        db.commit()
        cur.close()
        return data


def create_pages_stats(output_csv, db):
    with open(output_csv, 'w', newline='') as csvfile:
        articles_writer = csv.writer(csvfile, delimiter=',')
        articles_writer.writerow(["date", "stat", "type", "value"])
        cur = db.cursor()
        res = cur.execute("""SELECT mesures.date,
    min(articles.requests),max(articles.requests),cast(avg(articles.requests) as INT),
    min(articles.transfered),max(articles.transfered),cast(avg(articles.transfered) as INT),
    min(articles.elements),max(articles.elements),cast(avg(articles.elements) as INT)
FROM articles
INNER JOIN mesures on mesures.ROWID = articles.mesureid
GROUP BY articles.mesureid""")
        for row in res.fetchall():
            mesure_date = row[0]
            articles_writer.writerow([mesure_date, "REQUESTS", "MIN", row[1]])
            articles_writer.writerow([mesure_date, "REQUESTS", "MAX", row[2]])
            articles_writer.writerow([mesure_date, "REQUESTS", "MOY", row[3]])
            articles_writer.writerow([mesure_date, "TRANSFERED", "MIN", row[4]])
            articles_writer.writerow([mesure_date, "TRANSFERED", "MAX", row[5]])
            articles_writer.writerow([mesure_date, "TRANSFERED", "MOY", row[6]])
            articles_writer.writerow([mesure_date, "ELEMENTS", "MIN", row[7]])
            articles_writer.writerow([mesure_date, "ELEMENTS", "MAX", row[8]])
            articles_writer.writerow([mesure_date, "ELEMENTS", "MOY", row[9]])
    cur.close()

def create_images_stats(output_csv, db):
    with open(output_csv, 'w', newline='') as csvfile:
        images_writer = csv.writer(csvfile, delimiter=',')
        images_writer.writerow(["date", "value"])
        cur = db.cursor()
        res = cur.execute("""select mesures.date,
count(images.url)
from images
inner join mesures on mesures.ROWID = images.mesureid
group by images.mesureid""")
        images_writer.writerows([list(row) for row in res.fetchall()])
    cur.close()

if not (os.path.isfile(ACTU_PATH) and os.path.isfile(IMAGES_PATH)):
    print("Input CSV files are missing", file=sys.stderr)
    sys.exit(1)

if not is_sqlite_db(DB_FILE):
    if os.path.isfile(DB_FILE):
        print(f'WARNING : ${DB_FILE} is not a DB file', file=sys.stderr)
        os.remove(DB_FILE)
    else:
        print(f'WARNING : ${DB_FILE} does not exist', file=sys.stderr)

db = init_db()
mesure_id = new_mesure(db)
save_mesures_articles(ACTU_PATH, db, mesure_id)
save_mesures_articles(RV_PATH, db, mesure_id)
save_mesures_images(IMAGES_PATH, db, mesure_id)
create_pages_stats(PAGES_TIME_PATH, db)
create_images_stats(IMAGES_TIME_PATH, db)