File size: 77,790 Bytes
6facec8
cd6821f
6facec8
 
267dca4
 
f711364
 
c5da91f
 
 
 
8585ec5
 
61ede51
 
0ede16d
cd6821f
 
 
 
7e17f8c
cd6821f
 
0ede16d
19af96b
 
 
 
 
 
 
 
cd6821f
19af96b
cd6821f
 
19af96b
 
cd6821f
19af96b
 
fc70f30
c5da91f
6facec8
 
 
 
f711364
c5da91f
089bb71
f6d7c87
 
e9ce666
 
 
0e8c67a
 
e9ce666
0e8c67a
e9ce666
f711364
58099b4
e9ce666
f711364
e9ce666
0e8c67a
f711364
 
 
 
 
0e8c67a
f711364
e9ce666
 
0e8c67a
d9b2260
0e8c67a
267dca4
0e8c67a
997cdd8
c5da91f
cd6821f
8abbfeb
bd73b8d
 
 
 
 
f711364
0e8c67a
f711364
0e8c67a
f711364
 
0e8c67a
f711364
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c5da91f
f711364
 
 
 
 
 
 
c5da91f
0e8c67a
f711364
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c5da91f
f711364
 
 
a4fc4be
e9fb0d7
089bb71
c5da91f
 
 
089bb71
 
 
 
 
71a1536
 
 
 
 
e9fb0d7
71a1536
 
 
0e8c67a
e9fb0d7
 
 
71a1536
e9fb0d7
 
 
 
 
 
 
 
 
 
 
 
 
 
71a1536
e9fb0d7
 
 
71a1536
e9fb0d7
 
 
 
 
 
089bb71
 
e9fb0d7
089bb71
 
0e8c67a
e9fb0d7
0e8c67a
e9fb0d7
 
 
0e8c67a
e9fb0d7
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
089bb71
 
 
 
c5da91f
089bb71
88103fd
 
 
0ede16d
a41c298
4a36a64
88103fd
 
0ede16d
88103fd
 
0e8c67a
bd73b8d
88103fd
 
 
61ede51
 
88103fd
0ede16d
4a36a64
 
 
 
88103fd
 
a41c298
 
 
e7905be
 
 
0ede16d
e7905be
a41c298
88103fd
a41c298
 
88103fd
 
 
 
0ede16d
a41c298
88103fd
 
a41c298
bd73b8d
a41c298
bd73b8d
e7905be
 
 
 
 
a41c298
 
 
 
 
 
e7905be
 
 
 
 
a41c298
 
 
4a36a64
 
a41c298
e7905be
 
 
 
4a36a64
 
 
a41c298
e7905be
 
 
 
4a36a64
 
 
 
0ede16d
4a36a64
a41c298
4a36a64
0ede16d
4a36a64
 
 
 
88103fd
 
 
0ede16d
 
88103fd
0ede16d
88103fd
 
0ede16d
88103fd
 
bd73b8d
88103fd
0ede16d
88103fd
 
0ede16d
4a36a64
88103fd
 
 
 
 
0b8c7f2
 
 
c5da91f
0b8c7f2
 
 
 
8585ec5
38f1f83
0b8c7f2
997cdd8
0b8c7f2
 
 
c5da91f
0b8c7f2
 
71a1536
 
 
 
 
 
 
 
 
 
 
 
 
 
 
0b8c7f2
 
71a1536
0b8c7f2
38f1f83
0b8c7f2
 
 
 
 
 
 
 
 
c5da91f
997cdd8
0b8c7f2
 
 
c5da91f
997cdd8
0b8c7f2
 
 
0e8c67a
0b8c7f2
 
 
 
 
c5da91f
 
0b8c7f2
 
 
8585ec5
 
0e8c67a
 
 
8585ec5
61ede51
8585ec5
 
 
0e8c67a
8585ec5
 
 
61ede51
8585ec5
0e8c67a
8585ec5
0b8c7f2
8585ec5
0e8c67a
089bb71
f711364
c5da91f
38f1f83
089bb71
997cdd8
c5da91f
bd73b8d
 
 
 
a4fc4be
0b8c7f2
58099b4
dcd9524
a4fc4be
8585ec5
 
0e8c67a
8585ec5
dcd9524
bd73b8d
dcd9524
bd73b8d
 
 
 
 
 
 
 
997cdd8
bd73b8d
 
f961f82
8abbfeb
e9ce666
bd73b8d
 
 
f711364
0e8c67a
bd73b8d
 
089bb71
 
bd73b8d
 
 
 
089bb71
 
 
bd73b8d
 
 
 
 
 
 
 
089bb71
bd73b8d
 
8abbfeb
089bb71
c5da91f
bd73b8d
c5da91f
bd73b8d
089bb71
0e8c67a
61ede51
089bb71
0e8c67a
bd73b8d
 
 
 
 
 
 
 
 
 
 
 
 
 
089bb71
bd73b8d
 
 
 
 
 
 
 
0e8c67a
bd73b8d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c5da91f
 
 
 
0e8c67a
bd73b8d
c5da91f
 
0e8c67a
c5da91f
0e8c67a
 
c5da91f
 
 
c08fe92
0ede16d
c5da91f
 
 
 
 
 
c08fe92
 
 
c5da91f
 
 
 
 
 
bd73b8d
c5da91f
 
bd73b8d
c5da91f
 
 
 
 
 
 
 
 
0e8c67a
c5da91f
 
 
 
 
 
0e8c67a
bd73b8d
 
c5da91f
0e8c67a
c5da91f
 
 
0e8c67a
c5da91f
 
 
 
 
 
 
bd73b8d
 
0e8c67a
c5da91f
 
0e8c67a
c5da91f
 
 
 
 
f711364
0e8c67a
c5da91f
0e8c67a
bd73b8d
61ede51
c5da91f
 
bd73b8d
c5da91f
bd73b8d
f711364
c5da91f
 
bd73b8d
c5da91f
0e8c67a
089bb71
bd73b8d
 
 
 
 
 
 
 
58099b4
c5da91f
 
 
 
 
 
bd73b8d
c5da91f
71a1536
c5da91f
71a1536
 
 
 
 
 
bd73b8d
71a1536
 
 
 
 
d9b2260
a41c298
71a1536
 
 
 
bd73b8d
d9b2260
a41c298
71a1536
 
 
d9b2260
a41c298
71a1536
 
d9b2260
a41c298
71a1536
 
d9b2260
a41c298
71a1536
 
c5da91f
 
71a1536
c5da91f
 
bd73b8d
c5da91f
 
bd73b8d
c5da91f
 
 
bd73b8d
0e8c67a
 
 
c5da91f
 
 
 
 
bd73b8d
c5da91f
 
 
 
71a1536
bd73b8d
71a1536
 
c5da91f
71a1536
c5da91f
 
0e8c67a
 
 
c5da91f
 
 
 
bd73b8d
0e8c67a
c5da91f
0e8c67a
c5da91f
0e8c67a
c5da91f
 
71a1536
bd73b8d
71a1536
 
c5da91f
 
 
bd73b8d
0e8c67a
 
c5da91f
 
 
 
 
bd73b8d
c5da91f
 
 
 
 
 
 
 
0e8c67a
c5da91f
 
 
bd73b8d
 
 
c5da91f
 
 
089bb71
 
c5da91f
bd73b8d
c5da91f
0e8c67a
61ede51
8abbfeb
c5da91f
bd73b8d
0e8c67a
bd73b8d
 
0e8c67a
61ede51
8abbfeb
bd73b8d
c5da91f
bd73b8d
0b8c7f2
88103fd
8abbfeb
c5da91f
0b8c7f2
bd73b8d
0b8c7f2
0ede16d
bd73b8d
8abbfeb
089bb71
bd73b8d
0e8c67a
88103fd
 
0ede16d
 
88103fd
 
0e8c67a
c5da91f
8abbfeb
cd6821f
 
 
 
 
 
 
0ede16d
cd6821f
 
0ede16d
cd6821f
 
0ede16d
cd6821f
 
 
 
0ede16d
 
cd6821f
 
0ede16d
 
cd6821f
 
0ede16d
cd6821f
0ede16d
 
 
cd6821f
 
0ede16d
 
 
 
 
 
cd6821f
 
0ede16d
cd6821f
 
0ede16d
 
 
 
 
cd6821f
 
0ede16d
 
 
 
cd6821f
 
 
 
 
0ede16d
 
 
 
cd6821f
 
0ede16d
cd6821f
0ede16d
cd6821f
0ede16d
 
cd6821f
 
 
 
0ede16d
cd6821f
 
 
 
 
0ede16d
cd6821f
 
0ede16d
 
cd6821f
 
0ede16d
cd6821f
0ede16d
cd6821f
 
0ede16d
cd6821f
 
0ede16d
cd6821f
 
 
 
 
0ede16d
cd6821f
 
0ede16d
 
 
cd6821f
0ede16d
bd73b8d
0ede16d
bd73b8d
cd6821f
 
 
 
0ede16d
cd6821f
 
 
 
 
 
0ede16d
cd6821f
 
0ede16d
cd6821f
 
8abbfeb
cd6821f
 
8abbfeb
 
cd6821f
 
bd73b8d
0ede16d
bd73b8d
38f1f83
bd73b8d
8abbfeb
089bb71
cd6821f
089bb71
0e8c67a
 
cd6821f
 
 
bd73b8d
fc70f30
 
 
cd6821f
997cdd8
bd73b8d
 
 
 
 
 
 
997cdd8
cd6821f
 
 
089bb71
 
0e8c67a
997cdd8
bd73b8d
fc70f30
089bb71
58099b4
089bb71
0ede16d
 
 
 
 
997cdd8
 
8585ec5
0e8c67a
0ede16d
 
 
 
 
61ede51
997cdd8
0ede16d
 
 
 
c5da91f
 
 
bd73b8d
0ede16d
 
 
bd73b8d
0ede16d
 
 
61ede51
997cdd8
0ede16d
61ede51
089bb71
0ede16d
4a36a64
 
0ede16d
 
997cdd8
089bb71
0ede16d
61ede51
997cdd8
0ede16d
997cdd8
a41c298
 
e7905be
 
 
 
0ede16d
a41c298
 
bd73b8d
a41c298
bd73b8d
997cdd8
 
0ede16d
997cdd8
a41c298
997cdd8
 
 
a41c298
bd73b8d
a41c298
 
bd73b8d
997cdd8
 
bd73b8d
 
a41c298
bd73b8d
e7905be
 
 
 
 
a41c298
 
bd73b8d
a41c298
bd73b8d
997cdd8
 
0ede16d
997cdd8
a41c298
 
e7905be
 
 
 
 
a41c298
 
bd73b8d
a41c298
bd73b8d
997cdd8
 
0ede16d
 
 
 
4a36a64
 
a41c298
e7905be
 
 
 
4a36a64
 
 
bd73b8d
4a36a64
bd73b8d
4a36a64
 
0ede16d
4a36a64
 
a41c298
e7905be
 
 
 
4a36a64
 
 
bd73b8d
a41c298
bd73b8d
61ede51
997cdd8
4a36a64
997cdd8
 
0ede16d
997cdd8
0ede16d
997cdd8
bd73b8d
 
 
 
 
089bb71
bd73b8d
089bb71
 
f711364
089bb71
61ede51
 
 
089bb71
0ede16d
 
 
 
 
 
 
 
 
 
 
 
bd73b8d
 
 
 
 
 
 
 
 
0ede16d
 
 
 
bd73b8d
0ede16d
 
 
 
 
bd73b8d
 
0ede16d
 
 
 
 
 
bd73b8d
 
0ede16d
 
 
7e17f8c
 
bd73b8d
 
7e17f8c
 
 
 
0ede16d
 
 
bd73b8d
 
0ede16d
 
 
 
bd73b8d
 
 
0ede16d
 
bd73b8d
0ede16d
 
 
 
 
 
 
 
 
 
bd73b8d
0ede16d
bd73b8d
0ede16d
 
 
 
 
 
 
 
 
 
 
 
 
 
bd73b8d
0ede16d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7e17f8c
 
bd73b8d
 
7e17f8c
 
 
 
bd73b8d
 
 
 
089bb71
 
0ede16d
bd73b8d
089bb71
 
 
 
0ede16d
 
bd73b8d
 
089bb71
bd73b8d
 
7e17f8c
0ede16d
bd73b8d
 
 
 
7e17f8c
0ede16d
 
 
2b524e1
 
 
0ede16d
2b524e1
 
bd73b8d
0ede16d
 
bd73b8d
 
089bb71
bd73b8d
 
7e17f8c
0ede16d
2b524e1
0ede16d
 
bd73b8d
0ede16d
 
 
 
089bb71
 
 
 
 
0ede16d
089bb71
 
 
 
 
 
0ede16d
 
089bb71
 
 
dcd9524
61ede51
 
0ede16d
0e8c67a
61ede51
 
 
8585ec5
0ede16d
089bb71
dcd9524
 
 
61ede51
 
bd73b8d
 
dcd9524
 
f223b5b
0ede16d
089bb71
 
 
8585ec5
0ede16d
089bb71
 
 
 
 
bd73b8d
0ede16d
 
089bb71
 
 
 
 
0e8c67a
8585ec5
0e8c67a
089bb71
 
 
 
0ede16d
089bb71
0ede16d
089bb71
 
 
 
0e8c67a
089bb71
0ede16d
089bb71
a4fc4be
 
 
 
e9ce666
a4fc4be
 
 
 
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
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
import os
import sys
import gradio as gr
from dotenv import load_dotenv
import pandas as pd
from datetime import datetime
import io
import re
import google.generativeai as genai
import faiss
import numpy as np
import time
import json
import tempfile
import pytz # Import pytz for timezone handling

# --- Import WeasyPrint ---
from weasyprint import HTML, CSS
# --- End WeasyPrint import ---

from docx import Document as DocxDocument
from docx.shared import Inches, Pt, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH, WD_PARAGRAPH_ALIGNMENT

# --- Python Environment Diagnostics ---
print("\n--- Python Environment Diagnostics ---", file=sys.stderr)
print(f"Gradio version at runtime: {gr.__version__}", file=sys.stderr)
print(f"Python executable: {sys.executable}", file=sys.stderr)
print(f"Python PATH: {sys.path}", file=sys.stderr)

try:
    import pkg_resources
    installed_packages = {p.project_name: p.version for p in pkg_resources.working_set}
    print(f"Installed packages (excerpt):", file=sys.stderr)
    for package_name in sorted(installed_packages.keys()):
        # List relevant packages for diagnostics
        if any(keyword in package_name.lower() for keyword in ["weasyprint", "gradio", "pandas"]):
            print(f"  {package_name}: {installed_packages[package_name]}", file=sys.stderr)
except Exception as e:
    print(f"Could not list installed packages: {e}", file=sys.stderr)
print("--- End of Python Environment Diagnostics ---\n", file=sys.stderr)


# Load environment variables
load_dotenv()

# --- RAG Setup ---
rag_initialized = False
gemini_llm_model = None
gemini_embedding_model_name = "models/embedding-001"

google_api_key = os.getenv("GOOGLE_API_KEY")

if not google_api_key:
    print("\n" + "="*80)
    print("CRITICAL ERROR: GOOGLE_API_KEY environment variable is NOT SET.")
    print("RAG functionality will NOT be initialized. Please set it in your .env file (local) or Hugging Face Space secrets.")
    print("="*80 + "\n", file=sys.stderr) # Print to stderr for deployment logs
else:
    print("GOOGLE_API_KEY successfully loaded from environment.")
    try:
        genai.configure(api_key=google_api_key)
        llm_model_id = "gemini-1.5-flash"
        
        gemini_llm_model = genai.GenerativeModel(llm_model_id)
        
        # Test embedding model access
        test_embedding = genai.embed_content(model=gemini_embedding_model_name, content=["test"])
        if test_embedding and test_embedding['embedding']:
            rag_initialized = True
            print(f"Google Generative AI LLM ({llm_model_id}) and embeddings initialized successfully.")
        else:
            raise Exception("Failed to get a valid test embedding from the configured API key. Check API key permissions.")

    except Exception as e:
        rag_initialized = False
        print(f"Error initializing Google Generative AI components: {e}", file=sys.stderr)
        print(f"Please verify your GOOGLE_API_KEY's validity and ensure the specified AI model ('{llm_model_id}' for LLM) and embedding model ('{gemini_embedding_model_name}') are available to your API key.", file=sys.stderr)
        print("RAG functionality will not work without a valid Google API key and accessible model.", file=sys.stderr)

# Global variables to store results for download functions
global_analysis_df_for_export = pd.DataFrame()
global_raw_markdown_for_export = ""
global_html_report_for_export = "" # New global variable to store the full HTML report for PDF conversion

# Global variables for filename versioning
_last_uploaded_filename_base = None
_file_versions = {}
_original_total_tickets_count = 0 # New global variable to store the total ticket count from the original file

# --- Custom Document Structure ---
# Documents are dictionaries: {"page_content": "...", "metadata": {...}}

# --- Custom Text Splitter (if ever needed for very long 'page_content') ---
def custom_character_text_splitter(text_documents, chunk_size=1000, chunk_overlap=200):
    """
    A simple character-based text splitter. Splits larger texts into smaller chunks with overlap.
    text_documents: list of dictionaries {"page_content": "...", "metadata": {...}}
    """
    chunks = []
    for doc in text_documents:
        content = doc["page_content"]
        metadata = doc["metadata"]
        
        if len(content) <= chunk_size:
            chunks.append({"page_content": content, "metadata": metadata})
            continue

        current_position = 0
        while current_position < len(content):
            chunk_end = min(current_position + chunk_size, len(content))
            chunk_text = content[current_position:chunk_end]
            chunks.append({"page_content": chunk_text, "metadata": metadata})
            
            if chunk_end == len(content):
                break
            current_position += (chunk_size - chunk_overlap)
            if current_position >= len(content):
                break
    return chunks


# --- Custom FAISS Integration (for searching, not direct analysis flow for 800k tickets) ---
# This class is included for RAG conceptual completeness, though not directly used in the current two-phase analysis
class CustomFAISSVectorStore:
    def __init__(self, embedding_model_name, dimension=768): # Gemini embedding-001 has 768 dimensions
        self.embedding_model_name = embedding_model_name
        self.index = faiss.IndexFlatL2(dimension) # L2 for Euclidean distance
        self.documents = [] # Store original documents/chunks for retrieval

    def add_documents(self, documents):
        if not documents:
            return

        contents_to_embed = [doc["page_content"] for doc in documents]
        
        # Batch embedding for efficiency and to avoid API limits for large inputs
        batch_size = 100 # Adjust based on API limits and performance
        all_embeddings = []
        for i in range(0, len(contents_to_embed), batch_size):
            batch_contents = contents_to_embed[i:i + batch_size]
            try:
                embeddings_response = genai.embed_content(
                    model=self.embedding_model_name,
                    content=batch_contents
                )
                all_embeddings.extend(embeddings_response['embedding'])
            except Exception as e:
                raise Exception(f"Error during embedding generation for FAISS: {e}")

        embeddings_np = np.array(all_embeddings).astype('float32')
        
        if embeddings_np.shape[1] != self.index.d:
            raise ValueError(f"Embedding dimension mismatch: Expected {self.index.d}, got {embeddings_np.shape[1]}.")

        self.index.add(embeddings_np)
        self.documents.extend(documents) # Store documents in the same order as embeddings

    def search(self, query_text, k=4):
        if not query_text:
            return []
        
        try:
            query_embedding_response = genai.embed_content(
                model=self.embedding_model_name,
                content=[query_text]
            )
            query_embedding = np.array(query_embedding_response['embedding'][0]).astype('float32').reshape(1, -1)
        except Exception as e:
            raise Exception(f"Error embedding query: {e}")

        if query_embedding.shape[1] != self.index.d:
            raise ValueError(f"Query embedding dimension mismatch: Expected {self.index.d}, got {query_embedding.shape[1]}.")

        distances, indices = self.index.search(query_embedding, k)
        
        retrieved_docs = []
        for i in indices[0]:
            if 0 <= i < len(self.documents):
                retrieved_docs.append(self.documents[i])
        return retrieved_docs


def get_ticket_documents_iterator(file_path, file_extension, filename, chunksize=5000):
    """
    Loads and returns text from Excel or CSV files in chunks (iterator).
    For very large files, chunksize helps manage memory.
    Yields list of dictionaries: {"page_content": "...", "metadata": {...}}
    """
    print(f"Attempting to load customer ticket data: {filename} with extension {file_extension}")

    if file_extension in ['.xlsx', '.xls']:
        try:
            # Read the entire Excel file into a DataFrame first
            df_full = pd.read_excel(file_path, engine='openpyxl')
            if df_full.empty:
                raise ValueError("The uploaded Excel file contains no data.")
            
            total_rows_processed = 0
            # Manually chunk the DataFrame
            for i in range(0, len(df_full), chunksize):
                df_chunk = df_full.iloc[i : i + chunksize]
                
                chunk_documents = []
                for index, row in df_chunk.iterrows():
                    ticket_content_parts = []
                    ticket_id = f"Row {index + 1}" # Default ticket ID based on original DataFrame index

                    for col_name, value in row.items():
                        if pd.notna(value) and str(value).strip() != '':
                            formatted_col_name = col_name.replace('_', ' ').title()
                            ticket_content_parts.append(f"{formatted_col_name}: {value}")
                            
                            if col_name.lower() in ['ticketid', 'ticket_id', 'id']:
                                ticket_id = str(value).strip()
                                
                    if not ticket_content_parts:
                        ticket_content = f"Ticket {ticket_id}: No detailed content provided."
                    else:
                        ticket_content = "\n".join(ticket_content_parts)

                    metadata = {"source": filename, "ticket_row_number": index + 1, "ticket_id": ticket_id}
                    metadata.update(row.to_dict()) # Include all original row data in metadata
                    
                    chunk_documents.append({"page_content": ticket_content, "metadata": metadata})
                    total_rows_processed += 1 # Update count based on actual processed rows in this chunk
                
                yield chunk_documents, total_rows_processed # Yield chunk and running total
            
            if total_rows_processed == 0:
                raise ValueError("The uploaded Excel file contains no data or no rows could be parsed after processing chunks.")

        except Exception as e:
            raise ValueError(f"Could not process Excel file '{filename}': {e}. Ensure it's a valid Excel format for ticket data.")

    elif file_extension == '.csv':
        try:
            df_reader = pd.read_csv(file_path, chunksize=chunksize)
            total_rows_processed = 0
            for i, df_chunk in enumerate(df_reader):
                if df_chunk.empty:
                    continue

                chunk_documents = []
                for index, row in df_chunk.iterrows():
                    ticket_content_parts = []
                    ticket_id = f"Row {total_rows_processed + 1}" # Default ticket ID

                    for col_name, value in row.items():
                        if pd.notna(value) and str(value).strip() != '':
                            formatted_col_name = col_name.replace('_', ' ').title()
                            ticket_content_parts.append(f"{formatted_col_name}: {value}")
                            
                            if col_name.lower() in ['ticketid', 'ticket_id', 'id']:
                                ticket_id = str(value).strip()

                    if not ticket_content_parts:
                        ticket_content = f"Ticket {ticket_id}: No detailed content provided."
                    else:
                        ticket_content = "\n".join(ticket_content_parts)

                    metadata = {"source": filename, "ticket_row_number": total_rows_processed + 1, "ticket_id": ticket_id}
                    metadata.update(row.to_dict()) # Include all original row data in metadata

                    chunk_documents.append({"page_content": ticket_content, "metadata": metadata})
                    total_rows_processed += 1
                
                yield chunk_documents, total_rows_processed # Yield chunk and running total

            if total_rows_processed == 0:
                raise ValueError("The uploaded CSV file contains no data or no rows could be parsed.")

        except Exception as e:
            raise ValueError(f"Could not process CSV file '{filename}': {e}. Ensure it's a valid CSV format for ticket data.")
    else:
        raise ValueError(f"Unsupported file type: {file_extension}. Only .xlsx, .xls, or .csv files are accepted for ticket data.")
    

def generate_summary_html_by_category(df):
    """
    Generates HTML summary blocks for each unique category in the DataFrame,
    following the executive summary format.
    Splits multi-line text into bullet points.
    Categories are sorted from max to min count.
    """
    if df.empty or 'Category' not in df.columns:
        return "<p style='color:#CC3300;'>No categories found or data is empty for detailed summarization. This could be due to issues with the AI's output parsing or content generation.</p>"

    summary_html = ""
    # Ensure all required columns exist, fill with N/A if missing
    required_cols = ['Issue Description (Summary)', 'Sub-Category', 'Potential Root Cause (Inferred)', # Changed to Potential Root Cause
                     'Potential Resolution Steps', 'Future Prediction', 'Proactive Measures', 'Count']
    for col in required_cols:
        if col not in df.columns:
            df[col] = 'N/A' 
            if col == 'Count': df[col] = 0 

    # Group by category and sum the counts, then sort by count in descending order
    category_counts = df.groupby('Category')['Count'].sum().sort_values(ascending=False)

    for category in category_counts.index: # Iterate through categories in sorted order (max to min count)
        category_df = df[df['Category'] == category] # Get the DataFrame for the current category
        total_count = category_df['Count'].sum()
        
        # --- Issue Description (Summary) ---
        issue_descriptions_list = []
        for summary_text in category_df['Issue Description (Summary)'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', summary_text)
            for line in lines:
                line = line.strip()
                if line:
                    issue_descriptions_list.append(f"<li>{line}</li>")
        issue_descriptions = f"<ul style='margin-top: 5px; margin-bottom: 5px;'>{''.join(issue_descriptions_list)}</ul>" if issue_descriptions_list else "<p>No specific summary available.</p>"


        # --- Sub-Category Summary ---
        sub_category_summary = "<ul style='margin-top: 5px; margin-bottom: 5px;'>"
        if 'Sub-Category' in category_df.columns:
            sub_cat_counts = category_df.groupby('Sub-Category')['Count'].sum().sort_values(ascending=False)
            for sub_cat, count in sub_cat_counts.items():
                if str(sub_cat).strip() and str(sub_cat).strip().lower() != 'n/a':
                    sub_category_summary += f"<li>{sub_cat} (Count: {count})</li>"
        sub_category_summary += "</ul>" if 'Sub-Category' in category_df.columns and not sub_cat_counts.empty else "<p>No sub-categories identified.</p>"


        # --- Potential Root Cause (Inferred) --- Changed to Potential Root Cause
        root_causes_list = []
        for rc_text in category_df['Potential Root Cause (Inferred)'].astype(str).drop_duplicates().tolist(): # Changed to Potential Root Cause
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', rc_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'undetermined from available data' and line.lower() != 'undetermined.':
                    root_causes_list.append(f"<li>{line}</li>")
        root_causes = f"<ul style='margin-top: 5px; margin-bottom: 5px;'>{''.join(root_causes_list)}</ul>" if root_causes_list else "<p>Undetermined.</p>"


        # --- Potential Resolution Steps ---
        resolution_steps_list = []
        for res_text in category_df['Potential Resolution Steps'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', res_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'n/a':
                    resolution_steps_list.append(f"<li>{line}</li>")
        resolution_steps = f"<ul style='margin-top: 5px; margin-bottom: 5px;'>{''.join(resolution_steps_list)}</ul>" if resolution_steps_list else "<p>No specific steps identified.</p>"


        # --- Future Prediction & Proactive Measures (Revised) ---
        future_prediction_html_list = []
        for pred_text in category_df['Future Prediction'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', pred_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'n/a':
                    future_prediction_html_list.append(f"<li>{line}</li>")
        
        proactive_measures_html_list = []
        for proactive_text in category_df['Proactive Measures'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', proactive_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'n/a':
                    proactive_measures_html_list.append(f"<li>{line}</li>")

        prediction_and_proactive_html = ""
        if future_prediction_html_list:
            prediction_and_proactive_html += "<p style='font-weight: bold; color: #4A148C; margin-top: 10px; margin-bottom: 5px;'>Future Prediction:</p>"
            prediction_and_proactive_html += f"<ul style='margin-top: 5px; margin-bottom: 5px;'>{''.join(future_prediction_html_list)}</ul>"
        
        if proactive_measures_html_list:
            prediction_and_proactive_html += "<p style='font-weight: bold; color: #4A148C; margin-top: 10px; margin-bottom: 5px;'>Proactive Measures:</p>"
            prediction_and_proactive_html += f"<ul style='margin-top: 5px; margin-bottom: 5px;'>{''.join(proactive_measures_html_list)}</ul>"

        if not prediction_and_proactive_html:
            prediction_and_proactive_html = "<p>No future prediction or proactive measures identified.</p>"


        summary_html += f"""
        <div style="background-color: #ffffff; border: 1px solid #e0e0e0; border-radius: 4px; padding: 15px; margin-bottom: 15px; box-shadow: 0 1px 3px rgba(0,0,0,0.08);">
            <h3 style="color: #1A5276; margin-top: 0; margin-bottom: 8px;">{category} (Count: {total_count})</h3>
            
            <p style="font-weight: bold; color: #333; margin-bottom: 5px;">Summary:</p>
            {issue_descriptions}

            <p style="font-weight: bold; color: #333; margin-bottom: 5px;">Sub-Categories:</p>
            {sub_category_summary}

            <p style="font-weight: bold; color: #333; margin-bottom: 5px;">Potential Root Cause:</p> {root_causes}

            <p style="font-weight: bold; color: #333; margin-bottom: 5px;">Resolution Steps:</p>
            {resolution_steps}

            <p style="font-weight: bold; color: #333; margin-bottom: 5px;">Insights & Recommendations:</p>
            {prediction_and_proactive_html}
        </div>
        """
    return summary_html


def parse_markdown_table_to_df(markdown_string):
    """
    Robustly parses a markdown table string into a pandas DataFrame.
    Assumes a standard markdown table with a header and separator line.
    """
    lines = markdown_string.strip().split('\n')
    
    header_line = None
    separator_line_index = -1

    for i, line in enumerate(lines):
        if '|' in line and '---' not in line and not line.strip().startswith('|---'):
            header_line = line.strip()
        elif '|---' in line:
            separator_line_index = i
            break

    if header_line is None or separator_line_index == -1:
        # If no header/separator found, assume the first non-empty line is a data row if it looks like one
        if any(line.strip().startswith('|') and '|' in line.strip() for line in lines):
            # Attempt to parse as if it's just a data row without a header.
            # This is a fallback and might not be perfect.
            potential_data_line = next((line for line in lines if line.strip().startswith('|') and '|' in line.strip()), None)
            if potential_data_line:
                # Create dummy headers if no header was provided
                num_cols = len([v for v in potential_data_line.strip('|').split('|') if v])
                headers = [f"Col{i+1}" for i in range(num_cols)]
                data = [[v.strip() for v in potential_data_line.strip('|').split('|') if v]]
                if len(data[0]) == len(headers):
                    df = pd.DataFrame(data, columns=headers)
                    df = df.dropna(axis=1, how='all')
                    df.columns = df.columns.str.strip()
                    return df, "No explicit Markdown table header or separator found, parsed as a single data row."
        return pd.DataFrame(), "No valid Markdown table header or separator found."


    data_lines = []
    for line in lines[separator_line_index + 1:]:
        if line.strip().startswith('|') and '|' in line.strip():
            data_lines.append(line.strip())
        else:
            break

    if not data_lines:
        return pd.DataFrame(), "No data rows found in the Markdown table."

    headers = [h.strip() for h in header_line.strip('|').split('|')]
    headers = [h for h in headers if h] # Remove empty strings

    data = []
    for line in data_lines:
        row_values = [v.strip() for v in line.strip('|').split('|')]
        row_values = [v for v in row_values if v]

        if len(row_values) == len(headers):
            data.append(row_values)
        else:
            print(f"Skipping malformed row: {line} - expected {len(headers)} columns, got {len(row_values)}", file=sys.stderr)

    if not data:
        return pd.DataFrame(), "No valid data rows parsed after cleaning."

    df = pd.DataFrame(data, columns=headers)
    df = df.dropna(axis=1, how='all')
    df.columns = df.columns.str.strip()

    return df, None

# --- Helper function for timezone formatting ---
def format_time_for_display(dt_utc, user_timezone_str):
    """
    Formats a UTC datetime object to a user-specified timezone string.
    """
    if not user_timezone_str:
        return dt_utc.strftime('%Y-%m-%d %H:%M:%S UTC (No timezone selected)')
    
    try:
        user_tz = pytz.timezone(user_timezone_str)
        # Ensure dt_utc is timezone-aware as UTC before converting
        dt_user_tz = dt_utc.replace(tzinfo=pytz.utc).astimezone(user_tz)
        return dt_user_tz.strftime('%Y-%m-%d %H:%M:%S %Z%z')
    except pytz.UnknownTimeZoneError:
        return dt_utc.strftime(f'%Y-%m-%d %H:%M:%S UTC (Invalid Timezone: {user_timezone_str})')
    except Exception as e:
        # Fallback to UTC display with error message
        return dt_utc.strftime(f'%Y-%m-%d %H:%M:%S UTC (Timezone Error: {e})')


def perform_ticket_analysis(file, user_query, selected_timezone, progress=gr.Progress()):
    """
    Main function to analyze customer tickets using custom RAG implementation and format output as rich HTML.
    Implements a two-phase iterative approach to handle large ticket sets robustly.
    Uses 'yield' to update Gradio UI progressively.
    """
    global global_analysis_df_for_export
    global global_raw_markdown_for_export
    global global_html_report_for_export
    global _last_uploaded_filename_base
    global _file_versions
    global _original_total_tickets_count # Use the new global variable

    start_time = time.time()
    error_message_markdown = ""
    log_messages = [] # Initialize log messages list

    # Get the current time in UTC
    now_utc = datetime.utcnow()
    display_time = format_time_for_display(now_utc, selected_timezone)

    def append_log_message(message):
        timestamp = datetime.now().strftime("%H:%M:%S")
        log_messages.append(f"[{timestamp}] {message}")
        # When logging, we don't want to change the HTML output yet, so we yield an empty update.
        # This ensures both outputs are always returned.
        return gr.update(value="", visible=False), gr.update(value="\n".join(log_messages)) # Return HTML and Textbox updates

    # Initial yield to clear previous outputs, make log visible, and update progress bar to 0
    # Corrected: yield directly instead of yield from for the first log
    yield gr.update(value="", visible=False), gr.update(value="\n".join(log_messages), visible=True) # Hide HTML output, show log
    yield append_log_message("Starting analysis...") # Corrected: yield directly
    progress(0, desc="Starting analysis...")
    time.sleep(0.01) # Small sleep for progress bar to render


    if not rag_initialized:
        error_message_markdown = "RAG system not initialized. Please ensure your GOOGLE_API_KEY is configured correctly and the specified AI model is accessible."
        yield gr.update(value=f"<p style='color:#CC3300;'>{error_message_markdown}</p>", visible=True), gr.update(value="\n".join(log_messages))
        return

    if gemini_llm_model is None:
        error_message_markdown = "Google Generative AI LLM was not initialized. Please check startup logs for API key or model access issues."
        yield gr.update(value=f"<p style='color:#CC3300;'>{error_message_markdown}</p>", visible=True), gr.update(value="\n".join(log_messages))
        return

    if file is None:
        error_message_markdown = "No file has been uploaded. Please upload a customer ticket file to proceed."
        yield gr.update(value=f"<p style='color:#CC3300;'>{error_message_markdown}</p>", visible=True), gr.update(value="\n".join(log_messages))
        return

    file_path = file.name
    filename = os.path.basename(file_path)
    file_extension = os.path.splitext(filename)[1].lower()
    base_filename = os.path.splitext(filename)[0]

    # Update versioning
    if _last_uploaded_filename_base != base_filename:
        _last_uploaded_filename_base = base_filename
        _file_versions[_last_uploaded_filename_base] = 1
    else:
        _file_versions[_last_uploaded_filename_base] = _file_versions.get(_last_uploaded_filename_base, 0) + 1


    disclaimer_text_for_raw = "\n\n**Note:** 'Potential Root Cause', 'Future Prediction', and 'Proactive Measures' are AI-inferred based on descriptions provided. For critical decisions, human expert review is essential."

    try:
        # --- PHASE 1: Initial Categorization of ALL Tickets ---
        yield append_log_message("Phase 1/2: Initializing categorization...") # Corrected
        progress(0.1, desc="Phase 1/2: Initializing categorization...")
        time.sleep(0.01) # Small sleep for progress bar to render
        
        # Using a generator for `get_ticket_documents_iterator` for memory efficiency
        ticket_iterator = get_ticket_documents_iterator(file_path, file_extension, filename, chunksize=5000) 
        
        all_categorized_tickets_data = [] # To accumulate all categorized ticket dictionaries
        # total_tickets_count = 0 # This will be updated by the iterator's last yield

        # Get the full DataFrame once for total rows, then iterate chunks
        df_tickets_full = None
        if file_extension in ['.xlsx', '.xls']:
            df_tickets_full = pd.read_excel(file_path, engine='openpyxl')
        elif file_extension == '.csv':
            df_tickets_full = pd.read_csv(file_path)
        
        if df_tickets_full is None or df_tickets_full.empty:
            raise ValueError("No data found in the uploaded file after initial read.")

        _original_total_tickets_count = len(df_tickets_full) # Store global total
        yield append_log_message(f"Detected {_original_total_tickets_count} total tickets in the file.") # Corrected

        chunk_size = 5000 # Must match the chunksize in get_ticket_documents_iterator
        
        processed_chunks_count = 0
        total_tickets_processed_in_phase1 = 0
        
        # Iterate manually through the DataFrame in chunks for consistent processing and progress
        for i in range(0, _original_total_tickets_count, chunk_size):
            df_chunk = df_tickets_full.iloc[i : i + chunk_size]
            
            chunk_documents = []
            current_chunk_tickets_count = 0
            for index, row in df_chunk.iterrows():
                ticket_content_parts = []
                ticket_id = f"Row {index + 1}" # Default ticket ID based on original DataFrame index

                for col_name, value in row.items():
                    if pd.notna(value) and str(value).strip() != '':
                        formatted_col_name = col_name.replace('_', ' ').title()
                        ticket_content_parts.append(f"{formatted_col_name}: {value}")
                        
                        if col_name.lower() in ['ticketid', 'ticket_id', 'id']:
                            ticket_id = str(value).strip()
                            
                if not ticket_content_parts:
                    ticket_content = f"Ticket {ticket_id}: No detailed content provided."
                else:
                    ticket_content = "\n".join(ticket_content_parts)

                metadata = {"source": filename, "ticket_row_number": index + 1, "ticket_id": ticket_id}
                metadata.update(row.to_dict()) # Include all original row data in metadata
                
                chunk_documents.append({"page_content": ticket_content, "metadata": metadata})
                current_chunk_tickets_count += 1
            
            processed_chunks_count += 1
            total_tickets_processed_in_phase1 += current_chunk_tickets_count

            # Phase 1 progress (0.1 to 0.5)
            progress_val = 0.1 + (0.4 * (total_tickets_processed_in_phase1 / _original_total_tickets_count))
            progress(progress_val, desc=f"Phase 1/2: Categorizing tickets... {total_tickets_processed_in_phase1}/{_original_total_tickets_count} processed.")
            time.sleep(0.01) # Small sleep for progress bar to render
            
            if not chunk_documents:
                continue

            categorization_context_parts = []
            ticket_id_map = {}
            for doc in chunk_documents:
                ticket_id_for_prompt = str(doc["metadata"].get("ticket_id", f"Row {doc['metadata']['ticket_row_number']}"))
                categorization_context_parts.append(f"--- Ticket ID: {ticket_id_for_prompt} ---\n{doc['page_content']}\n")
                ticket_id_map[ticket_id_for_prompt] = doc["page_content"] 
            
            categorization_context = "\n".join(categorization_context_parts)

            categorization_prompt = f"""
            You are an expert customer support ticket analyst. Your task is to categorize ALL provided customer tickets in this batch.
            For each ticket, identify a concise main category (e.g., 'Technical Issue', 'Billing Inquiry', 'Feature Request', 'Account Management', 'Order Status', 'Delivery Issue', 'Product Defect', 'General Inquiry', 'Performance Problem', 'Data Access').
            You MUST aim for at least 5-6 distinct main categories, even if some categories only have a few tickets. Do not merge categories unless absolutely necessary.
            Your output MUST be a simple list, one ticket per line, in the format:
            Ticket ID: [Original Ticket ID from metadata] - Category: [Determined Category] - Issue Summary: [Very brief 1-2 sentence summary of the core issue]

            Example:
            Ticket ID: TKT-001 - Category: Technical Issue - Issue Summary: User cannot log in due to password error.
            Ticket ID: INV-567 - Category: Billing Inquiry - Issue Summary: Customer dispute charge for duplicate service.
            Ticket ID: FEAT-123 - Category: Feature Request - Issue Summary: Customer wants dark mode for mobile app.
            Ticket ID: ACC-987 - Category: Account Management - Issue Summary: User unable to update profile information.
            Ticket ID: DEL-456 - Category: Delivery Issue - Issue Summary: Package delivery delayed, tracking not updated.

            Context (Customer Tickets - analyze ALL of these, total {len(chunk_documents)} tickets in this batch):
            {categorization_context}
            """
            
            try:
                yield append_log_message(f"Calling LLM for categorization of {len(chunk_documents)} tickets...") # Corrected
                categorization_response = gemini_llm_model.generate_content(categorization_prompt)
                categorization_raw_output = categorization_response.text
                yield append_log_message("LLM categorization response received. Parsing...") # Corrected

                for line in categorization_raw_output.split('\n'):
                    line = line.strip()
                    match = re.match(r'Ticket ID: (.*?) - Category: (.*?) - Issue Summary: (.*)', line)
                    if match:
                        ticket_id = match.group(1).strip()
                        category = match.group(2).strip()
                        issue_summary = match.group(3).strip()
                        original_content = ticket_id_map.get(ticket_id, "Original content not found for this ID.")
                        all_categorized_tickets_data.append({
                            "ticket_id": ticket_id,
                            "category": category,
                            "issue_summary_short": issue_summary,
                            "original_content": original_content
                        })
                    elif line:
                        # Fallback for malformed lines: try to extract best effort
                        print(f"Warning: Line did not fully match categorization format in chunk {processed_chunks_count}: {line}", file=sys.stderr)
                        yield append_log_message(f"Warning: Malformed categorization line in chunk {processed_chunks_count}, attempting partial parse: {line}") # Corrected
                        parts = line.split("Category:")
                        ticket_id_part = parts[0].replace("Ticket ID:", "").strip() if len(parts) > 0 else f"Unparsed_Line_{len(all_categorized_tickets_data)+1}"
                        category_part = parts[1].split("Issue Summary:")[0].strip() if len(parts) > 1 and "Issue Summary:" in parts[1] else "Uncategorized/Parsing Error"
                        issue_summary_part = parts[1].split("Issue Summary:", 1)[1].strip() if len(parts) > 1 and "Issue Summary:" in parts[1] else line
                        original_content = ticket_id_map.get(ticket_id_part, "Original content not found / Raw line.")
                        all_categorized_tickets_data.append({
                            "ticket_id": ticket_id_part,
                            "category": category_part,
                            "issue_summary_short": issue_summary_part,
                            "original_content": original_content
                        })

            except Exception as e:
                yield append_log_message(f"Error during categorization of chunk {processed_chunks_count}: {e}") # Corrected
                print(f"Error during categorization of chunk {processed_chunks_count}: {e}", file=sys.stderr)
                # If an error occurs during API call for a chunk, categorize its tickets as error
                for doc in chunk_documents:
                    ticket_id = str(doc["metadata"].get("ticket_id", f"Row {doc['metadata']['ticket_row_number']}"))
                    all_categorized_tickets_data.append({
                        "ticket_id": ticket_id,
                        "category": "Error_Processing_Chunk",
                        "issue_summary_short": f"Failed to categorize due to error: {e}",
                        "original_content": doc["page_content"]
                    })
        
        final_categorized_df = pd.DataFrame(all_categorized_tickets_data)
        if final_categorized_df.empty:
            error_message_markdown = "AI failed to categorize any tickets. Please ensure the ticket data is clear or increase AI model's tolerance."
            yield gr.update(value=f"<p style='color:#CC3300;'>{error_message_markdown}</p>", visible=True), gr.update(value="\n".join(log_messages))
            return 

        # --- PHASE 2: Detailed Analysis per Category ---
        yield append_log_message("Phase 2/2: Detailing analysis for categories...") # Corrected
        progress(0.5, desc=f"Phase 2/2: Detailing analysis for categories...")
        time.sleep(0.01) # Small sleep for progress bar to render
        
        final_analysis_rows = []
        unique_categories = final_categorized_df['category'].unique()
        yield append_log_message(f"Identified {len(unique_categories)} unique categories for detailed analysis.") # Corrected

        category_batch_size = 5 # Process categories in small batches if many unique categories exist
        
        # Calculate total number of category batches for progress bar
        total_category_batches = max(1, (len(unique_categories) + category_batch_size - 1) // category_batch_size) # Ceiling division

        for i, category_chunk in enumerate(np.array_split(unique_categories, total_category_batches)):
            # Progress from 0.5 to 0.95
            progress_val = 0.5 + (0.45 * (i+1) / total_category_batches)
            progress(progress_val, desc=f"Phase 2/2: Detailing analysis for category batch {i+1} of {total_category_batches}...")
            time.sleep(0.01) # Small sleep for progress bar to render

            for category in category_chunk:
                category_specific_tickets_df = final_categorized_df[final_categorized_df['category'] == category]
                
                category_tickets_content = "\n\n".join(category_specific_tickets_df['original_content'].tolist())
                
                category_count = len(category_specific_tickets_df)
                yield append_log_message(f"Analyzing category: '{category}' with {category_count} tickets.") # Corrected
                
                # --- START: OPTIMIZED PROMPT FOR DETAILED ANALYSIS ---
                detail_prompt = f"""
                You are an expert customer support ticket analyst, specializing in NLP and data interpretation.
                Deeply analyze the following raw customer support tickets, which all belong to the **'{category}'** category.
                Your goal is to synthesize the information into a single, comprehensive markdown table row, providing highly contextualized and actionable insights for this specific category.

                **Crucial Instructions:**
                1.  **Strict Output Format:** Your output MUST be a **single Markdown table row**. Do NOT include the header row, only the data row.
                    **Table Columns (MUST be in this exact order):** `Category`, `Sub-Category`, `Count`, `Issue Description (Summary)`, `Potential Root Cause (Inferred)`, `Potential Resolution Steps`, `Future Prediction`, `Proactive Measures`.
                2.  **Category:** This MUST be '{category}'.
                3.  **Count:** This MUST be the **exact number of tickets provided for this category ({category_count})**.
                4.  **Issue Description (Summary):**
                    * Synthesize the **common themes and recurring patterns** observed across ALL tickets in this category.
                    * Identify and mention **key affected systems, user actions, or specific features** causing issues.
                    * Present each distinct point as a new bullet point (e.g., "* Point 1\\n* Point 2").
                    * Keep it concise (3-5 bullet points), focusing on the most prevalent aspects.
                5.  **Sub-Category:**
                    * Based on internal patterns, identify one or two **most prominent, granular sub-categories**.
                    * If no clear sub-category emerges, state 'General {category}' or 'N/A'.
                    * Examples: For 'Technical Issue' -> 'Login Failure', 'Software Bug'; for 'Billing Inquiry' -> 'Incorrect Charge', 'Subscription Error'.
                6.  **Potential Root Cause (Inferred):** * Perform an **in-depth inference** of the **underlying systemic or causal factors** leading to these issues. Do not just restate the symptom.
                    * Present each distinct root cause as a new bullet point (e.g., "* Root cause 1\\n* Root cause 2").
                    * Think about why the problem exists. (e.g., "* System misconfiguration", "* User error due to lack of documentation", "* Third-party integration failure", "* Defect in recent software update").
                    * If truly undetermined from the data, state 'Undetermined from available data'.
                7.  **Potential Resolution Steps:**
                    * Suggest **actionable, generic steps** that would effectively resolve or mitigate the issues in this category.
                    * Present each distinct step as a new bullet point (e.g., "* Step 1\\n* Step 2").
                    * These should be **strategic resolutions**, not just individual ticket fixes. (e.g., "* Develop new API endpoint", "* Update user guides", "* Patch database system", "* Conduct user training").
                8.  **Future Prediction:**
                    * Based on the trends and nature of these tickets, predict **what might happen if the underlying issues are not addressed**.
                    * Present each distinct prediction as a new bullet point (e.g., "* Prediction 1\\n* Prediction 2").
                    * Consider potential increase in ticket volume, customer churn, or emergence of new, related problems. (e.g., "* Increased call volume for billing disputes", "* Decline in user retention for feature X", "* Escalation to higher severity issues").
                9.  **Proactive Measures:**
                    * Suggest **specific, actionable steps to prevent recurrence or significantly reduce the volume** of these types of tickets in the future.
                    * Present each distinct measure as a new bullet point (e.g., "* Measure 1\\n* Measure 2").
                    * Focus on **system improvements, process changes, documentation, or user education**. (e.g., "* Implement automated system health checks", "* Revise onboarding flow documentation", "* Roll out a mandatory security update", "* Train support staff on new refund process").

                Context (Raw Customer Tickets for Category '{category}' - Total {category_count} tickets):
                {category_tickets_content}
                """
                # --- END: OPTIMIZED PROMPT FOR DETAILED ANALYSIS ---
                
                try:
                    yield append_log_message(f"Calling LLM for detailed analysis of category '{category}'...") # Corrected
                    detail_response = gemini_llm_model.generate_content(detail_prompt)
                    detail_raw_output = detail_response.text
                    yield append_log_message(f"LLM detailed response for '{category}' received. Parsing...") # Corrected
                    
                    detailed_df, parsing_err = parse_markdown_table_to_df(detail_raw_output)
                    if parsing_err or detailed_df.empty:
                        yield append_log_message(f"Warning: Parsing error or empty DataFrame for category '{category}': {parsing_err or 'Empty DataFrame'}") # Corrected
                        print(f"Warning: Parsing error or empty DataFrame for category '{category}': {parsing_err or 'Empty DataFrame'}", file=sys.stderr)
                        print(f"Raw AI output for detail: {detail_raw_output}", file=sys.stderr)
                        # Fallback row if parsing fails
                        default_row = {
                            "Category": category,
                            "Sub-Category": "N/A",
                            "Count": category_count,
                            "Issue Description (Summary)": category_specific_tickets_df['issue_summary_short'].iloc[0] if not category_specific_tickets_df.empty else "Error in summary generation/parsing for this category.",
                            "Potential Root Cause (Inferred)": "Undetermined due to parsing error or insufficient data.", # Changed
                            "Potential Resolution Steps": "N/A",
                            "Future Prediction": "N/A",
                            "Proactive Measures": "N/A"
                        }
                        # Add a check to ensure all expected columns are present, add if missing
                        for col in ['Sub-Category', 'Potential Root Cause (Inferred)', 'Potential Resolution Steps', 'Future Prediction', 'Proactive Measures']: # Changed
                            if col not in default_row:
                                default_row[col] = 'N/A'
                        final_analysis_rows.append(default_row)

                    else:
                        detailed_row = detailed_df.iloc[0].to_dict()
                        detailed_row['Category'] = category # Ensure category is correctly assigned

                        # Correct the 'Count' if AI provides a different number
                        if 'Count' in detailed_row:
                            try:
                                ai_reported_count = pd.to_numeric(detailed_row['Count'], errors='coerce')
                                if pd.isna(ai_reported_count) or int(ai_reported_count) != category_count:
                                    yield append_log_message(f"Warning: AI-reported count {ai_reported_count} for '{category}' does not match actual count {category_count}. Correcting.") # Corrected
                                    print(f"Warning: AI-reported count {ai_reported_count} for '{category}' does not match actual count {category_count}. Correcting.", file=sys.stderr)
                                    detailed_row['Count'] = category_count
                            except: # Catch any other conversion errors
                                detailed_row['Count'] = category_count
                        else: # Add 'Count' if missing from AI output
                            detailed_row['Count'] = category_count 

                        # Ensure all required columns are present in the parsed row
                        for col in ['Sub-Category', 'Potential Root Cause (Inferred)', 'Potential Resolution Steps', 'Future Prediction', 'Proactive Measures']: # Changed
                            if col not in detailed_row:
                                detailed_row[col] = 'N/A' # Default to N/A if LLM missed a column
                        final_analysis_rows.append(detailed_row)

                except Exception as e:
                    yield append_log_message(f"Error detailing analysis for category '{category}': {e}") # Corrected
                    print(f"Error detailing analysis for category '{category}': {e}", file=sys.stderr)
                    # Fallback row if API call fails for a category
                    default_row = {
                        "Category": category,
                        "Sub-Category": "N/A",
                        "Count": category_count,
                        "Issue Description (Summary)": f"Error during detailed analysis for this category: {e}",
                        "Potential Root Cause (Inferred)": "Undetermined due to error.", # Changed
                        "Potential Resolution Steps": "N/A",
                        "Future Prediction": "N/A",
                        "Proactive Measures": "N/A"
                    }
                    final_analysis_rows.append(default_row)
            
        analysis_df = pd.DataFrame(final_analysis_rows)
        if 'Count' in analysis_df.columns:
            # Ensure 'Count' is integer type for accurate summation
            analysis_df['Count'] = pd.to_numeric(analysis_df['Count'], errors='coerce').fillna(0).astype(int)

        summarized_count = analysis_df['Count'].sum()
        if summarized_count != _original_total_tickets_count: # Use total_rows_in_file from the full dataframe read
            yield append_log_message(f"CRITICAL WARNING: Total count in summarized DF ({summarized_count}) does not match original tickets ({_original_total_tickets_count}). This might indicate a parsing or AI categorization issue.") # Corrected
            print(f"CRITICAL WARNING: Total count in summarized DF ({summarized_count}) does not match original tickets ({_original_total_tickets_count}). This might indicate a parsing or AI categorization issue.", file=sys.stderr)
            
        global_analysis_df_for_export = analysis_df.copy()
        global_raw_markdown_for_export = analysis_df.to_markdown(index=False) + disclaimer_text_for_raw

    except ValueError as e:
        error_message_markdown = f"Ticket Analysis Error: {e}"
        yield gr.update(value=f"<p style='color:#CC3300;'>{error_message_markdown}</p>", visible=True), gr.update(value="\n".join(log_messages))
        global_raw_markdown_for_export = error_message_markdown + disclaimer_text_for_raw
        global_analysis_df_for_export = pd.DataFrame() # Clear dataframe on error
        return 
    except Exception as e:
        error_message_markdown = f"An unexpected error occurred during analysis: {e}. Check server logs for details."
        yield gr.update(value=f"<p style='color:#CC3300;'>{error_message_markdown}</p>", visible=True), gr.update(value="\n".join(log_messages))
        print(f"Error during RAG analysis: {e}", file=sys.stderr)
        # Typo fixed here: disclaimer_text_for_export changed to disclaimer_text_for_raw
        global_raw_markdown_for_export = error_message_markdown + disclaimer_text_for_raw 
        global_analysis_df_for_export = pd.DataFrame() # Clear dataframe on error
        return 

    yield append_log_message("Generating summary report for display...") # Corrected
    progress(0.98, desc="Generating summary report for display...")
    time.sleep(0.01) # Small sleep for progress bar to render
    
    summarized_analysis_html = ""
    try:
        if not analysis_df.empty:
            summarized_analysis_html = generate_summary_html_by_category(analysis_df)
            yield append_log_message("HTML summary generated successfully.") # Corrected
        else:
            summarized_analysis_html = "<p style='color:#CC3300;'>AI output parsed into an empty DataFrame or no categories were generated. Please check the uploaded file and try again.</p>"
            yield append_log_message("Warning: No categories generated, HTML summary might be empty.") # Corrected

    except Exception as e:
        yield append_log_message(f"Failed to generate HTML summary from DataFrame: {e}") # Corrected
        print(f"Failed to generate HTML summary from DataFrame: {e}", file=sys.stderr)
        summarized_analysis_html = f"""
        <div style="margin-bottom: 20px;">
            <p style="color: #CC3300;'>An unexpected error occurred while attempting to transform AI output into a visual summary: {e}</p>
            <pre style="background-color: #f0f0f0; padding: 15px; border: 1px solid #ddd; overflow-x: auto; white-space: pre-wrap; word-wrap: break-word;">{global_raw_markdown_for_export}</pre>
        </div>
        """
        global_analysis_df_for_export = pd.DataFrame() # Clear dataframe on error


    # Construct the full HTML for the report, including styling
    full_html_report = f"""
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Customer Ticket Analysis Report</title>
        <style>
            body {{
                font-family: 'Segoe UI', Arial, sans-serif;
                margin: 0;
                padding: 0;
                background-color: #f8f8f8;
                color: #333;
                line-height: 1.6;
            }}
            .container {{
                max-width: 900px;
                margin: 30px auto;
                padding: 30px;
                background-color: #ffffff;
                border-radius: 8px;
                box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            }}
            h1 {{
                color: #1A237E; /* Deep Indigo */
                text-align: center;
                margin-bottom: 25px;
                font-size: 2.2em;
                font-weight: 600;
            }}
            h2 {{
                color: #283593; /* Indigo */
                margin-top: 30px;
                padding-bottom: 8px;
                border-bottom: 1px solid #E0E0E0;
                font-size: 1.6em;
                font-weight: 500;
            }}
            h3 {{
                color: #1A5276; /* Darker Blue-Green */
                margin-top: 20px;
                margin-bottom: 10px;
                font-size: 1.2em;
                font-weight: 600;
            }}
            p {{
                margin-bottom: 8px;
            }}
            .overview-section {{
                background-color: #E8EAF6; /* Lighter Indigo */
                padding: 20px;
                border-radius: 6px;
                margin-bottom: 25px;
                border: 1px solid #C5CAE9;
            }}
            .category-block {{
                background-color: #ffffff;
                border: 1px solid #e0e0e0;
                border-radius: 4px;
                padding: 15px;
                margin-bottom: 15px;
                box-shadow: 0 1px 3px rgba(0,0,0,0.08);
            }}
            .disclaimer-section {{
                background-color: #f5f5f5; /* Light gray */
                padding: 15px;
                border-radius: 6px;
                margin-top: 30px;
                border: 1px solid #e0e0e0;
                font-size: 0.85em;
                color: #555;
            }}
            ul {{
                list-style-type: disc;
                margin-left: 25px;
                margin-top: 5px;
                margin-bottom: 5px;
            }}
            li {{
                margin-bottom: 3px;
                padding-left: 2px;
            }}
            strong {{
                color: #333;
                font-weight: 600;
            }}
            .warning-text {{
                color: #CC3300; /* Darker Red */
                font-weight: bold;
                margin-top: 10px;
            }}
            .report-metadata {{
                font-size: 0.75em;
                color: #777;
                text-align: right;
                margin-top: 15px;
            }}
        </style>
    </head>
    <body>
        <div class="container">
            <h1>Executive Summary: Customer Ticket Analysis</h1>

            <div class="overview-section">
                <h2>Report Overview</h2>
                <p>
                    This report presents a comprehensive analysis of recent customer support tickets, leveraging advanced AI to identify key trends, underlying root causes, and actionable recommendations.
                </p>
                <p>
                    <strong style="color: #283593;">Total Tickets Processed:</strong> {_original_total_tickets_count}<br>
                    <strong style="color: #283593;">Total Categories Identified:</strong> {len(unique_categories) if 'unique_categories' in locals() else 0}
                    {'<p class="warning-text">WARNING: Summarized ticket count ({summarized_count}) does not match original total ({_original_total_tickets_count}). Data parsing or AI categorization may have occurred.</p>' if summarized_count != _original_total_tickets_count else ''}
                </p>
            </div>

            <div class="main-content">
                <h2>Categorized Insights</h2>
                {summarized_analysis_html}
            </div>

            <div class="disclaimer-section">
                <h3>Disclaimer</h3>
                <p>
                    AI-generated insights, including inferred root causes, future predictions, and proactive measures, are based on the provided ticket data. These analyses are intended for guidance and require human expert validation for critical business decisions.
                </p>
                <p class="report-metadata">
                    Report Generation Timestamp: {display_time}
                </p>
            </div>
        </div>
    </body>
    </html>
    """

    global_html_report_for_export = full_html_report # Store the full HTML content
    
    yield append_log_message("Analysis complete. Report generated.") # Corrected
    progress(1, desc="Analysis complete. Report generated.")
    time.sleep(0.01) # Final small sleep for progress bar to render
    
    yield gr.update(value=full_html_report, visible=True), gr.update(value="\n".join(log_messages))


# --- Output Download Functions (using global_html_report_for_export) ---


def generate_pdf_report(selected_timezone):
    # Use the pre-generated HTML report
    html_content = global_html_report_for_export
    
    if not html_content or _last_uploaded_filename_base is None:
        return gr.update(value=None, visible=False)

    try:
        html_obj = HTML(string=html_content)
        
        version_num = _file_versions.get(_last_uploaded_filename_base, 1)
        output_filename = f"{_last_uploaded_filename_base}_Ticket_Analysis_Report_v{version_num}.pdf"
        temp_file_path = os.path.join(tempfile.gettempdir(), output_filename)
        
        html_obj.write_pdf(temp_file_path) 
        
        return gr.update(value=temp_file_path, visible=True, label=f"Download {output_filename}")

    except Exception as e:
        print(f"Error generating PDF with WeasyPrint: {e}", file=sys.stderr)
        return gr.update(value=None, visible=False)


def generate_docx_report(selected_timezone):
    df = global_analysis_df_for_export
    if df.empty or _last_uploaded_filename_base is None:
        return gr.update(value=None, visible=False)

    doc = DocxDocument()
    
    # Set document-wide font/size for professionalism if needed
    # doc.styles['Normal'].font.name = 'Arial'
    # doc.styles['Normal'].font.size = Pt(11)

    heading = doc.add_heading("Customer Ticket Analysis Report", level=1)
    heading.alignment = WD_ALIGN_PARAGRAPH.CENTER
    
    now_utc = datetime.utcnow()
    current_gen_time = format_time_for_display(now_utc, selected_timezone)
    
    # Metadata paragraph
    p_metadata = doc.add_paragraph(f"Report Generation Timestamp: {current_gen_time}")
    p_metadata.alignment = WD_ALIGN_PARAGRAPH.RIGHT
    p_metadata.runs[0].font.size = Pt(9)
    doc.add_paragraph("") 

    doc.add_heading("Report Overview", level=2)
    doc.add_paragraph(
        "This report presents a comprehensive analysis of recent customer support tickets, leveraging advanced AI to identify key trends, underlying root causes, and actionable recommendations."
    )
    total_tickets_processed = df['Count'].sum() if 'Count' in df.columns else 0
    total_categories_identified = len(df['Category'].unique()) if 'Category' in df.columns else 0

    doc.add_paragraph(f"Total Tickets Processed: {_original_total_tickets_count}")
    doc.add_paragraph(f"Total Categories Identified: {total_categories_identified}")
    
    # Add warning if counts don't match
    if '_original_total_tickets_count' in globals() and _original_total_tickets_count != total_tickets_processed:
        warning_p = doc.add_paragraph("WARNING: Summarized ticket count does not match original total. Data parsing or AI categorization may have occurred.")
        warning_p.runs[0].font.color.rgb = RGBColor(0xFF, 0x00, 0x00) # Red color for warning
        warning_p.runs[0].bold = True
    doc.add_paragraph("") 

    doc.add_heading("Categorized Insights", level=2)
    doc.add_paragraph("") 

    # Group by category and sum the counts, then sort by count in descending order
    category_counts = df.groupby('Category')['Count'].sum().sort_values(ascending=False)

    for category in category_counts.index:
        category_df = df[df['Category'] == category]
        total_count = category_df['Count'].sum()
        
        cat_heading = doc.add_heading(f"{category} (Count: {total_count})", level=3)
        doc.add_paragraph("") 

        # Summary
        doc.add_paragraph("Summary:").runs[0].bold = True
        issue_descriptions_list = []
        for summary_text in category_df['Issue Description (Summary)'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', summary_text)
            for line in lines:
                line = line.strip()
                if line:
                    issue_descriptions_list.append(line)
        if issue_descriptions_list:
            for item in issue_descriptions_list:
                doc.add_paragraph(item, style='List Bullet')
        else:
            doc.add_paragraph("No specific summary available.", style='List Bullet')
        doc.add_paragraph("")

        # Sub-Categories
        doc.add_paragraph("Sub-Categories:").runs[0].bold = True
        sub_cat_found = False
        if 'Sub-Category' in category_df.columns:
            sub_cat_counts = category_df.groupby('Sub-Category')['Count'].sum().sort_values(ascending=False)
            for sub_cat, count in sub_cat_counts.items():
                if str(sub_cat).strip() and str(sub_cat).strip().lower() != 'n/a':
                    doc.add_paragraph(f"{sub_cat} (Count: {count})", style='List Bullet')
                    sub_cat_found = True
        if not sub_cat_found: 
            doc.add_paragraph("No sub-categories identified.", style='List Bullet')
        doc.add_paragraph("")

        # Potential Root Cause
        doc.add_paragraph("Potential Root Cause:").runs[0].bold = True # Changed to Potential Root Cause
        root_causes_list = []
        for rc_text in category_df['Potential Root Cause (Inferred)'].astype(str).drop_duplicates().tolist(): # Changed to Potential Root Cause
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', rc_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'undetermined from available data' and line.lower() != 'undetermined.':
                    root_causes_list.append(line)
        if root_causes_list:
            for item in root_causes_list:
                doc.add_paragraph(item, style='List Bullet')
        else:
            doc.add_paragraph("Undetermined.", style='List Bullet')
        doc.add_paragraph("")

        # Resolution Steps
        doc.add_paragraph("Resolution Steps:").runs[0].bold = True
        resolution_steps_list = []
        for res_text in category_df['Potential Resolution Steps'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', res_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'n/a':
                    resolution_steps_list.append(line)
        if resolution_steps_list:
            for item in resolution_steps_list:
                doc.add_paragraph(item, style='List Bullet')
        else:
            doc.add_paragraph("No specific steps identified.", style='List Bullet')
        doc.add_paragraph("")

        # Insights & Recommendations section
        doc.add_paragraph("Insights & Recommendations:").runs[0].bold = True 
        
        # Future Prediction
        doc.add_paragraph("Future Prediction:").runs[0].bold = True 
        future_prediction_items = []
        for pred_text in category_df['Future Prediction'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', pred_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'n/a':
                    future_prediction_items.append(line)
        if future_prediction_items:
            for item in future_prediction_items:
                doc.add_paragraph(item, style='List Bullet')
        else:
            doc.add_paragraph("No future predictions identified.", style='List Bullet')
        doc.add_paragraph("") 

        # Proactive Measures
        doc.add_paragraph("Proactive Measures:").runs[0].bold = True 
        proactive_measures_items = []
        for proactive_text in category_df['Proactive Measures'].astype(str).drop_duplicates().tolist():
            lines = re.split(r'\s*[\*\-]\s*|\s*\n\s*', proactive_text)
            for line in lines:
                line = line.strip()
                if line and line.lower() != 'n/a':
                    proactive_measures_items.append(line)
        if proactive_measures_items:
            for item in proactive_measures_items:
                doc.add_paragraph(item, style='List Bullet')
        else:
            doc.add_paragraph("No proactive measures identified.", style='List Bullet')
        doc.add_paragraph("") 


    doc.add_heading("Disclaimer", level=2)
    doc.add_paragraph(
        "AI-generated insights, including inferred root causes, future predictions, and proactive measures, are based on the provided ticket data. These analyses are intended for guidance and require human expert validation for critical business decisions."
    )
    doc.add_paragraph(f"Report Generation Timestamp: {current_gen_time}")

    version_num = _file_versions.get(_last_uploaded_filename_base, 1)
    output_filename = f"{_last_uploaded_filename_base}_Ticket_Analysis_Report_v{version_num}.docx"
    temp_file_path = os.path.join(tempfile.gettempdir(), output_filename)
    
    doc.save(temp_file_path) 

    return gr.update(value=temp_file_path, visible=True, label=f"Download {output_filename}")


# --- Gradio Interface Setup ---

# Get a list of common timezones for the dropdown
COMMON_TIMEZONES = sorted(pytz.common_timezones)

with gr.Blocks(theme=gr.themes.Soft(), css="""
    /* General container styling for a clean, centered layout */
    .gradio-container { 
        max-width: 960px; /* Slightly wider for better content presentation */
        margin: 20px auto; 
        padding: 30px; 
        background-color: #ffffff; 
        border-radius: 8px; 
        box-shadow: 0 4px 15px rgba(0,0,0,0.08); /* Subtle shadow */
        font-family: 'Segoe UI', Arial, sans-serif; /* Professional font */
        color: #333;
    }

    /* Target Gradio's markdown output blocks directly to reduce spacing between them */
    .gradio-container .output_markdown,
    .gradio-container .gr-html { /* .gr-html is also used for gr.HTML */
        margin-top: 5px !important;
        margin-bottom: 5px !important;
        padding-top: 0 !important;
        padding-bottom: 0 !important;
    }

    /* Headings for a consistent, professional look */
    h1 { 
        color: #1A237E; /* Deep Indigo */
        text-align: center; 
        margin-bottom: 20px; /* Reduced from 25px */
        font-size: 2.5em; /* Larger main title */
        font-weight: 600; /* Slightly bolder */
    }
    h2 { 
        color: #283593; /* Indigo */
        margin-top: 25px; /* Reduced from 30px */
        padding-bottom: 6px; /* Reduced from 8px */
        border-bottom: 1px solid #E0E0E0; /* Subtle separator */
        font-size: 1.8em; 
        font-weight: 500;
    }
    h3 { 
        color: #1A5276; /* Darker Blue-Green */
        margin-top: 10px; /* Reduced from 15px */
        margin-bottom: 5px; /* Reduced from 8px */
        font-size: 1.3em; 
        font-weight: 600;
    }
    h4 { /* Added h4 styling */
        color: #3F51B5; /* Medium Indigo */
        margin-top: 8px; /* Reduced from 12px */
        margin-bottom: 4px; /* Reduced from 6px */
        font-size: 1.1em;
        font-weight: 600;
    }


    /* Paragraphs and list items */
    p { 
        line-height: 1.5; /* Slightly tighter line height */
        margin-bottom: 6px; /* Reduced from 10px */
    }
    ul {
        list-style-type: disc; /* Standard disc bullets */
        margin-left: 25px; /* Indent lists properly */
        margin-top: 4px; /* Reduced from 8px */
        margin-bottom: 4px; /* Reduced from 8px */
        line-height: 1.4; /* Slightly tighter line height */
    }
    li {
        margin-bottom: 2px; /* Reduced from 4px */
        padding-left: 2px; /* Small padding for visual comfort */
    }
    strong {
        font-weight: 600; /* Use font-weight instead of color for emphasis */
        color: #333; /* Keep strong text dark for readability */
    }

    /* Specific section styling for minimalist appeal */
    .overview-section {
        background-color: #E8EAF6; /* Lightest Indigo background */
        padding: 18px; /* Reduced from 20px */
        border-radius: 6px;
        margin-bottom: 18px; /* Reduced from 25px */
        border: 1px solid #C5CAE9; /* Subtle border */
    }
    .category-block {
        background-color: #ffffff;
        border: 1px solid #e0e0e0;
        border-radius: 4px;
        padding: 15px;
        margin-bottom: 15px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.08); /* Minimal shadow */
    }
    .disclaimer-section {
        background-color: #f5f5f5; /* Light gray for disclaimers */
        padding: 15px;
        border-radius: 6px;
        margin-top: 25px; /* Reduced from 30px */
        border: 1px solid #e0e0e0;
        font-size: 0.85em;
        color: #555;
    }

    /* Buttons for a clean, professional interaction */
    .gr-button { 
        background-color: #388E3C; /* Darker green for primary actions */
        color: white; 
        border-radius: 5px; 
        padding: 10px 20px;
        font-weight: 500;
        transition: background-color 0.2s ease; /* Smooth transition */
    }
    .gradio-container .gr-button:hover { 
        background-color: #2E7D32; /* Slightly darker green on hover */
    }

    /* Input elements */
    .gr-textarea, .gr-file, .gr-dropdown { 
        border-radius: 5px; 
        border: 1px solid #bbb; /* Slightly darker border for inputs */
        padding: 8px;
    }

    /* Download buttons alignment */
    #download-buttons { 
        display: flex; 
        justify-content: center; 
        gap: 15px; /* More space between download buttons */
        margin-top: 20px; 
    }
    .download-button { 
        flex-grow: 1; 
        max-width: 250px; /* Allow buttons to be a bit wider */
    }

    /* Warning text style */
    .warning-text {
        color: #CC3300; /* Darker red for warnings */
        font-weight: bold;
        margin-top: 10px;
    }

    /* Report metadata for subtle timestamp */
    .report-metadata {
        font-size: 0.8em;
        color: #777;
        text-align: right;
        margin-top: 15px;
    }

    /* Log box styling */
    #progress-log-box {
        background-color: #f0f0f0;
        border: 1px solid #e0e0e0;
        border-radius: 4px;
        padding: 10px;
        font-family: 'Consolas', 'Courier New', monospace; /* Monospace for logs */
        font-size: 0.9em;
        color: #555;
        overflow-y: auto; /* Enable scrolling for long logs */
        height: 120px; /* Fixed height for log box */
    }
    /* Specific hr styling for Markdown content separators */
    .gradio-container hr { /* Target hr within the gradio container, particularly for Markdown */
        margin-top: 12px; /* Reduced from 15px */
        margin-bottom: 12px; /* Reduced from 15px */
        border: 0;
        height: 1px;
        background-color: #E0E0E0;
    }
    /* CSS to hide the Hugging Face Space footer */
    footer {
        display: none !important;
    }
""") as demo:
    gr.HTML("""
    <h1 align="center">Advanced AI Customer Ticket Analyst</h1>
    <p align="center" style="font-size: 1.0em; color: #555; margin-bottom: 15px;">Derive actionable insights from your customer support data.</p>
    """)

    gr.Markdown(
        """
        ### System Overview
        This tool provides structured insights from customer support tickets. Upload your data to receive a comprehensive analysis including categorization, root causes, strategic recommendations, and future outlooks.
        """
    )

    gr.Markdown(
        """
        #### Important Note on AI Inferences:
        AI-generated insights, including inferred root causes, future predictions, and proactive measures, are based on the provided ticket data. These analyses are intended for guidance and require human expert validation for critical business decisions.
        """
    )
    gr.Markdown(
        """
        #### Input File Requirements:
        * **Supported Formats:** `.xlsx` (Excel), `.xls` (Legacy Excel), and `.csv` (Comma Separated Values).
        * **Data Structure:** Files must contain tabular data with clear headers in the first row. Each row represents an individual customer ticket.
        * **Minimum Columns:** A minimum of two columns are recommended: one for a unique `Ticket ID` (or `ID`) and one for the `Issue` (or `Description`).
            * **Example:**
                ```
                TicketID,IssueDescription
                TKT001,User cannot log in; password reset not working.
                TKT002,Billing error: duplicate charge for last month's service.
                ```
        * **Recommended Columns:** For richer analysis, include columns suchs as `Status`, `Priority`, `CustomerName`, and `Timestamp`. More relevant details enhance AI inferences.
        * **Data Volume:** For large files (e.g., over 60,000 tickets), processing occurs in batches to optimize memory and API usage. Processing times may vary.
        * **Data Privacy:** Please do not upload highly sensitive or confidential customer information. This is a public demonstration environment.
        """
    )

    gr.Markdown(
        """
        #### Output Structure:
        The AI will generate a clear, data-driven report based solely on your uploaded file. The visual output will feature:
        * **Category Name (Total Count for Category)**
            * **Summary:** Key issue descriptions and common themes.
            * **Sub-Categories:** More granular sub-categories and their counts.
            * **Potential Root Cause:** AI-inferred underlying reasons for issues.
            * **Resolution Steps:** Strategic actions to resolve issues.
            * **Insights & Recommendations:**
                * **Future Prediction:** Predictions if underlying issues persist.
                * **Proactive Measures:** Preventive steps to reduce recurrence.
        """
    )

    with gr.Row():
        file_input = gr.File(
            label="Upload Customer Ticket Data",
            file_types=[".xlsx", ".xls", ".csv"],
            type="filepath",
            file_count="single",
            interactive=True
        )
        query_input = gr.Textbox(
            label="Optional Analysis Request (Leave blank for full report)",
            placeholder="e.g., 'Summarize all high-priority issues' or leave empty for comprehensive analysis.",
            lines=2,
            interactive=True
        )
    
    timezone_dropdown = gr.Dropdown(
        choices=COMMON_TIMEZONES,
        label="Select Local Timezone (for report timestamps)",
        value="America/Chicago", # Set a reasonable default
        allow_custom_value=False,
        interactive=True
    )

    submit_btn = gr.Button("Generate Analysis Report", variant="primary")

    progress_log = gr.Textbox(
        label="Processing Log",
        interactive=False,
        lines=5, 
        autoscroll=True, 
        elem_id="progress-log-box",
        visible=False # Hidden on load
    )

    output_html_display = gr.HTML(
        label="AI Ticket Analysis Report"
    )

    with gr.Group(elem_id="download-buttons"):
        pdf_output_file = gr.File(label="Download PDF", visible=False)
        docx_output_file = gr.File(label="Download Word Document", visible=False)
        pdf_btn = gr.Button("Download as PDF Report", elem_classes="download-button")
        docx_btn = gr.Button("Download as Word Document", elem_classes="download-button")


    gr.HTML("""
        <hr style="margin-top: 20px; margin-bottom: 10px; border-color: #E0E0E0;">
        <p align="center" style="font-size: 0.8em; color: #777;">
        *All AI-generated insights are for informational purposes and should be verified by human experts.*
        </p>
    """)

    submit_btn.click(
        fn=perform_ticket_analysis,
        inputs=[file_input, query_input, timezone_dropdown],
        outputs=[output_html_display, progress_log],
        queue=True # Enable queueing for potentially long running tasks
    )

    pdf_btn.click(
        fn=generate_pdf_report,
        inputs=[timezone_dropdown],
        outputs=pdf_output_file,
        api_name="download_pdf"
    )

    docx_btn.click(
        fn=generate_docx_report,
        inputs=[timezone_dropdown],
        outputs=docx_output_file,
        api_name="download_docx"
    )

if __name__ == "__main__":
    if not os.getenv("GOOGLE_API_KEY"):
        print("\n" + "="*80)
        print("REMINDER: GOOGLE_API_KEY environment variable is NOT SET for local execution.")
        print("RAG functionality will not work locally without it. Please set it in a .env file or your environment.")
        print("Example: GOOGLE_API_KEY='your-gemini-api-key-here'")
        print("="*80 + "\n")
    demo.launch()