----------------------- FORMS 1-2A DATA TAGGING ANALYSIS -------------

SELECT 
	bp.id_code AS "IC",
	CASE 
	  WHEN v.is_kbbl IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "BL",	
	
	CASE 
	  WHEN v.is_kbpl IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "PL",	

----------------------------------------------------------------------

	CASE 
	  WHEN v.is_kbbm IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "PM",	

----------------------------------------------------------------------

	v.full_name AS "CITIZEN",
	COALESCE(v.birth_date,'') AS "DOB",	
	t.name AS "TOWN",
	b.name AS "BARANGAY",
	COALESCE(p.name,'') as "PUROK",
	COALESCE(v1.full_name,'') AS "IYANG PUROK LEADER",	
	
----------------------------------------------------------------------

	CASE 
	  WHEN bp.voters_name IS NOT NULL THEN '   ✓'
	  ELSE ''
	END AS "PROFILED",	

----------------------------------------------------------------------

	CASE 
	  WHEN v.is_a IS TRUE THEN '   ✓'
	  WHEN v.is_kbbl IS TRUE THEN '   ✓'
	  WHEN v.is_kbpl IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "A",

	CASE 
	  WHEN v.is_b IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "B",
	
	CASE 
	  WHEN v.is_k IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "K",	

----------------------------------------------------------------------

	CASE 
	  WHEN v.is_inc IS TRUE OR v.is_jehovah IS TRUE OR v.is_out_of_town IS TRUE OR v.is_abroad IS TRUE OR v.is_deceased IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "ABSTAINER",	

	CASE 
	  WHEN v.is_inc IS TRUE THEN '   ✓'
	  ELSE ''	  
	END AS "IGLESIA",

	CASE 
	  WHEN v.is_jehovah IS TRUE THEN '   ✓'
	  ELSE ''
	END AS "JEHOVAH",

	CASE 
	  WHEN v.is_out_of_town IS TRUE THEN '   ✓'
	  ELSE ''	  
	END AS "OUT-OF-TOWN",	

	CASE 
	  WHEN v.is_abroad IS TRUE THEN '   ✓'
	  ELSE ''	  
	END AS "NAG-ABROAD",		
	
	CASE 
	  WHEN v.is_deceased IS TRUE THEN '   ✓'
	  ELSE ''	  
	END AS "DECEASED"

----------------------------------------------------------------------

FROM public.voters v  

----------------------------------------------------------------------

INNER JOIN public.towns t 			ON t.id = v.town_id 
INNER JOIN public.barangays b 		ON b.id = v.barangay_id 
LEFT OUTER JOIN public.purok_names p 		ON p.id = v.purok_name_id	
LEFT OUTER JOIN public.voters v1 			ON v1.origin_id = CAST(v.kbpl_id AS VARCHAR)
LEFT OUTER JOIN public.bagong_pilipinas bp	ON bp.voters_name = v.full_name 

----------------------------------------------------------------------

WHERE SIMILARITY(bp.id_code, 'KWNBU') > 0.5 OR SIMILARITY(v.full_name, '') > 0.5

ORDER BY bp.id_code  
	
----------------------------------------------------------------------

LIMIT 10;