
    {6i]                        d dl Z d dlZd dlmZ d dlmZmZmZmZm	Z	m
Z
mZmZ d dlmZmZmZmZ d dlmZ d dlmZ d dlmZ d dlmZmZmZ d d	lmZmZmZm Z  d d
l!m!Z!m"Z" d dl#m$Z$ d dl%m&Z& d dl'm(Z( d dl)m*Z*m+Z+m,Z,m-Z- d dl.m/Z/m0Z0 d dlm
Z
m	Z	 d dl1Z1d dl2m3Z3 d dl4m5Z5 d dl6Z7d dl8Z9d dl:m;Z; d dl<m=Z=  e       Z> e       Z?dZ@e>j                  de,      ddddd ed      d edd       ee&       ee?      f
d eBd!eBd"eeC   d#eCd$eCd%eeB   d&eCd'eBd(ed)efd*       ZDe>j                  d+e-       ee&       ee?      fd,e-d(ed)efd-       ZFe>j                  d.e,       ee&       ee?      fd/eBd(ed)efd0       ZGe>j                  d1e,       ee&       ee?      fd/eBd&eCd(ed)efd2       ZIe>j                  d3e,       ee&       ee?      fd/eBd,e-d(ed)efd4       ZJe>j                  d5e,       ee&       ee?      fd/eBd(ed)efd6       ZLe>j                  d7       e
d       ed      fd8e	d9eCfd:       ZMd;ZN e1j                  eNd<=       d>Z@e>j                  d?       e
d       ee&      fd8e	d(efd@       ZPy)A    N)Optional)	APIRouterDependsFormQuery
UploadFileFileHTTPExceptionstatus)distinctfunccaseor_)Session)JSONResponse)jsonable_encoder)OAuth2PasswordRequestForm
HTTPBearerHTTPAuthorizationCredentials)verify_passwordget_password_hashcreate_access_tokenverify_token)datetimetimezone)settings)get_db)User)TokenLoginRequestUserResponse
UserCreate)jwtJWTError)r	   r   )UserCourseLog)Course)Role)
Universityz0^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$/)response_model   
   idascall.zPlatform ID is required)descriptionpageNorecordsPerPagesearchsort_by
sort_orderrole_iduser_statusplatform_iddbcredentialsc
                 f  K   |	j                   }
t        |
      }|j                  t              j	                  t        j
                  |k(        j                         }|st        t        j                  d      	 t        |      }|j                  t              j	                  t        j                  |k(        }|j                         dk(  r#|j	                  t        j                  dk(        }n5|j                         dk(  r"|j	                  t        j                  dk(        }|"|j	                  t        j                   |k(        }|r]|j	                  t#        t        j$                  j'                  d
| d
      t        j(                  j'                  d
| d
                  }t+        t        |d	      }|st        dd| d      |j-                  |dk(  r|j/                         n|j1                               }| dz
  |z  }|j3                  |      j5                  |      j7                         }|st        dd      |j                  t8        j:                  t=        j>                  tA        t8        jB                  dk(  t8        jD                  fd            jG                  d      t=        j>                  tA        t8        jB                  dk(  t8        jD                  fd            jG                  d            jI                  t8        j:                        j7                         }|D ci c]  \  }}}||xs d|xs dd }}}}g }|D ]  }|jK                  |j
                  ddd      }|jM                  |j
                  |j$                  |j(                  |jN                  |j                  |jP                  |jR                  rB|jR                  j
                  |jR                  j$                  |jR                  jT                  dnd	|jV                  |jX                  |jZ                  |j                  |d   |d   d        |j                  t=        j\                  t        j
                              j	                  t        j                  |k(        }|j                         dk(  r#|j	                  t        j                  dk(        }n5|j                         dk(  r"|j	                  t        j                  dk(        }|"|j	                  t        j                   |k(        }|j_                         xs d}ta        ||| |||z   dz
  |z  t        jb                  dd      }te        |t        jb                        S # t        t        f$ r t        dd      w xY wc c}}}w w)z
    Retrieve a paginated list of users filtered by platform_id.
    Each user's total credited/debited courses are aggregated individually.
    Invalid user tokenstatus_codedetaili  zInvalid platform_id formatactiveTinactiveFN%zInvalid sort field ''r.   r+   i  z No users found for this platformCreditr   )else_total_credited_coursesDebittotal_debited_courses)rF   rH   )r-   nameinstitution_code)r-   rI   emailroler8   university_id
universityactivation_datephone_number
department	is_activerF   rH   zUsers retrieved successfully)userstotal_countpage_norecords_per_pagetotal_pagesr>   messagecontentr>   )3r:   r   queryr   filterr-   firstr
   r   HTTP_401_UNAUTHORIZEDint
ValueError	TypeErrorr8   lowerrR   r6   r   rI   ilikerK   getattrorder_byr.   descoffsetlimitr/   r%   user_idr   sumr   typenumber_of_courselabelgroup_bygetappendrL   rM   rN   rJ   rO   rP   rQ   countscalarr   HTTP_200_OKr   )r1   r2   r3   r4   r5   r6   r7   r8   r9   r:   tokenri   userdb_querysort_columnrg   paginated_usersuser_totalstotal_creditedtotal_debiteduser_totals_dict
users_listutotalstotal_querytotal_usersresponse_datas                              :/var/www/html/syllabuild_admin/backend/api/routes/users.pyget_active_usersr      s    $ ##E5!G88D>  G!34::<D(D(DMabbR+&
 xx~$$T%5%5%DEH h&??4>>T#9:				
	*??4>>U#:; ??4<<7#:; ??		!F81.

  1VHA/
 $.K6J7)ST4UVV  jE6I!2{O_O_OabH qjN*Foof-33NCGGIO4VWW 	!!HHT=--9=;Y;YZbcdekk  mE  FHHT=--8-:X:XYabcdjj  lC  D	

 
-''	(	  GRS SBG^]  N<Oa;H;MA"O O S S
 J !%%addqcd,ef$$FFWWFF==__
  lloo))$%LL$A$A $( 00NN,,&,-E&F%+,C%D#
 	. ((4::dgg./66t7G7G;7VWKh&!((4)?@				
	*!((5)@A!(()@A$$&+!K %"*#n4q8^K))1& M 6;M;MNNG 	" R4PQQRdSs,   A8V1;V
 J.V14V*H=V1
V''
V1z/createru   c                   K   |j                   }t        |      }|j                  t              j	                  t        j
                  |k(        j                         }|st        t        j                  d      	 g d}| j                         }t        j                  t        j                        |d<   t        j                  t        j                        |d<   t        | j                         f|d<   t#        j$                  t&        |d         s,t)        t        j*                  t        j*                  dd	
      S |D ]\  }||vs||   r|j-                  dd      j/                         }	t)        t        j*                  t        j*                  |	 dd	
      c S  |d= t        di |}
|j1                  |
       |j3                          |j5                  |
       t)        t7        t9        j:                  |
            t        j<                  ddt        j<                        S # t>        $ r}t        ddtA        |             d}~ww xY ww)z
    Create a new user.
    r<   r=   )	rI   rK   passwordr6   rM   rP   rO   educational_degreerQ   
created_at
updated_atpassword_hashrK   zEmail: Invalid value.r   rX   r>   rZ   _  is required.r   zUser created successfullyru   r>   rX   rY     Something went wrong: N )!r:   r   r[   r   r\   r-   r]   r
   r   r^   dictr   nowr   utcr   r   rematchEMAIL_REGEXr   HTTP_400_BAD_REQUESTreplace
capitalizeaddcommitrefreshr   r!   from_ormHTTP_201_CREATED	Exceptionstr)ru   r9   r:   rt   ri   current_userrequired_fields	user_datafield
filed_namenew_useres               r   create_userr      s      ##E5!G88D>((G);<BBDL(D(DMabb/W

 IIK	"*,,x||"<	,"*,,x||"<	,%6t}}%E%F	/" xxYw%78"77$996  % 		EI%Yu-="]]34??A
# & ; ;"("="=&0\#? 		 j!$)$
x
		


8()>)>x)HI%666
 //
 	
  W6LSQRVH4UVVWsK   A8I;CH' <I=H' AH' IB	H' &I'	I0I		IIz/view/{user_id}ri   c                 J  K   |j                   }t        |      }|j                  t              j	                  t        j
                  |k(        j                         }|st        t        j                  d      |j                  t              j	                  t        j
                  | k(        j                         }|st        t        j                  d      t        t        t        j                  |            t        j                  ddt        j                        S w)z 
    Retrieve a user by ID.
    r<   r=   User not foundzUser retrieved successfullyr   rY   )r:   r   r[   r   r\   r-   r]   r
   r   r^   HTTP_404_NOT_FOUNDr   r   r!   r   rs   ri   r9   r:   rt   current_user_idr   ru   s          r   get_user_by_idr      s      ##E"5)O88D>((O)CDJJLL(D(DMabb88D>  G!34::<D(A(AJZ[[$\%:%:4%@A!--4

 && s   D!D#z&/change-status/{user_id}/{user_status}c                   K   |j                   }t        |      }|j                  t              j	                  t        j
                  |k(        j                         }|st        t        j                  d      |j                  t              j	                  t        j
                  | k(        j                         }|st        t        j                  d      |dk(  r0d|_        t        j                  t        j                        |_        n/|dk(  rd|_        d |_        nt        t        j"                  d      |j%                          t'        t        j(                  d	d
t        j(                        S w)Nr<   r=   r   r@   TrA   FzInvalid statusz User status updated successfullyr>   rX   rY   )r:   r   r[   r   r\   r-   r]   r
   r   r^   r   rR   r   r   r   r   rO   r   r   r   rs   )ri   r7   r9   r:   rt   r   r   ru   s           r   change_user_statusr      s      ##E"5)O88D>((O)CDJJLL(D(DMabb88D>  G!34::<D(A(AJZ[[h'||HLL9	
	"#(C(CL\]]IIK!--9
 && s   E8E:z/update/{user_id}c                   K   |j                   }t        |      }|j                  t              j	                  t        j
                  |k(        j                         }|st        t        j                  d      |j                  t              j	                  t        j
                  | k(        j                         }|st        t        j                  d      	 |j                  |_        |j                  |_        |j                  r~t        |j                  |j                        r,t!        t        j"                  ddt        j"                        S |j                  rt%        |j                        n|j                  |_        |j&                  |_        |j(                  |_        |j*                  r|j*                  nd|_        |j,                  r|j,                  nd|_        |j.                  r|j.                  nd|_        |j0                  r|j0                  nd|_        |j2                  r|j2                  nd|_        |j4                  r|j4                  nd|_        |j6                  r|j6                  nd|_        |j8                  r|j8                  nd|_        |j:                  r|j:                  nd|_        |j<                  r|j<                  nd|_        |j>                  r|j>                  nd|_        |j@                  r|j@                  nd|_         tC        jD                  tF        jH                        |_%        |jM                          |jO                  |       t!        tQ        tS        jT                  |            t        jV                  d	d
t        jV                        S # tX        $ r}t        ddt[        |             d}~ww xY ww)z"
    Update a user's details.
    r<   r=   r   z3Password cannot be the same as the current passwordr   rY   NFzUser updated successfullyr   r   r   ).r:   r   r[   r   r\   r-   r]   r
   r   r^   r   rI   rK   r   r   r   r   r   r   rR   r6   rO   is_email_verifiedis_phone_verifiedrP   profile_picturerQ   r   subscription_packagesubscription_start_datesubscription_end_datesubscription_statussubscription_renewal_dater   r   r   r   r   r   r   r   r!   r   rs   r   r   )	ri   ru   r9   r:   rt   r   r   existing_userr   s	            r   update_userr   !  s     ##E"5)O88D>((O)CDJJLL(D(DMabbHHTN))$''W*<=CCEM(A(AJZ[[+W!YY"jj==t}}m.I.IJ#'-'B'B#X !' ; ;  OSmm*;DMM*Jana|a|M'"&.. $@D@T@T(<(<Z^%DHDZDZ$*@*@`e'DHDZDZ$*@*@`e':>:K:KT%6%6QU"@D@T@T(<(<Z^%6:oo4??4 FJF]F]4+B+Bcg(JNJcJcT-F-Fim*PTPlPl0L0Lrv-LPLfLfd.H.Hlp+HLH`H`D,D,Dfj)TXTrTr$2P2Px|/#+<<#= 
		


=!()>)>})MN%116
 **
 	
  W6LSQRVH4UVVWs8   COA9N/ OIN/ .O/	O8OOOz/delete/{user_id}c                 R  K   |j                   }t        |      }|j                  t              j	                  t        j
                  |k(        j                         }|st        t        j                  d      |j                  t              j	                  t        j
                  | k(        j                         }|st        t        j                  d      |j                  |       |j                          t        t        j                  ddt        j                        S w)z
    Delete a user by ID.
    r<   r=   r   zUser deleted successfullyr   rY   )r:   r   r[   r   r\   r-   r]   r
   r   r^   r   deleter   r   rs   r   s          r   delete_userr   c  s      ##E"5)O88D>((O)CDJJLL(D(DMabb88D>  G!34::<D(A(AJZ[[IIdOIIK!--2
 && s   D%D'z/upload/filerk   c                   K   d}g d}	 |dvrt        t        j                  d      | j                  j	                  d      d   j                         }||vr>t        t        j                  t        j                  dd	j                  |       d
      S | j                          d{   }t        |      |kD  rt        t        j                  d      d| }t        j                  |d       t        j                  t        j                        j!                  d      }| d| j                   }t        j"                  j                  ||      }	t%        |	d      5 }
|
j'                  |       ddd       t        d|z   dz   |z   ddt        j(                        S 7 # 1 sw Y   2xY w# t*        $ r"}t        dt-        |      id      cY d}~S d}~ww xY ww)zS
    Uploads a single file and saves it to the local directory with timestamp.
    i    )jpgjpegpngsvg)profilelogozInvalid file typer=   .z!Invalid file extension. Allowed: , r   r   NzFile size exceeds 2MB limit.zuploads/Texist_ok%Y%m%d%H%M%Sr   wbr)   u   File uploaded successfully ✅)filenamerX   )r>   errorr   )r
   r   r   r   splitrb   r   joinreadlenosmakedirsr   r   r   r   strftimepathopenwriters   r   r   )r   rk   MAX_FILE_SIZEALLOWED_EXTENSIONSextcontents
upload_dir	timestampnew_filename	file_pathfr   s               r   upload_filer     s     $M62@**F,G,GPcdd mm!!#&r*002(("77$99!B499M_C`Bab  $ x==(F,G,G'EG G  v&

J. LL.77G	#Admm_5GGLL\:	 )T" 	aGGH	 #D,\9Ffg**
 	
5 %.	 	  @Wc!f-3??@sl   G BF2 G F2 *F$+B8F2 #F&5.F2 #G $F2 &F/+F2 2	G;GGG GG zuploads/excelTr   z^[\w\.-]+@[\w\.-]+\.\w+$z/upload-excelc                   K   	 | j                   j                         j                  d      st        t        j
                  d      t        j                         j                  d      }t        j                  j                  t        | d| j                          }t        |d      5 }|j                  | j                          d{          ddd       	 t!        j"                  |      }|j)                  t*        j,                  t*        j.                  t*        j.                   gd      }g d	}|D cg c]  }||j0                  vs| }	}|	r-t        t        j
                  d
dj                  |	             g }
g }|j3                         D ]  \  }}|dz   }|D ci c]  }|||   
 }}g }|D ]:  }||   r	|j5                  |j)                  dd      j7                          d       < |j9                  d      r=t;        j<                  t>        t'        |d               s|j5                  d|d           d}|j9                  d      r`|jA                  tB              jE                  tB        jF                  |d   k(        jI                         }|s)|j5                  d|d           n|j5                  d       d}|j9                  d      r`|jA                  tJ              jE                  tJ        jL                  |d   k(        jI                         }|s)|j5                  d|d           n|j5                  d       |r|
j5                  ||d       |jN                  |d<   |jN                  |d<   |j5                  |        |
r3tQ        ddtS        |      tS        |
      |
dt        j
                        S |D ]  }	 tU        d6i d|d   d|d   dtW        t'        |d                d!|d!   d|d   d|d   d"|d"   d#|d#   d$|d$   d%|d%   d&|d&   d'|d'   d(|d(   d)|d)   d*d+d,t        j                  tX        jZ                        d-t        j                  tX        jZ                        }|j]                  |        |
r/|j_                          tQ        dd/|
d0t        j
                        S |ja                          tQ        d1tS        |       d2|D cg c]  }|d   	 c}d3t        jb                        S 7 Q# 1 sw Y   MxY w# t$        $ r,}t        t        j
                  dt'        |             d}~ww xY wc c}w c c}w # t$        $ r,}|
j5                  d.t'        |       gd       Y d}~d}~ww xY wc c}w # t        $ r}|d}~wt$        $ r.}|j_                          t        d4d5t'        |             d}~ww xY ww)7u   
    Upload Excel file (.xls or .xlsx), validate all rows first.
    If all rows are valid → insert all records.
    If any row has error → return all errors, no insertion happens.
    )z.xlsz.xlsxz0Invalid file format. Only .xls or .xlsx allowed.r=   r   r   r   NzUnable to read Excel file: )rI   rK   r   rL   rJ   rP   rO   r   rQ   r   r   r   r   r   zMissing columns in Excel: r      r   r   rK   zInvalid email format: rL   zInvalid role: zRole is required.rJ   zInvalid university code: zInstitution code is required.)rowerrorsr6   rM   r   z=Validation failed. Please fix the errors before re-uploading.)r   rX   
total_rowsfailed_rowsr   rY   rI   r   r   rP   rO   r   rQ   r   r   r   r   r   rR   Tr   r   zDatabase error: zSome rows failed to insert.)r   rX   r   successz users imported successfully.)r   rX   inserted_usersr   zInternal server error: r   )2r   rb   endswithr
   r   r   r   r   r   r   r   r   
UPLOAD_DIRr   r   r   pd
read_excelr   r   r   npnaninfcolumnsiterrowsrp   r   ro   r   r   r   r[   r'   r\   rI   r]   r(   rJ   r-   r   r   r   r   r   r   r   rollbackr   rs   )r   r9   r   r   r   dfr   r   colmissing_colsr   valid_usersindexr   
row_numberr   
row_errorsr   	role_datauniversity_datar   r~   s                         r   upload_excelr    s    c
}}""$--.?@"77I  LLN++N;	GGLL	{!DMM?-KL	)T" 	'aGG$))+%&	'	y)B ZZ"&&148
 (7P#RZZ:OPP"773DIIl4K3LM 
  ++- +	.JE3J2AB3c#hBIBJ ) ^ '%%sC)@)K)K)M(Nm&\]^
 }}W%bhh{C	RYHZD[.\!!$:9W;M:N"OP I}}V$HHTN11$))y?P2PQWWY	 %%y7H6I&JK!!"56 #O}}/0"$((:"6"="=//9=O3PP#%'   '%%(A)L^B_A`&ab!!"AB %( 
 (1||	)$-<-?-?	/*""9-W+	.\ %^"%b'#&v;$ #77	 	 % 	I "6*#G, #4C	*8M4N"O "+>!:	
 &i0 #,O"< %..?$@ (11E'F  )6 *33I)J -66O,P +44K*L )22G(H /88S.T #   (||HLL9!"  (||HLL9#& x +	: KKM%<$
 #77  			#!+.//LM7B"C!1W:"C
 **
 	
M &	' 	'  	"774SVH= 	" Q C^  %!1#a&:;  0 #D
   

,SVH5
 	

s   W1B&V) *T	T

TV) T* 0AV) 8U"U"AV) %U'2V) HV) 	W1
V) CU,2V) W1'V) 5V$V) W1TT'"V) *	U3'UUV) ,	V!5!VV) V!!V) )	W.2V44W. )W))W..W1)Qr   iotypingr   fastapir   r   r   r   r   r	   r
   r   
sqlalchemyr   r   r   r   sqlalchemy.ormr   fastapi.responsesr   fastapi.encodersr   fastapi.securityr   r   r   core.securityr   r   r   r   r   r   core.configr   
db.sessionr   db.models.userr   db.schemas.userr   r    r!   r"   joser#   r$   r   db.models.user_course_logr%   db.models.courser&   pandasr   numpyr   db.models.roler'   db.models.universityr(   routersecurityr   ro   r_   r   r   postr   r   putr   r   r   r   r   r   r   r  r       r   <module>r     s   	 	  ] ] ] 0 0 " * - _ _ _ _ '     I I  % 	 3 #    +	<AC- "4[S.GH&/070A}O}O}O SM}O 	}O
 }O c]}O }O }O 	}O .}O .}O@ Yz2 &/070A=W
=W=W .=W 3=W~ l; &/070A . <6 4\R &/070A	!!! 	! .	! S!H = &/070A	?W?W
?W 	?W .	?W >?WB "<@ &/070A . A< ZCyS	<@
<@
<@ <@B 
 J &)_Cy&/l

l
l
 l
r  