/************************************************
  FX MARKET STRESS MONITOR
  VERSION: 1.0
************************************************/


/************************************************
  НАСТРОЙКИ
************************************************/


const CONFIG = {


  BANKS: [
    'ALFA',
    'GAZPROM',
    'RAIFFEISEN',
    'SBER',
    'SOVCOM',
    'TINKOFF',
    'VTB'
  ],


  /*
    Тинькофф не участвует
    в median расчёте
  */
  EXCLUDED_FROM_MEDIAN: [
    'TINKOFF'
  ],


  SUMMARY_SHEET: 'SUMMARY',
  INDEX_SHEET: 'INDEX',
  LOGS_SHEET: 'LOGS',


  TELEGRAM_BOT_TOKEN: 'ваш токен бота',
  TELEGRAM_CHAT_ID: 'ваш чат ид'


};


/************************************************
  ГЛАВНЫЙ ПРОЦЕСС
************************************************/


function buildMarketAnalytics() {


  try {


    logEvent('SYSTEM', 'START', 'buildMarketAnalytics');


    const summarySheet =
      getOrCreateSheet(CONFIG.SUMMARY_SHEET);


    const indexSheet =
      getOrCreateSheet(CONFIG.INDEX_SHEET);


    clearSummary(summarySheet);


    const bankRows = [];


    const spreadsForMedian = [];


    /************************************************
      СБОР ДАННЫХ
    ************************************************/


    CONFIG.BANKS.forEach(bank => {


      const data = getLatestBankData(bank);


      if (!data) {


        bankRows.push([
          bank,
          'NO DATA',
          '',
          '',
          '',
          'NO DATA'
        ]);


        logEvent(bank, 'WARN', 'NO DATA');


        return;


      }


      let status = 'NORMAL';


      /*
        Тинькофф пока считаем аномальным
      */


      if (bank === 'TINKOFF') {
        status = 'OUTLIER';
      }


      /*
        Только банки
        без Тинькофф
      */


      if (
        !CONFIG.EXCLUDED_FROM_MEDIAN.includes(bank)
      ) {


        spreadsForMedian.push(data.spread);


      }


      bankRows.push([
        bank,
        data.buy,
        data.sell,
        data.spread,
        data.date,
        status
      ]);


      logEvent(bank, 'OK', 'DATA LOADED');


    });


    /************************************************
      MEDIAN
    ************************************************/


    const medianSpread =
      calculateMedian(spreadsForMedian);


    const minSpread =
      Math.min(...spreadsForMedian);


    const maxSpread =
      Math.max(...spreadsForMedian);


    const stressLevel =
      getStressLevel(medianSpread);


    /************************************************
      SUMMARY TABLE
    ************************************************/


    summarySheet.getRange(1,1,1,6).setValues([[
      'Банк',
      'USD buy',
      'USD sell',
      'Спред',
      'Дата',
      'Status'
    ]]);


    if (bankRows.length > 0) {


      summarySheet.getRange(
        2,
        1,
        bankRows.length,
        6
      ).setValues(bankRows);


    }


    /************************************************
      MARKET BLOCK
    ************************************************/


    const statsRow = bankRows.length + 4;


    summarySheet.getRange(statsRow,1,5,2)
      .setValues([


        ['Median Spread', medianSpread],
        ['Min Spread', minSpread],
        ['Max Spread', maxSpread],
        ['Stress Level', stressLevel],
        ['Updated', new Date()]


      ]);


    /************************************************
      INDEX HISTORY
    ************************************************/


    indexSheet.appendRow([


      Utilities.formatDate(
        new Date(),
        Session.getScriptTimeZone(),
        'dd.MM.yyyy'
      ),


      medianSpread,
      minSpread,
      maxSpread,
      stressLevel


    ]);


    /************************************************
      ГРАФИК
    ************************************************/


    buildIndexChart(indexSheet);


    /************************************************
      TELEGRAM
    ************************************************/


    sendTelegramReport({
      medianSpread,
      stressLevel,
      bankRows
    });


    logEvent(
      'SYSTEM',
      'OK',
      'buildMarketAnalytics FINISHED'
    );


  } catch(e) {


    logEvent(
      'SYSTEM',
      'ERROR',
      e.toString()
    );


  }


}


/************************************************
  ПОЛУЧЕНИЕ ПОСЛЕДНЕЙ СТРОКИ БАНКА
************************************************/


function getLatestBankData(sheetName) {


  try {


    const ss =
      SpreadsheetApp.getActiveSpreadsheet();


    const sheet =
      ss.getSheetByName(sheetName);


    if (!sheet) return null;


    const lastRow =
      sheet.getLastRow();


    if (lastRow < 2) return null;


    const row =
      sheet.getRange(lastRow,1,1,5)
      .getValues()[0];


    return {


      date: row[0],
      buy: row[2],
      sell: row[3],
      spread: row[4]


    };


  } catch(e) {


    logEvent(
      sheetName,
      'ERROR',
      e.toString()
    );


    return null;


  }


}


/************************************************
  MEDIAN
************************************************/


function calculateMedian(values) {


  const sorted =
    values.sort((a,b) => a-b);


  const middle =
    Math.floor(sorted.length / 2);


  if (sorted.length % 2 === 0) {


    return +(
      (sorted[middle - 1] + sorted[middle]) / 2
    ).toFixed(4);


  }


  return sorted[middle];


}


/************************************************
  STRESS LEVEL
************************************************/


function getStressLevel(spread) {


  if (spread < 1.5) return 'GREEN';
  if (spread < 2.5) return 'YELLOW';


  return 'RED';


}


/************************************************
  TELEGRAM REPORT (FIXED + DATE CONTEXT)
************************************************/


function sendTelegramReport(data) {


  try {


    const emoji =
      data.stressLevel === 'GREEN'
        ? '🟢'
        : data.stressLevel === 'YELLOW'
          ? '🟡'
          : '🔴';


    /************************************************
      ДАТА СИСТЕМЫ
    ************************************************/


    const now = new Date();


    const date = Utilities.formatDate(
      now,
      Session.getScriptTimeZone(),
      'dd-MM-yyyy'
    );


    /************************************************
      ФОРМИРОВАНИЕ СООБЩЕНИЯ
    ************************************************/


    let text = '';


    text += `${emoji} FX Spread Monitor\n\n`;


    text += `🗓 Спред — ${date}\n\n`;


    text += `Средний спред:\n`;
    text += `${data.medianSpread}\n\n`;


    text += `────────────\n\n`;


    /************************************************
      БАНКИ (АЛФАВИТ)
    ************************************************/


    data.bankRows.forEach(row => {


      text += `${row[0]}\n\n`;


      text += `USD buy: ${row[1]}\n`;
      text += `USD sell: ${row[2]}\n`;
      text += `Спред: ${row[3]}\n\n`;


    });


    /************************************************
      TELEGRAM REQUEST
    ************************************************/


    const url =
      `https://api.telegram.org/bot${CONFIG.TELEGRAM_BOT_TOKEN}/sendMessage`;


    const payload = {


      chat_id: CONFIG.TELEGRAM_CHAT_ID,


      text: text,


      parse_mode: 'HTML'


    };


    const response = UrlFetchApp.fetch(url, {


      method: 'post',


      contentType: 'application/json',


      payload: JSON.stringify(payload),


      muteHttpExceptions: true


    });


    /************************************************
      LOG RESPONSE (ВАЖНО ДЛЯ ДЕБАГА)
    ************************************************/


    logEvent(
      'TELEGRAM',
      'OK',
      response.getContentText()
    );


  } catch(e) {


    logEvent(
      'TELEGRAM',
      'ERROR',
      e.toString()
    );


  }


}


/************************************************
  ГРАФИК INDEX
************************************************/


function buildIndexChart(sheet) {


  try {


    const charts =
      sheet.getCharts();


    charts.forEach(chart => {
      sheet.removeChart(chart);
    });


    const lastRow =
      sheet.getLastRow();


    if (lastRow < 2) return;


    const chart =
      sheet.newChart()


      .setChartType(
        Charts.ChartType.LINE
      )


      .addRange(
        sheet.getRange(1,1,lastRow,2)
      )


      .setPosition(2,7,0,0)


      .setOption(
        'title',
        'Median Spread Dynamics'
      )


      .build();


    sheet.insertChart(chart);


  } catch(e) {


    logEvent(
      'CHART',
      'ERROR',
      e.toString()
    );


  }


}


/************************************************
  LOGS
************************************************/


function logEvent(action, status, details) {


  const sheet =
    getOrCreateSheet(CONFIG.LOGS_SHEET);


  sheet.appendRow([


    new Date(),
    action,
    status,
    details


  ]);


}


/************************************************
  SHEETS
************************************************/


function getOrCreateSheet(name) {


  const ss =
    SpreadsheetApp.getActiveSpreadsheet();


  let sheet =
    ss.getSheetByName(name);


  if (!sheet) {


    sheet = ss.insertSheet(name);


  }


  return sheet;


}


/************************************************
  CLEAR SUMMARY
************************************************/


function clearSummary(sheet) {


  sheet.clearContents();


}


/************************************************
  TRIGGERS
************************************************/


function createDailyTriggers() {


  /*
    ОБНОВЛЕНИЕ ДАННЫХ
    16:00 МСК
  */


  ScriptApp.newTrigger('updateRates')


    .timeBased()


    .everyDays(1)


    .atHour(16)


    .create();


  /*
    АНАЛИТИКА + TG
    через 5 минут
  */


  ScriptApp.newTrigger(
    'buildMarketAnalytics'
  )


    .timeBased()


    .everyDays(1)


    .atHour(16)


    .nearMinute(5)


    .create();


}


/************************************************
  РУЧНОЙ ЗАПУСК
************************************************/


function runAll() {


  updateRates();


  Utilities.sleep(5000);


  buildMarketAnalytics();


}