const SHEET_NAME = 'Tasks'; // The tab that holds the task table.
const LISTS_SHEET_NAME = 'Lists'; // The tab whose column A holds the owner names.
const HEADER = ['Task ID', 'Title', 'Owner', 'Status', 'Due date', 'Hours', 'Urgent', 'Last changed'];
const FIRST_DATA_ROW = 2; // Row 1 is the header.
const RULE_ROWS = 200; // Rules cover rows 2 to 201.
const STATUSES = ['New', 'In progress', 'Blocked', 'Done'];
const MAX_HOURS = 40; // Largest accepted estimate in the Hours column.
const HELP_PREFIX = 'Task tracker: '; // Marks validation rules this script created.
const PROTECTION_DESCRIPTION = 'Task tracker header'; // Marks the header protection.

const OWNER_COLUMN = 3;
const STATUS_COLUMN = 4;
const DUE_COLUMN = 5;
const HOURS_COLUMN = 6;
const URGENT_COLUMN = 7;
const LAST_CHANGED_COLUMN = 8;

/**
 * Checks the Tasks table, then applies this script's validation rules,
 * conditional format rules, timestamp format, and header warning.
 * Rules that this script did not create are kept.
 *
 * @returns {void}
 */
function applyTaskRules() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = getTasksSheet_(spreadsheet);
  checkHeader_(sheet);
  if (sheet.getMaxRows() < FIRST_DATA_ROW + RULE_ROWS - 1) {
    throw new Error('Tasks needs at least ' + (FIRST_DATA_ROW + RULE_ROWS - 1) + ' rows. Add rows at the bottom of the tab.');
  }

  const ownerRange = getOwnerListRange_(spreadsheet);
  const ownerNames = ownerRange.getValues().map(function (row) {
    return row[0];
  });
  checkExistingValues_(sheet, ownerNames);

  const validations = buildValidationRules_(ownerRange);
  validations.forEach(function (item) {
    checkValidationOwnership_(sheet, item.column);
  });

  // Every check has passed. The writes start here.
  validations.forEach(function (item) {
    sheet.getRange(FIRST_DATA_ROW, item.column, RULE_ROWS, 1).setDataValidation(item.rule);
  });
  const formatResult = replaceOwnedFormatRules_(sheet);
  sheet.getRange(FIRST_DATA_ROW, LAST_CHANGED_COLUMN, RULE_ROWS, 1).setNumberFormat('yyyy-mm-dd hh:mm');
  const protectionCreated = protectHeaderWithWarning_(sheet);

  Logger.log('Applied ' + validations.length + ' validation rules.');
  Logger.log('Kept ' + formatResult.kept + ' conditional format rule(s) this script does not own; replaced ' +
    formatResult.replaced + ' owned rule(s) with ' + formatResult.added + '.');
  Logger.log(protectionCreated ? 'Added a warning-only header protection.' : 'Header protection already present.');
}

/**
 * Removes only the validation rules, conditional format rules, and header
 * protection that applyTaskRules created. Cell values are not changed.
 *
 * @returns {void}
 */
function removeTaskRules() {
  const sheet = getTasksSheet_(SpreadsheetApp.getActiveSpreadsheet());
  const columns = [OWNER_COLUMN, STATUS_COLUMN, DUE_COLUMN, HOURS_COLUMN, URGENT_COLUMN];
  columns.forEach(function (column) {
    checkValidationOwnership_(sheet, column);
  });

  columns.forEach(function (column) {
    sheet.getRange(FIRST_DATA_ROW, column, RULE_ROWS, 1).setDataValidation(null);
  });

  const ownedSignatures = buildFormatRules_(sheet).map(ruleSignature_);
  const kept = sheet.getConditionalFormatRules().filter(function (rule) {
    return ownedSignatures.indexOf(ruleSignature_(rule)) === -1;
  });
  sheet.setConditionalFormatRules(kept);

  sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function (protection) {
    if (protection.getDescription() === PROTECTION_DESCRIPTION) {
      protection.remove();
    }
  });
  Logger.log('Removed this script\'s rules. Kept ' + kept.length + ' other conditional format rule(s).');
}

/**
 * Simple trigger. When a person edits Status in a task row, writes the
 * current time into that row's Last changed cell.
 *
 * @param {Events.SheetsOnEdit} e
 * @returns {void}
 */
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== SHEET_NAME) {
    return;
  }

  const firstColumn = range.getColumn();
  const lastColumn = range.getLastColumn();
  if (firstColumn > STATUS_COLUMN || lastColumn < STATUS_COLUMN) {
    return; // The edit did not touch Status.
  }

  const firstRow = Math.max(range.getRow(), FIRST_DATA_ROW);
  const lastRow = Math.min(range.getLastRow(), sheet.getLastRow());
  if (firstRow > lastRow) {
    return; // Header-only edit, or an edit below the table.
  }

  const isSingleCell = range.getNumRows() === 1 && range.getNumColumns() === 1;
  if (isSingleCell && e.value === e.oldValue) {
    return; // The same value was entered again.
  }

  const header = sheet.getRange(1, 1, 1, HEADER.length).getValues()[0];
  if (header[STATUS_COLUMN - 1] !== 'Status' || header[LAST_CHANGED_COLUMN - 1] !== 'Last changed') {
    return; // Columns moved. Do not guess where the timestamp belongs.
  }

  const rowCount = lastRow - firstRow + 1;
  const rows = sheet.getRange(firstRow, 1, rowCount, HEADER.length).getValues();
  const now = new Date();
  const stamps = rows.map(function (row) {
    const hasTaskId = row[0] !== '';
    return [hasTaskId ? now : row[LAST_CHANGED_COLUMN - 1]];
  });
  sheet.getRange(firstRow, LAST_CHANGED_COLUMN, rowCount, 1).setValues(stamps);
}

/**
 * @param {SpreadsheetApp.Spreadsheet} spreadsheet
 * @returns {SpreadsheetApp.Sheet}
 */
function getTasksSheet_(spreadsheet) {
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);
  if (!sheet) {
    throw new Error('Tasks sheet is missing. Import sample.csv into a sheet named Tasks.');
  }
  return sheet;
}

/**
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {void}
 */
function checkHeader_(sheet) {
  const header = sheet.getRange(1, 1, 1, HEADER.length).getValues()[0];
  const matches = header.every(function (value, index) {
    return value === HEADER[index];
  });
  if (!matches) {
    throw new Error('Tasks must use this header row: ' + HEADER.join(', ') + '.');
  }
}

/**
 * Returns Lists!A2:A<last> after checking that it holds at least one name.
 *
 * @param {SpreadsheetApp.Spreadsheet} spreadsheet
 * @returns {SpreadsheetApp.Range}
 */
function getOwnerListRange_(spreadsheet) {
  const lists = spreadsheet.getSheetByName(LISTS_SHEET_NAME);
  if (!lists || lists.getRange(1, 1).getValue() !== 'Owner') {
    throw new Error('Lists sheet with Owner in A1 is missing. Import owners.csv into a sheet named Lists.');
  }
  const lastRow = lists.getLastRow();
  if (lastRow < 2) {
    throw new Error('Lists needs at least one owner name below A1.');
  }
  return lists.getRange(2, 1, lastRow - 1, 1);
}

/**
 * Stops before any rule changes if existing task rows would break a rule.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {Array<*>} ownerNames
 * @returns {void}
 */
function checkExistingValues_(sheet, ownerNames) {
  const lastRow = sheet.getLastRow();
  if (lastRow > FIRST_DATA_ROW + RULE_ROWS - 1) {
    throw new Error('Tasks has more than ' + RULE_ROWS + ' data rows. Increase RULE_ROWS first.');
  }
  if (lastRow < FIRST_DATA_ROW) {
    return;
  }

  const rows = sheet.getRange(FIRST_DATA_ROW, 1, lastRow - 1, HEADER.length).getValues();
  rows.forEach(function (row, index) {
    const rowNumber = index + FIRST_DATA_ROW;
    const owner = row[OWNER_COLUMN - 1];
    const status = row[STATUS_COLUMN - 1];
    const due = row[DUE_COLUMN - 1];
    const hours = row[HOURS_COLUMN - 1];
    const urgent = row[URGENT_COLUMN - 1];

    if (owner !== '' && ownerNames.indexOf(owner) === -1) {
      throw new Error('Row ' + rowNumber + ': owner "' + owner + '" is not listed on the Lists tab.');
    }
    if (status !== '' && (typeof status !== 'string' || STATUSES.indexOf(status) === -1)) {
      throw new Error('Row ' + rowNumber + ': status "' + status + '" is not one of ' + STATUSES.join(', ') + '.');
    }
    if (due !== '' && !(due instanceof Date)) {
      throw new Error('Row ' + rowNumber + ': Due date is not a date value.');
    }
    if (hours !== '' && (typeof hours !== 'number' || hours < 0 || hours > MAX_HOURS)) {
      throw new Error('Row ' + rowNumber + ': Hours must be a number from 0 to ' + MAX_HOURS + '.');
    }
    if (urgent !== '' && typeof urgent !== 'boolean') {
      throw new Error('Row ' + rowNumber + ': Urgent must be blank, TRUE, or FALSE.');
    }
  });
}

/**
 * @param {SpreadsheetApp.Range} ownerRange
 * @returns {Array<{column: number, rule: SpreadsheetApp.DataValidation}>}
 */
function buildValidationRules_(ownerRange) {
  return [
    {
      column: OWNER_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireValueInRange(ownerRange, true)
        .setAllowInvalid(false)
        .setHelpText(HELP_PREFIX + 'choose an owner listed on the Lists tab.')
        .build()
    },
    {
      column: STATUS_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireValueInList(STATUSES, true)
        .setAllowInvalid(false)
        .setHelpText(HELP_PREFIX + 'choose ' + STATUSES.join(', ') + '.')
        .build()
    },
    {
      column: DUE_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireDate()
        .setAllowInvalid(true)
        .setHelpText(HELP_PREFIX + 'enter a date. Other values show a warning.')
        .build()
    },
    {
      column: HOURS_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireNumberBetween(0, MAX_HOURS)
        .setAllowInvalid(false)
        .setHelpText(HELP_PREFIX + 'enter hours from 0 to ' + MAX_HOURS + '.')
        .build()
    },
    {
      column: URGENT_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireCheckbox()
        .setHelpText(HELP_PREFIX + 'tick for urgent tasks.')
        .build()
    }
  ];
}

/**
 * Throws if any cell in the column's rule rows has a validation rule whose
 * help text does not start with HELP_PREFIX.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {number} column
 * @returns {void}
 */
function checkValidationOwnership_(sheet, column) {
  const rules = sheet.getRange(FIRST_DATA_ROW, column, RULE_ROWS, 1).getDataValidations();
  rules.forEach(function (row, index) {
    const rule = row[0];
    if (rule && (rule.getHelpText() || '').indexOf(HELP_PREFIX) !== 0) {
      throw new Error('Column ' + HEADER[column - 1] + ', row ' + (index + FIRST_DATA_ROW) +
        ' already has a validation rule this script did not create. Nothing was changed.');
    }
  });
}

/**
 * The conditional format rules this script owns, in priority order.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {SpreadsheetApp.ConditionalFormatRule[]}
 */
function buildFormatRules_(sheet) {
  const tableRange = sheet.getRange(FIRST_DATA_ROW, 1, RULE_ROWS, HEADER.length);
  const statusRange = sheet.getRange(FIRST_DATA_ROW, STATUS_COLUMN, RULE_ROWS, 1);
  const hoursRange = sheet.getRange(FIRST_DATA_ROW, HOURS_COLUMN, RULE_ROWS, 1);

  const overdue = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=AND($E2<>"", $E2<TODAY(), $D2<>"Done")')
    .setFontColor('#b3261e')
    .setBold(true)
    .setRanges([tableRange])
    .build();
  const blocked = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('Blocked')
    .setBackground('#fce8e6')
    .setRanges([statusRange])
    .build();
  const done = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('Done')
    .setBackground('#e6f4ea')
    .setFontColor('#5f6368')
    .setRanges([statusRange])
    .build();
  const hoursGradient = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMinpointWithValue('#ffffff', SpreadsheetApp.InterpolationType.NUMBER, '0')
    .setGradientMaxpointWithValue('#f6b26b', SpreadsheetApp.InterpolationType.NUMBER, String(MAX_HOURS))
    .setRanges([hoursRange])
    .build();

  return [overdue, blocked, done, hoursGradient];
}

/**
 * Keeps every rule this script does not own, then appends fresh copies of
 * the owned rules. Kept rules stay first, so they keep priority.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {{kept: number, replaced: number, added: number}}
 */
function replaceOwnedFormatRules_(sheet) {
  const ownedRules = buildFormatRules_(sheet);
  const ownedSignatures = ownedRules.map(ruleSignature_);
  const existing = sheet.getConditionalFormatRules();
  const kept = existing.filter(function (rule) {
    return ownedSignatures.indexOf(ruleSignature_(rule)) === -1;
  });
  sheet.setConditionalFormatRules(kept.concat(ownedRules));
  return { kept: kept.length, replaced: existing.length - kept.length, added: ownedRules.length };
}

/**
 * Describes a rule by its kind and ranges, plus its text for text rules.
 * Rules this script does not recognize get an empty signature.
 *
 * @param {SpreadsheetApp.ConditionalFormatRule} rule
 * @returns {string}
 */
function ruleSignature_(rule) {
  const ranges = rule.getRanges().map(function (range) {
    return range.getA1Notation();
  }).join(',');
  if (rule.getGradientCondition()) {
    return 'gradient|' + ranges;
  }
  const condition = rule.getBooleanCondition();
  if (!condition) {
    return '';
  }
  const type = condition.getCriteriaType();
  if (type === SpreadsheetApp.BooleanCriteria.TEXT_EQUAL_TO) {
    return 'text|' + ranges + '|' + condition.getCriteriaValues().join(',');
  }
  if (type === SpreadsheetApp.BooleanCriteria.CUSTOM_FORMULA) {
    return 'formula|' + ranges;
  }
  return '';
}

/**
 * Adds a warning-only protection to the header row once.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {boolean} true when a protection was created.
 */
function protectHeaderWithWarning_(sheet) {
  const existing = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).filter(function (protection) {
    return protection.getDescription() === PROTECTION_DESCRIPTION;
  });
  if (existing.length > 0) {
    return false;
  }
  sheet.getRange(1, 1, 1, HEADER.length)
    .protect()
    .setDescription(PROTECTION_DESCRIPTION)
    .setWarningOnly(true);
  return true;
}