package osm.jp.postgis;
 
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import javax.json.Json;
import javax.json.JsonObject;
import javax.json.JsonReader;
import javax.json.JsonValue;
import jp.co.areaweb.tools.csv.CsvFile;
import jp.co.areaweb.tools.csv.CsvRecord;
import osm.jp.api.Japan;
 
public class CoverageAll {
 
    /*
    public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException
    {
        if (args.length < 3) {
            System.out.println("osm.jp.postgis.CoverageAll [outputfile.csv] [outputfile.json] [TABLE_NAME]");
            return;
        }
        File outputFileCsv = new File(args[0]);
        File outputFileJson = new File(args[1]);
        String tableName = args[2];
        
        CoverageAll obj = new CoverageAll(tableName);
        
        Connection conPost = DatabaseTool.openDb("postgis");
        Connection conHsql = DatabaseTool.openDb("database");
        try {
            obj.load(conPost, conHsql);
            obj.outputCSV(outputFileCsv);
            obj.outputJson(outputFileJson, "Data source");
        }
        finally {
            DatabaseTool.closeDb(conHsql);
            DatabaseTool.closeDb(conPost);
        }
    }
    */
 
    public static DecimalFormat df3 = new DecimalFormat("##0.00");
    public String tableName;
    public File outputFile;
    public ArrayList<CoverageData> data;
    public String dateStr;
    
    /**
     * 
     * @param pbfDate {"date":"2017-09-22"}
     * @throws java.io.FileNotFoundException
     */
    public CoverageAll(File pbfDate) throws FileNotFoundException, IOException {
        if (pbfDate == null) {
            dateStr = LocalDate.now().format(DateTimeFormatter.ISO_DATE);
        }
        else {
            String tmp;
            try (FileReader fr = new FileReader(pbfDate);
                BufferedReader br = new BufferedReader(fr)) 
            {
                while((tmp=br.readLine()) != null){
                    
                    JsonReader jsonReader = Json.createReader(new StringReader(tmp));
                    JsonObject obj = jsonReader.readObject();
                    dateStr = obj.getString("date");
                    System.out.println(dateStr);
                    return;
                }
            }
        }
    }
    
    public void setTablename(String tableName) {
        this.tableName = tableName;
    }
    
    public void setData(ArrayList<CoverageData> data) {
        this.data = data;
    }
    
    /**
     * useually : load(conPost, conHsql, null);
     * 
     * @param conPost
     * @param conHsql
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws IOException 
     */
    /*
    public abstract void load(Connection conPost, Connection conHsql)
            throws ClassNotFoundException, SQLException, IOException;
    */
 
    /**
     *
     * @param conPost
     * @param conHsql
     * @param tableName
     * @param where
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws IOException
     */
    public void load(Connection conPost, Connection conHsql, String tableName, String where)
            throws ClassNotFoundException, SQLException, IOException 
    {
        // 都道府県名(name)の設定
        for (int i=0; i < Japan.areaArgs.length; i++) {
            CoverageData cover = new CoverageData(i, Japan.areaArgs[i], tableName);
 
            // 分母(denominator)、分子(molecule)、Lv を記入
            // 全国の設定
            if (i == 0) {
                // 全国の総計
                String whereStr = ((where == null) ? "" : String.format("WHERE (%s)", where));
                String sql = String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr);
                System.out.println(sql);
                PreparedStatement ps2 = conPost.prepareStatement(sql);
                ResultSet rset2 = ps2.executeQuery();
                if (rset2.next()) {
                    cover.denominator = rset2.getLong(1);
                }
                rset2.close();
 
                whereStr = "WHERE (fixed > 0)" + ((where == null) ? "" : String.format(" and (%s)", where));
                sql = String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr);
                System.out.println(sql);
                ps2 = conPost.prepareStatement(sql);
                rset2 = ps2.executeQuery();
                if (rset2.next()) {
                    cover.molecule = rset2.getLong(1);
                }
                rset2.close();
            }
            else {
                // 都道府県毎の小計
                String whereStr = "WHERE (area=?)" + ((where == null) ? "" : String.format(" and (%s)", where));
                String sql = String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr);
                PreparedStatement ps2 = conPost.prepareStatement(sql);
                ps2.setInt(1, i);
                System.out.println(String.format("'%s', %d", sql, i));
                ResultSet rset2 = ps2.executeQuery();
                if (rset2.next()) {
                    cover.denominator = rset2.getLong(1);
                }
                rset2.close();
 
                whereStr = "WHERE (fixed > 0) and (area=?)" + ((where == null) ? "" : String.format(" and (%s)", where));
                sql = String.format("SELECT COUNT(*) FROM t_%s %s", tableName, whereStr);
                System.out.println(String.format("'%s', %d", sql, i));
                ps2 = conPost.prepareStatement(sql);
                ps2.setInt(1, i);
                rset2 = ps2.executeQuery();
                if (rset2.next()) {
                    cover.molecule = rset2.getLong(1);
                }
                rset2.close();
            }
 
            if (cover.denominator > 0) {
                cover.par = (double)cover.molecule / cover.denominator;
                cover.par *= 100.0D;
            }
            else {
                cover.par = 100.0D;
            }
            data.add(cover);
        }
    }
 
    /**
     * CSVファイルに出力する
     * @param outputFile
     * @throws IOException 
     */
    public void outputCSV(File outputFile) throws IOException {
        CsvFile csv = new CsvFile(outputFile);
        CsvRecord line = new CsvRecord();
        line.add("コード");
        line.add("都道府県");
        line.add("母数");
        line.add("入力数");
        line.add("率(%)");
        csv.add(line);
        System.out.println(line.toString());
 
        try {
            for (CoverageData cover : this.data) {
                line = new CsvRecord();
                line.add(String.valueOf(cover.areacode));
                line.add(cover.name);
                line.add(String.valueOf(cover.denominator));
                line.add(String.valueOf(cover.molecule));
                line.add(String.valueOf(df3.format(cover.par)));
                csv.add(line);
                System.out.println(line.toString());
            }
        }
        finally {
            csv.save();
        }
    }
    
    public void outputJson(File outputFile, String sourcedata) throws FileNotFoundException, UnsupportedEncodingException, IOException {
        try (BufferedWriter hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), "UTF-8"))) {
            int level = 0;
            hw.write(space(level++) + "{");
            hw.newLine();
            hw.write(space(level) + "\"timestamp\": \""+ dateStr +"\",");
            hw.newLine();
            hw.write(space(level) + "\"sourcedata\": \""+ sourcedata +"\",");
            hw.newLine();
            hw.write(space(level++) + "\"coverage\": [");
            hw.newLine();
            boolean head = true;
            for (CoverageData cover : this.data) {
                if (head == false) {
                    hw.write(space(level) + ",");
                }
                else {
                    head = false;
                }
                hw.write(space(level++) + "{");
                hw.newLine();
 
                hw.write(space(level) + "\"code\": "+ String.valueOf(cover.areacode) +",");
                hw.newLine();
                hw.write(space(level) + "\"name\": \""+ cover.name +"\",");
                hw.newLine();
                hw.write(space(level) + "\"denominator\": "+ String.valueOf(cover.denominator) +",");
                hw.newLine();
                hw.write(space(level) + "\"molecule\": "+ String.valueOf(cover.molecule) +",");
                hw.newLine();
                hw.write(space(level) + "\"par\": "+ String.valueOf(df3.format(cover.par)));
                hw.newLine();
 
                hw.write(space(--level) + "}");
                hw.newLine();
            }
            hw.write(space(--level) + "]");
            hw.newLine();
            hw.write(space(--level) + "}");
            hw.newLine();
            hw.flush();
        }
    }
    static String space(int level) {
        return ("            ".substring(0,(level > 12 ? 12 : level)));
    }
}