database_schema.sql

-- Esquema do banco de dados para o aplicativo Lujeee no Supabase

-- Tabela de usuários
CREATE TABLE IF NOT EXISTS usuarios (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    nome TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    telefone TEXT UNIQUE NOT NULL,
    numero_contador TEXT UNIQUE NOT NULL,
    senha TEXT NOT NULL,
    tipo TEXT NOT NULL DEFAULT 'pre-pago', -- Tipo de plano: pre-pago ou pos-pago
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Habilitar RLS na tabela de usuários
ALTER TABLE usuarios ENABLE ROW LEVEL SECURITY;

-- Criar políticas RLS para a tabela de usuários
-- Permitir inserções anônimas (para cadastro)
CREATE POLICY "Permitir inserções anônimas" ON usuarios FOR INSERT TO anon WITH CHECK (true);

-- Permitir leitura apenas para o próprio usuário autenticado
CREATE POLICY "Usuários podem ver apenas seus próprios dados" ON usuarios FOR SELECT 
USING (auth.uid() = id OR auth.role() = 'anon');

-- Permitir atualização apenas para o próprio usuário
CREATE POLICY "Usuários podem atualizar apenas seus próprios dados" ON usuarios FOR UPDATE 
USING (auth.uid() = id) WITH CHECK (auth.uid() = id);

-- Permitir exclusão apenas para o próprio usuário
CREATE POLICY "Usuários podem excluir apenas seus próprios dados" ON usuarios FOR DELETE 
USING (auth.uid() = id);

-- Tabela de consumo de energia
CREATE TABLE IF NOT EXISTS consumo_energia (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    usuario_id UUID REFERENCES usuarios(id) ON DELETE CASCADE,
    valor_kwh DECIMAL(10, 2) NOT NULL,
    data_leitura DATE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Índice para melhorar a performance de consultas de consumo por usuário
CREATE INDEX IF NOT EXISTS idx_consumo_usuario ON consumo_energia(usuario_id);

-- Tabela de alarmes de consumo
CREATE TABLE IF NOT EXISTS alarmes_kwh (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    usuario_id UUID REFERENCES usuarios(id) ON DELETE CASCADE,
    ativado BOOLEAN DEFAULT FALSE,
    limite_kwh DECIMAL(10, 2),
    notificacao_app BOOLEAN DEFAULT TRUE,
    notificacao_email BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT unique_alarme_usuario UNIQUE (usuario_id)
);

-- Tabela de notificações de alarme
CREATE TABLE IF NOT EXISTS notificacoes_alarme (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    usuario_id UUID REFERENCES usuarios(id) ON DELETE CASCADE,
    alarme_id UUID REFERENCES alarmes_kwh(id) ON DELETE CASCADE,
    percentual INTEGER NOT NULL, -- 80, 90 ou 100% do limite
    lida BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela de feedback dos usuários
CREATE TABLE IF NOT EXISTS feedback (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    usuario_id UUID REFERENCES usuarios(id) ON DELETE SET NULL,
    titulo TEXT NOT NULL,
    descricao TEXT NOT NULL,
    categoria TEXT NOT NULL, -- Problema, Sugestão, Elogio
    classificacao INTEGER CHECK (classificacao BETWEEN 1 AND 5),
    resolvido BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela de dicas de economia
CREATE TABLE IF NOT EXISTS dicas (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    titulo TEXT NOT NULL,
    descricao TEXT NOT NULL,
    categoria TEXT NOT NULL, -- Iluminação, Eletrodomésticos, etc.
    economia_estimada TEXT,
    imagem_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela de controle de tema
CREATE TABLE IF NOT EXISTS preferencias_usuario (
    usuario_id UUID PRIMARY KEY REFERENCES usuarios(id) ON DELETE CASCADE,
    tema_escuro BOOLEAN DEFAULT FALSE,
    notificacoes_ativadas BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela de sessões (para substituir o auth do Supabase)
CREATE TABLE IF NOT EXISTS sessoes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    usuario_id UUID REFERENCES usuarios(id) ON DELETE CASCADE,
    token TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    CONSTRAINT unique_usuario_sessao UNIQUE (usuario_id, token)
);

-- Função para atualizar o timestamp de updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers para atualizar automaticamente o campo updated_at
CREATE TRIGGER update_usuario_updated_at
BEFORE UPDATE ON usuarios
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_alarmes_updated_at
BEFORE UPDATE ON alarmes_kwh
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_dicas_updated_at
BEFORE UPDATE ON dicas
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_preferencias_updated_at
BEFORE UPDATE ON preferencias_usuario
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Views para relatórios

-- View para consumo mensal por usuário
CREATE OR REPLACE VIEW consumo_mensal AS
SELECT 
    usuario_id,
    DATE_TRUNC('month', data_leitura) AS mes,
    SUM(valor_kwh) AS total_kwh
FROM 
    consumo_energia
GROUP BY 
    usuario_id, DATE_TRUNC('month', data_leitura);

-- View para consumo semanal por usuário
CREATE OR REPLACE VIEW consumo_semanal AS
SELECT 
    usuario_id,
    DATE_TRUNC('week', data_leitura) AS semana,
    SUM(valor_kwh) AS total_kwh
FROM 
    consumo_energia
GROUP BY 
    usuario_id, DATE_TRUNC('week', data_leitura);

-- Habilitar RLS para todas as tabelas
ALTER TABLE consumo_energia ENABLE ROW LEVEL SECURITY;
ALTER TABLE alarmes_kwh ENABLE ROW LEVEL SECURITY;
ALTER TABLE notificacoes_alarme ENABLE ROW LEVEL SECURITY;
ALTER TABLE feedback ENABLE ROW LEVEL SECURITY;
ALTER TABLE preferencias_usuario ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessoes ENABLE ROW LEVEL SECURITY;

-- Função para autenticação personalizada
CREATE OR REPLACE FUNCTION autenticar_usuario(
    p_telefone TEXT,
    p_senha TEXT
) RETURNS TABLE (
    id UUID,
    nome TEXT,
    telefone TEXT,
    token TEXT
) AS $$
DECLARE
    v_user_id UUID;
    v_nome TEXT;
    v_token TEXT;
BEGIN
    -- Obter ID e nome do usuário
    SELECT usuarios.id, usuarios.nome INTO v_user_id, v_nome
    FROM usuarios
    WHERE telefone = p_telefone AND senha = p_senha;
    
    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'Credenciais inválidas';
    END IF;
    
    -- Gerar token
    v_token := encode(gen_random_bytes(32), 'hex');
    
    -- Criar nova sessão
    INSERT INTO sessoes (usuario_id, token, expires_at)
    VALUES (v_user_id, v_token, NOW() + INTERVAL '30 days');
    
    -- Retornar dados do usuário e token
    RETURN QUERY
    SELECT v_user_id, v_nome, p_telefone, v_token;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Função para obter usuário a partir do token
CREATE OR REPLACE FUNCTION obter_usuario_por_token(
    p_token TEXT
) RETURNS UUID AS $$
DECLARE
    v_user_id UUID;
BEGIN
    SELECT usuario_id INTO v_user_id
    FROM sessoes
    WHERE token = p_token AND expires_at > NOW();
    
    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Função para registrar novo usuário
CREATE OR REPLACE FUNCTION registrar_usuario(
    p_nome TEXT,
    p_email TEXT,
    p_telefone TEXT,
    p_numero_contador TEXT,
    p_senha TEXT,
    p_tipo TEXT DEFAULT 'pos-pago'
) RETURNS UUID AS $$
DECLARE
    v_user_id UUID;
BEGIN
    -- Verificar se o telefone já está em uso
    IF EXISTS (SELECT 1 FROM usuarios WHERE telefone = p_telefone) THEN
        RAISE EXCEPTION 'Telefone já cadastrado';
    END IF;
    
    -- Verificar se o contador já está em uso
    IF EXISTS (SELECT 1 FROM usuarios WHERE numero_contador = p_numero_contador) THEN
        RAISE EXCEPTION 'Número do contador já cadastrado';
    END IF;
    
    -- Inserir usuário
    INSERT INTO usuarios (nome, email, telefone, numero_contador, senha, tipo)
    VALUES (p_nome, p_email, p_telefone, p_numero_contador, p_senha, p_tipo)
    RETURNING id INTO v_user_id;
    
    -- Criar preferências padrão
    INSERT INTO preferencias_usuario (usuario_id)
    VALUES (v_user_id);
    
    -- Criar alarme padrão (desativado)
    INSERT INTO alarmes_kwh (usuario_id, ativado, limite_kwh)
    VALUES (v_user_id, FALSE, 300);
    
    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Função para obter relatório de consumo
CREATE OR REPLACE FUNCTION obter_relatorio_consumo(
    p_usuario_id UUID,
    p_tipo TEXT, -- 'diario', 'semanal', 'mensal'
    p_data_inicio DATE,
    p_data_fim DATE
) RETURNS TABLE (
    periodo TEXT,
    consumo DECIMAL
) AS $$
BEGIN
    IF p_tipo = 'diario' THEN
        RETURN QUERY
        SELECT 
            TO_CHAR(data_leitura, 'DD/MM/YYYY') AS periodo,
            valor_kwh AS consumo
        FROM 
            consumo_energia
        WHERE 
            usuario_id = p_usuario_id
            AND data_leitura BETWEEN p_data_inicio AND p_data_fim
        ORDER BY 
            data_leitura;
    ELSIF p_tipo = 'semanal' THEN
        RETURN QUERY
        SELECT 
            TO_CHAR(DATE_TRUNC('week', data_leitura), 'DD/MM/YYYY') AS periodo,
            SUM(valor_kwh) AS consumo
        FROM 
            consumo_energia
        WHERE 
            usuario_id = p_usuario_id
            AND data_leitura BETWEEN p_data_inicio AND p_data_fim
        GROUP BY 
            DATE_TRUNC('week', data_leitura)
        ORDER BY 
            DATE_TRUNC('week', data_leitura);
    ELSIF p_tipo = 'mensal' THEN
        RETURN QUERY
        SELECT 
            TO_CHAR(DATE_TRUNC('month', data_leitura), 'MM/YYYY') AS periodo,
            SUM(valor_kwh) AS consumo
        FROM 
            consumo_energia
        WHERE 
            usuario_id = p_usuario_id
            AND data_leitura BETWEEN p_data_inicio AND p_data_fim
        GROUP BY 
            DATE_TRUNC('month', data_leitura)
        ORDER BY 
            DATE_TRUNC('month', data_leitura);
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;