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 jp.co.areaweb.tools.csv.CsvFile;
import jp.co.areaweb.tools.csv.CsvRecord;
import osm.jp.api.Japan;
import osm.jp.coverage.postoffice.DbPostoffice;

public abstract class CoverageAll extends ArrayList<CoverageData> {

    /*
    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;
    
    /**
     * コンストラクタ
     * @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;
    }
    
    /**
     * 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;


    public void load(Connection conPost, Connection conHsql, String where)
            throws ClassNotFoundException, SQLException, IOException 
    {
        // 都道府県名(name)の設定
        for (int i=0; i < Japan.areaArgs.length; i++) {
            CoverageData cover = new CoverageData(i, Japan.areaArgs[i], DbPostoffice.TABLE_NAME);

            // 分母(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);
                System.out.println(sql);
                PreparedStatement ps2 = conPost.prepareStatement(sql);
                ps2.setInt(1, 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));
                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 (CoverageData 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, 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\": \""+ LocalDate.now().format(DateTimeFormatter.ISO_DATE) +"\",");
            hw.newLine();
            hw.write(space(level) + "\"sourcedata\": \""+ sourcedata +"\",");
            hw.newLine();
            hw.write(space(level++) + "\"coverage\": [");
            hw.newLine();
            boolean head = true;
            for (CoverageData 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) + "\"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)));
    }
}