import tabula
import requests
import csv
import pandas as pd

import re
import parse
import pdfplumber
from collections import namedtuple
import datetime
from datetime import date
import os
import glob
import shutil
from os import path

# using pdminer i am extracting all the post name , grade name and month repporting to add to this cleaned data frame.


# ------------------------------------File name
file = "C:\\Users\\xxx\\Downloads\\test.pdf"

lines = []

pnames = []
gnames = []
mreports = []
with pdfplumber.open(file) as pdf:
    for page in pdf.pages:
        try:
            text = page.extract_text()
        except:
            text = ''
        if text is not None:
            liness = text.split('\n')
            lines += liness

for li in lines:
    if "Port:" in li:
        li = li.replace("Port:", "").strip()
        li_new = li.split("Month Reporting:")[-0].strip()
        m_repor = li.split("Month Reporting:")[-1].strip()

        if "Grade Name:" in li_new:
            g_name = li_new.split("Grade Name:")[-1].strip()
            p_name = li_new.split("Grade Name:")[0].strip()
            print(li_new)
        else:
            g_name = li_new.split()[1:]
            g_name = ' '.join(g_name).strip()
            p_name = li_new.split()[0].strip()
        pnames.append(p_name)
        gnames.append(g_name)
        mreports.append(m_repor)
print("PortName: ", len(pnames))
print("GradeName: ", len(gnames))
print("MonthReporting: ", len(mreports))

# i am using tabula to extract all the tables from pdf and this table is cleaned for final joining.
df = tabula.read_pdf(file, pages='all')
final_list = [
    ["PORT NAME", "GRADE NAME", "MONTH REPORTING", "BL DATE", "VESSEL", "DESTINATION", "CHARTERERS", "API"]]
# final_list=[]
print(final_list)
last_df = len(df)
print("Length of tables: ", last_df)

for i in range(0, len(pnames)):
    op_df = df[i]
    op_df = op_df.dropna(how='all')
    op_df_list = op_df.values.tolist()

    for li in op_df_list:
        if str(li[0]) == "nan":
            li = li[1:]
        else:
            print("check this case")
            print(li)
        li.insert(0, pnames[i])
        li.insert(1, gnames[i])
        li.insert(2, mreports[i])
        print(li)
        if "BL Date" in li:
            pass
        else:
            final_list.append(li)
    df_2 = pd.DataFrame(final_list)
    df_2.columns = df_2.iloc[0]
    df_2 = df_2[1:]
    max_row=len(df_2)
    curr_date = datetime.datetime.now()
    created_date = curr_date.strftime('%d-%b-%y')
    for row in range(max_row):
        df_2['created_by'] = 'created by'
        df_2['created_date'] = created_date

    print(df_2)
    df_2.rename(
        columns={'PORT NAME': 'port_name', 'GRADE NAME': 'crude', 'MONTH REPORTING': 'reporting_month', 'BL DATE': 'bl_date',
                 'VESSEL': 'vessel', 'DESTINATION': 'destination',
                 'CHARTERERS': 'charterer', 'API': 'api'}, inplace=True)

    df_2 = df_2.reindex(
        columns=["port_name", "crude", "reporting_month", "bl_date", "vessel", "destination", "Charterer",
                 "api"])

    # return df_2


df_2.to_csv('Outputfile.csv', index=False)
print("Sucessfully generated output CSV")