/**
 * 定数定義
 */
const SHEET_NAME_TEMPLATE = 'テンプレート';
const SHEET_NAME_LIST = 'リスト';
const SHEET_NAME_BEGIN_RESULT = '■テスト結果';

/**
 * テーブル並び順
 */
const enum ScoreTableOrder {
    TestType = 0
    , Name = 1
    , Score = 2
    , AnswerDate = 3
}

/**
 * エントリーメソッド
 */
function main(workbook: ExcelScript.Workbook) {
    
    // テーブルをソート
    const table = workbook.getTable("ScoreTable");
    sortTable(table);

    // テーブルから重複を取り除いた一覧を取得
    let member_names = getColumnValuesFromTable(table, "名前");
    member_names = Array.from(new Set(member_names));
    let test_types = getColumnValuesFromTable(table, "科目");
    test_types = Array.from(new Set(test_types));

    // 転記先座標を取得
    const destination_poisitons = getdestinationPositionList(workbook, SHEET_NAME_TEMPLATE, test_types);

    // シートを全削除
    recreateScoreSheets(workbook);
    // シートを再作成
    const destination_sheet_map = new Map<string, ExcelScript.Worksheet>();
    const template_worksheet = workbook.getWorksheet(SHEET_NAME_TEMPLATE);
    member_names.forEach((name) => {
        const target_worksheet = template_worksheet.copy(ExcelScript.WorksheetPositionType.end);
        target_worksheet.setName(name);
        destination_sheet_map.set(name, target_worksheet);
    });

    // 1件ずつ取得する。
    const table_data = table.getRange().getValues();
    table_data.shift();
    table_data.forEach((row) => {
        const test_type = row[ScoreTableOrder.TestType] as string;
        const name = row[ScoreTableOrder.Name] as string;
        const score = row[ScoreTableOrder.Score] as string;

        const target_sheet = destination_sheet_map.get(name);
        const target_position = destination_poisitons.get(test_type);
        const origin_position = target_sheet.getRange(target_position);
        origin_position.getOffsetRange(0, 2).setValue(score);
        // 終端列を取得
        const write_position = origin_position.getOffsetRange(0, 999).getRangeEdge(ExcelScript.KeyboardDirection.left);
        write_position.getOffsetRange(0, 1).setValue(score);
    });
}

/**
 * テーブルをソートする
 */
function sortTable(table: ExcelScript.Table) {
    table.getSort().apply(
        [
            { key: ScoreTableOrder.Name, ascending: true }
            , { key: ScoreTableOrder.TestType, ascending: true }
            , { key: ScoreTableOrder.AnswerDate, ascending: true }
        ]
    );
}

/**
 * シートを全削除する
 */
function recreateScoreSheets(workbook: ExcelScript.Workbook) {
    // シートの削除
    const begin_sheet_position = workbook.getWorksheet(SHEET_NAME_BEGIN_RESULT).getPosition();
    const last_sheet_position = workbook.getLastWorksheet().getPosition();
    for (let i = last_sheet_position; i > begin_sheet_position; i--) {
        workbook.getWorksheets()[i].delete();
    }
}

/**
 * 科目データの転記先座標を取得
 */
function getdestinationPositionList(
    workbook: ExcelScript.Workbook
    , template_sheet_name: string
    , test_types: Array
    ): Map<string, string> {
    const worksheet_template = workbook.getWorksheet(template_sheet_name);
    const map = new Map<string, string>();
    for (let i = 0; i < test_types.length; i++) {
        const test_type = test_types[i];
        const result = worksheet_template.findAll(test_type, { completeMatch: true });
        let address = result.getAreas()[0].getAddress(); 
        address = address.replace(template_sheet_name + '!', '');
        map.set(test_types[i], address);
    }
    return map;
}

/**
 * テーブルから列情報を配列で返却する
 */
function getColumnValuesFromTable(table: ExcelScript.Table, column_name: string): Array {
    const table_column = table.getColumn(column_name);
    const array_values = table_column.getRange().getValues();
    array_values.shift();
    let values = new Array();
    array_values.forEach((cell: Array) => {
        values = values.concat(cell);
    });
    return values;
}