
    G*i                       d dl mZmZmZmZmZmZmZ d dl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 d dlmZ d dlZd dlZd dlZd dlZd d	lmZ d d
l mZ d dl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m"Z"m#Z# d dlZ$d d
l mZ d dlmZm%Z% d dl mZ d dl&Z&d dl'Z'd dlm%Z% d dl(Z(d dl)Z)d dl*Z*d dl+Z+d dl(Z,d dl-Z-d dl.Z/d dlZ0d dl.m1Z1  ed      Z2 ejf                  e4      jk                         jl                  Z7e7dz  Z8 ejr                  dd      ju                         dv Z; ejr                  d      xs  e(jx                  d      Z=e2j}                  ee=de;dd        edgd !      Z?d"ed#dfd$Z@e2j                  d% ed&'      d()        ed*'      ZBd"ed+eCdz  fd,ZDe2j                  d-e	.      d"efd/       ZFh d0ZGd"efd1ZHd"efd2ZId"efd3ZJd4 ZKd5 ZLd6 ZMd7 ZNd#eOfd8ZPd#eQfd9ZRd:eOfd;ZSe2j                  d<d=)       ed>       ed      fd"ed:eOd?eCdz  fd@       ZUdAeCfdBZVd:eOfdCZW	 	 ddDeCdAeCdEeCdFeCdGeCdz  dHeCdz  fdIZXe2j                  dJe.      d"efdK       ZYe2j                  dLe.      d"edMeOfdN       ZZe2j                  dOe.      d"edPeOfdQ       Z[e2j                  dRe\S      d"edPeOfdT       Z]e2j                  dU       edVdVdWX       edYdVdZd[\       ed       ed       ed      fd]eOd^eOd:eeO   d_eeO   d`eeC   f
da       Z^db Z_ddceOd#eCfddZ`d"efdeZadf Zbd:eOfdgZcdheOfdiZddheOdjeOfdkZee2j                  dldm)       ed>       ed       ed       ed      fd"edMeOd?eCdz  dneOdz  doeCdz  f
dp       Zfe2j                  dqe	.      d"efdr       Zge2j                  dse	.      d"efdt       Zhe2j                  due	.      d"efdv       Zie2j                  dwdxe	y      d"efdz       Zje2j                  dwd{e	y       ed>       ed>      fd"edAeCdEeCfd|       Zke2j                  d}d~e	y      d"efd       Zle2j                  dde	y      d"efd       Zme2j                  ded      d:eOd"efd       Zne2j                  dde	y      d"efd       Zoe2j                  dde	y       ed>       ed>       ed>      fd"edDeCdAeCdEeCfd       Zpe2j                  dde	y      d"efd       Zqe7d(z  dz  dz  Zre2j                  dd)      d"efd       Zse2j                  de	.      d"efd       Zte2j                  de	d      d"efd       Zue2j                  dde	y      d"efd       Zve2j                  de	.      d"efd       Zwe2j                  dde	y      dd"ed:eOdz  fd       Zxd dlm%Z% e2j                  de%e\   S      	 	 	 dd"edeeC   deeC   d:eeO   fd       Zy G d de      Zze2j                  de\S      d"edezfd       Z{e2j                  de\S      d"edeOfd       Z}e2j                  dd)       ed>       ed>       ed>       ed       ed       ed      fd"edjeOdeCdeCd?eCdz  deOdz  doeCdz  fd       Z~e2j                  ded      d"efd       Zh dZh dZ G d de      Z G d de      Zd:eOdFeCfdZh dZ G d de      Ze2j                  dd)      d:eOded"efd       Ze2j                  ded       ed       ed      fd"edeeC   dFeeC   fd       Ze2j                  ddì)      d:eOded"efdĄ       Ze!eC e#dŬƫ      f   Ze!eC e#dǬƫ      f   Z G dȄ de      Z G dʄ de      Z G d̄ de      Zd΄ Ze2j                  dOe\S      d"edPeOdefdτ       Ze2j                  de\S      d"edeOfd҄       Zh dӣZe2j                  de\S      d"edefdՄ       Ze2j                  de%e\   S      	 	 	 	 	 dd"edeeC   deeC   d:eeO   d`eeC   d_eeO   fdք       Ze2j                  de	.      d"efd؄       Ze2j                  de	.      d"efdڄ       Ze2j                  de	.       ed>       ed>       ed       ed       ed       ed       ed       ed       ed>      f	d"edDeCdAeCdeCdedeCdeCdeCdeCdoeCfd       Ze2j                  de	.      d"efd       Z	 d dlmZ dZdeCd#eCfdZdeCdeCd#eQfdZe2j                  de	.       ed>       ed>       ed>       ed>      fd"edeCdeCdeCdoeCf
d       Z G d de      Ze2j                  de\S       ed>      fd"ed:eOde\fd       Zd dl m3Z3 h dZe2j                  de\S       e3d>       ed>      fd"ed:eOde\fd       Ze2j                  ded      d"edefd       Ze2j                  ded       ed      fd"edeeO   fd       Ze2j                  d      d"efd       Ze2j                  d      d        Zd d lmZm"Z" d dlm%Z%mZ d dl(Z( G d de      Z G d de      Ze2j                  dee\   S       ed>      fd"edeOfd       Ze2j                  de\S      d"ed:eOfd       Zd d	l mZmZ d d	l mZmZ d dlZd"efd
Zd"efdZe2j                  de\S      d"edefd       Zd dlmZ d dlmZmZ e2ji                  e      d"edefd       Zd:eOfdZe2jo                  d      d        Zy# e$ r dZY Sw xY w(      )FastAPIRequestForm
UploadFileFileBodyHTTPException)HTMLResponseRedirectResponseJSONResponse)StaticFiles)Jinja2Templates)SessionMiddleware)CryptContext)	BaseModelN)Optional)Query)datetime	timedelta)r   DictAny	Annotated)r   )r   FieldStringConstraints)r   List)r	   )r   )urlparse	TheL4FPRO)titlez
db.sqlite3APP_ENVdev)prod
productionAPP_SECRET_KEY@   laxi:	 thelfpro_session)
secret_key	same_site
https_onlymax_agesession_cookiebcryptauto)schemes
deprecatedrequestreturnc                    | j                   j                          t        j                  d      | j                   d<   t        j                  d      | j                   d<   t	        t        j
                               | j                   d<   y )N    sidcsrf
created_at)sessionclearsecretstoken_urlsafeinttimer0   s    (/home/mario/Escritorio/furbo/app/main.pyrotate_sessionr?   0   s]    OO$2226GOOE%33B7GOOF$'		$4GOOL!    z/staticz
app/static)	directorystatic)namezapp/templatestokenc                     | j                   j                  d      }|r.|r,t        j                  t	        |      t	        |xs d            st        dd      y )Nr5      u   CSRF token inválidostatus_codedetail)r7   gethmaccompare_digeststrr	   )r0   rD   sesss      r>   require_csrfrP   9   sL    ??v&DuD$7$7D	3u{PRCS$T4JKK %Ur@   z/contabilidad)response_classc                 h   K   t        |        t        |       }t        j                  d| |d      S w)Nzcontabilidad.htmlr0   user)require_admincurrent_user	templatesTemplateResponserS   s     r>   contabilidadrY   ?   s2     ' D%%&9wX\;]^^   02>   PUTPOSTPATCHDELETEc                 x    | j                   t        v r(| j                  j                  d      }t	        | |       y y )NX-CSRF-Token)methodUNSAFE_METHODSheadersrK   rP   r0   rD   s     r>   _enforce_csrf_if_unsafere   H   s1    ~~'##N3We$ (r@   c                    t        |       }|st        dd      t        |        t        |t        j
                        r|d   nt        |j                  dd            xs d}t        |      j                         dk7  rt        dd      |S )	N  No autenticadorH   rolerF   adminrG   zSolo administradores	rV   r	   re   
isinstancesqlite3RowrN   rK   lowerr0   meri   s      r>   rU   rU   M   sy    	g	B4DEEG$$R5BvJ3rvvfb?Q;RYWYD
4yG#4JKKIr@   c                    t        |       }|st        dd      t        |        t        |t        j
                        r|d   nt        |j                  dd            xs d}t        |      j                         dvrt        dd      |S )	Nrg   rh   rH   ri   rF   docenterj   rG   Solo docentes o adminrk   rp   s      r>   require_docente_or_adminrv   W   sz    	g	B4DEEG$$R5BvJ3rvvfb?Q;RYWYD
4y 444KLLIr@   c                  x   t        j                  t        d d      } t         j                  | _        | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       	 | j                  d	       | S # t        $ r Y | S w xY w)
NF)isolation_levelcheck_same_threadzPRAGMA foreign_keys = ON;zPRAGMA journal_mode = WAL;zPRAGMA synchronous = NORMAL;zPRAGMA busy_timeout = 5000;zPRAGMA cache_size = -20000;zPRAGMA temp_store = MEMORY;zPRAGMA mmap_size = 268435456;)rm   connectDB_PATHrn   row_factoryexecute	Exceptionconns    r>   get_connr   a   s    ??7DERD{{DLL,-LL-.LL/0LL./LL./LL./45 K  Ks   B, ,	B98B9c                      t               S N)r    r@   r>   _connr   p   s
    :r@   c                     	 | j                  d| d      j                         D ch c]  }|d   	 c}S c c}w # t        $ r t               cY S w xY w)NzPRAGMA table_info()   )r}   fetchallr~   set)r   tablers      r>   _table_columnsr   s   sQ    "ll-?wa+HIRRTU!UUU us   %; 6; ; AAc                     t               5 } | j                         }|j                  d       |j                  d       |j                  d      j                         D ch c]  }|d   	 }}d|vr|j                  d       d|vr|j                  d       d	|vr|j                  d
       |j                  d       |j                  d       |j                  d       |j                  d       |j                  d       |j                  d       |j                  d       |j                  d       |j                  d      j                         D ch c]  }|d   	 }}d|vr"|j                  d       |j                  d       d|vr|j                  d       d|vr|j                  d       d|vr|j                  d       d|vr|j                  d       d|vr|j                  d        d	|vr|j                  d!       d"|vr|j                  d#       d$|vr|j                  d%       d&|vr|j                  d'       d(|vr|j                  d)       d*|vr|j                  d+       |j                  d,       |j                  d-       |j                  d.       |j                  d/       |j                  d0       |j                  d1       |j                  d2       |j                  d3       |j                  d4       |j                  d5       |j                  d6       |j                  d7       |j                  d8       |j                  d9       |j                  d:       |j                  d;       |j                  d<       |j                  d=       |j                  d>       |j                  d?       | j	                          d d d        y c c}w c c}w # 1 sw Y   y xY w)@NzPRAGMA foreign_keys=ONa  
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL UNIQUE,
            name TEXT NOT NULL,
            first_name TEXT,
            last_name TEXT,
            phone TEXT,
            password_hash TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'alumno',
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime'))
        )
        zPRAGMA table_info(users)r   
first_namez,ALTER TABLE users ADD COLUMN first_name TEXT	last_namez+ALTER TABLE users ADD COLUMN last_name TEXTphonez'ALTER TABLE users ADD COLUMN phone TEXTa1  
        CREATE TABLE IF NOT EXISTS user_profile (
            user_id INTEGER PRIMARY KEY,
            phone TEXT,
            avatar TEXT,
            city TEXT,
            dni TEXT,
            birth_date TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        a  
        CREATE TABLE IF NOT EXISTS user_sports (
            user_id INTEGER PRIMARY KEY,
            team TEXT,
            category TEXT,
            position TEXT,
            dominant_foot TEXT,
            strengths TEXT,
            weaknesses TEXT,
            injury_history TEXT,
            training_type TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        a  
        CREATE TABLE IF NOT EXISTS user_health (
            user_id INTEGER PRIMARY KEY,
            physical_work INTEGER,
            physical_work_details TEXT,
            smoking INTEGER,
            alcohol INTEGER,
            recovery TEXT,
            chest_pain INTEGER,
            discomfort INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        ap  
        CREATE TABLE IF NOT EXISTS user_consent (
            user_id INTEGER PRIMARY KEY,
            whatsapp_content INTEGER,
            video_permission INTEGER,
            privacy_acceptance INTEGER,
            data_confirmation INTEGER,
            agreement INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        a0  
        CREATE TABLE IF NOT EXISTS user_guardians (
            user_id INTEGER PRIMARY KEY,
            occupation TEXT,
            study_place TEXT,
            parent_name TEXT,
            parent_email TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        a9  
        CREATE TABLE IF NOT EXISTS user_marketing (
            user_id INTEGER PRIMARY KEY,
            found_us TEXT,
            enjoyment TEXT,
            nerves_confidence TEXT,
            additional_comments TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        z
        CREATE TABLE IF NOT EXISTS user_finance (
            user_id INTEGER PRIMARY KEY,
            matricula_eur REAL NOT NULL DEFAULT 0,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        a  
        CREATE TABLE IF NOT EXISTS reservations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            date TEXT NOT NULL,
            time TEXT,
            duration_minutes INTEGER,
            price_cents INTEGER NOT NULL DEFAULT 0,
            name TEXT,
            email TEXT,
            phone TEXT,
            notes TEXT,
            user_id INTEGER,
            status TEXT NOT NULL DEFAULT 'confirmada',
            docente_id INTEGER NOT NULL,
            paid INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY(docente_id) REFERENCES users(id) ON DELETE CASCADE
        )
        zPRAGMA table_info(reservations)r6   z3ALTER TABLE reservations ADD COLUMN created_at TEXTzmUPDATE reservations SET created_at = strftime('%Y-%m-%dT%H:%M:%S','now','localtime') WHERE created_at IS NULLr<   z-ALTER TABLE reservations ADD COLUMN time TEXTduration_minutesz<ALTER TABLE reservations ADD COLUMN duration_minutes INTEGERprice_centszJALTER TABLE reservations ADD COLUMN price_cents INTEGER NOT NULL DEFAULT 0rC   z-ALTER TABLE reservations ADD COLUMN name TEXTemailz.ALTER TABLE reservations ADD COLUMN email TEXTz.ALTER TABLE reservations ADD COLUMN phone TEXTnotesz.ALTER TABLE reservations ADD COLUMN notes TEXTuser_idz3ALTER TABLE reservations ADD COLUMN user_id INTEGERstatuszMALTER TABLE reservations ADD COLUMN status TEXT NOT NULL DEFAULT 'confirmada'
docente_idzIALTER TABLE reservations ADD COLUMN docente_id INTEGER NOT NULL DEFAULT 1paidzCALTER TABLE reservations ADD COLUMN paid INTEGER NOT NULL DEFAULT 0ar  
        CREATE TABLE IF NOT EXISTS reservation_students (
            reservation_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            PRIMARY KEY (reservation_id, user_id),
            FOREIGN KEY(reservation_id) REFERENCES reservations(id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        aS  
        CREATE TABLE IF NOT EXISTS jugadores_gastos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            date TEXT NOT NULL,
            user_id INTEGER,
            reservation_id INTEGER,
            amount_cents INTEGER NOT NULL,
            category TEXT,
            concept TEXT,
            notes TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
        )
        z@CREATE INDEX IF NOT EXISTS idx_jg_date ON jugadores_gastos(date)zCCREATE INDEX IF NOT EXISTS idx_jg_user ON jugadores_gastos(user_id)zQCREATE INDEX IF NOT EXISTS idx_jg_reservation ON jugadores_gastos(reservation_id)a  
        CREATE TABLE IF NOT EXISTS notifications (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            title TEXT,
            body TEXT,
            sender_id INTEGER,
            recipient_id INTEGER NOT NULL,
            reservation_id INTEGER,
            is_read INTEGER NOT NULL DEFAULT 0,
            deleted_by_sender INTEGER NOT NULL DEFAULT 0,
            deleted_by_recipient INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY(recipient_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
        )
        zPCREATE INDEX IF NOT EXISTS idx_notif_rec ON notifications(recipient_id, is_read)zECREATE INDEX IF NOT EXISTS idx_notif_send ON notifications(sender_id)zICREATE INDEX IF NOT EXISTS idx_notif_created ON notifications(created_at)a  
        CREATE TABLE IF NOT EXISTS player_ratings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_id INTEGER NOT NULL,
            docente_id INTEGER NOT NULL,
            rating INTEGER NOT NULL,
            notes TEXT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            FOREIGN KEY(player_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(docente_id) REFERENCES users(id) ON DELETE CASCADE
        )
        a  
        CREATE TABLE IF NOT EXISTS valoraciones_jugador (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            eval_id TEXT NOT NULL,
            player_id INTEGER NOT NULL,
            docente_id INTEGER NOT NULL,
            area TEXT NOT NULL,
            subescala TEXT NOT NULL,
            indicador TEXT NOT NULL,
            score INTEGER NOT NULL,
            observaciones TEXT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            FOREIGN KEY(player_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(docente_id) REFERENCES users(id) ON DELETE CASCADE
        )
        a  
        CREATE TABLE IF NOT EXISTS "formulario-registro" (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            pin TEXT NOT NULL UNIQUE,
            email TEXT,
            name TEXT,
            used INTEGER NOT NULL DEFAULT 0,
            expires_at TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime'))
        )
        zZCREATE INDEX IF NOT EXISTS idx_formreg_used_exp ON "formulario-registro"(used, expires_at)zAINSERT OR IGNORE INTO user_profile(user_id)  SELECT id FROM userszAINSERT OR IGNORE INTO user_sports(user_id)   SELECT id FROM userszAINSERT OR IGNORE INTO user_health(user_id)   SELECT id FROM userszAINSERT OR IGNORE INTO user_consent(user_id)  SELECT id FROM userszAINSERT OR IGNORE INTO user_guardians(user_id)SELECT id FROM userszAINSERT OR IGNORE INTO user_marketing(user_id)SELECT id FROM userszTINSERT OR IGNORE INTO user_finance(user_id, matricula_eur) SELECT id, 0.0 FROM users)r   cursorr}   r   commit)r   cr   
cols_userscols_ress        r>   init_dbr   y   s   	 @DKKM			*+			  	 %&II.H$I$R$R$TUqadU
Uz)IIDEj(IICD*$II?@			 
 
	 	
		  	 	
		  	 	
		 
 
	 	
		 	 		 	
		 	 		 	
		  	 	
		  	( #$)),M"N"W"W"YZQAaDZZx'IIKLII  F  G!IIEFX-IITU(IIbc!IIEF("IIFG("IIFG("IIFGH$IIKL8#IIefx'IIab!II[\			  	 	
		  	 	
		TU			WX			ef			  	" 	
		de			YZ			]^			  	 	
		  	" 	
		 
 
	 	
		no			UV			UV			UV			UV			UV			UV			hiA@ @" V^ [A@ @s,   AO'O+C*O'O"!I3O'
O''O0c                      t               5 } | j                  d      j                         }t        |d   xs d      cd d d        S # 1 sw Y   y xY w)Nz2SELECT COUNT(1) AS c FROM users WHERE role='admin'r   r   )r   r}   fetchoner;   )r   rows     r>   count_adminsr   ~  sF    	 "DllOPYY[3s8=q!" " "s   1AAc                 $    t        | d         dk(  S )Nri   rj   )rN   )rq   s    r>   is_adminr     s    r&z?g%%r@   r   c                    | j                  d|f       | j                  d|f       | j                  d|f       | j                  d|f       | j                  d|f       | j                  d|f       | j                  d|f       y )N6INSERT OR IGNORE INTO user_profile(user_id) VALUES (?)5INSERT OR IGNORE INTO user_sports(user_id) VALUES (?)z5INSERT OR IGNORE INTO user_health(user_id) VALUES (?)z6INSERT OR IGNORE INTO user_consent(user_id) VALUES (?)z8INSERT OR IGNORE INTO user_guardians(user_id) VALUES (?)z8INSERT OR IGNORE INTO user_marketing(user_id) VALUES (?)JINSERT OR IGNORE INTO user_finance(user_id, matricula_eur) VALUES (?, 0.0))r}   )r   r   s     r>   _upsert_satellites_emptyr     s{    LLIG:VLLH7*ULLH7*ULLIG:VLLKgZXLLKgZXLL]`g_ijr@   z/admin/users/deleteadmin_delete_user.next_urlc                   K   t        |       }|st        dd      S t        |      st        dd      t	        |      }|st        dd      t        |d	         t        |d	         k(  rt        d
d      t               5 }	 |j                  d       |j                  d||f       |j                  d|f       |j                  d|f       |j                  d|f       |j                          	 d d d        |r6t        |      }|j                  dk(  r|j                  dk(  rt        |d      S t        dd      S # t        $ r |j                           w xY w# 1 sw Y   lxY ww)N/login/  urlrI   rG   z&Solo un admin puede eliminar usuarios.rH     Usuario no encontrado.id  z$No puedes eliminar tu propia cuenta.BEGINADELETE FROM notifications WHERE sender_id = ? OR recipient_id = ?2DELETE FROM reservation_students WHERE user_id = ?z*DELETE FROM reservations WHERE user_id = ?DELETE FROM users WHERE id = ?rF   /admin)rV   r   r   r	   get_user_by_idr;   r   r}   r   r~   rollbackr   schemenetloc)r0   r   r   rq   targetr   ps          r>   r   r     sR     
g	BH#>>B<4\]]G$F4LMM
2d8}F4L))4Z[[	 
D	<< <<[^egn]op<<LwjY<<DwjQ<<87*E;;=
 X88r>ahh"n#cBBc::  ==?
 
s2   BE+EA.E4AE+EEE($E+r   c                     t               5 }|j                  d| f      }|j                         cd d d        S # 1 sw Y   y xY w)Na"  
            SELECT u.id,u.name,u.email,u.password_hash,u.created_at,u.role,u.first_name,u.last_name,
                   p.phone,p.avatar,p.city,p.dni,p.birth_date,
                   s.team,s.category,s.position,s.dominant_foot,s.strengths,s.weaknesses,s.injury_history,s.training_type,
                   h.physical_work,h.physical_work_details,h.smoking,h.alcohol,h.recovery,h.chest_pain,h.discomfort,
                   c.whatsapp_content,c.video_permission,c.privacy_acceptance,c.data_confirmation,c.agreement,
                   g.occupation,g.study_place,g.parent_name,g.parent_email,
                   m.found_us,m.enjoyment,m.nerves_confidence,m.additional_comments,
                   COALESCE(f.matricula_eur,0.0) AS matricula_eur,
                   CAST(ROUND(COALESCE(f.matricula_eur,0.0)*100) AS INTEGER) AS matricula_cents
            FROM users u
            LEFT JOIN user_profile p   ON p.user_id=u.id
            LEFT JOIN user_sports s    ON s.user_id=u.id
            LEFT JOIN user_health h    ON h.user_id=u.id
            LEFT JOIN user_consent c   ON c.user_id=u.id
            LEFT JOIN user_guardians g ON g.user_id=u.id
            LEFT JOIN user_marketing m ON m.user_id=u.id
            LEFT JOIN user_finance f   ON f.user_id=u.id
            WHERE LOWER(u.email)=LOWER(?)
        r   r}   r   )r   r   curs      r>   get_user_by_emailr     sC    	 Dll & X'( ||~+  	   #8Ac                     t               5 }|j                  d| f      }|j                         cd d d        S # 1 sw Y   y xY w)Na  
            SELECT u.id,u.name,u.email,u.password_hash,u.created_at,u.role,u.first_name,u.last_name,
                   p.phone,p.avatar,p.city,p.dni,p.birth_date,
                   s.team,s.category,s.position,s.dominant_foot,s.strengths,s.weaknesses,s.injury_history,s.training_type,
                   h.physical_work,h.physical_work_details,h.smoking,h.alcohol,h.recovery,h.chest_pain,h.discomfort,
                   c.whatsapp_content,c.video_permission,c.privacy_acceptance,c.data_confirmation,c.agreement,
                   g.occupation,g.study_place,g.parent_name,g.parent_email,
                   m.found_us,m.enjoyment,m.nerves_confidence,m.additional_comments,
                   COALESCE(f.matricula_eur,0.0) AS matricula_eur,
                   CAST(ROUND(COALESCE(f.matricula_eur,0.0)*100) AS INTEGER) AS matricula_cents
            FROM users u
            LEFT JOIN user_profile p   ON p.user_id=u.id
            LEFT JOIN user_sports s    ON s.user_id=u.id
            LEFT JOIN user_health h    ON h.user_id=u.id
            LEFT JOIN user_consent c   ON c.user_id=u.id
            LEFT JOIN user_guardians g ON g.user_id=u.id
            LEFT JOIN user_marketing m ON m.user_id=u.id
            LEFT JOIN user_finance f   ON f.user_id=u.id
            WHERE u.id=?
        r   r   r   r   s      r>   r   r     sC    	 Dll & Z'( ||~+  r   rC   passwordri   r   r   c                    t         j                  |      }t               5 }t        |d      }t        j
                  j                         j                         }	d|v rd|v r|j                  d| |||	|||f      }
n|j                  d| |||	|f      }
|
j                  }t        ||       |j                          d d d        y # 1 sw Y   y xY w)Nusersr   r   znINSERT INTO users (name, email, password_hash, created_at, role, first_name, last_name) VALUES (?,?,?,?,?,?,?)zSINSERT INTO users (name, email, password_hash, created_at, role) VALUES (?,?,?,?,?))pwd_contexthashr   r   dtr   utcnow	isoformatr}   	lastrowidr   r   )rC   r   r   ri   r   r   password_hashr   colsr6   r   uids               r>   create_userr     s    $$X.M	 DdG,[['')335
4K4$7,,)umZz9UC ,,eumZ>C mm s+!  s   BCCz/api/notifications/unreadc                    K   t        |       }|st        dd      t        t        |d               }d }|D cg c]
  } ||       c}S c c}w w)Nrg   rh   rH   r   c                 P    | j                         D ci c]  }|| |   
 c}S c c}w r   )keysr   ks     r>   to_dictz)api_notifications_unread.<locals>.to_dict  s#    QVVX6AqtG666s   #)rV   r	   !get_unread_notifications_for_userr;   )r0   rq   rowsr   r   s        r>   api_notifications_unreadr     sN     	g	B4DEE,SD];D6 $%1GAJ%%%s   9AA
Az./api/notifications/{notification_id}/mark-readnotification_idc           	      8  K   t        |       }|st        dd      | j                  j                  d      }t	        | |       t               5 }|j                  d|t        |d         f       |j                          d d d        ddiS # 1 sw Y   ddiS xY ww)	Nrg   rh   rH   r`   zx
            UPDATE notifications
            SET is_read = 1
            WHERE id = ? AND recipient_id = ?
            r   okT)	rV   r	   rc   rK   rP   r   r}   r;   r   )r0   r   rq   rD   r   s        r>   api_notifications_mark_readr      s     	g	B4DEEOO/E% 	 	D
 c"T(m,	
 		 $<	 $<s   AB1B?BB
Bz/api/reservations/{res_id}res_idc                 
  K   t        |       }|st        dd      t               5 }|j                  d|f      j	                         }d d d        st        dd      t        |      }t        |      S # 1 sw Y   /xY ww)Nrg   rh   rH   a  
            SELECT
                id,
                created_at,
                date,
                time,
                duration_minutes,
                price_cents,
                name,
                email,
                phone,
                notes,
                user_id,
                status,
                docente_id,
                paid
            FROM reservations
            WHERE id = ?
            r   zNo encontrada)content)rV   r	   r   r}   r   _reservation_row_to_dictr   )r0   r   rq   r   r   datas         r>   api_reservation_getr     s     	g	B4DEE	 Dll& I)
* (*+ 	0 ODD#C(D%%9 s   %B"A7	.B7B <Bz#/api/reservations/{res_id}/students)response_modelc                    K   t        |       }|st        dd      t               5 }t        j                  |_        |j                  d|f      j                         }|sQ|j                  d|f      j                         }|r.|d   r)|j                  d|d   f      j                         }|r|g}d d d        D cg c]  }t        |d         |d	   |d
   d }}|t        |      |dS # 1 sw Y   =xY wc c}w w)Nrg   rh   rH   z
            SELECT u.id, u.name, u.email
            FROM reservation_students rs
            JOIN users u ON u.id = rs.user_id
            WHERE rs.reservation_id = ?
            ORDER BY COALESCE(u.name, u.email)
        z-SELECT user_id FROM reservations WHERE id = ?r   .SELECT id, name, email FROM users WHERE id = ?r   rC   r   r   rC   r   )reservation_idcountstudents)rV   r	   r   rm   rn   r|   r}   r   r   r;   len)r0   r   rq   r   r   r   ur   s           r>   api_reservation_studentsr   9  s    	g	B4DEE	 D";;||  Y !
 	 LviXaacAQy\LL!QTUV_T`Sbclln3D W[[QRs1T7|QvY7L[H[$s8}(SS!  \s)   %C>B
C-1C>=C9C>-C62C>z /api/reservations/list/paginatedr   u   P�gina (1-based))gedescription
   d   u   Tama�o de p�gina)r   ler   pagesizer   r   c                    	 | dz
  |z  }dg}g }|"|j                  d       |j                  |       |"|j                  d       |j                  |       |"|j                  d       |j                  |       ddj                  |      z   }t               5 }	|	j                  d| t	        |            j                         }
t        |
d	   xs d
      }|	j                  d| dt	        |      ||fz         j                         }d d d        D cg c]  }t        |       }}r"t        dt        j                  ||z              nd}|| |||ddS # 1 sw Y   QxY wc c}w # t        $ r  t        $ r t        dd      w xY w)Nr   z1=1user_id = ?docente_id = ?z
status = ?zWHERE  AND z'SELECT COUNT(*) AS c FROM reservations r   r   a  
                SELECT
                    id,
                    created_at,
                    date,
                    time,
                    duration_minutes,
                    price_cents,
                    name,
                    email,
                    phone,
                    notes,
                    user_id,
                    status,
                    docente_id
                FROM reservations
                zn
                ORDER BY datetime(created_at) DESC, id DESC
                LIMIT ? OFFSET ?
                )r   r   totalpages)itemsmetai  zError interno listando reservasrH   )appendjoinr   r}   tupler   r;   r   r   maxmathceilr	   r~   )r   r   r   r   r   offsetwhere_clausesparams	where_sqlr   	total_rowr   r   r   r   r   s                   r>   reservations_paginatedr  P  s   9W(d"  /MM'"!  !12MM*%  .MM&!w||M::	W 	9)Ef hj  	#+!,E<<   !( fv.+, hj- 	: 7;;)!,;;38Atyy./a	
 	
?	 	: <   W4UVVWs1   BE+ A1E
E+ E&(1E+ E#E+ + Fc                      t               5 } | j                  dt        j                  j	                         j                         f       | j                          d d d        y # 1 sw Y   y xY w)NzADELETE FROM "formulario-registro" WHERE used=0 AND expires_at < ?)r   r}   _dtr   r   r   r   r   s    r>   purge_expired_temp_regsr    sR    	 DX[^[g[g[n[n[p[z[z[|Z~  s   AA""A+lengthc                 `   t         j                  	 dj                  fdt        |       D              }t	               5 }|j                  d|t        j                  j                         j                         f      j                         }|s|cd d d        S 	 d d d        # 1 sw Y   
xY w)NrF   c              3   H   K   | ]  }t        j                          y wr   )_secretschoice).0_digitss     r>   	<genexpr>z&generate_unique_pin.<locals>.<genexpr>  s     E!hoof-Es   "zQSELECT 1 FROM "formulario-registro" WHERE pin = ? AND used = 0 AND expires_at > ?)stringr  r  ranger   r}   r  r   r   r   r   )r  pinr   r   r  s       @r>   generate_unique_pinr    s    ]]F
ggEuV}EEW 	,,ccll))+5578 hj  	 	
 	 	 	s    AB$$B-c                 f    | j                   j                  d      }|sy t        t        |            S )Nr   )r7   rK   r   r;   )r0   r   s     r>   rV   rV     s+    oo!!),G#g,''r@   c                      t               5 } | j                  d      }|j                         cd d d        S # 1 sw Y   y xY w)NzCSELECT id, name, email FROM users WHERE role='alumno' ORDER BY namer   r}   r   )r   r   s     r>   get_studentsr     s6    	 Dll`a||~  s   !6?c                     t               5 }|j                  d| f      }|j                         cd d d        S # 1 sw Y   y xY w)Na  
            SELECT n.id, n.created_at, n.title, n.body,
                   s.name AS sender_name, n.sender_id, n.recipient_id,
                   n.reservation_id
            FROM notifications n
            LEFT JOIN users s ON s.id = n.sender_id
            WHERE n.recipient_id = ?
              AND COALESCE(n.deleted_by_recipient, 0) = 0
            ORDER BY n.created_at DESC
        r  r   s      r>   get_notifications_for_userr"    sC    	 Dll 	 Z	 ||~  r   	sender_idc                     t               5 }|j                  d| f      }|j                         cd d d        S # 1 sw Y   y xY w)Na  
            SELECT n.id, n.created_at, n.title, n.body,
                   r.name AS recipient_name, n.sender_id, n.recipient_id,
                   n.reservation_id
            FROM notifications n
            LEFT JOIN users r ON r.id = n.recipient_id
            WHERE n.sender_id = ?
              AND COALESCE(n.deleted_by_sender, 0) = 0
            ORDER BY n.created_at DESC
        r  )r#  r   r   s      r>   get_notifications_sent_by_userr%    sC    	 Dll 	 \	 ||~  r   recipient_idc                     t               5 }|j                  d| |f      }|j                         cd d d        S # 1 sw Y   y xY w)Na  
            SELECT n.id, n.created_at, n.title, n.body,
                   r.name AS recipient_name, n.sender_id, n.recipient_id,
                   n.reservation_id
            FROM notifications n
            LEFT JOIN users r ON r.id = n.recipient_id
            WHERE n.sender_id = ?
              AND n.recipient_id = ?
              AND COALESCE(n.deleted_by_sender, 0) = 0
              AND COALESCE(n.deleted_by_recipient, 0) = 0
            ORDER BY n.created_at DESC
        r  )r#  r&  r   r   s       r>   &get_notifications_sent_by_user_to_userr(    sF    	 Dll  &( ||~  s	   $9Az/notificaciones/eliminardelete_notificationtarget_recipient_idr5   c                 `  K   t        |       }|st        dd      S t        | |       t               5 }|j	                  d|f      j                         }|st        dd      t        |d         }|d	   t        |d	         nd }	t        |d
         }
||
k(  r$|j	                  d|f       |j                          n|	M||	k(  rH|"t        |      |
k(  r|j	                  d|f       n|j	                  d|f       |j                          nH|d   dv r4|2t        |      |
k(  r$|j	                  d|f       |j                          nt        dd      d d d        |r6t        |      }|j                  dk(  r|j                  dk(  rt        |d      S t        dd      S # 1 sw Y   NxY ww)Nr   r   r   zBSELECT id, sender_id, recipient_id FROM notifications WHERE id = ?r   u   Notificaci�n no encontrada.rH   r   r#  r&  z>UPDATE notifications SET deleted_by_recipient = 1 WHERE id = ?zUUPDATE notifications SET deleted_by_sender = 1, deleted_by_recipient = 1 WHERE id = ?z;UPDATE notifications SET deleted_by_sender = 1 WHERE id = ?ri   rs   rG   u'   No puedes eliminar esta notificaci�n.rF   /notificaciones)rV   r   rP   r   r}   r   r	   r;   r   r   r   r   )r0   r   r   r*  r5   rq   r   r   me_idr#  r&  parseds               r>   r)  r)    s     
g	BH#>>$	 cDllP
 (* 	 C8WXXBtH-0-=-ICK()t	3~./L LLY\k[mnKKM"u	'9".37J3K|3[t  xG  wI  JZ]l\noKKMZ//4G4SX[\oXp  uA  YALLY\k[mnKKMC8abb/c0 (#==B6==B#6#cBB 1sCC9c cs   1F.D"F"AF."F+'F./c                 R   K   t        |       }t        j                  d| |d      S w)Nz
index.htmlrS   rV   rW   rX   r0   rq   s     r>   homer3    )     	g	B%%lQS4TUU   %'z/aboutc                 R   K   t        |       }t        j                  d| |d      S w)Nz
about.htmlrS   r1  r2  s     r>   aboutr7  
  r4  r5  z
/ticketingc                 N   K   t         j                  d| t        |       d      S w)Nzticketing.htmlrS   )rW   rX   rV   r=   s    r>   	ticketingr9    s&     %%&6GUabiUj8klls   #%r   login)rC   rQ   c                 <   K   t         j                  d| d d      S w)N
login.htmlr0   error)rW   rX   r=   s    r>   	login_getr?    s     %%lRV4WXXs   
login_postc                   K   |j                         j                         }t        |      }|rt        j	                  ||d         st
        j                  d| dd      S t        |        t        |d         | j                  d<   |d   | j                  d<   |d   dk(  s|d	k(  rt        | j                  d      d
      S |d   dk(  rt        | j                  d      d
      S t        | j                  d      d
      S w)Nr   r<  u   Credenciales inv�lidasr=  r   r   ri   rj   admin@admin.esr   r   rt   	dashboard)stripro   r   r   verifyrW   rX   r?   r;   r7   r   url_for)r0   r   r   
email_normrT   s        r>   r@  r@    s     $$&JZ(D{))(D4IJ)),GVp8qrr7!$T$ZGOOI"6lGOOFF|w*0@"@GOOG$<#NN	f	"GOOI$>CPPGOOK$@cRRs   C?Dz/docentert   c                    K   t        |       }|st        | j                  d      d      S g }	 |d   dv r
t               }t
        j                  d| ||d      S # t        $ r g }Y 'w xY ww)Nr:  r   r   ri   rs   zdocente.html)r0   rT   r   )rV   r   rF  r   r~   rW   rX   )r0   rq   r   s      r>   docente_dashboardrI  '  s     	g	BGOOG$<#NNHf:--#~H %% 	
   s(   ,A+A  A+A(%A+'A((A+z/valoracionesvaloracionesc                 .  K   t        |       }|st        | j                  d      d      S d|j                         v rt	        |d         j                         nd}|dvrt        | j                  d      d      S t        j                  d| |d	      S w)
Nr:  r   r   ri   rF   rs   rC  zvaloraciones.htmlrS   )rV   r   rF  r   rN   ro   rW   rX   rp   s      r>   valoraciones_pagerL  ;  s     	g	BGOOG$<#NN&,	&93r&z?  "rD''GOOK$@cRR%%&9wXZ;[\\s   BBz/api/users/{user_id}api_user_detail)rQ   rC   c                   K   t        |      }|st        dd      S |d   dvrt        dd      t               5 }|j	                  d	| f      j                         }d d d        st        d
d      t        |      S # 1 sw Y   #xY ww)Nr   r   r   ri   rs   rG   ru   rH   a  
            SELECT
                u.id,u.name,u.email,u.created_at,u.role,
                p.phone,p.avatar,p.dni,p.birth_date,p.city,
                s.dominant_foot,s.team,s.category,s.position,s.injury_history,s.strengths,s.weaknesses,s.training_type,
                h.physical_work,h.physical_work_details,h.smoking,h.alcohol,h.recovery,h.chest_pain,h.discomfort,
                c.whatsapp_content,c.video_permission,c.privacy_acceptance,c.data_confirmation,
                COALESCE(f.matricula_eur,0.0) AS matricula_eur,
                CAST(ROUND(COALESCE(f.matricula_eur,0.0)*100) AS INTEGER) AS matricula_cents,
                c.agreement,
                g.occupation,g.study_place,g.parent_name,g.parent_email,
                m.found_us,m.enjoyment,m.nerves_confidence,m.additional_comments
            FROM users u
            LEFT JOIN user_profile p   ON p.user_id=u.id
            LEFT JOIN user_sports s    ON s.user_id=u.id
            LEFT JOIN user_health h    ON h.user_id=u.id
            LEFT JOIN user_consent c   ON c.user_id=u.id
            LEFT JOIN user_guardians g ON g.user_id=u.id
            LEFT JOIN user_marketing m ON m.user_id=u.id
            LEFT JOIN user_finance f   ON f.user_id=u.id
            WHERE u.id = ?
        r   Usuario no encontrado)rV   r   r	   r   r}   r   dict)r   r0   rq   r   r   s        r>   rM  rM  E  s     	g	BH#>>	&z--4KLL	 $Dll * Z+* "+ 	$. 4KLL93$ $s   9B"A?"B?BBz	/registerregisterc                 h   K   t        |       }|xs dddd}t        j                  d| d |d      S w)NrF   rC   r   r   register.htmlr0   r>  rT   r1  )r0   rq   ctx_users      r>   register_getrW  g  s=     	g	B;b2;H%%o7UYck7lmmrZ   register_postc           	      \  K   |j                         j                         }t        |      r,t        j	                  d| d|j                         |ddd      S t        |      dk  r,t        j	                  d| d|j                         |ddd      S t        |j                         ||d	       t        |      }t        |d
         | j                  d<   |d   | j                  d<   |d   dk(  s|dk(  rt        | j                  d      d      S t        | j                  d      d      S w)NrT  zEse email ya existerF   rS  rU     u(   La contrase�a debe tener 6+ caracteresalumno)ri   r   r   ri   rj   rB  r   r   rC  )rD  ro   r   rW   rX   r   r   r;   r7   r   rF  )r0   rC   r   r   rG  rT   s         r>   rX  rX  m  s:    $$&J$))*?RVR\R\R^is  A  JB  C
 	
 8}q))*Tgkgqgqgs  I  TV  _W  X
 	
 

j(BZ(D!$T$ZGOOI"6lGOOFF|w*0@"@GOOG$<#NNGOOK$@cRRs   D*D,z/academy-registeracademy_register_getc                 R   K   t        |       }t        j                  d| |d      S w)Nzacademy-register.htmlrS   r1  r2  s     r>   r\  r\    s.     	g	B%%R( r5  uploadsacademyacademy_register_postc                    K   t        d       t        d       t        d       | j                          d {   }t        dt        |j                                       t        dd|v         |j	                  d      xs dj                         j                         }|j	                  d      xs dj                         }|j	                  d	      xs d}|j	                  d
      xs dj                         }|j	                  d      xs dj                         }t        d| d| d| d|        |st        dd      |rt        |      dk  rt        dd      |rt        |      dk  rt        dd      |rt        |      dk  rt        dd      |rt        |      dk  rt        dd      t        |      }|rt        dd      d }d }	|j	                  d      }
t        dt        |
              t        |
d      xr t        |
d      }t        d|        |rt        d|
j                          |r|
j                  r	 |
j                          d {   }t        d |rt        |      nd!        |rIt        j                  j!                  |
j                        d"   j                         xs d#}	t        d$       t)        |j                         ||d&||'       t        |      }t+        |d(         }d }|r|	r	 t,        d)z  d*z  }|j/                  d+d+,       d-| |	 }||z  }t        d.|        t1        |d/      5 }|j3                  |       d d d        t        d0|        d1| }t        d2|        i  |j5                         D ])  \  }}t7        |t8              r|d4vst;        |       |<   +  fd5} |d6d7       |d8       |d9d:       |d;      |xs	  |dd<      d=} |d>d?d@       |dA       |dBdCdD       |dE       |dF       |dG       |dH       |dIdJdK      dL} |dM       |dN       |dO       |dP       |dQ       |dR       |dS      dT} |dU       |dV       |dW       |dX       |dY      dZ} |d[       |d\d]       |d^d_       |d`da      db} |dcdd       |de       |dfdg       |dhdi      dj}t=               5 }t?        ||       tA        dk |jC                         D              r\dljE                  |j                         D cg c]
  }| dm| dn c}      }|jG                  do| dpg |jC                         |       tA        dq |jC                         D              r\dljE                  |j                         D cg c]
  }| dm| dn c}      }|jG                  dr| dpg |jC                         |       tA        ds |jC                         D              r\dljE                  |j                         D cg c]
  }| dm| dn c}      }|jG                  dt| dpg |jC                         |       tA        du |jC                         D              r\dljE                  |j                         D cg c]
  }| dm| dn c}      }|jG                  dv| dpg |jC                         |       tA        dw |jC                         D              r\dljE                  |j                         D cg c]
  }| dm| dn c}      }|jG                  dx| dpg |jC                         |       tA        dy |jC                         D              r\dljE                  |j                         D cg c]
  }| dm| dn c}      }|jG                  dz| dpg |jC                         |       |jI                          d d d        t        d       t        d{       t        d       tK        d+d|d}      S 7 7 # t"        $ r-}t        d%|        d!d l}|j'                          Y d }~d }~ww xY w# 1 sw Y   xY w# t"        $ r-}t        d3|        d!d l}|j'                          Y d }~d }~ww xY wc c}w c c}w c c}w c c}w c c}w c c}w # 1 sw Y   xY ww)~NzP================================================================================z,DEBUG: academy_register_post function calledzDEBUG: Form keys: zDEBUG: Form has playerPhoto: playerPhotor   rF   rC   r   	firstNamelastNamezDEBUG: email=z, name=z, first_name=z, last_name=r   zEl email es obligatorio.rH      zIndica tu nombre.zIndica tus apellidos.u(   Indica tu nombre (mínimo 2 caracteres).rZ  u0   La contraseña debe tener al menos 6 caracteres.  u3   Ya existe un usuario con ese email. Inicia sesión.zDEBUG: player_photo type: filenamereadz"DEBUG: is_upload (hasattr check): zDEBUG: player_photo.filename: zDEBUG: content length: r   r   .jpgz<DEBUG: Photo stored in memory, will save after user creationzERROR: Failed to read photo: r[  )ri   r   r   r   rB   r^  Tparentsexist_okuser_zDEBUG: Saving photo to: wbz#DEBUG: Photo saved successfully to z/static/uploads/zDEBUG: avatar_path: z%ERROR: Failed to save photo to disk: NrF   nullc                  F    | D ]  }j                  |      }|dvs|c S  y )Nro  )rK   )r   r   valr   s      r>   pickz#academy_register_post.<locals>.pick  s2     	A((1+C,,
	 r@   r   whatsappdni	birthDate
birth_datecityavatar)r   ru  rw  rx  ry  currentTeamteamclubcategorycurrentPositionpreferredPositionpositiondominantFoot	strengths
weaknessestrainingTypeinjuriesinjuryHistoryinjury_history)r{  r}  r  dominant_footr  r  training_typer  physicalWorkphysicalWorkDetailssmokingalcoholrecovery	chestPain
discomfort)physical_workphysical_work_detailsr  r  r  
chest_painr  whatsappContentvideoPermissionprivacyAcceptancedataConfirmation	agreement)whatsapp_contentvideo_permissionprivacy_acceptancedata_confirmationr  
occupation
studyPlacestudy_place
parentNameparent_nameparentEmailparent_email)r  r  r  r  foundUsfound_us	enjoymentnervesConfidencenerves_confidenceadditionalCommentsadditional_comments)r  r  r  r  c              3   $   K   | ]  }|d u 
 y wr   r   r  vs     r>   r  z(academy_register_post.<locals>.<genexpr>       <q}<   , z=COALESCE(?, r   zUPDATE user_profile SET z WHERE user_id=?c              3   $   K   | ]  }|d u 
 y wr   r   r  s     r>   r  z(academy_register_post.<locals>.<genexpr>       ;q};r  zUPDATE user_sports SET c              3   $   K   | ]  }|d u 
 y wr   r   r  s     r>   r  z(academy_register_post.<locals>.<genexpr>  r  r  zUPDATE user_health SET c              3   $   K   | ]  }|d u 
 y wr   r   r  s     r>   r  z(academy_register_post.<locals>.<genexpr>  r  r  zUPDATE user_consent SET c              3   $   K   | ]  }|d u 
 y wr   r   r  s     r>   r  z(academy_register_post.<locals>.<genexpr>       >q}>r  zUPDATE user_guardians SET c              3   $   K   | ]  }|d u 
 y wr   r   r  s     r>   r  z(academy_register_post.<locals>.<genexpr>  r  r  zUPDATE user_marketing SET zHDEBUG: About to return JSON response: {'ok': True, 'redirect': '/login'}r   )r   redirect)&printformlistr   rK   rD  ro   r	   r   r   typehasattrrg  rh  ospathsplitextr~   	traceback	print_excr   r;   BASE_DIRmkdiropenwriter   rl   r   rN   r   r   anyvaluesr  r}   r   r   )!r0   r  r   rC   r   r   r   existingphoto_content	photo_extplayer_photo	is_uploader  rT   r   avatar_pathuploads_dirrg  	file_pathfr   r  rs  profile_valssports_valshealth_valsconsent_valsguardians_valsmarketing_valsr   setsr   s!                                   @r>   r`  r`    s    	&M	
89	&MD	tDIIK01
23	)-4*?)@
ABXXg$"++-335EXXf$"++-D$*H((;'-2446J*%+224I	M%v]:,lS\R]
^_4NOOZ1,4GHHI*4KLL3t9q=4^__s8}q(4fgg 'H4ijjMI88M*L	&tL'9&:
;<j1SglF6SI	.yk
:;.|/D/D.EFG\**		""."3"3"55M+-C,>UV+WXYGG,,\-B-BCAFLLNXRX	TV 

eXH_hiU#D$t*oG K	""X-	9KdT:wi	{3H#h.I,YK89i& '!&'7	{CD,XJ7K(67 D

 1a$&&!fDG	
 gz*E{;5V>mX!>L ]FF3$*,?Ln-+&<(n-z?<LM	K n-!%&;!<	?	?$;'<(K !!23 !23"#67!"45+&L <(L-8L-8]N;	N J/+&!"46IJ#$8:OP	N 
 D w/<l&9&9&;<<99|?P?P?RS!=15STDLL3D69IJLmlNaNaNcLmelLmn;k&8&8&:;;99{?O?O?QR!=15RSDLL24&8HIKk[M_M_MaKkcjKkl;k&8&8&:;;99{?O?O?QR!=15RSDLL24&8HIKk[M_M_MaKkcjKkl<l&9&9&;<<99|?P?P?RS!=15STDLL3D69IJLmlNaNaNcLmelLmn>n&;&;&=>>99~?R?R?TU!=15UVDLL5dV;KLNqP^PePePgNqipNqr>n&;&;&=>>99~?R?R?TU!=15UVDLL5dV;KLNqP^PePePgNqipNqr)* 
&M	
TU	&Mt:;;i  B 6
  	"1!56!!	"&' '
  	"9!=>!!	"B T S S T V V% s  5c`$Hc`* %`'&A*`* >cAa0 a#&)a0 -c=D+c(A
c2b)
A-c.b.
=A-c*b3
9A-c&b8
5A-c"b=
1A-cc
-A c-8c'`* *	a 3"aca  c#a-(a0 0	b&9"b!c!b&&c)cccz/info-usuariosc                    K   t        |       }|st        | j                  d      d      S t        j	                  d| |d      S w)Nr:  r   r   zinfo-usuarios.htmlrS   )rV   r   rF  rW   rX   r2  s     r>   info_usuarior  *  sJ     	g	BGOOG$<#NN%%&:=  s   AAz/gestion-usuariosgestion_usuariosc                 T  K   t        |       }|st        | j                  d      d      S t        |t        j
                        rt        |      }t        t        |t              r|j                  d      n|d   xs d      j                         }|dk7  rt        | j                  d      d      S t               5 }|j                  d      }|j                         D cg c]  }t        |       }}d d d        t        j                  d	| |d
      S c c}w # 1 sw Y   (xY ww)Nr:  r   r   ri   rF   rj   rC  aX  
            SELECT
                u.id,
                u.name,
                u.email,
                u.role,
                u.created_at,
                LOWER(COALESCE(s.category, '')) AS category
            FROM users u
            LEFT JOIN user_sports s ON s.user_id = u.id
            ORDER BY u.created_at DESC, u.id DESC
        zgestion-usuarios.html)r0   rT   r   )rV   r   rF  rl   rm   rn   rP  rN   rK   ro   r   r}   r   rW   rX   )r0   rq   ri   r   r   r   r   s          r>   r  r  4  s    	g	BGOOG$<#NN"gkk""X*R"6vBvJM2NTTVDwGOOK$@cRR	 2Dll   #&,,.1Qa112 %%	
  22 2s0   B;D(=$D!D3D5"D(DD%!D(z
/dashboardrC  c                 J  K   t        |       }|st        | j                  d      d      S t        |t        j
                        rt        |d         nt        |j                  d            }t        |t        j
                        r|d   n|j                  d      xs dj                         }t               5 }|j                  d|||f      j                         }t        |r
|d   |d   nd	      }d d d        t        j                  d
| |d      S # 1 sw Y   #xY ww)Nr:  r   r   r   r   rF   a  
            SELECT COUNT(DISTINCT r.id) AS c
            FROM reservations r
            LEFT JOIN reservation_students rs ON rs.reservation_id = r.id
            WHERE rs.user_id = ?
               OR r.user_id = ?
               OR LOWER(COALESCE(r.email,'')) = LOWER(?)
        r   r   zdashboard.htmlr0   rT   reservation_count)rV   r   rF  rl   rm   rn   r;   rK   rD  r   r}   r   rW   rX   r0   rq   r-  me_emailr   r   r  s          r>   rC  rC  Y  s    	g	BGOOG$<#NN'GKK8C4Mc"&&,>OE)"gkk:7w@USU\\^H	 	QDll  UH%' (0xz 	  CCH4HCaP	Q %%!2	
 	Q 	Qs   B8D#:;D5"D#D D#r   c                 p   K   t        |       }|st        dd      S t        j                  d| |d      S w)Nr   r   r   z
admin.htmlrS   )rV   r   rW   rX   rS   s     r>   rj   rj   s  s9      DH#>>%%lQU4VWWs   46r,  notificacionesc                 X  K   t        |       }|st        dd      S t        |d         }d }|0	 t        |      }|r"|d   dv rt	        |      }|rt        |d         }t        |      }g }|d   dv r2|rt        t        |d         |      }nt        t        |d               }g }|d   dv r
t               }d }|xs g D 	cg c]
  }	 ||	       }
}	g }i }g }|rst               5 }|j                  d|f      j                         }d d d        |xs g D cg c]
  } ||       }}|xs g D ci c]  }t        |d          ||       }}t        j                  d	| ||rt        |      nd ||||
|||d

      S # t        $ r d }Y Pw xY wc c}	w # 1 sw Y   xY wc c}w c c}w w)Nr   r   r   r   ri   rs   c                     	 | j                         D ci c]  }|| |   
 c}S c c}w # t        $ r t        |       cY S w xY wr   )r   r~   rP  r   s     r>   _row_to_dictz$notificaciones.<locals>._row_to_dict  sA    	%&VVX.AqtG... 	7N	s   ) $) ) A A z
                SELECT id, user_id, date, time, duration_minutes, status, created_at, name, email, phone, notes, price_cents
                FROM reservations
                WHERE user_id = ?
                ORDER BY date ASC, time ASC
            znotificaciones.html)
r0   rT   	view_usernotificationssent_notificationsr   notifications_jsonappointmentsappointments_jsonappointments_map)rV   r   r;   r~   r   r"  r(  r%  r   r   r}   r   rW   rX   rP  )r0   r   rq   target_user_idr  inboxsentr   r  nr  r  r  r  r   as                   r>   r  r    s    	g	BH#>>D]NI	'lG r&z%99&w/I!$Yt_!5&~6ED	&z))9#bh-XD1#bh-@DH	&z))>
 5:KRAa,q/AALW 	/<< )
 !"$
 %-HJ 	/ 8D7IrK\!_KKDPDVTVXaC$L,q/9XX%%,5i4""& "4(!2 0	
 G  	G	* B
	/ 	/ LXsd   -F*E= BF*FF*&"FF*F '
F*1F%0F*=FF*FF*FF*z/api/expenses	date_fromdate_toc                   K   t        |        dt        fd}g }g }|r* ||       |j                  d       |j                  |       |r* ||       |j                  d       |j                  |       |+|j                  d       |j                  t        |             d}|r|ddj	                  |      z   z  }|d	z  }t               5 }t        j                  |_        |j                  |t        |            j                         }	g }
|	D ]M  }|
j                  |d
   |d   |d   |d   |d   |d   |d   t        |d   xs d      |d   |d   |d   d       O |
cd d d        S # 1 sw Y   y xY ww)Nsc                 n    	 t        j                  | d       y # t        $ r t        dd|  d      w xY w)N%Y-%m-%dr   u   Fecha inv�lida: z (YYYY-MM-DD)rH   r   strptimer~   r	   r  s    r>   _ok_datez#api_expenses_list.<locals>._ok_date  s@    	_a, 	_C:LQC}8]^^	_    4zjg.date >= ?zjg.date <= ?zjg.user_id = ?a  
        SELECT
            jg.id,
            jg.created_at,
            jg.date,
            jg.user_id,
            jg.reservation_id,
            jg.amount_cents,
            jg.category,
            jg.concept,
            jg.notes,
            u.name  AS user_name,
            u.email AS user_email
        FROM jugadores_gastos jg
        LEFT JOIN users u ON u.id = jg.user_id
     WHERE r   z  ORDER BY jg.date ASC, jg.id ASCr   r6   dater   	user_name
user_emailr   amount_centsr   r}  conceptr   )r   r6   r   r   r  r  r   r  r}  r  r   )rU   rN   r  r;   r  r   rm   rn   r|   r}   r  r   )r0   r  r  r   r  wherer	  sqlr   r   outr   s               r>   api_expenses_listr    s     '_C _ EFU\\.96==;S5<<7w9O%&c'l(CC  y7<<...--C	 D";;||Cv/88: 	AJJgo&	Y<{^o"#$4"5 #An$5$: ;jMY<7 	 %  s   C	E5BE)
E5)E2.E5c                       e Zd ZU eed<    ed      Zeed<   dZe	e   ed<   dZ
e	e   ed<   dZe	e   ed<   dZe	e   ed	<   dZe	e   ed
<   y)	ExpenseInr   r   r   r  Nr}  r  r   r   r   )__name__
__module____qualname__rN   __annotations__r   r  r;   r}  r   r  r   r   r   r   r@   r>   r
  r
    s[    
IL##"Hhsm"!GXc]!E8C=!GXc]!$(NHSM(r@   r
  payloadc                   K   t        |        	 t        j                  |j                  d       t        j                         j                  d      }t               5 }|j                  d||j                  j                         |j                  t        |j                        nd |j                  rt        |j                        nd t        |j                  xs d      |j                  xs dj                         xs d |j                   xs dj                         xs d |j"                  xs dj                         xs d f      }|j$                  }d d d        d	d
S # t        $ r t        dd      w xY w# 1 sw Y   (xY ww)Nr  r   u   Fecha inválida (YYYY-MM-DD).rH   %Y-%m-%d %H:%M:%Sz
            INSERT INTO jugadores_gastos (created_at, date, user_id, reservation_id, amount_cents, category, concept, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        r   rF   Tr   r   )rU   r   r  r   r~   r	   nowstrftimer   r}   rD  r   r;   r   r  r}  r  r   r   )r0   r  r6   r   r   gids         r>   api_expenses_creater     sT    'U',,
3 (()<=J	 Dll  LL $+OO$?C T+2+A+AC&&'t$$)*#**,4__"))+3t]] b'')1T	
 mm c""%  U4STTU s4   F E! -FC8E;F!E88F;F Fz/api/expenses/{gid}r  c                    K   t        |        t               5 }|j                  d|f       d d d        ddiS # 1 sw Y   ddiS xY ww)Nz)DELETE FROM jugadores_gastos WHERE id = ?r   T)rU   r   r}   )r0   r  r   s      r>   api_expenses_deleter  9  sL     '	 JD@3&IJ$<J$<s   A8AA
Acreate_notificationr   bodyr   c           
      0  K   t        |       }|st        dd      S |d   dvrt        dd      t        | |       |xs d	j	                         }|xs d	j	                         }|r|st        d
d      	 t        |      }d }|t        |      j	                         d	k7  rr	 t        |      }t               5 }	|	j                  d|f      j                         }
|
st        dd      t        |
d   xs d      |k7  rt        d
d      	 d d d        t               5 }	|	j                  dt        j                  j                         j                         t        |d         ||||f       |	j                          d d d        |r6t!        |      }|j"                  d	k(  r|j$                  d	k(  rt        |d      S t        dd      S # t        $ r t        d
d      w xY w# t        $ r t        d
d      w xY w# 1 sw Y   xY w# 1 sw Y   xY ww)Nr   r   r   ri   rs   rG   z3Solo docentes o admin pueden enviar notificaciones.rH   rF   r   u$   T�tulo y mensaje son obligatorios.u   Destinatario inv�lido.u   Reserva inv�lida.z1SELECT id, user_id FROM reservations WHERE id = ?r   Reserva no encontrada.r   r   z/La reserva no pertenece al alumno seleccionado.z
            INSERT INTO notifications (created_at, sender_id, recipient_id, title, body, is_read, reservation_id)
            VALUES (?, ?, ?, ?, ?, 0, ?)
            r   r,  )rV   r   r	   rP   rD  r;   r~   rN   r   r}   r   r   r   r   r   r   r   r   r   )r0   r&  r   r  r   r   r5   rq   res_id_to_saver   resr.  s               r>   r  r  A  s.     
g	BH#>>	&z--4ijj$[b!EJBD4Z[[P<( N!c.&9&?&?&AR&G	O 0N W 	o,,C! hj  #<TUU3y>&Q'<7#<mnn 8	o 
 D [[!++-s2d8}lESWYgh	
 	 (#==B6==B#6#cBB 1sCC=  P4NOOP  	OC8MNN	O	o 	o si   A4H7G
  H#G$ .
H8AG>HAH
=AH
G!!H$G;;H>HH
HHz/api/teachersapi_teachersc                    K   t        |       }|st        dd      S t               5 }|j                  d      }|j	                         D cg c]  }t        |       }}d d d        |S c c}w # 1 sw Y   S xY ww)Nr   r   r   z\SELECT id, name, email, created_at, role FROM users WHERE role = 'docente' ORDER BY name ASC)rV   r   r   r}   r   rP  )r0   rT   r   r   r   r   s         r>   r   r   w  sy      DH#>>	 1Dllj
 "%0AQ00	1
 K 1	1
 Ks.   %A;$A.A)A.
A;)A..A83A;>   rj   r[  rt   >   seniorcadetesjuvenilalevinesbenjamin
infantilesprebenjaminc                   "    e Zd ZU dZee   ed<   yCategoryUpdateNr}  r  r  r  r}  r   rN   r  r   r@   r>   r+  r+        "Hhsm"r@   r+  c                       e Zd ZU eed<   y)
RoleUpdateri   N)r  r  r  rN   r  r   r@   r>   r/  r/    s    
Ir@   r/  c                     t               5 }|j                  d|| f       |j                          d d d        y # 1 sw Y   y xY w)N&UPDATE users SET role = ? WHERE id = ?)r   r}   r   )r   ri   r   s      r>   set_user_roler2    s:    	 D=gO  s	   %9Ac                   "    e Zd ZU dZee   ed<   yr*  r,  r   r@   r>   r+  r+    r-  r@   z/api/users/{user_id}/categoryapi_users_set_categoryc                   K   t        |       t        |       }|st        dd      |j                  xs dj	                         j                         }|r|t        vrt        dd      t               5 }|j                  d| f       |j                  d|r|nd | f       |j                          d d d        d	| |d
S # 1 sw Y   xY ww)Nr   rO  rH   rF   r   u   Categoría no válidar   z5UPDATE user_sports SET category = ? WHERE user_id = ?T)r   r   r}  )
rU   r   r	   r}  rD  ro   ALLOWED_CATEGORIESr   r}   r   )r   r  r0   r   catr   s         r>   r4  r4    s     'G$F4KLL!r
(
(
*
0
0
2C
s,,4KLL	 DLwjYLVYs_celNmn 7<<	 s   A3C5<B?1C?CCz
/api/users	api_usersrolesc                 d  K   t        |       }|st        dd      S g }|rL|j                  d      D cg c]1  }|j                         s|j                         j	                         3 }}n!|r|j                         j	                         g}d}g }|r4dj                  dt        |      z        }d| d}|j                  |       t               5 }	|	j                  d	| d
|      }
|
j                         D cg c]  }t        |       }}d d d        |S c c}w c c}w # 1 sw Y   S xY ww)Nr   r   r   ,rF   ?zWHERE lower(u.role) IN (r   a  
            SELECT
                u.id,u.name,u.email,u.created_at,u.role,u.first_name,u.last_name,
                p.phone,p.avatar,
                s.team,s.category,s.position,s.injury_history,
                p.dni,p.birth_date,p.city,
                s.dominant_foot,s.strengths,s.weaknesses,s.training_type,
                h.physical_work,h.physical_work_details,h.smoking,h.alcohol,h.recovery,h.chest_pain,h.discomfort,
                c.whatsapp_content,c.video_permission,c.privacy_acceptance,c.data_confirmation,
                COALESCE(f.matricula_eur,0.0) AS matricula_eur,
                CAST(ROUND(COALESCE(f.matricula_eur,0.0)*100) AS INTEGER) AS matricula_cents,
                c.agreement,
                g.occupation,g.study_place,g.parent_name,g.parent_email,
                m.found_us,m.enjoyment,m.nerves_confidence,m.additional_comments
            FROM users u
            LEFT JOIN user_profile p   ON p.user_id=u.id
            LEFT JOIN user_sports s    ON s.user_id=u.id
            LEFT JOIN user_health h    ON h.user_id=u.id
            LEFT JOIN user_consent c   ON c.user_id=u.id
            LEFT JOIN user_guardians g ON g.user_id=u.id
            LEFT JOIN user_marketing m ON m.user_id=u.id
            LEFT JOIN user_finance f   ON f.user_id=u.id
            z(
            ORDER BY u.id DESC
        )rV   r   splitrD  ro   r  r   extendr   r}   r   rP  )r0   r9  ri   rT   
roles_listr   r  r	  placeholdersr   r   r   s               r>   r8  r8    s,      DH#>>J16S1AOAQWWYaggioo'O
O	jjl((*+
EFxxc*o 56*<.:j!	 1Dll  , G 	-0 12 "%0AQ00516 KI PF 1516 KsA   2D0D
 D*A(D0)D#;DD#D0D##D-(D0z/api/users/{user_id}/roleapi_users_set_rolec                   K   t        |       t        |       }|st        dd      |j                  xs dj	                         j                         }|t        vrt        dd      t        |d         j                         }|dk(  }|xr |dk7  }||k(  rd	| |d
dS |rt               }|dk  rt        dd      t        | |       d	| |d	dS w)Nr   rO  rH   rF   r   u   Rol inv�lidori   rj   TF)r   r   ri   changedr   uU   Debe existir al menos un usuario con rol admin. No puedes degradar al �ltimo admin.)
rU   r   r	   ri   rD  ro   ALLOWED_ROLESrN   r   r2  )	r   r  r0   r   new_rolecurrent_role	was_adminis_demoting_adminadmins_counts	            r>   rA  rA    s     'G$F4KLL"))+113H}$4DEEvf~&,,.L'I!9h'&9xwUSS#~1n  '8$7HNNs   CCz^\d{4}-\d{2}-\d{2}$)patternz^\d{2}:\d{2}$c                      e Zd ZU eed<   eed<    eddd      Zeed<    edd	      Z	eed
<    ee
      Zee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZeed<   y)ReservationInr   r<   <        r   r   r   r   r  r   )default_factoryuser_idsNr   rC   r   r   
confirmadar   r   r   Fr   )r  r  r  DateStrr  TimeStrr   r   r;   r   r  rR  r   r   r   rC   rN   r   r   r   r   r   r   boolr   r@   r>   rL  rL    s    
M
M!"u5c5Q1~K%5Hd3i5!GXc]!D(3-E8C=E8C=(FHSM(E8C= $J$D$r@   rL  c                   :   e Zd ZU dZee   ed<   dZee   ed<    e	ddd      Z
ee   ed<    e	dd	      Zee   ed
<   dZeee      ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   dZee   ed<   y)ReservationUpdateNr   r<   rN  rO  rP  r   r   r  r   rR  r   rC   r   r   r   r   r   r   )r  r  r  r   r   rT  r  r<   rU  r   r   r;   r   rR  r   r   rC   rN   r   r   r   r   r   r   rV  r   r@   r>   rX  rX    s    "D(7
""D(7
"&+DQ5&AhsmA!&t!2K#2$(HhtCy!(!GXc]!D(3-E8C=E8C= FHSM E8C= $J$D(4.r@   rX  c                   J    e Zd ZU eed<    edd      Zeed<   dZee	   ed<   y)RatingIn	player_idr   rN  rP  scoreNcomment)
r  r  r  r;   r  r   r\  r]  r   rN   r   r@   r>   rZ  rZ    s&    N!"E3"!GXc]!r@   rZ  c                 T   | d   | d   | d   | d   | d   t        | d   xs d      t        | d   xs d      dz  | d	   | d
   | d   | d   | d   | d   d| j                         v r| d   nd d| j                         v r| d   t        | d         ndd}t               5 }|j	                  d| d   f      j                         }d d d        D cg c]  }t        |d          c}|d<   |D cg c]  }t        |d         |d	   |d
   d c}|d<   |S # 1 sw Y   SxY wc c}w c c}w )Nr   r6   r   r<   r   r   r         Y@rC   r   r   r   r   r   r   r   F)r   r6   r   r<   r   r   	price_eurrC   r   r   r   r   r   r   r   z
            SELECT u.id, u.name, u.email
            FROM reservation_students rs
            JOIN users u ON u.id = rs.user_id
            WHERE rs.reservation_id = ?
            ORDER BY u.name
        student_idsr   r   )r;   r   rV  r   r}   r   )r   baser   srowsr   s        r>   r   r     s^   $i,'FF 233}-23#m,12U:FWWWy>h-+7388:+Ec,'4%+sxxz%9c&k>US[![`D" 
 &A		  $i\ $8: 	& 277A3qw<7D^cdYZs1T7|QvY7TdDK& & 8ds   %DD -D%Dc                   K   t        |        t        |d      r|j                  d      n|xs i }	 | j                          d {   }d|v r&d|vr"	 t        |j                  d      xs d      |d<   d|v rd|vr|j                  d      |d<   d	|v rDd
|vr@	 t        t        t        t        |d	         j                  dd            dz              |d
<   d|v rDd
|vr@	 t        t        t        t        |d         j                  dd            dz              |d
<   t        |      }t               5 }t        j                  |_        |j!                  d|f      j#                         }d d d        st%        dd      	 t        |d         }|}	d|v r|d   t        |d         }	n|j                  d|j                  d            }
|
]t'        |
t              r|
}	nJt'        |
t
        t        f      r4t        |
      j)                         j+                         }|dv rd}	n|dv rd}	|s|	|k(  rt%        dd      |	rdnd|d<   d|v r)|d   $	 t,        j.                  j1                  |d   d       d|v r|d   	 t        |d         j)                         d d }t3        t
        |j5                  d             \  }}d|cxk  rd"k  rn nd|cxk  rd#k  sn t%        dd$      |dk(  r|dk(  rt%        dd%      d&|cxk  rd'k  sn t%        dd(      |d)d |d)|d<   d|v r*|d   %t        |d         }|dk  s|d*kD  rt%        dd+      d,|v rL|d,   Gt        |d,         j)                         j+                         }|t6        vrt%        dd-|       ||d,<   d|v rN|d   I|d   }t               5 }|j!                  d.|f      j#                         }|st%        dd/      	 d d d        d	|v rDd
|vr@	 t        t        t        t        |d	         j                  dd            dz              |d
<   d
|v r+	 t        |d
   xs d      |d
<   |d
   dk  rt%        dd0      h d1}g }g }|j9                         D ]/  \  }}||v s|j;                  | d2       |j;                  |       1 |st%        dd3      	 t        d
|v r|j                  d
      n|d
   xs d      }| xr |	}|j;                  |       t               5 }t        j                  |_        |rM|dkD  rG|j!                  d4|f      j=                         }|D cg c]  }|d5   	t        |d5          }}|s|d5   r	 t        |d5         g}i }	 |j                  d6      xs i }t'        |t              rbi }|j9                         D ]K  \  }}	 t'        |t              rt        |      n
t        |      } 	 t        |xs d      }!t?        d|!      || <   M |}|ra|jA                  d7|D "cg c]  }"|"f c}"       i }#|r4|D ].  }"t?        dt        |j                  |"|      xs d            |#|"<   0 n#|D ]  }"t?        dt        |xs d            |#|"<     t,        j.                  jC                         jE                  d8      }$|j                  d      xs |d   }%|j                  d      xs	 |d   xs d9d d }&d:|% d;|& }'g }(|#j9                         D ]P  \  }"})t        |)d<z  d=      }*|j!                  d>|*|"f       |)dkD  s/|(j;                  |$|%|"|t        |)      d?|'d f       R |(r|jA                  d@|(       |j!                  dAdBjG                  |       dCtI        |             |jK                          d d d        d|dDS 7 |# t        $ r i }Y w xY w# t        $ r Y nw xY w# t        $ r Y w xY w# t        $ r Y w xY w# 1 sw Y   xY w# t        $ r d}Y w xY w# t        $ r t%        dd      w xY w# t        $ r t%        dd!      w xY w# 1 sw Y   mxY w# t        $ r	 d|d
<   Y 9w xY w# t        $ r	 d|d
<   Y 4w xY w# t        $ r d}Y w xY wc c}w # t        $ r g }Y -w xY w# t        $ r Y w xY w# t        $ r d}!Y w xY w# t        $ r i }Y w xY wc c}"w # 1 sw Y   @xY ww)ENrP  T)exclude_unsetdurationr   r   
teacher_idr   r`  r   r;  .r   price'SELECT * FROM reservations WHERE id = ?r   zReserva no encontradarH   r   Fhas_paid1trueu   sísion)0falsenooffr   zNada que actualizarr   r   r  u(   Fecha inválida. Usa formato YYYY-MM-DD.r<   rN  :u"   Hora inválida. Usa formato HH:MM.   rM     Hora inválida.,No se admiten reservas a medianoche (00:00).      u&   Hora fuera de horario (08:00–22:00).02drO     Duración inválida.r   Estado no permitido: FSELECT id FROM users WHERE id=? AND LOWER(COALESCE(role,''))='docente'/   docente_id no corresponde a un docente válido.   Precio inválido.>   r   rC   r   r<   r   r   r   r   r   r   r   r   z = ?u'   Ningún campo permitido para actualizarzASELECT user_id FROM reservation_students WHERE reservation_id = ?r   per_student_centsr   z%Y-%m-%dT%H:%M:%Sz00:00Reserva  r_  re  cUPDATE user_finance SET matricula_eur = ROUND(COALESCE(matricula_eur,0.0) - ?, 2) WHERE user_id = ?reservaz
                        INSERT INTO jugadores_gastos
                        (created_at, date, user_id, reservation_id, amount_cents, category, concept, notes)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    zUPDATE reservations SET r  z WHERE id = ?r  )&rv   r  rP  jsonr~   r;   rK   roundfloatrN   replacerV  r   rm   rn   r|   r}   r   r	   rl   rD  ro   r   r   r  mapr=  ALLOWED_STATUSESr   r  r   r  executemanyr  r  r  r  r   )+r0   r   r  updatesrawhas_other_updatesr   prev_row	prev_paidnew_paidrr  r  thhmmdminstatus_normr   dallowed	set_partsargsr   r  effective_price_centsdo_charge_nowr   r   ra  r  psctmpkkvvr4   sharesnow_txtdate_txttime_txtconcept_baseto_insshare	share_eurs+                                              r>   api_reservations_updater  :  s
    W%29'62Jglll.QXQ^\^GLLN" S/w>	*-cggj.A.FQ*GG&' s|7: # 5cm7:	%(uS[9I5J5R5RSVX[5\/]`c/c)d%eGM" #~-w6	%(uSW5F5N5NsTW5X/Y\_/_)`%aGM" W	 aD";;<< IF9U^^`a 4KLL&)*	 HWV_8(ggfcggj12?#t$C#s,HNN$**,88#H55$HY!64IJJ#aGFOWV_8	dKK  *= WV_8	^GFO$**,Ra0Aaggcl+FB R"b2C8IJJ7rQwC8fggR"C8`aaHAbX.W$1C)D)P7-./19uC8NOO7wx0<'(+,224::<..C:OP[}8]^^'w7<#8#D\*
W 	oX hj  #<mnn 	o g-w">	'%(uS9M5N5V5VWZ\_5`/adg/g)h%iGM" 	'%()?)D1%EGM" =!A%C8KLLG ID 1<s$Z(KKN 4]^^" #-SZBZGKK$>aijwaxa}|}  #]0MKK	 QD";;2Q6<<S	 hj  7;Wa	l>V3q|,WKW8I#6%#&x	':#;"<K !#'gg128bc4(C #		 	-1%+5a+=Q3q6B#!$QV!B #&a*B	- ),%   `'23cV3
 $* j&)!S1B1F1FsLa1b1gfg-h&isj  + N&)!S1F1K!-L&MsN ++//+445HI";;v.B(62B#KK/N8F3CNwPRQRS!)(1XJ?"(,,. JC %eemQ 7ILL}"C( qy#$"J%( 	' 	$ $$ &  	! 	/		)0D/E]SUZ[_U`acQf f%%_ # 
  		  		
  		
a a  	6  	dC8bcc	d  	^C8\]]	^(	o 	o  	'%&GM"	'
  	'%&GM"	'&  " !" X
 ! %"$K%  ) %$%  ) #!"B#  '$&!' 4MQ Qsn  0d _ __ d !_ 5$d ?_' d "?_7 !d 67`-d ` C d #`& 7	d Aa  C;d =1a/d  ?a' ?d a< 7d 6d 'b /"d Ac3
b# b#0	c3:b(	c3=c
&b:1c
 cc3&
c.0Dc3?A6c35d _ _d _d 	_$ d #_$$d '	_40d 3_44d 7	` d `d `d `#d "`##d &`==d  ad a$d 'a95d 8a99d <b
d bd b d b  d #c3(b73c36b77c3:	cccc
ccccc+'c3*c++c33c=8d z/api/reservations/{rid}ridc           
        K   t        |        dt        dt        fddt        dt        t           ffd}t               5 }t        j                  |_        |j                  d|f      j                         }|st        dd	      d
}	 t        |d         }|r$t        |j                               }d|v rt        |d   xs d      nd}g }d|v r9|d   r4	 t        j                   |d         xs g D 	cg c]  }	t        |	       }}	nd|v r|d   r	 t        |d         g}|s	 |||      }t#        |D 
ch c]  }
t%        |
t              s|
 c}
      }i }d|v r_|d   rZ	 t        j                   |d         xs i }|j'                         D ])  \  }}	 t        |      }|r||v rt        |xs d      ||<   + 	 |s|}t)        |      }|dkD  rmt+        t-        |      |      \  }}|D ci c]  }|| }}|d | D ]  }||xx   dz  cc<    |dk  r't	        |j                               D ]  }||    ||<    |}|j'                         D ]Z  \  }}|xs ddz  }|j                  d|f      j                         }|r|j                  d||f       G|j                  d||f       \ |j                  d|f       |j                  d|f       |j/                          d d d        ddiS # t        $ r d
}Y dw xY wc c}	w # t        $ r g }Y w xY w# t        $ r g }Y w xY wc c}
w # t        $ r Y w xY w# t        $ r i }Y w xY wc c}w # 1 sw Y   ddiS xY ww)NrC   r1   c                 Z    | j                  d|f      j                         }t        |      S )Nz>SELECT name FROM sqlite_master WHERE type='table' AND name = ?)r}   r   rV  )r   rC   r   s      r>   _table_existsz.api_reservations_delete.<locals>._table_exists  s0    llLG
 (* 	 Cyr@   r  c                     dD ]X  } | |      s	 | j                  d| d|f      j                         }|r$|D cg c]  }|d   	t        |d          c}c S Z g S c c}w # t        $ r Y mw xY w)N)reservation_usersreservation_studentsreservation_alumnoszSELECT user_id FROM z WHERE reservation_id = ?r   )r}   r   r;   r~   )r   r  r  r   r   r  s        r>   _get_uids_from_bridgez6api_reservations_delete.<locals>._get_uids_from_bridge  s    U 
	AT1%<<.qc1JK hj  37La1Q4;KAaD	LL 
	 	  M  s(   +A&
A!A!A&!A&&	A21A2rj  r   r  rH   Fr   r   r   user_ids_jsonr   per_student_cents_jsonr   r_  z2SELECT user_id FROM user_finance WHERE user_id = ?zWUPDATE user_finance SET matricula_eur = COALESCE(matricula_eur,0) + ? WHERE user_id = ??INSERT INTO user_finance (user_id, matricula_eur) VALUES (?, ?)z5DELETE FROM jugadores_gastos WHERE reservation_id = ?z%DELETE FROM reservations WHERE id = ?r   T)rv   rN   rV  r;   r  r   rm   rn   r|   r}   r   r	   r~   r   r   r  loadssortedrl   r   r   divmodabsr   )r0   r  r  r   r   	paid_flagr   r   uidsxr   per_mapjr   r  r4   sidsr  rb  remr  centseurr   r  s                           @r>   api_reservations_deleter    s    W%# $  c  
 MD";;llDsfMVVXC8PQQ		S[)I sxxz?D:G4:O#c-05A6UVK D$&3+?,0JJs?7K,L,RPRSqCFSDS d"s9~I/0D ,T37dAjC.@1ABD&(G'4/C8P4Q
!

3'?#@AGRA !	 %1%"%a&C#'3$;/216{	% Iq5 &s;'7 ;ID#045395C5#DSz &CA&"Q!%chhj!1 -A&)!fWCF-!G%mmo 
UzU*llHF (*  LLqc

 LLYc
" LLPSVRXY<sfE[M^ $<M  	I	 T  D
 ! D B  ) %$%  ! G! 6cM^ $<s  ?M%AMK/?ML7L	L
ML%M9L*
L*
M&2L?#L/<L?>1M/
M9C*M#M%/K>:M=K>>MLLMLML'#M&L''M/	L<8L?;L<<L??M
MMMM"
M%>   	cancelada	pendienterS  z/api/reservationsc                   K   t        |        	 | j                          d {   }d}t        |d      r"|j                  t        |j                        }nw|j                  d|j                  d            }t        |t
              r|}nCt        |t        t        f      r-t        |      j                         j                         }|dv rd}|j                  j                         }|j                  j                         d d }	 t        j                  j                  |       	 t#        t        |j%                  d
            \  }}	d|cxk  rdk  rn nd|	cxk  rdk  sn t!        dd	      |dk(  r|	dk(  rt!        dd	      d|cxk  rdk  sn t!        dd	      |dd
|	d}t        |j&                        }
|
dk  s|
dkD  rt!        dd	      t        |j(                  xs d      }t+        |dd       }|!|j                  d|j                  d            }|sJ|Ht        |      dk7  r:	 t        t-        t/        t        |      j1                  dd            dz              }|dk  rt!        dd	      |j2                  xs dj                         j                         }|t4        vrt!        dd| 	      |j6                  xs d }|j8                  xs d }|j:                  xs d }t=        |j>                  xs g       }|j@                  r|jC                  |j@                         	 tE        tG        d  |D                    }|rtI               5 }|jK                  d"djM                  d#tO        |      z         d$tQ        |            jS                         }d d d        D ci c]  }t        |d%         | }}|D cg c]  }||vs||   d&   d'k7  s| }}|rt!        dd(| 	      tO        |      d)k(  r||d      }|s|d*   }|s|d+   }|s|d,   }|jT                  |jT                  nd }|d-|v r|j                  d-      }|X	 t        |      }|dkD  rEtI               5 }|jK                  d/|f      jW                         }|st!        dd0	      	 d d d        nd }tO        |      d)k(  r|d   nd }t        jX                  j[                         j]                  d1      }i }	 |j                  d2      xs i }t        |t^              rbi }|ja                         D ]K  \  } }!	 t        | t              rt        |       n
t        |       }"	 t        |!xs d      }#tc        d|#      ||"<   M |}|rui }$|r4|D ].  }tc        dt        |j                  ||      xs d            |$|<   0 n#|D ]  }tc        dt        |xs d            |$|<     te        |$jg                               }%ni }$t        |xs d      }%	 tI               5 }|jK                  d3||||
|%||||jh                  ||||rd)ndf      }&|&jj                  }'|r|jm                  d4|D cg c]  }|'|f c}       |jm                  d5|D cg c]  }|f c}       |r|$ja                         D ](  \  }}(t-        |(d6z  d7      })|jK                  d8|)|f       * g }*d9| d:| }+|$ja                         D ]6  \  }}(t        |(      dkD  s|*jC                  ||||'t        |(      d;|+d f       8 |*r|jm                  d<|*       |jo                          d d d        '},	 tI               5 }3|3jK                  dB,f      jW                         }-|3jK                  dC|,f      jS                         }4|4rt=        |4      ng }5|5s[d }6|-dA   r$|3jK                  dD|-dA   f      jW                         }6|6s)|-d+   r$|3jK                  dE|-d+   f      jW                         }6|6r|6g}5d }7|-dF   -|3jK                  dG|-dF   f      jW                         }|r|d*   nd }7|5rdH}8dI|-dJ    dK|-dL    |7rdM|7 ndz   dz   }9	 t        tu        |       d%         }:t        jX                  jw                         jy                  dNO      };|3jm                  dP|5D cg c]  }|;|:t        |d%         |8|9|,f c}       |3jo                          d d d        d,dQS 7 # t        $ r i }Y w xY w# t        $ r t!        dd	      w xY w# t        $ r t!        dd	      w xY w# t        $ r d}Y w xY w# t        $ r t!        dd!	      w xY w# 1 sw Y   xY wc c}w c c}w # t        $ r t!        dd.	      w xY w# 1 sw Y   xY w# t        $ r Y w xY w# t        $ r d}#Y Vw xY w# t        $ r i }Y Uw xY wc c}w c c}w # 1 sw Y   xY w# tp        jr                  $ r tI               5 }|jK                  d=|||f      jW                         }-|-st!        d>d?	      t        |-d%         }.|jK                  d@|.f      jS                         }/|/D cg c]  }t        |dA          nc c}w }0}|D cg c]	  }||0vs| nc c}w }1}|1s|.},n|jm                  d4|1D cg c]  }|.|f nc c}w c}       |jm                  d5|1D cg c]  }|f nc c}w c}       g }*d9| d:| }+|r|1D ]  }|j                  ||      }2	 t        |2xs d      }2n# t        $ r d}2Y nw xY wt-        tc        d|2      d6z  d7      })|jK                  d8|)|f       |2dkD  sj|*jC                  ||||.t        |2      d;|+d f        |*r|jm                  d<|*       |jo                          |.},d d d        n# 1 sw Y   nxY wY w xY w# t        $ r d}:Y ww xY wc c}w # 1 sw Y   xY w# t        $ r Y !w xY ww)RNFr   rk  rl  TrN  r   u!   Fecha inválida (usa YYYY-MM-DD).rH   ru  u   Hora inválida (usa HH:MM).r   rv  rM  rw  rx  ry  rz  z$Hora fuera de horario (08:00-22:00).r{  rO  r|  r`  ri  rF   r;  rh  r   r  rS  r}  c              3   8   K   | ]  }|t        |        y wr   )r;   )r  r  s     r>   r  z*api_reservations_create.<locals>.<genexpr>  s      NAQ Ns   u   IDs de alumnos inválidos.zXSELECT id, LOWER(COALESCE(role,'')) AS role, name, email, phone FROM users WHERE id IN (r<  r   r   ri   r[  u   IDs no v�lidos o no alumnos: r   rC   r   r   rg  u   docente_id inválido.r~  r  r  r  z
              INSERT INTO reservations
              (created_at, date, time, duration_minutes, price_cents, name, email, phone, notes, user_id, status, docente_id, paid)
              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            zQINSERT OR IGNORE INTO reservation_students(reservation_id, user_id) VALUES (?, ?)r   r_  re  r  r  r  r  z
                            INSERT INTO jugadores_gastos
                            (created_at, date, user_id, reservation_id, amount_cents, category, concept, notes)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        z\SELECT id FROM reservations WHERE date=? AND time=? AND COALESCE(docente_id,0)=COALESCE(?,0)rf  z%Ya existe una reserva en ese horario.z?SELECT user_id FROM reservation_students WHERE reservation_id=?r   rj  z
              SELECT u.id, u.name, u.email
              FROM reservation_students rs
              JOIN users u ON u.id = rs.user_id
              WHERE rs.reservation_id = ?
              ORDER BY u.name
            r   z?SELECT id, name, email FROM users WHERE lower(email) = lower(?)r   z#SELECT name FROM users WHERE id = ?zReserva creadaz!Se ha creado una reserva para el r   z a las r<   z con secondstimespecz
                  INSERT INTO notifications
                  (created_at, sender_id, recipient_id, title, body, is_read, reservation_id)
                  VALUES (?, ?, ?, ?, ?, 0, ?)
                r  )=rv   r  r~   r  r   rV  rK   rl   r;   rN   rD  ro   r   r<   r   fromisoformatr	   r  r=  r   r   getattrr  r  r  r   r  rC   r   r   r  rR  r   r  r  r   r   r}   r  r   r  r   r   r   r   r  r  rP  r   r  sumr  r   r   r  r   rm   IntegrityErrorrV   r   r   )<r0   r  r  r  rr  r  r  r  r  r  r  r   price_eur_rawr  rC   r   r   ra  r   r   r   foundr4   badu1r   r  legacy_user_idr  r  r  r  r   r  r  r  r  total_price_centsr   new_idr  r  r  r  created_reservation_idr   existing_idcurrentcurrent_idsto_addr  conn2rc  
recipientsrecipdocente_nametitulocuerpor#  now_isos<                                                               r>   api_reservations_creater    s6    W%LLN" IwGLL$<&	ggfcggj12c4 Ic3Z(C &&(A44 	||!!#H||!!#BQ'HY
h'SS(..-.B LbLQ"\r\4EFF	Qw274bccLbL4Z[[S2c(#Hw''(DqyD5L4JKKg)).Q/KG[$7MSWWW-=>m7C<NRT<T	eE#m*<*D*DS#*N$ORU$UVWK Q4GHH>>1\88:@@BK**6KK=4YZZLL DD]]"dE]]"dEw''-2.K7??+RS N NNO W 	<<jknkskstwx{  }H  yI  uI  lJ  kK  KL  Mk" hj 	
 +//QQtWq//)`sS-=sFASW_A_s``C:\]`\a8bcc{q {1~&B&z77'.'9'9'E##4Jlc1WW\*
	QZJ > sDLL\M (*  'C@qrr s s J'*;'71'<[^$Nkkoo(()<=Ggg)*0bc4 C		 	%1#-a#5Q3q6BQV!B a*B	% !$ " X!!S):)>)>sK)P)UTU%VWsX # <!!S)9%:;s<0 0q1h5W +	,,   8Xt5FeUZ\c\i\iky  |G  IS  Zc  UV  ij  k	lC
 ]]F  g.9:sfc]:   `'23cV3 &,lln 
U$)%%-$;	 B&,  F%-hZq
#CL&,lln 
Uu:>"MM ' ( # & #E
 ) , $	+ 	 (( * $	%
 KKMW+	X "(x3W 0	-- ILbKdennpCMM # )*, -5HJ  ).e2Jy>!MMF9~' hj  W!MMW7|o hj  "'JL< ,MM"G#lJ[I]^ggi,-qy4)5c&k]'#f+W/;u\N+E 
" #L$9$$? @I ++,,.88)8L!! # & Is1T7|VVE[\	 a0	f 455w # "  Y4WXXY  S4QRRS&  	K	  R4PQQR	 	
 0`"  	QC8OPP	Qs s& !  ! B  2 ; 4+	 +	Z !! :5W 9	5,,l:. hj  #<cddc$i.KllOn hj  7>>3q|,>>K>%0KcC{4JcKKFK)4&  e178#K%88   ^%+,cC6,, !)(1XJ?%  1 5 5c; G&$'
OE( &$%E&$)#a-%*?$C	$c* !19"MM ' ( # + #E
 ) , $	+ 	, (( * $	%
 )4&s9	5 9	5 9	5:5B ! " !I"Y0	 0	b  s  pc. c+c. Cpd  &"d C)p29d4 +Cp2e pA	e "p.e-p
e2e2#A2pe7 !p01f"Ap2=g  0&ff.&g  9Bp
g) Agg#g:
gA2g7Ag?
g) 

o; Do.o.>o.,o)o.o; &p+c. .c=9p<c==p dpd11p4e?pepep e*%p7fpfp	f+'g  *f++g  .f=9g  <f==g   gpgp
gg&!g) )oA&o-jo	jjo:ko"
k-,0ol-,o-l;	8o:l;	;5o1A
o;	oo		opopo&"o.%o&&o..o83o; ;	ppppc           	        K   t        |        dt        dd fd}|r ||       |r ||       |r|r	||kD  r||}}d }|rS|j                         j                         }|t        vr-t        dddj                  t        t                     d      g }g }	|r"|j                  d	       |	j                  |       |r"|j                  d
       |	j                  |       |"|j                  d       |	j                  |       |r"|j                  d       |	j                  |       |"|j                  d       |	j                  |       |rddj                  |      z   nd}
d|
 d}t               5 }|j                  ||	      j                         }d d d        D cg c]  }t        |       c}S # 1 sw Y   "xY wc c}w w)Nr  r1   c                 n    	 t        j                  | d       y # t        $ r t        dd|  d      w xY w)Nr  r   u   Fecha inválida: z. Usa formato YYYY-MM-DD.rH   r  r  s    r>   _check_datez*api_reservations_list.<locals>._check_date  sA    	ja, 	jC:KA3Ng8hii	jr  r   u   Estado inválido. Usa uno de: r  rh  rH   z	date >= ?z	date <= ?r   zLOWER(status) = ?r   r  r   rF   aR  
        SELECT
            id,
            created_at,
            date,
            time,
            duration_minutes,
            price_cents,
            name,
            email,
            phone,
            notes,
            user_id,
            status,
            docente_id,
            paid
        FROM reservations
        z1
        ORDER BY date ASC, time ASC, id ASC
    )rv   rN   rD  ro   r  r	   r  r  r  r   r}   r   r   )r0   r  r  r   r   r   r  r  r  r	  clauser  r   r   r   s                  r>   api_reservations_listr    s     W%js jt j IGWW!4$i7	!%Klln**,..7		&IYBZ8[7\\]^ 
 EF[!i [!g]#g()k"%&j!27i',,u--RF	" 
 #C* 
 4D||C(1134 266A$Q'664 4 7s*   E0G2!F4GG 1G4F=9G/perfilc                   K   t        |       }|st        dd      S t        |t        j                        rt        |d         nt        |j                  d            }t        |t        j                        r|d   n|j                  d      xs dj                         }t               5 }|j                  d||f      j                         }|r|d   nd	}d d d        t        j                  d
| |d      S # 1 sw Y   #xY ww)Nr   r   r   r   r   rF   z
            SELECT COUNT(*) AS c
            FROM reservations r
            WHERE r.user_id = ?
               OR LOWER(COALESCE(r.email,'')) = LOWER(?)
            r   r   zprofile.htmlr  )rV   r   rl   rm   rn   r;   rK   rD  r   r}   r   rW   rX   r  s          r>   perfilr    s     	g	BH#>>'GKK8C4Mc"&&,>OE)"gkk:7w@USU\\^H	 
3Dll H
 (* 	 ),CH
3 %%!2	
 
3 
3s   B)D+,C9"D9D>D/ajustesc                 H  K   t        |       }|st        dd      S t               5 }|j                  d|d   f      j	                         }|j                  d|d   f      j	                         }|j                  d|d   f      j	                         }d d d        rt        |      ni }r|j                  t        |             r|j                  t        |             t        j                  d| || j                  j                  d	      d
      S # 1 sw Y   xY ww)Nr   r   r   z8SELECT id, name, email, created_at FROM users WHERE id=?r   z6SELECT phone, avatar FROM user_profile WHERE user_id=?zPSELECT team, category, position, injury_history FROM user_sports WHERE user_id=?settings.htmlr5   )r0   rT   r5   )rV   r   r   r}   r   rP  updaterW   rX   r7   rK   )r0   rq   r   row_corerow_profile
row_sportsctxs          r>   ajustes_getr  	  s%    	g	BH#>>	 ND<< Z]_`d]e\ghqqsll#[^`ae^f]hirrt\\"twyz~w  wB  C  L  L  N
N %$x."C

4$%

4
#$%%OO''/	
 N Ns   %D"A-DBD"DD"r   ry  r{  r}  r  r  c
           
      6  K   t        |       }
|
st        dd      S t        | |	       |xs dj                         }|xs dj                         }|xs dj                         xs d }|xs dj                         xs d }|xs dj                         xs d }|xs dj                         xs d }|xs dj                         xs d }t	               5 }|j                  d|
d   f      j                         }|r|d   nd }d d d        }|rt        |dd       rt        j                  j                  |j                        d	   j                         }|d
vr4t        j                  d| |
d| j                  j!                  d      d      S t"        dz  dz  }|j%                  dd       |d|
d    | z  }t'        |d      5 }|j)                  |j+                          d {          d d d        d|
d    | }	 t	               5 }|j                  d|||
d   f       |j                  d|
d   f       |j                  d|||
d   f       |j                  d|
d   f       |j                  d|||||
d   f       |j-                          d d d        t        dd      S # 1 sw Y   xY w7 # 1 sw Y   xY w# 1 sw Y   1xY w# t.        j0                  $ r7 t        j                  d| |
d| j                  j!                  d      d      cY S w xY ww)Nr   r   r   rF   z1SELECT avatar FROM user_profile WHERE user_id = ?r   ry  rg  r   )z.pngri  z.jpegz.webpr  z:Formato de imagen no permitido. Usa PNG, JPG, JPEG o WebP.r5   )r0   rT   r>  r5   rB   r^  Trj  rm  rn  z/static/uploads/user_z+UPDATE users SET name=?, email=? WHERE id=?r   z9UPDATE user_profile SET phone=?, avatar=? WHERE user_id=?r   zWUPDATE user_sports SET team=?, category=?, position=?, injury_history=? WHERE user_id=?u+   El correo ya est� en uso por otra cuenta.r  )rV   r   rP   rD  r   r}   r   r  r  r  r  rg  ro   rW   rX   r7   rK   r  r  r  r  rh  r   rm   r  )r0   rC   r   r   ry  r{  r}  r  r  r5   rq   r   r   current_avatarr  extr  r  r  s                      r>   ajustes_postr  +	  sN     
g	BH#>>$JBD[b!Ekr  "*dEZR (DDR&&(0DHR&&(0DH%+224<N	 8DllNQSTXQYP[\eeg*-X48 !K'&*d3ggv/288:88--o"U++F3	@   )I5$6E"T(C5"99	)T" 	)aGG&++-'(	)-bhZu=W 	LLFuVXY]V^H_`LLQTVW[T\S^_LLTW\^ikmnrksVtuLLPSUVZS[R]^LLruy  |D  FN  P^  `b  cg  `h  ui  jKKM	 	s;;E8 8" (	) 	)	 	 !! ))/BOO''/	<
  	s   CL.J%CLJ4#J2
$J4,L?
K 	BK K L%J/*L2J44J=9L K	K ALLLLz/notesc                   K   t        |       }|st        dd      S d}g }d|j                         v r|d   t        |d         nd}|dk(  rnJ|dk(  r#|d	z  }|j	                  t        |d
                n"|dz  }|j	                  t        |d
                |dz  }t               5 }|j                  ||      j                         }d d d        d fdfd}fd}g }	D ]  }
 |
dd      xs d} |
ddd      xs d}|sP |
d      rG|
d   rBt        |
d         j                         }t        |      dk\  r|d   }t        |      dk\  r|d   }|s|r| d| j                         n |
d      r|
d   nd}|	j	                   |
d
      r|
d
   nd ||| |
d      r|
d   nd |
d    ||
       ||
      d        t        j                  d| ||	d      S # 1 sw Y   xY ww)Nr   r   r   aY  
      SELECT
        r.*,
        su.name  AS student_name,
        su.email AS student_email,
        du.name  AS docente_name,
        du.email AS docente_email
      FROM reservations r
      LEFT JOIN users su ON su.id = r.user_id
      LEFT JOIN users du ON du.id = r.docente_id
      WHERE r.notes IS NOT NULL AND TRIM(r.notes) <> ''
    ri   rF   rj   rt   z AND r.docente_id = ?r   z AND r.user_id = ?z& ORDER BY r.created_at DESC, r.id DESCc                 F    	 || j                         v S # t        $ r Y yw xY w)NF)r   r~   )r   keys     r>   hasznotes.<locals>.has	  s*    	#((*$$ 		s    	  c                 x    |D ]4  } | |      s| |   }|t        |      j                         dk7  s2|c S  y )NrF   )rN   rD  )r   namesr  rr  r  s       r>   rs  znotes.<locals>.pick	  sE     	A3{!f?s3x~~'72'=J		
 r@   c                 J     | d      xs  | d      xs  | d      xs dS )Nstudent_namerC   r      —r   r   rs  s    r>   student_labelznotes.<locals>.student_label	  s.    C(_Df,=_c7AS_W__r@   c                 4     | d      xs  | d      xs dS )Nr  docente_emailr  r   r  s    r>   docente_labelznotes.<locals>.docente_label	  s!    C(RDo,FR(Rr@   r   fechar<   hourhorar6   r   r   re  r  r   )r   r   r<   
dt_displayr6   r   studentrt   z
notes.html)r0   rT   r   )rV   r   r   rN   r  r;   r   r}   r   r=  r   rD  rW   rX   )r0   rq   r  r	  ri   r   r   r  r  r   r   r  r  partsr
  r  rs  s                  @@r>   r   r   h	  sF    	g	BH#>>C F$	1bj6L3r&z?RTDw		&&c"T(m$##c"T(m$33C	 4D||C(1134
`SE VW-3VVV4:C\2s<7HL)*002E5zQ 85zQ 8;Cx
!H:.557knor  uA  lBVYZfVg  HJ
"3~#d)4$/23/E#l+4\$S)$S)	
 		( %%	
 O4 4s   B%G1)!G$
DG1$G.)G1)r,   TFplainc                     | xs d} t         rt        j                  |       S dt        j                  | j                  d            j                         z   S )NrF   sha256$utf-8)_HAS_BCRYPTr,   r   hashlibsha256encode	hexdigest)r  s    r>   hash_passwordr  	  sC    KRE{{5!!w~~ell7&;<FFHHHr@   stored_hashc                    | xs d} |xs d}|j                  d      s"|j                  d      s|j                  d      rt        r	 t        j                  | |      S y|j                  d      r]|j                  dd      d   }t        j                  | j                  d            j                         }t        j                  ||      S y# t        $ r Y yw xY w)	NrF   z$2a$z$2b$z$2y$Fr  r   r  )
startswithr  r,   rE  r~   r=  r  r  r  r  rL   rM   )r  r  expecteddigests       r>   verify_passwordr  	  s    KRE#Kf%)?)?)G;KaKabhKi}}UK88 i($$Y215W 56@@B""6844  s   C 	CCz/ajustes/passwordcurrent_passwordnew_passwordconfirm_passwordc                   K   t        |       }|st        dd      S t        | |       |xs dj                         }|xs dj                         }||k7  rt        j                  d| |dd      S t        |      dk  rt        j                  d| |d	d      S t        |t              r|j                  d
      n|d
   }t        ||      st        j                  d| |dd      S t        |      }t               5 }|j                  d||d   f       |j                          d d d        t        dd      S # 1 sw Y   xY ww)Nr   r   r   rF   r  u   Las contrase�as no coinciden.)r0   rT   r>  ry  u7   La nueva contrase�a debe tener al menos 8 caracteres.r   u&   La contrase�a actual no es correcta.z+UPDATE users SET password_hash=? WHERE id=?r   r  )rV   r   rP   rD  rW   rX   r   rl   rP  rK   r  r  r   r}   r   )	r0   r  r  r  r5   rq   r  new_hashr   s	            r>   ajustes_passwordr"  	  sW     
g	BH#>>$ &B--/L(.B557''))/6<
  	
 <1))/N<
  	
 .8D-A"&&)r/GZK+[9))/=<
  	
 \*H	 DBXrRVxDXY 
<< s   C7E9(D6!E6D?;Ec                   >    e Zd ZU  ed      Zeed<   dZee	   ed<   y)MatriculaPayloadr   r  matricula_centsaddmodeN)
r  r  r  r   r%  r;   r  r'  r   rN   r   r@   r>   r$  r$  	  s     A;OS&D(3-r@   r$  z/api/users/{user_id}/matriculac           	      8  K   t        |        t        |j                  dd            j                         }|dvrt	        dd      d|v }d|v }|s|st	        dd	      |r3	 t        t        t        |d         j                  d
d            d      }n	 t        |d         }t        |dz  d      }t               5 }|j                  d|f      j                         }	|	s|j                  d|df       d}
nt        |	d   xs d      }
|dk(  r|}nt        |
|z   d      }|j                  d||f       d|t        t        |dz              dcd d d        S # t        $ r t	        dd      w xY w# t        $ r t	        dd      w xY w# 1 sw Y   y xY ww)Nr'  r   >   r&  r   r   zmode debe ser 'set' o 'add'.rH   matricula_eurr%  zFalta matricula_eur.r;  rh  re  u   matricula_eur inv�lido.u   matricula_cents inv�lido.r_  z8SELECT matricula_eur FROM user_finance WHERE user_id = ?r  g        r   z;UPDATE user_finance SET matricula_eur = ? WHERE user_id = ?Tr   )r   r)  r%  )rU   rN   rK   ro   r	   r  r  r  r~   r;   r   r}   r   )r0   r   r  r'  has_eur	has_centseurosr  r   r   r  new_vals               r>   api_users_update_matricular.  	  s    'w{{65)*002D>!4RSS(G!W,I94JKK	U%GO$< = E Ec3 OPRSTE	W 123E eemQ'	 
DllUX_WabkkmLLZ]dfi\jkGCFMc*G5=GGeOQ/GRU\^eTfg $"53#78

 
  	UC8STT	U
  	WC8UVV	W
 
sO   A!F$1E FE4 %F>BF
FE11F4FFFF)Pathc                   K   t        |        |j                  d      xs dj                         j                         }|t        vrt        dd      t               5 }|j                  d|f      j                         }|st        dd      |j                  d	||f       |j                          |j                  d
|f      j                         }d d d        dt              dS # 1 sw Y   xY ww)Nri   rF   r   u   Rol inválidorH   z'SELECT id, role FROM users WHERE id = ?r   rO  r1  z@SELECT id, name, email, role, created_at FROM users WHERE id = ?T)r   rT   )rU   rK   rD  ro   VALID_ROLESr	   r   r}   r   r   rP  )r0   r   r  target_roler   r   updateds          r>   api_users_update_roler4  .
  s     ';;v&,"335;;=K+%ODD	 zDllDwjQZZ\C8OPP=W?UV,,adkcmnwwyz W..z zs   AC6A6C*C6*C3/C6z/api/ratingsapi_ratings_createc           	      &  K   t        |       }t        |j                        }|st        dd      t	        |d         j                         dk7  rt        dd      t        |j                        }|j                  xs dj                         }t        j                         j                  d	
      }t               5 }|j                  dt        |j                        t        |d         |||f       d d d        ddiS # 1 sw Y   ddiS xY ww)Nr   zJugador no encontradorH   ri   r[  r   zSolo se pueden valorar alumnosrF   r  r  zaINSERT INTO player_ratings (player_id, docente_id, score, comment, created_at) VALUES (?,?,?,?,?)r   r   T)rv   r   r[  r	   rN   ro   r;   r\  r]  rD  r   r   r   r   r}   )r0   r  rq   playerr\  r]  r  r   s           r>   r5  r5  ?
  s     	!'	*BG--.F4KLL
6&>  "h.4TUUE$"++-G
//

%
%y
%
9C	 
Do""#SD]E7CH	


 $<

 $<s   B=D?7D6DD
Dapi_ratings_listr[  c                 *  K   t        |       }g }d}|rd}|j                  t        |             t               5 }|j	                  d| d|      }|j                         D cg c]  }t        |       }}d d d        |S c c}w # 1 sw Y   S xY ww)NrF   zWHERE r.player_id = ?a%  
            SELECT r.id, r.player_id, r.docente_id, r.score, r.comment, r.created_at,
                   p.name AS player_name, d.name AS docente_name
            FROM player_ratings r
            JOIN users p ON p.id = r.player_id
            JOIN users d ON d.id = r.docente_id
            z(
            ORDER BY r.id DESC
        )rv   r  r;   r   r}   r   rP  )	r0   r[  rq   r	  r  r   r   r  r   s	            r>   r8  r8  Q
  s     	!'	*BFE'c)n%	 
1Dll   G 	  "%0AQ00
1 K 1
1 Ks.   8B)B#B5B7
BBBBz/logoutc                 X   K   | j                   j                          t        dd      S w)Nr/  r   r   )r7   r8   r   r=   s    r>   logoutr;  f
  s#     OO55s   (*z/api/healthc                     K   ddiS w)Nr   r   r   r   r@   r>   healthr=  k
  s     ds   )r   r   )r   r   c                   ^    e Zd ZU eed<   eed<   eed<    edd      Zeed<   dZe	e   ed	<   y)
ValoracionItemarea	subescala	indicadorr   rN  rP  r\  Nobservaciones)
r  r  r  rN   r  r   r\  r;   rC  r   r   r@   r>   r?  r?  s
  s0    
INN!"E3"#'M8C='r@   r?  c                   (    e Zd ZU eed<   ee   ed<   y)ValoracionJugadorInr[  r   N)r  r  r  r;   r  r   r?  r   r@   r>   rE  rE  z
  s    Nr@   rE  z/api/valoraciones_jugadorc                    K   t        |        t               5 }|j                  d|f      j                         }|D cg c]  }t	        |       c}cd d d        S c c}w # 1 sw Y   y xY ww)Na  
            SELECT eval_id, created_at, docente_id, 
                   area, subescala, indicador, score, COALESCE(observaciones,'') AS observaciones,
                   (SELECT name FROM users WHERE id = docente_id) AS docente_name
            FROM valoraciones_jugador
            WHERE player_id = ?
            ORDER BY created_at DESC, eval_id, area, subescala, indicador
        )require_docenter   r}   r   rP  )r0   r[  r   r   r   s        r>   api_valoraciones_jugador_listrH  
  sm     G	 	'D||  \ $8: 	 "&&AQ&	' 	' '	' 	's-   A-&A!AA!
A-A!!A*&A-c                   K   t        |        t        |       }d }	 t        |d         }||t        |      k(  rt        dd      t               5 }t        j                  |_
        |j                  d|f      j                         }|st        dd      	 |j                  d|f       	 |j                  d	||f       	 |j                  d
|f       |j                  d|f       |j                  d|f       |j                  d|f       |j                  d|f       |j                  d|f       |j                  d|f       |j                  d|f       d d d        dt        |      dS # t        $ r: 	 t        t	        |      j                  d            }n# t        $ r d }Y nw xY wY w xY w# t        $ r Y w xY w# t        $ r Y w xY w# t        $ r Y w xY w# 1 sw Y   xY ww)Nr   r   z%No puedes eliminar tu propio usuario.rH   z!SELECT id FROM users WHERE id = ?r   r   r   r   z*DELETE FROM user_profile WHERE user_id = ?z*DELETE FROM user_sports  WHERE user_id = ?z*DELETE FROM user_health  WHERE user_id = ?z*DELETE FROM user_consent WHERE user_id = ?z,DELETE FROM user_guardians WHERE user_id = ?z,DELETE FROM user_marketing WHERE user_id = ?z,DELETE FROM user_finance   WHERE user_id = ?r   T)r   deleted)rU   rV   r;   r~   rP  rK   r	   r   rm   rn   r|   r}   r   )r0   r   rq   r-  r   r   s         r>   api_users_deleterK  
  s    '	g	BEBtH Uc'l24[\\	 CD";;ll>
KTTVC8PQQ	LLMPWzZ	LL\_fho^pq		LLEzRLLEzRLLEzRLLEzRLLG'TLLG'TLLG'T 	5zB3C6 3w<00I  	RT*+E 	E	"  		  		  		-C Cs   G0E/ 'G0AG$F5,GBGG$G0/	F29#FF2F+(F2*F++F2.G01F22G05	G>G$GG$	GG$GG$	G!G$ G!!G$$G-)G0)r	   r   c                     t        |       }|st        dd      	 |d   }|dvrt        dd      |S # t        $ r t        |dd       }Y +w xY w)NrG   zSe requiere rol docente.rH   ri   rs   )rV   r	   r~   r  rp   s      r>   rG  rG  
  sh    	g	B4NOO)&z ''4NOOI	  )r64()s   4 AAc                     | j                   j                  d      xs | j                   j                  d      xs d}t        | |       y )Nr`   zX-Csrf-TokenrF   )rc   rK   rP   rd   s     r>   require_csrf_headerrN  
  s:    OO/\7??3F3F~3V\Z\E% r@   c                   K   t        |       }t        |        |j                  st        dd      t	        j
                  d      }t               5 }|j                         }|j                  D ]  }|j                  d||j                  |d   |j                  j                         |j                  j                         |j                  j                         t        |j                        |j                   xs dj                         xs d f        |j#                          d d d        d|d	S # 1 sw Y   xY ww)
Nr   u   Sin �tems en la evaluaci�n.rH   ry  z
                INSERT INTO valoraciones_jugador
                (eval_id, player_id, docente_id, area, subescala, indicador, score, observaciones)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            r   rF   T)r   eval_id)rG  rN  r   r	   r9   	token_hexr   r   r}   r[  r@  rD  rA  rB  r;   r\  rC  r   )r0   r  rq   rP  r   r   its          r>   api_valoraciones_jugador_createrS  
  s    		!B ==4UVV"G	 Dkkm-- 		BKK 
 **BtH!3!3!5r||7I7I7KBHH 0 0 6B==?G4			 	 7++ s   AD>CD2%D>2D;7D>)HTTP_403_FORBIDDENHTTP_404_NOT_FOUNDexcc                 R  K   	 t        |       }|j                  t        k(  rt        j                  d| |dt              S |j                  t        k(  rt        j                  d| |dt              S t        d|j                  i|j                        S # t        $ r d }Y w xY ww)Nz404.htmlrS   )rI   z403.htmlrJ   )	rV   r~   rI   rU  rW   rX   rT  r   rJ   )r0   rV  user_ctxs      r>   http_exception_handlerrY  
  s     ( ,,))*'S[6\j|)}}
,,))*'S[6\j|)}}3::.COOLL  s(   B'B BB'B$!B'#B$$B'c                     t               5 }|j                  d| f      j                         }|cd d d        S # 1 sw Y   y xY w)Na  
            SELECT id, created_at, title, body, sender_id, recipient_id, reservation_id
            FROM notifications
            WHERE recipient_id=? AND COALESCE(is_read,0)=0 AND COALESCE(deleted_by_recipient,0)=0
            ORDER BY created_at DESC
        r  )r   r   r   s      r>   r   r     sI    	 D|| 
 Z
 " 	   r   startupc                     	 t               5 } | j                  d       | j                          d d d        t	                	 t               5 } | j                  d       | j                  d       d d d        t                y # 1 sw Y   TxY w# t        $ r Y bw xY w# 1 sw Y   /xY w# t        $ r Y t                y w xY w)Nz
                UPDATE reservations
                SET name = (SELECT name FROM users WHERE users.id = reservations.user_id)
                WHERE (name IS NULL OR name='') AND user_id IS NOT NULL
            zPRAGMA optimize;z PRAGMA wal_checkpoint(TRUNCATE);)r   r}   r   r~   r   r  r   s    r>   
on_startupr]    s    	W 	LL  
 KKM	 IW 	=LL+,LL;<	=
 !	 	  	= 	=  sQ   
B "BB 
B( #B.B( B
B 	BBB%!B( (	B>=B>)r[  NN)rZ  r   )NNN)NNNNN)fastapir   r   r   r   r   r   r	   fastapi.responsesr
   r   r   fastapi.staticfilesr   fastapi.templatingr   starlette.middleware.sessionsr   passlib.contextr   pydanticr   rm   pathlibr  retypingr   r   r   r   r   r   r   r   r   r   r   r  rL   r9   r<   r  r  r  r  urllib.parseurllibr  r   appr/  __file__resolveparentr  r{   getenvro   IS_PRODr:   
SECRET_KEYadd_middlewarer   r?   mountrW   rN   rP   rK   rY   rb   re   rU   rv   r   r   r   r   r;   r   rV  r   r   postr   r   r   r   r   r   r   rP  r   r  r  r  rV   r   r"  r%  r(  r)  r3  r7  r9  r?  r@  rI  rL  rM  rW  rX  r\  UPLOADS_DIRr`  r  r  rC  rj   r  r  r
  r  deleter  r  r   rD  r6  r+  r/  r2  r4  r8  rA  rT  rU  rL  rX  rZ  r   patchr  r  r  r  r  r  r  r  r   passlib.hashr,   r  r~   r  r  r"  r$  r.  r1  r4  r5  r8  r;  r=  r?  rE  r  rH  rK  rG  rN  rS  starlette.exceptionsStarletteHTTPExceptionstarlette.statusrT  rU  exception_handlerrY  r   on_eventr]  r   r@   r>   <module>r}     s=   Q Q Q J J + . ; (    	   ( 1 1 * 8 8   ! !      6 6  !K 7<<!))+22
\
!
"))Iu
%
+
+
-1G
GRYY'(E,AG,A,A",E
   %   H:&A5G 5 5 		)[<8x	 Ho6	L' L#* L 6_ _ 7_ 4%W %
7 g AJ"c "
&D &kC k 
&9: 9:;;; Dj; ;;@S 0C 0 CKGKc #  C $J:=*, 	$\B&G & C& 
:<Xw   Y$ 	%lC!&w !& !& D!&J 	.tDTG TS T ET, 	+,aA+?@bQ34JK"4[ %d!$K@W
@W
@W c]@W 	@W
 SM@W -@WD

 
C 
(' (
 c c    
$+@A  9:&*4jDz'D'D'D Dj'D t	'D
 *'D B'DR \*V V +V ,/V V 0V l3mW m 4m =YW Y >Y (lC48ItTWy Sg Sc S S DS )LAW  B& ~lK]W ] L] 	CTU3   VB :lCn n Dn
 +OLI6:3idSViimnqir S S S Scf S JS* 	#9,W  X !I-	9
$;<X< X< =X<x 	,7  8 	\@RS G   T H KEW  F2 ,/X X 0XJ 	!1,O:' :C$J : P:x d4  $!!	<<}< c]< c]	< 5<|)	 ) /$/#w # # 0#0 !$7w S  8 
"78 S	cS	:!%dDz3D3D3D 3D 	3D
 Dj3D $J3D *3D 93Dj NK	 	 L	 / #Y # 3 c 
 #Y # 
)0HI=# = =QX = J= lE !;+..C=. 3-. F.` 
%,@AOc OJ O O BO. C*3IJJ
K
C*3CDD
EI   	   "y "< '=S&7 S&C S&J[ S& >S&l %d;g7 g g <gZ <  
d3^67 ^6] ^6 4^6F	 	T$Z8  $!!  $M7M7}M7 c]M7 c]	M7
 SMM7 M7 9M7b <0'  14 L1w  2. *\2 S	cddT
JJt*S	9<9<
9< 9< 	9<
 9< 9< 9< 9< 9< 9< 39<x ,/I I 0IV#KI I I3 S T   
l; !IS	 IS	$=$=$= $= 	$=
 $= <$=L y   +DAUYZ]U^ ,
g ,
 ,
d ,
 B,
\ ,&t<AEc\`ad\e / /3 /UY / =/  .<PQg   R" ;MNHMd G   O( 6' 6 6   & % ! ! (Y ( )  
 	$T$Z@KPQT: ' 'S ' A' "48+1G +1c +1 9+1` + * * * 
W 
! ! 
%d;,7 ,EX , <,: I C C-.	M' 	M8N 	M /	Ms  i o
  Ks   :m mm