-- Criar tabela de mensagens no schema gpi
CREATE TABLE IF NOT EXISTS gpi.messages (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    organization_id uuid REFERENCES gpi.organizations(id) ON DELETE CASCADE,
    from_user_id uuid REFERENCES gpi.users(id) ON DELETE SET NULL,
    to_user_id uuid REFERENCES gpi.users(id) ON DELETE SET NULL,
    message text NOT NULL,
    is_read boolean DEFAULT false,
    read_at timestamp with time zone,
    is_archived boolean DEFAULT false,
    is_deleted_by_recipient boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

-- Permissões para as roles do Supabase
GRANT ALL ON gpi.messages TO postgres, anon, authenticated, service_role;