package osm.jp.postgis;

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 java.util.ArrayList;
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;
import osm.jp.coverage.postoffice.DbPostoffice;

public class CoverageAll extends ArrayList<Coverage> {

    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);
        }
        finally {
            DatabaseTool.closeDb(conHsql);
            DatabaseTool.closeDb(conPost);
        }
    }

    public static DecimalFormat df3 = new DecimalFormat("##0.00");
    public String tableName;
    public File outputFile;
    
    /**
     * コンストラクタ
     * @param tableName 
     * @throws java.lang.ClassNotFoundException 
     * @throws java.sql.SQLException 
     * @throws java.io.IOException 
     */
    public CoverageAll(String tableName) 
            throws ClassNotFoundException, SQLException, IOException 
    {
        super();
        this.tableName = tableName;
    }
    
    public void load(Connection conPost, Connection conHsql)
            throws ClassNotFoundException, SQLException, IOException 
    {
        // 都道府県名(name)の設定
        for (int i=0; i < Japan.areaArgs.length; i++) {
            Coverage cover = new Coverage(i, Japan.areaArgs[i], DbPostoffice.TABLE_NAME);

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

                ps2 = conPost.prepareStatement("SELECT COUNT(*) FROM t_"+ tableName +" 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_"+ tableName +" 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_"+ tableName +" 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;
            this.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("率(%)");
        csv.add(line);
        System.out.println(line.toString());

        try {
            for (Coverage cover : this) {
                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();
        }
    }
    
    public void outputJson(File outputFile) 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 : this) {
                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)));
    }
}