File size: 29,191 Bytes
6ceaebb
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
90930ce
 
6ceaebb
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3b24624
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6ceaebb
 
 
 
 
 
 
 
4139434
6ceaebb
 
4139434
 
6ceaebb
4139434
 
 
 
6ceaebb
 
4139434
 
 
 
 
6ceaebb
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8b0312d
4a4580d
 
 
 
 
 
 
 
 
 
 
 
 
8b0312d
 
 
 
 
 
 
6ceaebb
 
 
 
21f88cc
 
6ceaebb
21f88cc
 
 
 
 
 
 
 
 
 
 
6ceaebb
 
 
4a4580d
6ceaebb
 
 
 
 
 
 
 
 
 
4a4580d
 
 
 
 
8b0312d
4a4580d
 
 
3b24624
 
4a4580d
 
 
 
 
 
 
 
 
 
8b0312d
 
4a4580d
 
 
 
 
 
 
 
6ceaebb
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4a4580d
 
6ceaebb
4a4580d
 
6ceaebb
4a4580d
6ceaebb
 
4a4580d
 
 
 
8b0312d
 
 
 
 
 
4a4580d
 
 
 
 
 
 
 
6ceaebb
4a4580d
 
 
 
6ceaebb
 
 
21f88cc
 
6ceaebb
 
 
 
 
 
4a4580d
 
 
 
 
 
 
 
 
 
ca861f0
4a4580d
 
 
 
ca861f0
4a4580d
 
 
 
 
ca861f0
 
 
 
4a4580d
 
 
 
 
ca861f0
 
 
 
4a4580d
ca861f0
8b0312d
 
4a4580d
ca861f0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4a4580d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3b24624
 
 
 
 
4a4580d
 
3b24624
 
 
 
 
 
 
 
 
 
 
4a4580d
3b24624
4a4580d
 
6ceaebb
 
 
4139434
6ceaebb
 
4139434
 
 
 
6a55662
6ceaebb
 
 
6a55662
 
 
6ceaebb
 
 
 
 
6a55662
 
 
 
 
 
 
 
 
 
6ceaebb
 
4139434
 
6a55662
4139434
6a55662
6ceaebb
 
6a55662
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6ceaebb
 
4139434
6ceaebb
6a55662
 
4139434
6ceaebb
6a55662
 
 
 
 
6ceaebb
6a55662
 
 
 
6ceaebb
 
4139434
6a55662
4139434
 
6a55662
 
6ceaebb
 
 
c2741d2
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8b0312d
 
c2741d2
 
 
 
 
 
 
 
 
 
 
 
8b0312d
 
 
c2741d2
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8b0312d
 
 
 
 
c2741d2
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8b0312d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
90930ce
 
 
 
 
 
8b0312d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
"""Google Forms CSV ใ‚คใƒณใƒใƒผใƒˆใƒ‘ใƒผใ‚ตใƒผ

ๆœŸๅพ…ใ™ใ‚‹ CSV ใƒ•ใ‚ฉใƒผใƒžใƒƒใƒˆ๏ผˆGoogle Forms ๅ›ž็ญ”ใฎใ‚นใƒ—ใƒฌใƒƒใƒ‰ใ‚ทใƒผใƒˆใ‚’ CSV ๅ‡บๅŠ›๏ผ‰:
  ๅˆ—1: ใ‚ฟใ‚คใƒ ใ‚นใ‚ฟใƒณใƒ—๏ผˆ็„ก่ฆ–๏ผ‰
  ๅˆ—2: ใŠๅๅ‰๏ผˆใ‚นใ‚ฟใƒƒใƒ•็™ป้ŒฒๅใจๅฎŒๅ…จไธ€่‡ด๏ผ‰
  ๅˆ—3ใ€œ: "4ๆœˆ1ๆ—ฅ(ๆœˆ)ใฎๅ‡บๅ‹คๅธŒๆœ›" ๅฝขๅผใฎๆ—ฅไป˜ๅˆ—๏ผˆใƒ—ใƒซใƒ€ใ‚ฆใƒณๅ€ค๏ผ‰
  ๆœซๅฐพ: ใ‚ซใ‚นใ‚ฟใƒ ๅ…ฅๅŠ›ใฎๆ™‚ๅˆป๏ผˆไปปๆ„๏ผ‰ใ€ๅ‚™่€ƒ๏ผˆไปปๆ„๏ผ‰
"""
from __future__ import annotations
import csv
import re
from dataclasses import dataclass, field
from typing import Optional
from models.schedule import ShiftRequest, SchedulePeriod
from models.employee import Employee
from utils.shift_patterns import ALL_PATTERNS


# ใƒฉใƒ™ใƒซ โ†’ ใƒ‘ใ‚ฟใƒผใƒณID ใฎ้€†ๅผ•ใใƒžใƒƒใƒ—๏ผˆGoogle Forms ้ธๆŠž่‚ขใƒฉใƒ™ใƒซใจไธ€่‡ดใ•ใ›ใ‚‹๏ผ‰
_LABEL_TO_ID: dict[str, Optional[str]] = {
    "ไผ‘ใฟ๏ผˆๅ‡บๅ‹คไธๅฏ๏ผ‰": None,
    "ไผ‘ใฟ": None,
    "ใ‚ซใ‚นใ‚ฟใƒ ๏ผˆๅ‚™่€ƒใซๆ™‚ๅˆปใ‚’่จ˜ๅ…ฅ๏ผ‰": "custom",
    "ใ‚ซใ‚นใ‚ฟใƒ ": "custom",
    "ๆœ‰็ตฆ": "paid_leave",
    "ๆœ‰็ตฆไผ‘ๆš‡": "paid_leave",
}
for _p in ALL_PATTERNS:
    _LABEL_TO_ID[_p.label] = _p.id


@dataclass
class ImportResult:
    requests: list[ShiftRequest] = field(default_factory=list)
    # ใ‚นใ‚ฟใƒƒใƒ•ๅ โ†’ ๅ–ๅพ—ไปถๆ•ฐ๏ผˆใƒžใƒƒใƒใ—ใŸใ‚นใ‚ฟใƒƒใƒ•ใฎใฟ๏ผ‰
    matched: dict[str, int]      = field(default_factory=dict)
    unmatched_names: list[str]   = field(default_factory=list)
    warnings: list[str]          = field(default_factory=list)


def _parse_date_from_header(header: str, period: SchedulePeriod) -> Optional[str]:
    """
    ใ€Œ4ๆœˆ1ๆ—ฅ(ๆœˆ)ใฎๅ‡บๅ‹คๅธŒๆœ›ใ€ใชใฉใฎใƒ˜ใƒƒใƒ€ใƒผใ‹ใ‚‰ YYYY-MM-DD ใ‚’่ฟ”ใ™ใ€‚
    ๆœŸ้–“ใซๅซใพใ‚Œใชใ„ๆ—ฅไป˜ใฏ None ใ‚’่ฟ”ใ™ใ€‚
    """
    m = re.search(r'(\d{1,2})ๆœˆ(\d{1,2})ๆ—ฅ', header)
    if not m:
        return None
    month, day = int(m.group(1)), int(m.group(2))
    for d in period.date_range():
        if d.month == month and d.day == day:
            return d.isoformat()
    return None


def _parse_numbered_shift_header(header: str, period: SchedulePeriod) -> Optional[str]:
    """
    ใ€ŒๅธŒๆœ›ใ‚ทใƒ•ใƒˆ [N]ใ€ๅฝขๅผใฎใƒ˜ใƒƒใƒ€ใƒผใ‹ใ‚‰ YYYY-MM-DD ใ‚’่ฟ”ใ™ใ€‚
    N ใฏ 1 ๅง‹ใพใ‚Šใง period.start_date ใ‹ใ‚‰ N-1 ๆ—ฅๅพŒใซๅฏพๅฟœใ™ใ‚‹ใ€‚
    ไพ‹: ใ€ŒๅธŒๆœ›ใ‚ทใƒ•ใƒˆ [3]ใ€โ†’ period.start_date + 2 days
    """
    m = re.search(r'\[(\d+)\]', header)
    if not m:
        return None
    n = int(m.group(1))
    dates = list(period.date_range())
    if 1 <= n <= len(dates):
        return dates[n - 1].isoformat()
    return None


def _time_range_to_pattern(time_str: str) -> tuple[Optional[str], Optional[str], Optional[str]]:
    """
    ใ€Œ6:00~10:00ใ€ใชใฉใฎๆ™‚ๅˆป็ฏ„ๅ›ฒๆ–‡ๅญ—ๅˆ—ใ‚’ (pattern_id, custom_start, custom_end) ใซๅค‰ๆ›ใ™ใ‚‹ใ€‚

    - ๆ—ขๅญ˜ใƒ‘ใ‚ฟใƒผใƒณใฎ start/end ใจๅฎŒๅ…จไธ€่‡ด โ†’ (pattern_id, None, None)
    - ไธ€่‡ดใชใ—                            โ†’ ("custom", start, end)
    - ่งฃๆžไธ่ƒฝ                            โ†’ (None, None, None)

    ๅฏพๅฟœใƒ•ใ‚ฉใƒผใƒžใƒƒใƒˆ: HH:MM ~ HH:MM๏ผˆๅŠ่ง’ใƒปๅ…จ่ง’ใฉใกใ‚‰ใ‚‚ๅฏ๏ผ‰
    """
    import unicodedata
    s = unicodedata.normalize('NFKC', time_str.strip())
    m = re.match(r'^(\d{1,2}:\d{2})\s*[~ใ€œ๏ฝž\-โ€“โ€”]+\s*(\d{1,2}:\d{2})$', s)
    if not m:
        return None, None, None

    def _norm(t: str) -> str:
        h, mn = t.split(':')
        return f"{int(h):02d}:{mn}"

    start = _norm(m.group(1))
    end   = _norm(m.group(2))

    for p in ALL_PATTERNS:
        if p.start and p.end:
            if _norm(p.start) == start and _norm(p.end) == end:
                return p.id, None, None

    # ๆ—ขๅญ˜ใƒ‘ใ‚ฟใƒผใƒณใซไธ€่‡ดใ—ใชใ„ โ†’ ใ‚ซใ‚นใ‚ฟใƒ ใจใ—ใฆๆ™‚ๅˆปใ‚’ไฟๆŒ
    return "custom", start, end


def _parse_custom_times(raw: str, period: SchedulePeriod) -> dict[str, tuple[str, str]]:
    """
    ใ€Œ4/1: 10:00ใ€œ16:00, 4/5: 11:00ใ€œ20:00ใ€ใชใฉใ‚’่งฃๆžใ—ใฆ
    {date_str: (start, end)} ใ‚’่ฟ”ใ™ใ€‚

    ๅฏพๅฟœใƒ•ใ‚ฉใƒผใƒžใƒƒใƒˆ:
      - 4/1: 10:00ใ€œ16:00
      - 4ๆœˆ1ๆ—ฅ: 10:00ใ€œ16:00
      - 4.1 10:00ใ€œ16:00
      - 4/1 10:00-16:00
      - 4/1 10:00๏ฝž16:00
      - 4/1 10:00โ€“16:00  ๏ผˆen dash๏ผ‰
      - ๅ…จ่ง’ๆ•ฐๅญ—ใƒปๅ…จ่ง’ใ‚ณใƒญใƒณ๏ผˆ่‡ชๅ‹•ใงๅŠ่ง’ใซๅค‰ๆ›๏ผ‰
    """
    import unicodedata
    # ๅ…จ่ง’่‹ฑๆ•ฐ่จ˜ๅทใ‚’ๅŠ่ง’ใซ็ตฑไธ€๏ผˆ๏ผ‘โ†’1ใ€๏ผšโ†’: ใชใฉ๏ผ‰
    raw = unicodedata.normalize('NFKC', raw)

    result: dict[str, tuple[str, str]] = {}
    pattern = re.findall(
        r'(\d{1,2})[/ๆœˆ.\-](\d{1,2})[ๆ—ฅ]?'   # ๆ—ฅไป˜: 4/1 4ๆœˆ1ๆ—ฅ 4.1 4-1
        r'[\s:๏ผš]*'                             # ๅŒบๅˆ‡ใ‚Š (ใ‚ณใƒญใƒณใƒปใ‚นใƒšใƒผใ‚นใƒปใชใ—)
        r'(\d{1,2}:\d{2})'                     # ้–‹ๅง‹ๆ™‚ๅˆป
        r'\s*[ใ€œ~๏ฝž\-โ€“โ€”]+\s*'                  # ็ฏ„ๅ›ฒ่จ˜ๅท
        r'(\d{1,2}:\d{2})',                    # ็ต‚ไบ†ๆ™‚ๅˆป
        raw,
    )
    for month_s, day_s, start, end in pattern:
        for d in period.date_range():
            if d.month == int(month_s) and d.day == int(day_s):
                result[d.isoformat()] = (start, end)
                break
    return result


def parse_forms_csv(
    csv_path: str,
    period: SchedulePeriod,
    employees: list[Employee],
) -> ImportResult:
    """
    Google Forms ใฎๅ›ž็ญ” CSV ใ‚’่งฃๆžใ—ใฆ ImportResult ใ‚’่ฟ”ใ™ใ€‚

    3็จฎ้กžใฎใƒ•ใ‚ฉใƒผใƒ ๅฝขๅผใซ่‡ชๅ‹•ๅฏพๅฟœ:

    ใ€ๆ—งๅฝขๅผใ€‘ใ‚ฐใƒชใƒƒใƒ‰่ณชๅ• / 1ๆ—ฅ1ๅˆ—
        ๅˆ—ใƒ˜ใƒƒใƒ€ใƒผไพ‹: ใ€Œ4ๆœˆ1ๆ—ฅ(ๆœˆ)ใฎๅ‡บๅ‹คๅธŒๆœ›ใ€
        ๅ€ค: ใƒ‘ใ‚ฟใƒผใƒณใƒฉใƒ™ใƒซ็›ดๆŽฅ

    ใ€ๆ–ฐๅฝขๅผใ€‘GASใƒ•ใ‚ฉใƒผใƒ  / 1ๆ—ฅ3ๅˆ—
        ๅˆ—ใƒ˜ใƒƒใƒ€ใƒผไพ‹:
          ใ€Œ4ๆœˆ1ๆ—ฅ๏ผˆๆœˆ๏ผ‰ใฏๅ‡บๅ‹คใงใใพใ™ใ‹๏ผŸใ€     โ†’ ๅ‡บๅ‹คๅฏๅฆๅˆ—
          ใ€Œ4ๆœˆ1ๆ—ฅ๏ผˆๆœˆ๏ผ‰ๆœ้ฃŸใฎๅธŒๆœ›ใ‚ทใƒ•ใƒˆใ‚’โ€ฆใ€   โ†’ ๆœ้ฃŸๆ™‚้–“ๅˆ—
          ใ€Œ4ๆœˆ1ๆ—ฅ๏ผˆๆœˆ๏ผ‰ใƒ‡ใ‚ฃใƒŠใƒผใฎๅธŒๆœ›ใ‚ทใƒ•ใƒˆใ‚’โ€ฆใ€โ†’ ใƒ‡ใ‚ฃใƒŠใƒผๆ™‚้–“ๅˆ—
        ๅ€ค:
          ๅฏๅฆๅˆ—: ใ€Œๆœ้ฃŸใฎใฟใ€ใ€Œใƒ‡ใ‚ฃใƒŠใƒผใฎใฟใ€ใ€Œใƒ€ใƒ–ใƒซ๏ผˆๆœ้ฃŸ๏ผ‹ใƒ‡ใ‚ฃใƒŠใƒผไธกๆ–น๏ผ‰ใ€ใ€Œไผ‘ใฟใ€
          ๆ™‚้–“ๅˆ—: ใƒ‘ใ‚ฟใƒผใƒณใƒฉใƒ™ใƒซ orใ€Œใใฎไป–๏ผˆๅ‚™่€ƒๆฌ„ใซๆ™‚ๅˆปใ‚’่จ˜ๅ…ฅ๏ผ‰ใ€

    ใ€ใƒใ‚งใƒƒใ‚ฏใƒœใƒƒใ‚ฏใ‚นๅฝขๅผใ€‘1ๆ—ฅ1ๅˆ—๏ผˆ่ค‡ๆ•ฐ้ธๆŠž๏ผ‰
        ๅˆ—ใƒ˜ใƒƒใƒ€ใƒผไพ‹:
          ใ€Œ4ๆœˆ1ๆ—ฅ๏ผˆๆœˆ๏ผ‰ใฎๅ‡บๅ‹คๅธŒๆœ›ๆ™‚้–“ใ€
        ๅ€ค:
          ่ค‡ๆ•ฐ้ธๆŠžใ•ใ‚ŒใŸๅ€คใŒใ‚ซใƒณใƒžๅŒบๅˆ‡ใ‚Šใง็ตๅˆใ•ใ‚ŒใŸๆ–‡ๅญ—ๅˆ—
          ๏ผˆไพ‹: "ๆœ้ฃŸ: 6:00ใ€œ10:00, ใƒ‡ใ‚ฃใƒŠใƒผ: 17:00ใ€œ21:00"๏ผ‰
    """
    result = ImportResult()
    name_to_emp: dict[str, Employee] = {e.name: e for e in employees}

    # ใ‚จใƒณใ‚ณใƒผใƒ‡ใ‚ฃใƒณใ‚ฐใ‚’ๅˆคๅฎš๏ผˆUTF-8 (BOMไป˜ใๅซใ‚€) ใง่ชญใ‚ใชใ‘ใ‚Œใฐ cp932 ใงๅ†่ฉฆ่กŒ๏ผ‰
    encoding = "utf-8-sig"
    try:
        with open(csv_path, encoding=encoding) as f:
            f.read()
    except UnicodeDecodeError:
        encoding = "cp932"
        result.warnings.append(
            "UTF-8 ใง่ชญใฟ่พผใ‚ใชใ‹ใฃใŸใŸใ‚ cp932 ใงๅ†่ฉฆ่กŒใ—ใพใ—ใŸใ€‚"
            "ๆฌกๅ›žใ‹ใ‚‰ใฏ CSV ใ‚’ UTF-8 ใงไฟๅญ˜ใ™ใ‚‹ใ“ใจใ‚’ๆŽจๅฅจใ—ใพใ™ใ€‚"
        )

    try:
        with open(csv_path, encoding=encoding, newline="") as f:
            reader = csv.DictReader(f)
            headers = list(reader.fieldnames or [])

            # โ”€โ”€ ๅๅ‰ๅˆ—ใ‚’็‰นๅฎš โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
            name_col = next(
                (h for h in headers if "ๅๅ‰" in h or "ๆฐๅ" in h), None
            )
            if not name_col:
                result.warnings.append(
                    "ๅๅ‰ๅˆ—ใŒ่ฆ‹ใคใ‹ใ‚Šใพใ›ใ‚“ใงใ—ใŸใ€‚"
                    "ใ€ŒใŠๅๅ‰ใ€ใพใŸใฏใ€Œๆฐๅใ€ใจใ„ใ†่ณชๅ•ใŒใ‚ใ‚‹ใ‹็ขบ่ชใ—ใฆใใ ใ•ใ„ใ€‚"
                )
                return result

            # ๅ‚™่€ƒๅˆ—ใƒปใ‚ซใ‚นใ‚ฟใƒ ๆ™‚ๅˆปๅˆ—
            note_col   = next((h for h in headers if "ๅ‚™่€ƒ" in h or "ใ‚ณใƒกใƒณใƒˆ" in h), None)
            custom_col = next((h for h in headers if "ใ‚ซใ‚นใ‚ฟใƒ " in h and "ๆ™‚ๅˆป" in h), None)

            # โ”€โ”€ ๆ—ฅไป˜ๅˆ—ใ‚’ๅˆ†้กž โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
            # date_str โ†’ {"avail": col, "b": col, "d": col, "checkbox": col, "direct": col}
            date_cols: dict[str, dict[str, str]] = {}
            for h in headers:
                ds = _parse_date_from_header(h, period)
                if not ds:
                    ds = _parse_numbered_shift_header(h, period)  # ใ€ŒๅธŒๆœ›ใ‚ทใƒ•ใƒˆ [N]ใ€ๅฝขๅผ
                if not ds:
                    continue
                if ds not in date_cols:
                    date_cols[ds] = {}
                if "ๅ‡บๅ‹คใงใใพใ™ใ‹" in h:
                    date_cols[ds]["avail"] = h          # ๆ–ฐๅฝขๅผ: ๅ‡บๅ‹คๅฏๅฆ
                elif "ๆœ้ฃŸ" in h and "ใ‚ทใƒ•ใƒˆ" in h:
                    date_cols[ds]["b"] = h              # ๆ–ฐๅฝขๅผ: ๆœ้ฃŸๆ™‚้–“
                elif "ใƒ‡ใ‚ฃใƒŠใƒผ" in h and "ใ‚ทใƒ•ใƒˆ" in h:
                    date_cols[ds]["d"] = h              # ๆ–ฐๅฝขๅผ: ใƒ‡ใ‚ฃใƒŠใƒผๆ™‚้–“
                elif "ๅ‡บๅ‹คๅธŒๆœ›ๆ™‚้–“" in h or "ๅธŒๆœ›ๆ™‚้–“" in h:
                    date_cols[ds]["checkbox"] = h       # ใƒใ‚งใƒƒใ‚ฏใƒœใƒƒใ‚ฏใ‚นไธ€ๆ‹ฌๅฝขๅผ
                else:
                    date_cols[ds]["direct"] = h         # ๆ—งๅฝขๅผ: ใƒ‘ใ‚ฟใƒผใƒณ็›ดๆŽฅ้ธๆŠž

            if not date_cols:
                result.warnings.append(
                    "ๆ—ฅไป˜ๅˆ—ใŒ่ฆ‹ใคใ‹ใ‚Šใพใ›ใ‚“ใงใ—ใŸใ€‚"
                    "ใƒ˜ใƒƒใƒ€ใƒผใซใ€Œ4ๆœˆ1ๆ—ฅใ€ใฎใ‚ˆใ†ใชๆ—ฅไป˜ใŒๅซใพใ‚Œใฆใ„ใ‚‹ใ‹็ขบ่ชใ—ใฆใใ ใ•ใ„ใ€‚"
                )

            # โ”€โ”€ ่กŒใ”ใจใซๅ‡ฆ็† โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
            for row_num, row in enumerate(reader, start=2):
                name = row.get(name_col, "").strip()
                if not name:
                    result.warnings.append(f"่กŒ{row_num}: ๅๅ‰ใŒ็ฉบใงใ™๏ผˆใ‚นใ‚ญใƒƒใƒ—๏ผ‰")
                    continue

                emp = name_to_emp.get(name)
                if emp is None:
                    if name not in result.unmatched_names:
                        result.unmatched_names.append(name)
                    result.warnings.append(
                        f"ใ€Œ{name}ใ€ใฏใ‚นใ‚ฟใƒƒใƒ•็ฎก็†ใซ็™ป้Œฒใ•ใ‚Œใฆใ„ใพใ›ใ‚“๏ผˆใ‚นใ‚ญใƒƒใƒ—๏ผ‰"
                    )
                    continue

                # ๅ‚™่€ƒใƒปใ‚ซใ‚นใ‚ฟใƒ ๆ™‚ๅˆปใ‚’่งฃๆž
                note = row.get(note_col, "").strip() if note_col else ""
                custom_times: dict[str, tuple[str, str]] = {}
                if note:
                    custom_times.update(_parse_custom_times(note, period))
                if custom_col and row.get(custom_col, "").strip():
                    custom_times.update(_parse_custom_times(row[custom_col], period))

                row_count = 0

                for date_str, cols in date_cols.items():
                    req = None

                    if "checkbox" in cols:
                        # โ”€โ”€ ใƒใ‚งใƒƒใ‚ฏใƒœใƒƒใ‚ฏใ‚นๅฝขๅผ๏ผˆ่ค‡ๆ•ฐ้ธๆŠž๏ผ‰ โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
                        req = _process_checkbox_fmt(
                            row, date_str, cols["checkbox"], custom_times, note, emp, result, name
                        )
                    elif "avail" in cols:
                        # โ”€โ”€ ๆ–ฐๅฝขๅผ: ๅฏๅฆๅˆ— + ๆ™‚้–“ๅˆ— โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
                        req = _process_new_fmt(
                            row, date_str, cols, custom_times, note, emp, result, name
                        )
                    elif "direct" in cols:
                        # โ”€โ”€ ๆ—งๅฝขๅผ: ็›ดๆŽฅใƒ‘ใ‚ฟใƒผใƒณ้ธๆŠž โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
                        req = _process_direct_fmt(
                            row, date_str, cols["direct"], custom_times, note, emp, result, name
                        )

                    if req is not None:
                        result.requests.append(req)
                        row_count += 1

                result.matched[name] = result.matched.get(name, 0) + row_count

    except UnicodeDecodeError as e:
        result.warnings.append(f"ๆ–‡ๅญ—ใ‚ณใƒผใƒ‰ใ‚จใƒฉใƒผ: {e}ใ€‚CSV ใ‚’ UTF-8 ใงไฟๅญ˜ใ—็›ดใ—ใฆใใ ใ•ใ„ใ€‚")
    except Exception as e:
        result.warnings.append(f"CSV ่งฃๆžใ‚จใƒฉใƒผ: {e}")

    return result


def _process_new_fmt(
    row: dict,
    date_str: str,
    cols: dict[str, str],
    custom_times: dict,
    note: str,
    emp,
    result: "ImportResult",
    name: str,
) -> "Optional[ShiftRequest]":
    """ๆ–ฐๅฝขๅผ๏ผˆGAS/APIๅˆ†ๅฒใƒ•ใ‚ฉใƒผใƒ ๏ผ‰ใฎ1ๆ—ฅๅˆ†ใ‚’ๅ‡ฆ็†ใ—ใฆ ShiftRequest ใ‚’่ฟ”ใ™ใ€‚"""
    avail_val = row.get(cols["avail"], "").strip()
    if not avail_val:
        return None  # ๆœชๅ›ž็ญ” = ใ‚นใ‚ญใƒƒใƒ—

    if avail_val in ("ใ„ใ„ใˆ๏ผˆไผ‘ใฟ๏ผ‰", "ใ„ใ„ใˆ", "ไผ‘ใฟ"):
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id=None, custom_start=None, custom_end=None, note=note,
        )

    b_val = row.get(cols.get("b", ""), "").strip() if "b" in cols else ""
    d_val = row.get(cols.get("d", ""), "").strip() if "d" in cols else ""

    # ไปฅๅ‰ใฎใ€Œๆœ้ฃŸใฎใฟใ€ใ€Œใƒ‡ใ‚ฃใƒŠใƒผใฎใฟใ€ใ€Œใƒ€ใƒ–ใƒซใ€ใฎ้ธๆŠž่‚ขใ ใฃใŸๅ ดๅˆใฎไบ’ๆ›ๅ‡ฆ็†
    if "ใƒ€ใƒ–ใƒซ" in avail_val:
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id="double", custom_start=None, custom_end=None, note=note,
        )
    elif "ๆœ้ฃŸ" in avail_val and "ใƒ‡ใ‚ฃใƒŠใƒผ" not in avail_val:
        return _resolve_time_value(b_val, date_str, custom_times, note, emp, result, name)
    elif "ใƒ‡ใ‚ฃใƒŠใƒผ" in avail_val and "ๆœ้ฃŸ" not in avail_val:
        return _resolve_time_value(d_val, date_str, custom_times, note, emp, result, name)

    # ๆ–ฐๅฝขๅผ๏ผˆใฏใ„ โž” ๆœ้ฃŸใƒปใƒ‡ใ‚ฃใƒŠใƒผไธกๆ–นใ‚’ใƒใ‚งใƒƒใ‚ฏ๏ผ‰
    has_b = bool(b_val and b_val != "ไผ‘ใฟ๏ผˆๅ‡บๅ‹คไธๅฏ๏ผ‰" and "ไผ‘ใฟ" not in b_val)
    has_d = bool(d_val and d_val != "ไผ‘ใฟ๏ผˆๅ‡บๅ‹คไธๅฏ๏ผ‰" and "ไผ‘ใฟ" not in d_val)

    if has_b and has_d:
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id="double", custom_start=None, custom_end=None, note=note,
        )
    elif has_b:
        return _resolve_time_value(b_val, date_str, custom_times, note, emp, result, name)
    elif has_d:
        return _resolve_time_value(d_val, date_str, custom_times, note, emp, result, name)
    else:
        # ๆ™‚้–“ๅธฏใซๆœ‰ๅŠนใชๅ€คใŒใชใ„ใ€ใพใŸใฏใ€Œไผ‘ใฟใ€ใŒ้ธใฐใ‚Œใฆใ„ใ‚‹ๅ ดๅˆ
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id=None, custom_start=None, custom_end=None, note=note,
        )


def _process_direct_fmt(
    row: dict,
    date_str: str,
    col: str,
    custom_times: dict,
    note: str,
    emp,
    result: "ImportResult",
    name: str,
) -> "Optional[ShiftRequest]":
    """ๆ—งๅฝขๅผ๏ผˆ็›ดๆŽฅใƒ‘ใ‚ฟใƒผใƒณ้ธๆŠž๏ผ‰ใฎ1ๆ—ฅๅˆ†ใ‚’ๅ‡ฆ็†ใ—ใฆ ShiftRequest ใ‚’่ฟ”ใ™ใ€‚"""
    value = row.get(col, "").strip()
    if not value:
        return None
    if value in ("ไผ‘ใฟ๏ผˆๅ‡บๅ‹คไธๅฏ๏ผ‰", "ไผ‘ใฟ"):
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id=None, custom_start=None, custom_end=None, note=note,
        )
    return _resolve_time_value(value, date_str, custom_times, note, emp, result, name)


def _resolve_time_value(
    value: str,
    date_str: str,
    custom_times: dict,
    note: str,
    emp,
    result: "ImportResult",
    name: str,
) -> "Optional[ShiftRequest]":
    """ใƒ‘ใ‚ฟใƒผใƒณใƒฉใƒ™ใƒซๆ–‡ๅญ—ๅˆ—ใ‚’ ShiftRequest ใซๅค‰ๆ›ใ™ใ‚‹ๅ…ฑ้€šๅ‡ฆ็†ใ€‚"""
    if not value:
        return None

    if "ใใฎไป–" in value or value in ("ใ‚ซใ‚นใ‚ฟใƒ ๏ผˆๅ‚™่€ƒๆฌ„ใซๆ™‚ๅˆปใ‚’่จ˜ๅ…ฅ๏ผ‰", "ใ‚ซใ‚นใ‚ฟใƒ "):
        cs = custom_times.get(date_str)
        if cs:
            custom_start, custom_end = cs
        else:
            custom_start = custom_end = None
            result.warnings.append(
                f"ใ€Œ{name}ใ€{date_str}: ใ€Œใใฎไป–/ใ‚ซใ‚นใ‚ฟใƒ ใ€้ธๆŠžใงใ™ใŒๅ‚™่€ƒๆฌ„ใซๆ™‚ๅˆปใŒ่ฆ‹ใคใ‹ใ‚Šใพใ›ใ‚“"
            )
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id="custom", custom_start=custom_start, custom_end=custom_end, note=note,
        )

    # ใƒฉใƒ™ใƒซใง็›ดๆŽฅไธ€่‡ดใ‚’่ฉฆใฟใ‚‹
    if value in _LABEL_TO_ID:
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id=_LABEL_TO_ID[value], custom_start=None, custom_end=None, note=note,
        )

    # ใ€ŒHH:MM~HH:MMใ€ๅฝขๅผใฎๆ™‚ๅˆป็ฏ„ๅ›ฒใ‚’่ฉฆใฟใ‚‹๏ผˆไพ‹: 6:00~10:00๏ผ‰
    if re.match(r'^\d{1,2}:\d{2}', value):
        pid, cs, ce = _time_range_to_pattern(value)
        if pid is not None:
            return ShiftRequest(
                employee_id=emp.id, date=date_str,
                pattern_id=pid, custom_start=cs, custom_end=ce, note=note,
            )

    result.warnings.append(
        f"ใ€Œ{name}ใ€{date_str}: ้ธๆŠžๅ€คใ€Œ{value}ใ€ใŒไธๆ˜Žใงใ™๏ผˆใ‚นใ‚ญใƒƒใƒ—๏ผ‰"
    )
    return None


# Google Forms ่จญๅ•ใƒ†ใƒณใƒ—ใƒฌใƒผใƒˆๆ–‡ๅญ—ๅˆ—
# โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€

def build_form_guide(period: SchedulePeriod, employees=None) -> str:
    """
    ๆŒ‡ๅฎšๆœŸ้–“็”จใฎ Google Forms ่จญๅ•ใƒ†ใƒณใƒ—ใƒฌใƒผใƒˆใ‚’ไบบ้–“ใŒ่ชญใ‚ใ‚‹ๆ–‡ๅญ—ๅˆ—ใง่ฟ”ใ™ใ€‚

    Parameters
    ----------
    period    : ๅฏพ่ฑกใ‚ทใƒ•ใƒˆๆœŸ้–“
    employees : ็™ป้Œฒๆธˆใฟใ‚นใ‚ฟใƒƒใƒ•ไธ€่ฆง๏ผˆNone ใฎๅ ดๅˆใฏใ€Œใ‚นใ‚ฟใƒƒใƒ•ๅใ‚’่ฟฝๅŠ ใ€ใจ่กจ่จ˜๏ผ‰
    """
    from utils.constants import DAY_OF_WEEK_LABELS

    all_dates = list(period.date_range())
    n = len(all_dates)

    lines = [
        "โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•",
        f"  Google Forms ใƒ†ใƒณใƒ—ใƒฌใƒผใƒˆ  ๏ผˆๅฏพ่ฑกๆœŸ้–“: {period.start_date} ๏ฝž {period.end_date}๏ผ‰",
        "โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•",
        "",
        "ใ€ใƒ•ใ‚ฉใƒผใƒ ๆง‹ๆˆใ€‘",
        "  Q1: ใŠๅๅ‰๏ผˆใƒ—ใƒซใƒ€ใ‚ฆใƒณ๏ผ‰",
        f"  Q2: ๅธŒๆœ›ใ‚ทใƒ•ใƒˆ๏ผˆ้ธๆŠžๅผใ‚ฐใƒชใƒƒใƒ‰ใƒป{n}่กŒร—ใ‚ทใƒ•ใƒˆๅˆ—๏ผ‰",
        "  Q3: ๅ‚™่€ƒ๏ผˆ่จ˜่ฟฐๅผใƒปไปปๆ„๏ผ‰",
        "",
        "โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€",
        "ใ€Q1ใ€‘ใŠๅๅ‰  โ† ใƒ—ใƒซใƒ€ใ‚ฆใƒณ",
        "โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€",
        "  ไฝœๆˆๆ‰‹้ †: ใ€Œใƒ—ใƒซใƒ€ใ‚ฆใƒณใ€ใ‚’้ธๆŠž โ†’ ้ธๆŠž่‚ขใซไปฅไธ‹ใ‚’่ฟฝๅŠ ",
        "",
    ]

    if employees:
        for emp in employees:
            lines.append(f"    {emp.name}")
    else:
        lines.append("    ๏ผˆ็™ป้Œฒๆธˆใฟใ‚นใ‚ฟใƒƒใƒ•ๅใ‚’1ๅใšใค่ฟฝๅŠ ใ—ใฆใใ ใ•ใ„๏ผ‰")

    lines += [
        "",
        "โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€",
        "ใ€Q2ใ€‘ๅธŒๆœ›ใ‚ทใƒ•ใƒˆ  โ† ้ธๆŠžๅผใ‚ฐใƒชใƒƒใƒ‰",
        "โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€",
        "  ไฝœๆˆๆ‰‹้ †:",
        "    1. ใ€Œ้ธๆŠžๅผใ‚ฐใƒชใƒƒใƒ‰ใ€ใ‚’้ธๆŠž",
        "    2. ่ณชๅ•ๆ–‡: ใ€ŒๅธŒๆœ›ใ‚ทใƒ•ใƒˆใ€",
        "    3. ใ€Œๅ„่กŒใซๅ›ž็ญ”ใ‚’ๅฟ…้ ˆใซใ™ใ‚‹ใ€ใฏ ใ‚ชใƒ•๏ผˆๆœชๅ›ž็ญ” = ไผ‘ใฟๆ‰ฑใ„๏ผ‰",
        "",
        "  โ–ผ ่กŒใƒฉใƒ™ใƒซ๏ผˆๆ—ฅไป˜ใ‚’ไธŠใ‹ใ‚‰้ †ใซ่ฟฝๅŠ ๏ผ‰:",
    ]

    for i, d in enumerate(all_dates, start=1):
        dow = DAY_OF_WEEK_LABELS[d.weekday()]
        lines.append(f"    {i}  ๏ผˆ{d.month}/{d.day}ใƒป{dow}๏ผ‰")

    lines += [
        "",
        "  โ–ผ ๅˆ—ใƒฉใƒ™ใƒซ๏ผˆใ‚ทใƒ•ใƒˆๆ™‚้–“ๅธฏใ‚’ๅทฆใ‹ใ‚‰้ †ใซ่ฟฝๅŠ ๏ผ‰:",
        "    โ€ป ๅบ—่ˆ—ใฎๅฎŸ้š›ใฎๅ‹คๅ‹™ๆ™‚้–“ๅธฏใซๅˆใ‚ใ›ใฆ่‡ช็”ฑใซ่จญๅฎšใ—ใฆใใ ใ•ใ„",
        "    โ€ป ไปฅไธ‹ใฏใ‚ขใƒ—ใƒชใซ็™ป้Œฒๆธˆใฟใฎๆจ™ๆบ–ใƒ‘ใ‚ฟใƒผใƒณไพ‹:",
        "",
    ]

    for p in ALL_PATTERNS:
        if p.id not in ("custom", "double") and p.start and p.end:
            lines.append(f"    {p.start}~{p.end}")

    lines += [
        "    ไผ‘ใฟ",
        "",
        "  โš ๏ธ  ๅˆ—ใƒฉใƒ™ใƒซใฏใ€ŒHH:MM~HH:MMใ€ๅฝขๅผใงๅ…ฅๅŠ›ใ—ใฆใใ ใ•ใ„",
        "     ๏ผˆไพ‹: 6:00~10:00๏ผ‰",
        "     ใ‚ขใƒ—ใƒชใซ็™ป้Œฒใ•ใ‚Œใฆใ„ใชใ„ๆ™‚้–“ๅธฏใฏใ‚ซใ‚นใ‚ฟใƒ ใจใ—ใฆ่‡ชๅ‹•็™ป้Œฒใ•ใ‚Œใพใ™",
        "",
        "โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€",
        "ใ€Q3ใ€‘ๅ‚™่€ƒ  โ† ่จ˜่ฟฐๅผ๏ผˆ้•ทๆ–‡๏ผ‰ใƒปไปปๆ„",
        "โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€",
        "  ่ณชๅ•ๆ–‡: ใ€Œใใฎไป–ใ€ใ‚ทใƒ•ใƒˆใซ้–ขใ™ใ‚‹้€ฃ็ตกไบ‹้ …ใŒใ‚ใ‚Œใฐ่จ˜ๅ…ฅใ—ใฆใใ ใ•ใ„ใ€",
        "",
        "โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•",
        "  ใ€CSV ๅ–ใ‚Š่พผใฟๆ‰‹้ †ใ€‘",
        "  1. ใƒ•ใ‚ฉใƒผใƒ ใฎๅ›ž็ญ”ใ‚ฟใƒ– โ†’ใ€Œใ‚นใƒ—ใƒฌใƒƒใƒ‰ใ‚ทใƒผใƒˆใซใƒชใƒณใ‚ฏใ€",
        "  2. ใ‚นใƒ—ใƒฌใƒƒใƒ‰ใ‚ทใƒผใƒˆใ‚’้–‹ใ",
        "  3. ใƒ•ใ‚กใ‚คใƒซ โ†’ ใƒ€ใ‚ฆใƒณใƒญใƒผใƒ‰ โ†’ CSV๏ผˆ.csv๏ผ‰",
        "  4. ใ“ใฎใ‚ขใƒ—ใƒชใฎใ€ŒๅธŒๆœ›ใ‚ทใƒ•ใƒˆๅ…ฅๅŠ›ใ€็”ป้ขใง",
        "     ใ€ŒGoogle Forms CSV ใ‚’ใ‚คใƒณใƒใƒผใƒˆใ€ใ‹ใ‚‰่ชญใฟ่พผใ‚€",
        "โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•โ•",
    ]
    return "\n".join(lines)


def parse_google_form_responses(
    form_schema: dict,
    responses: list[dict],
    period: SchedulePeriod,
    employees: list[Employee],
) -> ImportResult:
    """
    Google Forms API ใฎใƒฌใ‚นใƒใƒณใ‚น (JSON) ใ‚’่งฃๆžใ—ใฆ ImportResult ใ‚’่ฟ”ใ™ใ€‚
    """
    result = ImportResult()
    name_to_emp: dict[str, Employee] = {e.name: e for e in employees}

    question_id_to_title = {}
    titles = []
    name_col = None
    note_col = None

    items = form_schema.get("items", [])
    for item in items:
        title = item.get("title", "")
        q_item = item.get("questionItem")
        if q_item and "question" in q_item:
            q_id = q_item["question"]["questionId"]
            question_id_to_title[q_id] = title
            titles.append(title)
            
            if "ๅๅ‰" in title or "ๆฐๅ" in title:
                name_col = title
            elif "ๅ‚™่€ƒ" in title or "้€ฃ็ตกไบ‹้ …" in title:
                note_col = title

    if not name_col:
        result.warnings.append("ใŠๅๅ‰ใ‚’้ธๆŠžใ™ใ‚‹่ณชๅ•ใŒ่ฆ‹ใคใ‹ใ‚Šใพใ›ใ‚“ใงใ—ใŸใ€‚")
        return result

    # ๆ—ฅไป˜ใ”ใจใฎ่จญๅ•ใ‚’็‰นๅฎš
    date_cols: dict[str, dict[str, str]] = {}
    for title in titles:
        ds = _parse_date_from_header(title, period)
        if not ds:
            continue
        if ds not in date_cols:
            date_cols[ds] = {}
        if "ๅ‡บๅ‹คใงใใพใ™ใ‹" in title:
            date_cols[ds]["avail"] = title
        elif "ๆœ้ฃŸ" in title and "ใ‚ทใƒ•ใƒˆ" in title:
            date_cols[ds]["b"] = title
        elif "ใƒ‡ใ‚ฃใƒŠใƒผ" in title and "ใ‚ทใƒ•ใƒˆ" in title:
            date_cols[ds]["d"] = title
        elif "ๅ‡บๅ‹คๅธŒๆœ›ๆ™‚้–“" in title or "ๅธŒๆœ›ๆ™‚้–“" in title:
            date_cols[ds]["checkbox"] = title
        else:
            date_cols[ds]["direct"] = title

    for idx, resp in enumerate(responses, start=1):
        answers = resp.get("answers", {})
        row = {}
        for q_id, ans in answers.items():
            title = question_id_to_title.get(q_id)
            if not title:
                continue
            text_answers = ans.get("textAnswers", {}).get("answers", [])
            if text_answers:
                # ่ค‡ๆ•ฐ้ธๆŠžใฎใƒใ‚งใƒƒใ‚ฏใƒœใƒƒใ‚ฏใ‚นๅฏพๅฟœ: ใ‚ซใƒณใƒžๅŒบๅˆ‡ใ‚Šใฎๆ–‡ๅญ—ๅˆ—ใซใ—ใฆ CSV ๅฝขๅผใซ็ตฑไธ€ใ™ใ‚‹
                val = ", ".join(ta.get("value", "").strip() for ta in text_answers if ta.get("value"))
                row[title] = val

        name = row.get(name_col, "").strip()
        if not name:
            result.warnings.append(f"ๅ›ž็ญ”{idx}: ๅๅ‰ใŒ็ฉบใงใ™๏ผˆใ‚นใ‚ญใƒƒใƒ—๏ผ‰")
            continue

        emp = name_to_emp.get(name)
        if emp is None:
            if name not in result.unmatched_names:
                result.unmatched_names.append(name)
            result.warnings.append(
                f"ใ€Œ{name}ใ€ใฏใ‚นใ‚ฟใƒƒใƒ•็ฎก็†ใซ็™ป้Œฒใ•ใ‚Œใฆใ„ใพใ›ใ‚“๏ผˆใ‚นใ‚ญใƒƒใƒ—๏ผ‰"
            )
            continue

        # ๅ‚™่€ƒใƒปใ‚ซใ‚นใ‚ฟใƒ ๆ™‚ๅˆปใ‚’่งฃๆž
        note = row.get(note_col, "").strip() if note_col else ""
        custom_times: dict[str, tuple[str, str]] = {}
        if note:
            custom_times.update(_parse_custom_times(note, period))

        row_count = 0
        for date_str, cols in date_cols.items():
            req = None
            if "checkbox" in cols:
                req = _process_checkbox_fmt(
                    row, date_str, cols["checkbox"], custom_times, note, emp, result, name
                )
            elif "avail" in cols:
                req = _process_new_fmt(
                    row, date_str, cols, custom_times, note, emp, result, name
                )
            elif "direct" in cols:
                req = _process_direct_fmt(
                    row, date_str, cols["direct"], custom_times, note, emp, result, name
                )

            if req is not None:
                result.requests.append(req)
                row_count += 1

        result.matched[name] = result.matched.get(name, 0) + row_count

    return result


def _process_checkbox_fmt(
    row: dict,
    date_str: str,
    col_name: str,
    custom_times: dict,
    note: str,
    emp,
    result: "ImportResult",
    name: str,
) -> "Optional[ShiftRequest]":
    """ใƒใ‚งใƒƒใ‚ฏใƒœใƒƒใ‚ฏใ‚นๅฝขๅผใฎ่ค‡ๆ•ฐ้ธๆŠžใฎๅ›ž็ญ”ใ‹ใ‚‰ ShiftRequest ใ‚’ๆง‹็ฏ‰ใ™ใ‚‹ใ€‚"""
    val = row.get(col_name, "").strip()
    if not val or "ไผ‘ใฟ" in val:
        # ็ฉบใ€ใพใŸใฏใ€Œไผ‘ใฟใ€ใŒ้ธๆŠžใ•ใ‚Œใฆใ„ใ‚‹ๅ ดๅˆใฏไผ‘ใฟๆ‰ฑใ„
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id=None, custom_start=None, custom_end=None, note=note,
        )

    if "ๆœ‰็ตฆ" in val:
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id="paid_leave", custom_start=None, custom_end=None, note=note,
        )

    # ้ธๆŠž่‚ขใฎใ‚ซใƒณใƒžๅŒบๅˆ‡ใ‚Šใ‚’้…ๅˆ—ใซใƒ‘ใƒผใ‚น
    selections = [s.strip() for s in val.split(",")]

    has_breakfast = False
    has_dinner = False
    selected_b_val = None
    selected_d_val = None
    has_custom = False

    for sel in selections:
        if "ใใฎไป–" in sel:
            has_custom = True
        elif "ๆœ้ฃŸ" in sel:
            has_breakfast = True
            # ไพ‹ "ๆœ้ฃŸ: 6:00ใ€œ10:00" -> "6:00ใ€œ10:00" ใ‚’ๅ–ๅพ—
            parts = sel.split(":", 1)
            selected_b_val = parts[1].strip() if len(parts) > 1 else sel
        elif "ใƒ‡ใ‚ฃใƒŠใƒผ" in sel or "ๆ™ฉ" in sel:
            has_dinner = True
            parts = sel.split(":", 1)
            selected_d_val = parts[1].strip() if len(parts) > 1 else sel

    if has_custom:
        cs = custom_times.get(date_str)
        custom_start, custom_end = cs if cs else (None, None)
        if not cs:
            result.warnings.append(
                f"ใ€Œ{name}ใ€{date_str}: ใ€Œใใฎไป–ใ€้ธๆŠžใงใ™ใŒๅ‚™่€ƒๆฌ„ใซๆ™‚ๅˆปใŒ่ฆ‹ใคใ‹ใ‚Šใพใ›ใ‚“"
            )
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id="custom", custom_start=custom_start, custom_end=custom_end, note=note,
        )

    if has_breakfast and has_dinner:
        # ไธกๆ–นใƒใ‚งใƒƒใ‚ฏใŒใ‚ใ‚‹ๅ ดๅˆใฏใƒ€ใƒ–ใƒซ
        return ShiftRequest(
            employee_id=emp.id, date=date_str,
            pattern_id="double", custom_start=None, custom_end=None, note=note,
        )
    elif has_breakfast and selected_b_val:
        return _resolve_time_value(selected_b_val, date_str, custom_times, note, emp, result, name)
    elif has_dinner and selected_d_val:
        return _resolve_time_value(selected_d_val, date_str, custom_times, note, emp, result, name)

    return ShiftRequest(
        employee_id=emp.id, date=date_str,
        pattern_id=None, custom_start=None, custom_end=None, note=note,
    )