import os
import csv
import sqlite3
import datetime
import sys
import humanize

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"
PP_PAGES_TIME_PATH = os.path.dirname(__file__) + "/../target/politiques_publiques_time.csv"
PP_IMAGES_TIME_PATH = os.path.dirname(__file__) + "/../target/pp_images_time.csv"
PP_STATS = "stats"
PP_IMAGES = "images"
PP_STATS_HTML = os.path.dirname(__file__) + "/../target/polp_stats.html"
PP_IMAGES_STATS_HTML = os.path.dirname(__file__) + "/../target/polp_images_stats.html"

def export_stats():
    pass

import sqlite3
import os
from pathlib import Path

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))""")
    cur.execute("""CREATE TABLE IF NOT EXISTS politiques_publiques (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 polp_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_polp(mesure_type, db, mesure_id):
    dir = os.path.dirname(__file__) + "/../target"
    if mesure_type == "stats":
        polpFiles = list(Path(dir).glob('./polp[1-9]_stats.csv'))
    elif mesure_type == "images":
        polpFiles = list(Path(dir).glob('./polp[1-9]_images_stats.csv'))

    if not polpFiles:
        print("Input CSV files are missing", file=sys.stderr)
        sys.exit(1) 

    for file in polpFiles:
        get_polp_stats(file, db, mesure_id) if mesure_type == "stats" else get_polp_images(file, db, mesure_id)

    

def get_polp_stats(input_csv, db, mesure_id):   
    with open(input_csv, newline='',) as csvfile:
        polp_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 polp_reader]
        cur.executemany("INSERT INTO politiques_publiques 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 get_polp_images(input_csv, db, mesure_id):
    with open(input_csv, newline='') as csvfile:
        polp_images_reader = csv.DictReader(csvfile, delimiter='|')
        cur = db.cursor()
        data = [(row['Image'], row['Offre'], row['Taille'], mesure_id)
                for row in polp_images_reader]
        cur.executemany("INSERT INTO polp_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_polp_pages_stats(output_csv, db):
    with open(output_csv, 'w', newline='') as csvfile:
        polp_writer = csv.writer(csvfile, delimiter=',')
        polp_writer.writerow(["date", "stat", "type", "value"])
        cur = db.cursor()
        res = cur.execute("""SELECT mesures.date,
    min(politiques_publiques.requests),max(politiques_publiques.requests),cast(avg(politiques_publiques.requests) as INT),
    min(politiques_publiques.transfered),max(politiques_publiques.transfered),cast(avg(politiques_publiques.transfered) as INT),
    min(politiques_publiques.elements),max(politiques_publiques.elements),cast(avg(politiques_publiques.elements) as INT)
FROM politiques_publiques
INNER JOIN mesures on mesures.ROWID = politiques_publiques.mesureid
GROUP BY politiques_publiques.mesureid""")
        for row in res.fetchall():
            mesure_date = row[0]
            polp_writer.writerow([mesure_date, "REQUESTS", "MIN", row[1]])
            polp_writer.writerow([mesure_date, "REQUESTS", "MAX", row[2]])
            polp_writer.writerow([mesure_date, "REQUESTS", "MOY", row[3]])
            polp_writer.writerow([mesure_date, "TRANSFERED", "MIN", row[4]])
            polp_writer.writerow([mesure_date, "TRANSFERED", "MAX", row[5]])
            polp_writer.writerow([mesure_date, "TRANSFERED", "MOY", row[6]])
            polp_writer.writerow([mesure_date, "ELEMENTS", "MIN", row[7]])
            polp_writer.writerow([mesure_date, "ELEMENTS", "MAX", row[8]])
            polp_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()

def create_polp_stats_html(output_html, db, mesure_id):
    with open(output_html, 'w') as htmlfile:
        htmlfile.write('<div class="fr-table"><table>\n<thead><tr><th>Titre</th>\n\
<th>Date</th>\n\
<th>Url</th>\n\
<th>Nombre de requêtes</th>\n\
<th aria-sort="descending">Données transférées</th>\n\
<th>Données décodées</th>\n\
<th>Nombre d\'éléments</th></tr></thead>\n\
<tbody>')
        cur = db.cursor()
        res = cur.execute(f"""select title, date_article, url, requests, transfered, decoded, elements 
from politiques_publiques 
where mesureid = {mesure_id}""")
        for polp in res.fetchall():
            slug = polp[2].split("/")[-1]
            htmlfile.write(f'<tr><td>{polp[0]}</td>\n\
<td>{polp[1]}</td>\n\
<td><a href="{polp[2]}" target="_blank">{slug}</a></td>\n\
<td>{polp[3]}</td>\n\
<td>{humanize.naturalsize(polp[4])}</td>\n\
<td>{humanize.naturalsize(polp[5])}</td>\n\
<td>{polp[6]}</td></tr>\n')
        htmlfile.write('</tbody>\n\
</table>\n\
</div>')
        cur.close()
        htmlfile.close()

def create_polp_images_html(output_html, db, mesure_id):
    with open(output_html, 'w') as htmlfile:
        htmlfile.write('<div class="fr-table"><table>\n\
<thead><tr><th>Image</th>\n\
<th>Offre</th>\n\
<th aria-sort="descending">Taille</th></tr></thead>\n\
<tbody>')
        cur = db.cursor()
        res = cur.execute(f"""select url, page, size 
from polp_images 
where mesureid = {mesure_id}""")
        for polp in res.fetchall():
            imageFullName = polp[0].split("/")[-1]
            pageSlug = polp[1].split("/")[-1]
            htmlfile.write(f'<tr><td><a href="{polp[0]}" target="_blank">{imageFullName}</a></td>\n\
<td><a href="{polp[1]}" target="_blank">{pageSlug}</a></td>\n\
<td>{humanize.naturalsize(polp[2])}</td></tr>\n')
        htmlfile.write('</tbody>\n\
</table>\n\
</div>')
        cur.close()
        htmlfile.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)


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

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)
save_mesures_polp(PP_STATS, db, mesure_id)
save_mesures_polp(PP_IMAGES, db, mesure_id)
create_pages_stats(PAGES_TIME_PATH, db)
create_images_stats(IMAGES_TIME_PATH, db)
create_polp_pages_stats(PP_PAGES_TIME_PATH, db)
create_polp_images_stats(PP_IMAGES_TIME_PATH, db)
create_polp_images_html(PP_IMAGES_STATS_HTML, db, mesure_id)
create_polp_stats_html(PP_STATS_HTML, db, mesure_id)