const SHEET_EMPLOYEES = '従業員マスタ';
const SHEET_SCHEDULE = 'メール送信スケジュール';
const SHEET_RESPONSES = 'フォームの回答 1';

const EMPLOYEE_EMAIL_HEADER = 'メールアドレス';
const EMPLOYEE_NAME_HEADER = '氏名';
const EMPLOYEE_TARGET_HEADER = '健診対象';

function sendHealthCheckReminderToday() {
	const ss = SpreadsheetApp.getActiveSpreadsheet();

	const employeeSheet = ss.getSheetByName(SHEET_EMPLOYEES);
	const scheduleSheet = ss.getSheetByName(SHEET_SCHEDULE);
	const responseSheet = ss.getSheetByName(SHEET_RESPONSES);

	if (!employeeSheet || !scheduleSheet || !responseSheet) {
		throw new Error('必要なシートが見つかりません。シート名を確認してください。');
	}

	const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy/MM/dd');

	const mailSetting = getTodayMailSetting_(scheduleSheet, today);
	if (!mailSetting) {
		Logger.log('本日送信するメールはありません: ' + today);
		return;
	}

	const employees = getTargetEmployees_(employeeSheet);
	const answeredEmails = getAnsweredEmails_(responseSheet);

	const targets = employees.filter(emp => !answeredEmails.has(emp.email));

	if (targets.length === 0) {
		Logger.log('未回答者はいません。');
		return;
	}

	targets.forEach(emp => {
		const body = mailSetting.body
			.replaceAll('{{氏名}}', emp.name)
			.replaceAll('{{メールアドレス}}', emp.email);

		GmailApp.sendEmail(emp.email, mailSetting.subject, body);
	});

	Logger.log('送信完了: ' + targets.length + '件');
}

function getTodayMailSetting_(sheet, today) {
	const values = sheet.getDataRange().getValues();

	for (let i = 1; i < values.length; i++) {
		const sendDate = values[i][0];
		const subject = values[i][1];
		const body = values[i][2];

		if (!sendDate || !subject || !body) continue;

		const formattedDate = Utilities.formatDate(new Date(sendDate), Session.getScriptTimeZone(), 'yyyy/MM/dd');

		if (formattedDate === today) {
			return {
				subject: subject,
				body: body
			};
		}
	}

	return null;
}

function getTargetEmployees_(sheet) {
	const values = sheet.getDataRange().getValues();
	const headers = values[0];

	const emailCol = headers.indexOf(EMPLOYEE_EMAIL_HEADER);
	const nameCol = headers.indexOf(EMPLOYEE_NAME_HEADER);
	const targetCol = headers.indexOf(EMPLOYEE_TARGET_HEADER);

	if (emailCol === -1) {
		throw new Error('従業員マスタに「メールアドレス」列がありません。');
	}

	return values.slice(1)
		.map(row => ({
			name: nameCol >= 0 ? row[nameCol] : '',
			email: String(row[emailCol] || '').trim().toLowerCase(),
			target: targetCol >= 0 ? row[targetCol] : ''
		}))
		.filter(emp => {
			if (!emp.email) return false;
			if (targetCol === -1) return true;
			return emp.target === true || emp.target === 'TRUE' || emp.target === '○' || emp.target === '対象' || emp.target === 1;
		});
}

function getAnsweredEmails_(sheet) {
	const values = sheet.getDataRange().getValues();
	const headers = values[0];

	const emailCol = headers.findIndex(header => String(header).includes('メールアドレス'));

	if (emailCol === -1) {
		throw new Error('フォーム回答シートにメールアドレス列がありません。');
	}

	const emails = new Set();

	values.slice(1).forEach(row => {
		const email = String(row[emailCol] || '').trim().toLowerCase();
		if (email) emails.add(email);
	});

	return emails;
}