package osm.jp.coverage.fuel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
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 javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.TransformerException;
import org.xml.sax.SAXException;
import jp.co.areaweb.tools.csv.CsvFile;
import jp.co.areaweb.tools.csv.CsvRecord;
import jp.co.areaweb.tools.database.DatabaseTool;
import osm.jp.api.Japan;

public class Coverage {

    public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException
    {
        if (args.length < 1) {
            System.out.println("osm.jp.coverage.fuel.Coverage [outputfile.csv | outputfile.json]");
            return;
        }
        File outputFile = new File(args[0]);

        
        Connection conPost = DatabaseTool.openDb("postgis");
        Connection conHsql = DatabaseTool.openDb("database");
        try {
            // 都道府県名(name)の設定
            Coverage[] all = new Coverage[Japan.areaArgs.length];
            for (int i=0; i < Japan.areaArgs.length; i++) {
                Coverage cover = new Coverage(i, Japan.areaArgs[i]);

                // 分母(denominator)、分子(molecule)、Lv を記入
                // 全国の設定
                if (i == 0) {
                    PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_fuel;");
                    ResultSet rset2 = ps2.executeQuery();
                    if (rset2.next()) {
                        cover.denominator = rset2.getLong(1);
                    }
                    rset2.close();

                    ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_fuel WHERE fixed > 0;");
                    rset2 = ps2.executeQuery();
                    if (rset2.next()) {
                        cover.molecule = rset2.getLong(1);
                    }
                    rset2.close();
                }
                else {
                    PreparedStatement ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_fuel WHERE (area=?);");
                    ps2.setInt(1, i);
                    ResultSet rset2 = ps2.executeQuery();
                    if (rset2.next()) {
                        cover.denominator = rset2.getLong(1);
                    }
                    rset2.close();

                    ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_fuel WHERE (fixed > 0) and (area=?);");
                    ps2.setInt(1, i);
                    rset2 = ps2.executeQuery();
                    if (rset2.next()) {
                        cover.molecule = rset2.getLong(1);
                    }
                    rset2.close();
                }

                cover.par = (double)cover.molecule / cover.denominator;
                cover.par *= 100.0D;
                all[i] = cover;
            }
            if (outputFile.getName().toUpperCase().endsWith(".CSV")) {
                outputCSV(outputFile, all);
            }
            else if (outputFile.getName().toUpperCase().endsWith(".JSON")) {
                outputJson(outputFile, all);
            }
        }
        finally {
            DatabaseTool.closeDb(conHsql);
            DatabaseTool.closeDb(conPost);
        }
    }
    
    public static DecimalFormat df3 = new DecimalFormat("##0.00");
    
    /**
     * 以下はクラスのメンバ変数
     * setter/getterはめんどくさいのでつかわない
     */
    public int areacode;        // 1..47: 都道府県コード, 0: 全国合計
    public String name;         // 都道府県名称
    public long denominator;    // 分母(denominator)
    public long molecule;       // 分子(molecule)
    public double par;          // パーセンテージ

    /**
     * コンストラクタ
     * @param areacode
     * @param name 
     */
    public Coverage(int areacode, String name) {
        this.areacode = areacode;
        this.name = name;
        this.denominator = 1L;
        this.molecule = 0L;
        this.par = 0.0D;
    }
    
    /**
     * CSVファイルに出力する
     * @param outputFile
     * @param all   出力するCoverage[]
     * @throws IOException 
     */
    static void outputCSV(File outputFile, Coverage[] all) throws IOException {
        CsvFile csv = new CsvFile(outputFile);
        CsvRecord line = new CsvRecord();
        line.add("コード");
        line.add("都道府県");
        line.add("観測数");
        line.add("率(%)");
        csv.add(line);
        System.out.println(line.toString());

        try {
            for (Coverage cover : all) {
                line = new CsvRecord();
                line.add(String.valueOf(cover.areacode));
                line.add(cover.name);
                line.add(String.valueOf(cover.denominator));
                line.add(String.valueOf(df3.format(cover.par)));
                csv.add(line);
                System.out.println(line.toString());
            }
        }
        finally {
            csv.save();
        }
    }
    
    static void outputJson(File outputFile, Coverage[] all) 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\": \""+ LocalDate.now().format(DateTimeFormatter.ISO_DATE) +"\",");
            hw.newLine();
            hw.write(space(level) + "\"sourcedata\": \"国土数値情報　燃料給油所データ 平成２８年(2016)\",");
            hw.newLine();
            hw.write(space(level++) + "\"coverage\": [");
            hw.newLine();
            boolean head = true;
            for (Coverage cover : all) {
                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) + "\"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)));
    }
}