
SELECT 
RPAD( IF(LENGTH(p.id_number) = 13,COALESCE(p.id_number,''), ''),15,' ') as National_Id,
RPAD( IF(LENGTH(p.id_number) != 13,COALESCE(p.id_number,''), ''),20,' ') as Person_Alternate_Id,
RPAD(l_alt_id.che_lookup,3,' ') as Alternative_Id_Type,
RPAD(l_ethnicity_id.che_lookup,10,' ') as Equity_Code,
RPAD(l_nation_id.che_lookup,3,' ') as Nationality_Code,
RPAD(l_language_id.che_lookup,10,' ') as Home_Language_Code,
RPAD(l_gender_id.che_lookup,1,' ') as Gender_Code,
RPAD(l_permit_type_id.che_lookup,10,' ') as Citizen_Resident_Status_Code,

RPAD('U',2,' ') as Socioeconomic_Status_Code,
RPAD('N',10,' ') as Disability_Status_Code,

RPAD(COALESCE(u.last_name,''),45,' ') as Person_Last_Name,
RPAD(COALESCE(u.first_name,''),26,' ') as Person_First_Name,
RPAD('',50,' ') as Person_Middle_Name,
RPAD(COALESCE(u.title,''),10,' ') as Person_Title,
RPAD(DATE_FORMAT(p.date_of_birth, "%Y%m%d"),8,' ') as Person_Birth_Date,
RPAD(COALESCE(p.physical_address->>'$.city',''),50,' ') as Person_Home_Address_1,
RPAD(COALESCE(p.physical_address->>'$.line_1',''),50,' ') as Person_Home_Address_2,
RPAD(COALESCE(p.physical_address->>'$.line_2',''),50,' ') as Person_Home_Address_3,
RPAD(COALESCE(p.postal_address->>'$.city',''),50,' ') as Person_Postal_Address_1,
RPAD(COALESCE(p.postal_address->>'$.line_1',''),50,' ') as Person_Postal_Address_2,
RPAD(COALESCE(p.postal_address->>'$.line_2',''),50,' ') as Person_Postal_Address_3,
RPAD(COALESCE(p.physical_address->>'$.postal_code',''),4,' ') as Person_Home_Addr_Postal_Code,
RPAD(COALESCE(p.postal_address->>'$.postal_code',''),4,' ') as Person_Postal_Addr_Postal_Code,
RPAD(COALESCE(u.primary_contact_number,''),20,' ') as Person_Phone_Number,
RPAD(COALESCE(u.cellphone_number,''),20,' ') as Person_Cell_Phone_Number,
RPAD('',20,' ') as Person_Fax_Number,
RPAD(COALESCE(u.email,''),50,' ') as Person_Email_Address,

CASE
    WHEN LOWER(p.physical_address->>'$.province') = 'western cape' THEN RPAD(1,2,' ')
        WHEN LOWER(p.physical_address->>'$.province') = 'eastern cape' THEN RPAD(2,2,' ')
        WHEN LOWER(p.physical_address->>'$.province') = 'northern cape' THEN RPAD(3,2,' ')
        WHEN LOWER(p.physical_address->>'$.province') = 'free state' THEN RPAD(4,2,' ')
        WHEN LOWER(p.physical_address->>'$.province') = 'kwazulu natal' THEN RPAD(5,2,' ')
        WHEN LOWER(p.physical_address->>'$.province') = 'kzn' THEN RPAD(5,2,' ')
        WHEN LOWER(p.physical_address->>'$.province') = 'north west' THEN RPAD(6,2,' ')
        WHEN LOWER(p.physical_address->>'$.province') = 'gauteng' THEN RPAD(7,2,' ')
        WHEN LOWER(p.physical_address->>'$.country') = 'mpumalanga' THEN RPAD(2,2,' ')
        WHEN LOWER(p.physical_address->>'$.country') = 'limpopo' THEN RPAD(9,2,' ')
        WHEN LOWER(p.physical_address->>'$.country') = 'south africa' THEN RPAD('N',2,' ')
        WHEN LOWER(p.physical_address->>'$.country') = 'sa' THEN RPAD('N',2,' ')
        WHEN LOWER(p.physical_address->>'$.country') = 'rsa' THEN RPAD('N',2,' ')
    ELSE RPAD('X',2,' ')
END as Province_Code,

RPAD('CHED-13',20,' ') as Provider_Code,
RPAD(621,10,' ') as Provider_Etqa_Id,
RPAD('',45,' ') as Person_Previous_Lastname,
RPAD('',20,' ') as Person_Previous_Alternate_ID,
RPAD('',3,' ') as Person_Previous_Alternative_ID_Type,
RPAD('',20,' ') as Person_Previous_Provider_Code,
RPAD('',10,' ') as Person_Previous_Provider_Etqa_ID,
RPAD('',2,' ') as Seeing_Rating_ID,
RPAD('',2,' ') as Hearing_Rating_Id,
RPAD('',2,' ') as Communicating_Rating_Id,
RPAD('',2,' ') as Walking_Rating_Id,
RPAD('',2,' ') as Remembering_Rating_Id,
RPAD('',2,' ') as Selfcare_Rating_Id,
RPAD(DATE_FORMAT(p.updated_at, "%Y%m%d"),8,' ') as Date_Stamp

FROM users u
LEFT JOIN profiles p
ON u.id = p.user_id
LEFT JOIN lookups l_alt_id On l_alt_id.id = p.identification_type_id
LEFT JOIN lookups l_nation_id On l_nation_id.id = p.nationality_id
LEFT JOIN lookups l_ethnicity_id On l_ethnicity_id.id = p.ethnicity_id
LEFT JOIN lookups l_language_id On l_language_id.id = p.language_id
LEFT JOIN lookups l_gender_id On l_gender_id.id = p.gender_id
LEFT JOIN lookups l_permit_type_id On l_permit_type_id.id = p.permit_type_id

;
