-- obtenerDatosClinica
SELECT
    C.CODIGOCLINICA AS '_id_clinica',
    C.NOMBRECLINICA AS 'Nombre clínica',
    C.TELEFONO1 AS 'Teléfono 1',
    C.TELEFONO2 AS 'Teléfono 2',
    C.EMAIL AS 'Email',
    C.DIRECCION AS 'Calle',
    C.NUMEROINTERIOR AS 'Número interior',
    C.NUMEROEXTERIOR AS 'Número exterior',
    C.COLONIA AS 'Colonia',
    C.CODIGOPOSTAL AS 'Código postal',
    CP.DESCRIPCION AS 'País',
    CE.DESCRIPCION AS 'Estado',
    CC.DESCRIPCION AS 'Ciudad',
    CL.DESCRIPCION AS 'Localidad',
    CASE
        WHEN C.CODIGOCLINICA = 1 THEN 'Matriz'
        ELSE 'Sucursal'
    END AS 'Tipo',
    CASE
        WHEN C.ACTIVO = 1 THEN 'Sí'
        ELSE 'No'
    END AS 'Estatus'
FROM CLINICA C
LEFT JOIN CENTRAL.PAISES CP
    ON CP.ID_PAIS = C.ID_PAIS
LEFT JOIN CENTRAL.ESTADOS CE
    ON CE.ID_ESTADO = C.ID_ESTADO
   AND CE.ID_PAIS = CP.ID_PAIS
LEFT JOIN CENTRAL.CIUDADES CC
    ON CC.ID_CIUDAD = C.ID_CIUDAD
   AND CC.ID_ESTADO = CE.ID_ESTADO
   AND CC.ID_PAIS = CE.ID_PAIS
LEFT JOIN CENTRAL.C_LOCALIDADES CL
    ON CL.ID_LOCALIDAD = C.MUNICIPIO
   AND CL.ID_CIUDAD = CC.ID_CIUDAD
   AND CL.ID_ESTADO = CC.ID_ESTADO
   AND CL.ID_PAIS = CC.ID_PAIS;

-- obtenerListaMedicos
SELECT
    M.CODIGOMEDICO AS '_id_medico',
    CONCAT(COALESCE(M.NOMBRE, ''), ' ', COALESCE(M.APELLIDO_PATERNO, ''), ' ', COALESCE(M.APELLIDO_MATERNO, '')) AS 'Nombre completo',
    CASE
        WHEN M.SEXO = 'M' THEN 'Masculino'
        WHEN M.SEXO = 'F' THEN 'Femenino'
        ELSE M.SEXO
    END AS 'Sexo',
    DATE_FORMAT(M.FECHA_NACIMIENTO, '%d/%m/%Y') AS 'Fecha de nacimiento',
    M.RFC AS 'Rfc',
    M.CURP AS 'Curp',
    M.CALLE_Y_NUMERO AS 'Calle',
    M.COLONIA AS 'Colonia',
    M.CODIGO_POSTAL AS 'Código postal',
    CP.DESCRIPCION AS 'País',
    CE.DESCRIPCION AS 'Estado',
    CC.DESCRIPCION AS 'Ciudad',
    CL.DESCRIPCION AS 'Localidad',
    M.TELEFONO1 AS 'Teléfono 1',
    M.TELEFONO2 AS 'Teléfono 2',
    M.TCELULAR AS 'Celular',
    M.MAIL AS 'Email',
    M.UNIVERSIDAD AS 'Universidad',
    M.CEDULA_PROFECIONAL AS 'Cédula profesional',
    M.ESPECIALIDADES AS 'Especialidades',
    M.OBSERVACIONES AS 'Observaciones',
    CASE
        WHEN M.ACTIVO = 1 THEN 'Sí'
        ELSE 'No'
    END AS 'Estatus'
FROM MEDICOS M
LEFT JOIN CENTRAL.PAISES CP
    ON CP.ID_PAIS = M.PAIS
LEFT JOIN CENTRAL.ESTADOS CE
    ON CE.ID_ESTADO = M.ESTADO
   AND CE.ID_PAIS = CP.ID_PAIS
LEFT JOIN CENTRAL.CIUDADES CC
    ON CC.ID_CIUDAD = M.CIUDAD
   AND CC.ID_ESTADO = CE.ID_ESTADO
   AND CC.ID_PAIS = CE.ID_PAIS
LEFT JOIN CENTRAL.C_LOCALIDADES CL
    ON CL.ID_LOCALIDAD = M.LOCALIDAD
   AND CL.ID_CIUDAD = CC.ID_CIUDAD
   AND CL.ID_ESTADO = CC.ID_ESTADO
   AND CL.ID_PAIS = CC.ID_PAIS;

-- obtenerListaPacientes
SELECT
    P.CODIGOPACIENTE AS '_codigo',
    CONCAT(P.NOMBRE, ' ', P.APELLIDO_PATERNO, ' ', COALESCE(P.APELLIDO_MATERNO, '')) AS 'Paciente',
    CASE P.SEXO WHEN 'M' THEN 'Masculino' WHEN 'F' THEN 'Femenino' ELSE P.SEXO END AS 'Sexo',
    DATE_FORMAT(P.FECHA_NACIMIENTO, '%d/%m/%Y') AS 'Fecha de nacimiento',
    CONCAT_WS(', ', NULLIF(P.TELEFONO1,''), NULLIF(P.TELEFONO2,''), NULLIF(P.TCELULAR,''), NULLIF(P.MAIL,'')) AS 'Contacto',
    CASE WHEN P.ACTIVO = 1 THEN 'Sí' ELSE 'No' END AS 'Activo'
FROM PACIENTE P;

-- obtenerDatosPaciente
SELECT
    P.NOMBRE AS 'Nombre',
    P.APELLIDO_PATERNO AS 'Apellido paterno',
    COALESCE(P.APELLIDO_MATERNO, '') AS 'Apellido materno',
    P.FOTO AS 'Foto',
    DATE_FORMAT(P.FECHAALTA, '%d/%m/%Y') AS 'Fecha de registro',
    DATE_FORMAT(P.FECHA_NACIMIENTO, '%d/%m/%Y') AS 'Fecha de nacimiento',
    CASE P.SEXO WHEN 'M' THEN 'Masculino' WHEN 'F' THEN 'Femenino' ELSE P.SEXO END AS 'Sexo',
    TD.DESCRIPCION AS 'Tipo documento',
    P.DOCUMENTOID AS 'Documento',
    CP.DESCRIPCION AS 'País',
    CE.DESCRIPCION AS 'Estado',
    CC.DESCRIPCION AS 'Municipio',
    CL.DESCRIPCION AS 'Localidad',
    P.CALLE_Y_NUMERO AS 'Calle',
    P.NOEXTERIOR AS 'No. exterior',
    P.NOINTERIOR AS 'No. interior',
    P.COLONIA AS 'Colonia',
    P.CODIGO_POSTAL AS 'Código postal',
    P.NACIONALIDAD AS 'Nacionalidad',
    CONCAT_WS(', ', NULLIF(P.TELEFONO1,''), NULLIF(P.TELEFONO2,''), NULLIF(P.TCELULAR,'')) AS 'Telefonos',
    P.MAIL AS 'Email',
    PA.RECRFC AS 'Rfc',
    PA.RECCODIGOPOSTAL AS 'CP Facturación',
    PA.RECEMAIL AS 'Email facturación',
    OC.NOMBRE AS 'Origen',
    P.RECOMENDADO_POR AS 'Recomendado por',
    CASE
        WHEN P.TIPOPACIENTE = 'P' THEN 'Particular'
        WHEN P.TIPOPACIENTE = 'E' THEN CONCAT_WS(' ','Aseguradora', EMP.NOMBRE, P.NUMEROSEGURO ) ELSE P.TIPOPACIENTE
    END AS 'Tipo de paciente',
    CONCAT_WS(' ', P.RESPONSABLE, P.RECOMENDADO) AS 'Contacto de emergencia',
    P.ALARMA1 AS 'Alerta 1',
    P.ALARMA2 AS 'Alerta 2',
    P.ALERGIAS AS 'Alergias',
    P.HISTORIACLINICA AS 'Notas',
    EC.DESCRIPCION AS 'Estado civil',
    CONCAT(MD.NOMBRE, ' ', MD.APELLIDO_PATERNO, ' ', MD.APELLIDO_MATERNO) AS 'Titular',
    PA.NOMBRECORTO AS 'Nombre corto',
    PA.OCUPACION AS 'Ocupación',
    PA.TIPOSANGRE AS 'Tipo de sangre',
    PA.IDIOMA AS 'Idioma',
    P.OBSERVACIONES AS 'Observaciones'
FROM PACIENTE P
LEFT JOIN PAC_ADICIONALES PA
    ON PA.CODIGOPACIENTE = P.CODIGOPACIENTE
LEFT JOIN CENTRAL.C_ESTADO_CIVIL EC
    ON EC.CODIGO = PA.ESTADOCIVIL
LEFT JOIN C_TIPOS_DOCUMENTOS TD
    ON TD.ID_TIPO_DOCUMENTO = P.ID_TIPO_DOCUMENTO
LEFT JOIN CENTRAL.PAISES CP
    ON CP.ID_PAIS = P.id_pais
LEFT JOIN CENTRAL.ESTADOS CE
    ON CE.ID_PAIS = P.id_pais
   AND CE.ID_ESTADO = P.id_estado
LEFT JOIN CENTRAL.CIUDADES CC
    ON CC.ID_PAIS = P.id_pais
   AND CC.ID_ESTADO = P.id_estado
   AND CC.ID_CIUDAD = P.id_ciudad
LEFT JOIN CENTRAL.C_LOCALIDADES CL
    ON CL.ID_PAIS = P.id_pais
   AND CL.ID_ESTADO = P.id_estado
   AND CL.ID_CIUDAD = P.id_ciudad
   AND CL.ID_LOCALIDAD = P.id_localidad
LEFT JOIN RECOMENDADOR OC
    ON OC.CODIGO = P.RECOMENDADOR
LEFT JOIN C_EMPRESA EMP
    ON EMP.CODIGOEMPRESA = P.CODIGOEMPRESA
LEFT JOIN MEDICOS MD
    ON MD.CODIGOMEDICO = PA.CODIGOMEDICO
WHERE P.CODIGOPACIENTE = ?;