-- LOS JUECES DEBEN ESTAR ACTIVOS EN EL EVENTO CORRESPONDIENTE

-- inicializar CertamenTabasco_ronda
update "CertamenTabasco_ronda" set activo = False;
update "CertamenTabasco_ronda" set activo = true where id = 1;

-- vaciamos las calificaiones
delete from "CertamenTabasco_calificacion";

delete from "CertamenTabasco_juez";
delete from "CertamenTabasco_notario";
delete from  "CertamenTabasco_ronda";
delete from "CertamenTabasco_ronda_evento";
delete from "CertamenTabasco_evento";
delete from "CertamenTabasco_rol";

DELETE FROM auth_user WHERE FIRST_NAME = 'juez';


delete from "CertamenTabasco_ronda_criterio";


--select * from "CertamenTabasco_ronda";



insert into "CertamenTabasco_ronda"
select 1 id, now() fecha_registro, 1 usuario_id, True activo, Null ronda_padre_id, 1 descripcion, 1 tipo_ronda_id, 17 participantes_entrada, 10 participantes_salida, 10 puntuacion_maxima union
select 2, now(), 1, False, 1, 2, 1, 10, 5, 10  union
select 3, now(), 1, False, 2, 3, 1, 5, 3,5 union
select 4, now(), 1, False, 3, 4, 1, 3, 1, 1 union
	
select 5, now(), 1, False, 1, 1, 2, 17, 10, 10 union
select 6, now(), 1, False, 2, 2, 2, 10, 5, 10 union
select 7, now(), 1, False, 3, 3, 2, 5, 3, 5 union
select 8, now(), 1, False, 4, 4, 2, 3, 1, 1 union

select 9, now(), 1, False, 4, 5, 1, 1, 1, 1;

insert into "CertamenTabasco_evento"
select 1 as id, now() as fecha_registro, 1 as usuario_id, True as activo, 'Elección Tabasco 2014', '19/03/2014' as fecha, '' as observaciones;


--select * from "CertamenTabasco_ronda_evento";

insert into "CertamenTabasco_ronda_evento"
select 1, 1, 1 union
select 2, 2, 1 union
select 3, 3, 1 union
select 4, 4, 1 union
select 5, 5, 1 union
select 6, 6, 1 union
select 7, 7, 1 union
select 8, 8, 1 union
select 9, 9, 1;






insert into "CertamenTabasco_notario"
select 1 as id, now() as fecha_registro, 1 usario_id, True as activo, 'notario 1' as nombre, 'paterno' as apellido_paterno,'materno' as apellido_materno
, '' as direccion, '' as telefono, '' as correo, now() as fecha_nacimiento, 4 as municipio_id, 1 as evento_id;

--DELETE FROM "CertamenTabasco_calificacion" WHERE RONDA_ID >= 2;
-- iniciamos participantes a primera ronda
update "CertamenTabasco_participante" set ronda_id = 1;







insert into 
auth_user (username, first_name, last_name,email,password,is_staff,is_active, is_superuser,last_login,date_joined)
select 'juez1', 'juez', 'Pre', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez2', 'juez', '2', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez3', 'juez', '3', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez4', 'juez', '4', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez5', 'juez', '5', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez6', 'juez', '6', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez7', 'juez', '7', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez8', 'juez', '8', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez9', 'juez', '9', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION

select 'juez10', 'juez', '10', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 UNION
select 'juez11', 'juez', '11', '', 'juez', false, true, false , last_login,date_joined from auth_user where id= 1 

;
--select * from "CertamenTabasco_evento"
--select * from "CertamenTabasco_juez"
--select * from "CertamenTabasco_rol"



insert into "CertamenTabasco_rol" 
(id, fecha_registro, usuario_id, activo, nombre)
select 1, now(), 1,true, 'Juez Presidente' union
select 2, now(), 1,true, 'jurado 2' union
select 3, now(), 1,true, 'Jurado 3' union
select 4, now(), 1,true, 'Jurado 4' union
select 5, now(), 1,true, 'Jurado 5' union
select 6, now(), 1,true, 'Jurado 6' union
select 7, now(), 1,true, 'Jurado 7' union
select 8, now(), 1,true, 'Jurado 8' union
select 9, now(), 1,true, 'Jurado 9' union
select 10, now(), 1,true, 'Jurado 10' union
select 11, now(), 1,true, 'Jurado 11' union
select 12, now(), 1,true, 'Control' union
select 13, now(), 1,true, 'Maestro de Ceremonia' union
select 14, now(), 1,true, 'Notario' union
select 15, now(), 1,true, 'TVT';


insert into "CertamenTabasco_juez"
(user_ptr_id, ocupacion, evento_id, rol_id, fecha_registro, usuario_id, activo, direccion)
select id, 'juez', 1, 
(
select id from "CertamenTabasco_rol" where nombre like '%'||LAST_NAME||'%'
) 
, '22/04/2013', 1,true,''  from auth_user WHERE FIRST_NAME = 'juez';



update auth_user
set password = 'pbkdf2_sha256$10000$gfsSTSrdCFo2$66DzMHegkzoTWKu/7+zaxAht8Yu8IVekq3Eyw38Xwbo=' -- Tabasco2013 "pbkdf2_sha256$10000$gfsSTSrdCFo2$66DzMHegkzoTWKu/7+zaxAht8Yu8IVekq3Eyw38Xwbo="
where first_name = 'juez';

UPDATE "CertamenTabasco_juez" 
SET ACTIVO = TRUE 
WHERE activo = false;


UPDATE AUTH_USER 
SET IS_ACTIVE = TRUE
WHERE IS_ACTIVE = False;



--select id, ronda_id, criterio_id from  "CertamenTabasco_ronda_criterio";



insert into "CertamenTabasco_ronda_criterio"
select 1, 1, 1 union
select 2, 1, 2 union
select 3, 1, 3 union
select 4, 1, 4 union
select 5, 2, 4 union
select 6, 4, 4 union
select 7, 5, 4 union
select 8, 6, 4 union
select 9, 8, 4 union
select 10, 9, 5 union
select 11, 3, 5 union
select 12, 7, 5;