USE [Levelcode-LevelOS]
GO

/* =========================================================
   VIEW: TODOS OS CHAMADOS
========================================================= */
CREATE OR ALTER VIEW VW_Chamados
AS
SELECT
    *
FROM Chamado;
GO

/* =========================================================
   VIEW: CHAMADOS PENDENTES
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosPendentes
AS
SELECT
    *
FROM Chamado
WHERE
    REALIZADO IS NULL
    OR REALIZADO = ''
    OR REALIZADO = 'NAO'
    OR REALIZADO = 'NÃO';
GO

/* =========================================================
   VIEW: CHAMADOS FINALIZADOS
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosFinalizados
AS
SELECT
    *
FROM Chamado
WHERE
    REALIZADO = 'SIM';
GO

/* =========================================================
   VIEW: CHAMADOS PRIORIDADE ALTA
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosPrioridadeAlta
AS
SELECT
    *
FROM Chamado
WHERE
    PRIORIDADE = 'ALTA';
GO

/* =========================================================
   VIEW: CHAMADOS PRIORIDADE MÉDIA
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosPrioridadeMedia
AS
SELECT
    *
FROM Chamado
WHERE
    PRIORIDADE = 'MEDIA'
    OR PRIORIDADE = 'MÉDIA';
GO

/* =========================================================
   VIEW: CHAMADOS PRIORIDADE BAIXA
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosPrioridadeBaixa
AS
SELECT
    *
FROM Chamado
WHERE
    PRIORIDADE = 'BAIXA';
GO

/* =========================================================
   VIEW: CHAMADOS AVULSOS
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosAvulsos
AS
SELECT
    *
FROM Chamado
WHERE
    COD_CLIENTE IS NULL
    OR COD_CLIENTE = '';
GO

/* =========================================================
   VIEW: CHAMADOS COM CLIENTE
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosClientes
AS
SELECT
    *
FROM Chamado
WHERE
    COD_CLIENTE IS NOT NULL
    AND COD_CLIENTE <> '';
GO

/* =========================================================
   VIEW: CHAMADOS DO DIA
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosHoje
AS
SELECT
    *
FROM Chamado
WHERE
    DIA_CHAMADO = CONVERT(VARCHAR, GETDATE(), 103);
GO

/* =========================================================
   VIEW: DASHBOARD DE CHAMADOS
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosDashboard
AS
SELECT
    COUNT(*) AS TOTAL_CHAMADOS,

    SUM(CASE
        WHEN REALIZADO = 'SIM'
        THEN 1
        ELSE 0
    END) AS FINALIZADOS,

    SUM(CASE
        WHEN REALIZADO IS NULL
        OR REALIZADO = ''
        OR REALIZADO = 'NAO'
        OR REALIZADO = 'NÃO'
        THEN 1
        ELSE 0
    END) AS PENDENTES,

    SUM(CASE
        WHEN PRIORIDADE = 'ALTA'
        THEN 1
        ELSE 0
    END) AS PRIORIDADE_ALTA
FROM Chamado;
GO

/* =========================================================
   VIEW: CHAMADOS PARA VISITA
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosVisita
AS
SELECT
    *
FROM Chamado
WHERE
    PARA = 'VISITA';
GO

/* =========================================================
   VIEW: CHAMADOS REMOTOS
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosRemotos
AS
SELECT
    *
FROM Chamado
WHERE
    PARA = 'REMOTO';
GO

/* =========================================================
   VIEW: CHAMADOS COM EMAIL
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosComEmail
AS
SELECT
    *
FROM Chamado
WHERE
    EMAIL IS NOT NULL
    AND EMAIL <> '';
GO

/* =========================================================
   VIEW: CHAMADOS SEM EMAIL
========================================================= */
CREATE OR ALTER VIEW VW_ChamadosSemEmail
AS
SELECT
    *
FROM Chamado
WHERE
    EMAIL IS NULL
    OR EMAIL = '';
GO